Forum Stats

  • 3,767,857 Users
  • 2,252,726 Discussions
  • 7,874,366 Comments

Discussions

PL/SQL - How to execute queries generated within a query?

Jasper Tanglib
Jasper Tanglib Member Posts: 67 Green Ribbon
edited Apr 4, 2021 1:45PM in SQL & PL/SQL

Hi,

I have this code in Oracle Apex that generates multiple queries.

I want to execute the Insert queries generated as seen in the above screenshot. Now my code looks like this. please refer to code below.

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 q'[insert into ALL_PORT_OBJS (loadid,xtid,xt,name) SELECT 150 , 'xtid' , 'xt_xml', name FROM xt_xml]' metasql from cols}';

    EXECUTE IMMEDIATE l_insert;

END;

This returns Statement processed but it actually did not execute the insert statements.

I think there is something else I need to do in the "select q'[insert into..." section?

Please help I am stucked on this. Any idea or suggestion is appreciated.


-Jazz

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond
    edited Apr 4, 2021 2:13PM

    Hi, @Jasper Tanglib

    Whenever you write dynamic SQL, display the SQL statement before you execute it. For example

    ...
    dbms_output.put_line (l_insert || '= l_insert before EXECUTE IMMEDIATE')
    EXECUTE IMMEDIATE  l_insert;
    ...
    

    You can comment-out the call to put_line when you finish debugging.

    In this case, you'll see that l_insert is not an INSERT statement; it's just a SELECT statment. You need to save the output from that SELECT statement into a variable, and then execute that.

    Do you really need tp do dynamic SQL here? Why can't you do what yu need with an INSERT (or MERGE) statement?

  • Jasper Tanglib
    Jasper Tanglib Member Posts: 67 Green Ribbon
    edited Apr 4, 2021 2:38PM

    Hi Frank,

    Like this? added INTO l_insert

    DECLARE 

        l_insert varchar2(4000); 

    BEGIN

        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

        INTO l_insert

        WHERE table_name LIKE 'XT%'

        )

        SELECT 'insert into ALL_PORT_OBJS (loadid,xtid,xt,name) SELECT 150 , 'xtid' , 'xt_xml', name FROM xt_xml'  FROM cols;


        EXECUTE IMMEDIATE  l_insert;

    END;


    Yes I need to do dynamic SQL. I actually just simplified the code for discussion purposes here. I just wanted to find out how to execute these queries being generated before I revert it back to the original code.

  • Jasper Tanglib
    Jasper Tanglib Member Posts: 67 Green Ribbon

    Hi Frank,

    Would it be like this? I added INTO l_insert. Please refer to code below

    DECLARE 

        l_insert varchar2(4000); 

    BEGIN

        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

        INTO l_insert

        WHERE table_name LIKE 'XT%'

        )

        SELECT 'insert into ALL_PORT_OBJS (loadid,xtid,xt,name) SELECT 150 , 'xtid' , 'xt_xml', name FROM xt_xml'  FROM cols;


        EXECUTE IMMEDIATE  l_insert;

    END;


    Yes I needed to have it as a dynamic SQL because this process will generate queries depending on earlier codes that will run. I actually simplified the code here for discussion purposes. I wanted to find out how to execute queries being generated first before I revert it back to its original code.

  • Jasper Tanglib
    Jasper Tanglib Member Posts: 67 Green Ribbon

    I basically have to substitute this script into PL/SQL. The code will be inserted into spool insert_from_xts.sql which then gets executed afterwards in @insert_from_xts.sql.

    spool insert_from_xts.sql

    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 &&loadid. , ''&&xtid.'' , '''||lower(table_name)||''','||column_list

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

     FROM cols;

    spool off

    @insert_from_xts.sql

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond

    Hi,

    I actually simplified the code here for discussion purposes. 

    Are you saying the question you posted doesn't show what you really want to do? Then explain what you really want to do. Post CREATE TABLE and INSERT statements for tables as they exist before the INSERT (some of the tables may not have any rows at that point), and the exact results you want from that sample data (that is, the contents of the changed table(s) after all the INSERTs are finished). Explain how you get those results from taht data.

    You don't need to include all tables and all columns: two or three tables with two or three columns each should be enough.

  • Jasper Tanglib
    Jasper Tanglib Member Posts: 67 Green Ribbon
    edited Apr 5, 2021 4:18AM

    No the question I posted is exactly what I want to do.

    you'll see that l_insert is not an INSERT statement; it's just a SELECT statment. You need to save the output from that SELECT statement into a variable, and then execute that.

    This part here you mentioned did make a lot of sense and I agree with it. Now I am trying to do just that but I am not sure how to do it either as I am still new to PL/SQL, and I can't seem to find an answer from all the examples I came across in my googling.

    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.

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

        FROM cols}';

        EXECUTE IMMEDIATE  l_insert;

    END;

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond

    Hi,

    Sorry, it's not clear what you want to do. What is the big picture here? You you want to execute a variable number (say, 51) of INSERT statements, or do you want to create a .sql file that has those 51 INSERT statements? Do you really need to use PL/SQL, or would you be content using just SQL and SQL*Plus features such as SPOOL?

    In any case, post CREATE TABLE and INSERT statements for a little sample data for all_port_objs and a couple of other tables (maybe xt1, xt2 and xt3) as they exist before the INSERTs. (All_port_objs may not have any rows at this point.) All of the tables except all_port_obj should have only 2 or 3 columns. Show what all_port_objs should look like after all the inserts are completed, and say whether you actually want to INSERTs, or just generate a .sql file that will do the INSERTs.