Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
PL/SQL - How to execute queries generated within a query?
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
-
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?
-
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.
-
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.
-
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
-
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.
-
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;
-
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.
