How to get result from output of execute immediate statement

Question

The procedue is only executing the below output but I am trying to get result from output of execute immediate statement.

Current OutPut :

PL/SQL procedure successfully completed.

select 'PRJA' AS "PRJ_ID", EVENT, email,modified_by,modified from PRJA.TableX UNION ALL 
select 'PRJB' AS "PRJ_ID", EVENT, email,modified_by,modified from PRJB.TableX UNION ALL 
select 'PRJC' AS "PRJ_ID", EVENT, email,modified_by,modified from PRJC.TableX UNION ALL 
select 'PRJD' AS "PRJ_ID", EVENT, email,modified_by,modified from PRJD.TableX;

I am expecting the result above select / output :

SET SERVEROUTPUT ON

Declare
TYPE T IS TABLE OF MYTABLE.ID%TYPE INDEX BY PLS_INTEGER;
MYROW T;
v_sql varchar2(500);
v_sql2 varchar2(500);
v_prj_id varchar2(4000):='PRJA,PRJB,PRJC,PRJD';

BEGIN

 FOR i IN (SELECT trim(regexp_substr(v_prj_id, '[^,]+', 1, LEVEL)) l
         FROM dual 
         CONNECT BY LEVEL <= regexp_count(v_prj_id, ',') + 1 
 ) LOOP


   v_sql :=  v_sql || 'select '''|| i.l ||''' AS "PRJ_ID", EVENT, email,modified_by,modified from ' 
  || i.l || '.TableX UNION ALL ' || chr(10) ;

  END LOOP;

   v_sql2 :=  RTRIM(v_sql, 'UNION ALL ' || chr(10) ) || ';';

   EXECUTE IMMEDIATE v_sql2 BULK COLLECT INTO MYROW;
   DBMS_OUTPUT.PUT_LINE(MYROW.XXX);
   END;
      /

try to use v_sql2 := RTRIM(v_sql, 'UNION ALL ' || chr(10) ) || ';'; EXECUTE IMMEDIATE v_sql2 BULK COLLECT INTO MYROW; DBMS_OUTPUT.PUT_LINE(MYROW.XXX); inside for loop because you want to print the result of this select query and not the select query itself... — Marcus
– Marcus, Commented Nov 15, 2019 at 9:51

APC · Accepted Answer · 2019-11-15 10:35:22Z

Your code has two glaring problems:

The projection of the target collection doesn't match the projection of the dynamic query. They must have the same number (and datatype) of columns.
The dbms_output statement will fail because MYROW is a collection, and put_line() only takes scalar values.

There is also the clumsiness of the loop which assembles the query. We can use PL/SQL collections to make it tidier.

declare
  -- record type to match projection of required output
  type r is record (
   prj_id        varchar2(30)
   , event       PRJA.TableX.event%type
   , email       PRJA.TableX.email%type
   , modified_by PRJA.TableX.modified_by%type
   , modified    PRJA.TableX.modified%type
  );

  TYPE T IS TABLE OF R;
  MYROWS T; -- plural because it's a table not a record variable

  v_sql       varchar2(32767);

  -- collection of schemas to query ...
  v_prj_ids sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll('PRJA','PRJB','PRJC','PRJD');

BEGIN

  FOR i IN 1 .. v_prj_ids.count() LOOP

  if i > 1 then
    v_sql := v_sql || chr(10) || ' UNION ALL ' || chr(10)
  end if

  v_sql :=  v_sql || 'select '''|| v_prj_ids(i) ||''' AS "PRJ_ID", EVENT, email,modified_by,modified from ' 
  || v_prj_ids(i) || '.TableX ' ;

  END LOOP;

  EXECUTE IMMEDIATE v_sql2 BULK COLLECT INTO MYROWS;

  DBMS_OUTPUT.PUT_LINE(' number of records =' || MYROWS.count());

END;
/

Caution: not tested because I don't have access to a multi-user environment

Popeye · Accepted Answer · 2019-11-15 10:16:35Z

Your variable MYROW is of type T which is TABLE OF MYTABLE.ID%TYPE so It will be able to hold only one value per record of data type which is the same as the data type of MYTABLE.ID.

In your code, you are fetching "PRJ_ID", EVENT, email, modified_by, modified (5) columns and trying to assign it to MYROW which can not hold this much of column.

You must remove all the columns except "PRJ_ID" from your select clause if you are only interested in the PRJ_ID field.

something like:

V_SQL := V_SQL
         || 'select ''' || I.L
         || ''' AS "PRJ_ID" ' -- , EVENT, email,modified_by,modified from
         || I.L || '.TableX UNION ALL '|| CHR(10);

If you want all the data then you need to create the object which can hold the proper attributes. see this forum for implementing a table of an object.

Cheers!!

Collectives™ on Stack Overflow

How to get result from output of execute immediate statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related