All Forums Database
tekriewa 8 posts Joined 04/13
14 Jul 2014
Stored procedure conversion from Oracle to Teradata

Hello,
I have a stored procedure that was written for Oracle.  I am curious how to convert it to a Teradata-friendly format:
 
create procedure RegionSelect (cur_OUT OUT PKGENTLIB_ARCHITECTURE.CURENTLIB_ARCHITECTURE) as
  BEGIN OPEN cur_OUT FOR select * from Region Order By RegionId; END;
 
Can someone advise?
Thanks!

dnoeth 4628 posts Joined 11/04
15 Jul 2014

What is this SP supposed to do?
Simply returning a result set, then it's:

CREATE PROCEDURE RegionSelect ()
DYNAMIC RESULT SETS 1
BEGIN
   DECLARE cur_OUT CURSOR WITH RETURN ONLY FOR
   select * from Region Order By RegionId;
   OPEN cur1;
END;

 

Dieter

tekriewa 8 posts Joined 04/13
15 Jul 2014

Yes, it simply returns a result set.  Thank you for the guidance; I was in particular unclear how to handle the out parameter but this helps to clarify.

gopinadhs 18 posts Joined 10/11
08 Aug 2014

Dieter,
I have a quick question on Stored procedures in Teradata.
Will a Stored procedure uses --teradata parallelism architecture?. How ?.
Will a Stored procedure uses multiple SESSIONS ?. If yes, how do you define a session limit ?.
I believe it is always be a single session window ?.
I heard SP slow down the ETL batch window ?.

dnoeth 4628 posts Joined 11/04
08 Aug 2014

A Stored Procedure in Teradata is rewritten as a C program and then compiled into a shared object on Linux. When you CALL the SP it's actually calling an exported function of that shared object.
Any SP specific syntax like WHILE will run on a single PE in a single thread (and use a single session), but the SELECTs/UPDATEs/etc. submitted by that SP will run in parallel like any other SQL.
So the SQL itself will not be faster or slower, but stupid usage of LOOPs or cursors result in serial processing and such can be slower than set-based processing.

Dieter

09 Aug 2014

Dieter ,
Can you please provide more info. on below stmt.
**When you CALL the SP it's actually calling an exported function of that shared object **
 
While calling exported function of that shared object , how many sessions will get allocated , where that info maintained .
Will it get change in every TD relaese.?

dnoeth 4628 posts Joined 11/04
09 Aug 2014

There's no new session assigned, it's within the calling user's session.

Dieter

You must sign in to leave a comment.