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?
Dieter or other esteemed Teradata expert,
The solution shown by DIeter works great for me. However, now I find that i will need to use global temp tables. However, for some reason the global temp table is not getting filled:
First, creating the temp table...
CREATE MULTISET GLOBAL TEMPORARY TABLE DLAB_CIO.AATestTemp as DLAB_CIO.NSC_MESSAGING with no data;
Then attempting to fill it from another table that has data, then return that data:
replace procedure DLAB_CIO.AATestTabproc2 ()dynamic result sets 1 begin declare rslt cursor with return only to client for select * from DLAB_CIO.AATestTemp; insert into DLAB_CIO.AATestTemp select * from DLAB_CIO.NSC_MESSAGING; open rslt; -- don't close it!! end;
For some (probably dumb) reason, when call the SP, the data is not getting into my temp table... no errors, just no rows (should be a couple thousand rows). I know i cannot use a commit after the insert... In general, what are my options for filling and sending back the contents of a temporary table in a stored procedure?
Well the data actually gets into your table, but you forgot to add ON COMMIT PRESERVE ROWS and your GTT was created with the default ON COMMIT DELETE ROWS.
Now you run your Insert/Select and the last step in Explain show an END TRANSACTION step :-)
As always thanks Dieter, of course it worked. I would have thought that within the execution of the SP, it was the same session, so no commit/preserve would be needed for the data to be visible within that session. But I get it now.
The request within an SP are part of the calling session, if it's an ANSI session you wouldn't need PRESERVE ROWS.
In a Teradata session you might be able to use BT/ET, but I'm not sure because of the cursor:
BT; insert into DLAB_CIO.AATestTemp select * from DLAB_CIO.NSC_MESSAGING; open rslt; -- don't close it!! ET;