How to convert the "Execute immediate .. into .." From Oracle to Teradata

Database
Enthusiast

How to convert the "Execute immediate .. into .." From Oracle to Teradata

Hi :

      I have a question about the procedure.During the O2T ,there are some SQL just like

     Execute immediate 'select count(*) from  deom' into v_count;

     I know in the Teradata's procedure  can support the dynamitic sql with  DBC.SYSEXECSQL or Execute immediate.

     But it seemed that kind of SQL can not covert to Teradata's . 

6 REPLIES
Junior Contributor

Re: How to convert the "Execute immediate .. into .." From Oracle to Teradata

SELECT INTO is not supported in Dynamic SQL.

As a workaround you have to use a cursor:

DECLARE SqlStr VARCHAR(500);
DECLARE c CURSOR FOR s;
SET SqlStr = 'select count(*) from deom;';
PREPARE s FROM SqlStr;
OPEN c;
FETCH c INTO v_count;
CLOSE c;

Dieter

Enthusiast

Re: How to convert the "Execute immediate .. into .." From Oracle to Teradata

Hi dieter:

     Thank you for your answer, there is a example in the document like this. But it seemed can not

compile in the procedure _

CREATE PROCEDURE  DEMO_DYNA(OUT OUT_PUT INT)

BEGIN

DECLARE sqlstr VARCHAR(300);

DECLARE V_COUNT INT;

DECLARE C CURSOR FOR S;

SET sqlstr='SELECT COUNT(*) FROM DBC.TABLES;';

PREPARE S FROM sqlstr;

OPEN C ;

FETCH C INTO V_COUNT;

SET OUT_PUT=V_COUNT;

CLOSE C;

END;

------------------------------------------------------------------

SPL5009:E(L6), Dynamic cursor 'C' is not allowed with WITHOUT RETURN, WITH RETURN TO CALLER/CLIENT.

SPL5004:E(L8), Referring to undefined statement 'S'.

SPL1030:E(L9), Referring to undefined cursor 'C'.

SPL1030:E(L12), Referring to undefined cursor 'C'.

Junior Contributor

Re: How to convert the "Execute immediate .. into .." From Oracle to Teradata

Your example compiles as-is on TD13+, what's your release?

Dieter

Enthusiast

Re: How to convert the "Execute immediate .. into .." From Oracle to Teradata

Hi Dieter:

   You are right . The version 13.0, I will try it on the 13.1 .Thank you very much!!

Junior Contributor

Re: How to convert the "Execute immediate .. into .." From Oracle to Teradata

What's the exact release as returned by "select * from dbc.dbcinfoV;"?

TD13+ includes 13.0, i was running it on a 13.00.00.22 and a TD14, but no 13.10

Dieter

Enthusiast

Re: How to convert the "Execute immediate .. into .." From Oracle to Teradata

Thank you Dieter,I will research it , Have a good weekend!