All Forums Database
acire818 3 posts Joined 05/13
16 May 2013
Returning a Results from Stored Procedure

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?

dnoeth 4628 posts Joined 11/04
17 May 2013

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
begin
   ...
   declare rslt cursor with return only to client for
   select .....;
   open rslt; -- don't close it!!

 
Dieter
 
 
 

Dieter

acire818 3 posts Joined 05/13
17 May 2013

Thanks so much.  That gave me the results I was looking for.

Michaelcanady 11 posts Joined 05/13
29 May 2013

newbie here joined to groom my knowledge and also hope to enjoy my stay here

..

HanC 7 posts Joined 07/14
07 Jul 2016
replace PROCEDURE dbschema.sp_test2  
(
 IN "SRC_DB_NM" VARCHAR(30)
, In "SRC_TBL_NM" VARCHAR(30)
)    
DYNAMIC RESULT SETS 1
BEGIN       
DECLARE cur1 CURSOR WITH RETURN ONLY TO CLIENT FOR 
 'select * from ' || SRC_DB_NM || '.' || SRC_TBL_NM || ';';    
  OPEN cur1;    
END;

Hi Dieter,
How can I make the above dynamic sql work please?
Thanks,
HC
 
 

You must sign in to leave a comment.