Oracle Apex - How to execute query generated from a query?

Jasper Tanglib

In Oracle Apex 20.2,

I have a query that generates a set of queries. Please refer to screenshot below.

How do I execute these newly generated queries in PLSQL?

Any idea or suggestion is appreciated.

-Jazz

msammour

If I understand it correctly, you need to run the insert statement in the screenshot?

You can use EXECUTE IMMEDIATE

https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems017.htm

Note: it is not recommended to use it in your application if there is a bind variables in the PLSQL you passed for this statement.

jariola

Please explain what is purpose of this. What you mean by "PLSQL"?

You can copy row from result and execute it same way in SQL Workshop where you have run your original query, if that is what you asking.

msammour

If I understand it correctly, you need to run the insert statement in the screenshot?

You can use EXECUTE IMMEDIATE

https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems017.htm

Note: it is not recommended to use it in your application if there is a bind variables in the PLSQL you passed for this statement.

Jasper Tanglib

Ah right sorry it was not detailed.

I have an app in apex where I want to add a Process using PLSQL that runs after clicking an Upload button.

The code above returns several insert queries I want to execute during the Process. I am adding that code into PLSQL and now it looks like this:

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

    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

        into l_insert

    FROM cols;

    dbms_output.put_line(l_insert);

    return;

END;

Now that it is in PLSQL, I have there the "into l_insert" but this returns ORA-01422: exact fetch returns more than requested number of rows. I think this error is caused by the multiple results it queries but I need to run all those queries and not only one.

jariola

Loop records returned from query

https://docs.oracle.com/database/121/LNPLS/for_loop_statement.htm#LNPLS1515

Jasper Tanglib

https://community.oracle.com/tech/developers/discussion/comment/16790879#Comment_16790879

Thank you @msammour and @jariola !

I was in the middle of trying a FOR LOOP until I tried to search for this EXECUTE IMMEDIATE. I found the exact solution from http://www.dba-oracle.com/t_oracle_execute_immediate.htm

Thanks so much!

-Jazz