Stored procedure conversion from Oracle to Teradata

Database
Enthusiast

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!

Tags (1)
6 REPLIES
Senior Apprentice

Re: Stored procedure conversion from Oracle to Teradata

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

Re: Stored procedure conversion from Oracle to Teradata

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.

Enthusiast

Re: Stored procedure conversion from Oracle to Teradata

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

Senior Apprentice

Re: Stored procedure conversion from Oracle to Teradata

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.

Enthusiast

Re: Stored procedure conversion from Oracle to Teradata

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

Senior Apprentice

Re: Stored procedure conversion from Oracle to Teradata

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