I am new to Teradata and I am trying to convert and Oracle stored procedure. I have been successful at getting the procedure to run but I need to know how to get the correct results from the results set. In our Oracle procedure we run a set of queries to insert data into a table and then sent the results from that table in a ref cursor. How can we do the same in Teradata? I have gotten as far as running the procedure and and inserts the data into the tables. Does anyone know if teradata has the equivalent of a ref cursor as in Oracle?
Do you actually need to access the result several times?
Then you should materialize it in a Global Temporary Table.
When you only want to return it to the client, it's
replace procedure xxx (...)
dynamic result sets 1
declare rslt cursor with return only to client for
open rslt; -- don't close it!!
replace PROCEDURE dbschema.sp_test2
IN "SRC_DB_NM" VARCHAR(30)
, In "SRC_TBL_NM" VARCHAR(30)
DYNAMIC RESULT SETS 1
DECLARE cur1 CURSOR WITH RETURN ONLY TO CLIENT FOR
'select * from ' || SRC_DB_NM || '.' || SRC_TBL_NM || ';';
How can I make the above dynamic sql work please?