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 seen several examples in google but have not seen a solution to my problem.
Any idea or suggestion is appreciated.
-Jazz