Forum Stats

  • 3,728,681 Users
  • 2,245,675 Discussions
  • 7,853,700 Comments

Discussions

PL/SQL - How to save a query's output into a variable and execute it?

Jasper Tanglib
Jasper Tanglib Member Posts: 53 Green Ribbon

Hi,

I am creating a Process in Oracle Apex 20.2 that will run when a user clicks the Upload button.

The following code below generates 51 INSERT query statements. I need to execute all of those 51 INSERT query statements. Please help me on how I can save the output(51 insert statements) from the SELECT statement and assign it into a variable, and then execute it.

WITH cols as (

    SELECT drv.table_name,

        (SELECT listagg(column_name,',') WITHIN GROUP (ORDER BY column_id)

            FROM user_tab_cols

            where table_name = drv.table_name

        ) column_list

    FROM user_tables drv

    where table_name like 'XT%'

    )

    select 'insert into ALL_PORT_OBJS (loadid,xtid,xt,'||column_list||') SELECT 150 , ''&&xtid.'' , '''||lower(table_name)||''','||column_list

        ||' FROM '|| table_name ||';' metasql

    FROM cols

Result:


I have have tried the following code below but found out this only runs as a SELECT statement thus, it does not execute the generated INSERT statements.

DECLARE 

l_insert varchar2(4000); 

BEGIN

     l_insert := q'{WITH cols as (

    SELECT drv.table_name,

        (SELECT listagg(column_name,',') WITHIN GROUP (ORDER BY column_id)

            FROM user_tab_cols

            where table_name = drv.table_name

        ) column_list

    FROM user_tables drv

    where table_name like 'XT%'

    )

    select 'insert into ALL_PORT_OBJS (loadid,xtid,xt,'||column_list||') SELECT 150 , ''&&xtid.'' , '''||lower(table_name)||''','||column_list

        ||' FROM '|| table_name ||';' metasql

    FROM cols}';

    EXECUTE IMMEDIATE  l_insert;

END;

Result:


I am still new to PL/SQL and have searched several examples but have not seen a solution to my problem.

Any idea or suggestion is appreciated.


-Jazz

Best Answer

  • msammour
    msammour Member Posts: 31 Blue Ribbon
    Accepted Answer

    Hello Jazz,


    This best approach is to use FOR LOOP with EXECUTE IMMEDIATE:

    Pseudo Code:

    FOR I IN (Your select statement that returns the INSERTs) LOOP

    EXECUTE IMMEDIATE I.STATEMENT;

    END LOOP;

    In this way you can make sure every DML command will be executed successfully.

Answers

  • msammour
    msammour Member Posts: 31 Blue Ribbon
    Accepted Answer

    Hello Jazz,


    This best approach is to use FOR LOOP with EXECUTE IMMEDIATE:

    Pseudo Code:

    FOR I IN (Your select statement that returns the INSERTs) LOOP

    EXECUTE IMMEDIATE I.STATEMENT;

    END LOOP;

    In this way you can make sure every DML command will be executed successfully.

  • Jasper Tanglib
    Jasper Tanglib Member Posts: 53 Green Ribbon

    Hi msammour,

    I'll try that.

    EXECUTE IMMEDIATE I.STATEMENT;

    What is the STATEMENT here referring to?

  • msammour
    msammour Member Posts: 31 Blue Ribbon

    That is the column from your query that holds the insert statement.

  • Jasper Tanglib
    Jasper Tanglib Member Posts: 53 Green Ribbon

    Hi msammour,

    I tried the format based on the pseudocode you provided. I made sure the query inside For i IN(query) has the correct syntax but it still returned this error.


  • Jasper Tanglib
    Jasper Tanglib Member Posts: 53 Green Ribbon

    Oh I tried this simpler INSERT statement and it worked as it returned Statement processed and I checked my table if it really received the data and it did! Which means the other one above I just posted with SQL command not properly ended must have a wrong syntax in it.

    Thanks so much msammour! Now I just have to find out the correct syntax I should have inside my For i IN(query)

Sign In or Register to comment.