Forum Stats

  • 3,768,984 Users
  • 2,252,889 Discussions
  • 7,874,824 Comments

Discussions

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

Jasper Tanglib
Jasper Tanglib Member Posts: 67 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: 59 Bronze Badge
    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