Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

Jasper TanglibApr 4 2021 — edited Apr 4 2021

Hi,
I have this code in Oracle Apex that generates multiple queries.
generate_queries.pngI 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

Comments

Frank Kulash

Hi, [Jasper Tanglib](/ords/forums/user/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

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

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

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

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

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

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.

1 - 7

Post Details

Added on Apr 4 2021
7 comments
2,224 views