CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sql
Ganesan VC
Ganesan VC

Reputation: 53

How to get result from output of execute immediate statement

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;
      /

Upvotes: 2

Views: 12776

Answers (2)

APC
APC

Reputation: 146239

Your code has two glaring problems:

  1. 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.
  2. 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

Upvotes: 2

Popeye
Popeye

Reputation: 35900

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!!

Upvotes: 0

Related Questions