Returning a Results from Stored Procedure

Database

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?

Tags (1)
8 REPLIES 8
Ambassador

Re: Returning a Results from Stored Procedure

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

 

 

Re: Returning a Results from Stored Procedure

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

Enthusiast

Re: Returning a Results from Stored Procedure

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

Enthusiast

Re: Returning a Results from Stored Procedure

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

Enthusiast

Re: Returning a Results from Stored Procedure

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?

Ambassador

Re: Returning a Results from 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 :-)

 

 

Enthusiast

Re: Returning a Results from Stored Procedure

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.

Highlighted
Ambassador

Re: Returning a Results from Stored Procedure

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;
...