Put the query result of "EXECUTE IMMEDIATE" command in a local table

[Former Member]

Hi all.

Is it possible to put the output of the "EXECUTE IMMEDIATE" command in a local table so that the ouput can be accessed through other procedures.

Regards,

Andila

[former_member182302]

Hi Andila,

You can do that as mentioned by peter in the above reply.

But why do you want to persist the information into a Table?

Also would want to understand if you are having a Varied "select column" list and using EXECUTE IMMEDIATE for the same?

if not you can use "EXEC" and then use a local temporary table to hold the result which returns the value to your Table Type which can be further utilized by other procedures calling this procedure.

Can you be more clear on the requirement on why you want to do this?

Regards,

Krishna Tangudu

[Former Member]

Hi Krishna.

There is one column in the query which will be passed as input.

I tried using EXEC also to store the results in a local table but could not succeed.

Can you let me know the syntax to store the result in a local table by using EXEC command.

Regards,

Andila

[former_member182302]

Hi Andila,

Have just updated you the sample code above. Have a look and let me know if you got any doubts.

Regards,

Krishna Tangudu

[Former Member]

Hi Krishna.

Just saw it. Will try it and update. Thanks.

Regards,

Andila

[Former Member]

Hi Krishna.

I do not have the authorization to use "create local temporary table" in a procedure.

It is working in SQL Console.

Regards,

Andila

[former_member182302]

Ok are you creating through .hdbprocedure?

Then you have to enable some settings as mentioned in this thread

Then define a procedure with "WRITE" mode and then you can use Temporary Table. Also have a look on this thread where patrick faced a similar issue of authorization because he didnt grant access to the schema he is working on to _SYS_REPO user

Regards,

Krishna Tangudu

[Former Member]

No, I am not creating through .hdbprocedure.

Regards,

Andila

[former_member182302]

Can you be clear on way you are creating the procedure?

Regards,

Krishna Tangudu

[former_member187673]

Hi Andila, well you could just make your dynamic sql statement an insert statement based on your select. See example below

create column table test_table_1

(

"COL1" nvarchar(10),

"COL2" nvarchar(10)

);

CREATE PROCEDURE INSERT_P() 

LANGUAGE SQLSCRIPT AS 

  sql_string NVARCHAR(2000) := '';

BEGIN 

  sql_string := 'insert into test_table_1 (select ''val1'', ''val2'' from dummy) ';

  EXECUTE IMMEDIATE (:sql_string); 

END; 

call insert_p();

select * from test_table_1;

However you may want to investigate other options instead of using dynamic SQL as this is not a recommended approach. Less optimized compared to standard sql.

Peter

[Former Member]

Thanks Peter for the reply . I wanted to store the result in a local table instead of a physical table.

Regards,

Andila

[former_member182302]

Hi Andila,

You can use Local Temporary table and store your result there by using EXEC or even EXECUTE IMMEDIATE

Which can be further captured via Table Type.

I would say you can go for EXEC.

Sample Code below:

DROP PROCEDURE SAMPLE_PROC;
CREATE PROCEDURE TEMPTABLE(OUT OUTTAB TT_EMPLOYEE)
AS
BEGIN
DECLARE QUERY VARCHAR (200);
CREATE LOCAL TEMPORARY TABLE #TEMPTABLE ( "ID" INTEGER, "NAME" VARCHAR(30));
SQL_QUERY := 'insert into #TEMPTABLE (Select ID,NAME from EMPLOYEE )';
EXEC (:QUERY);
OUTTAB = SELECT * FROM #TEMPTABLE;
DROP TABLE #TEMPTABLE;
END;

Regards,

Krishna Tangudu