Как получить результат из вывода выполнения немедленного оператора

Процедура выполняет только приведенный ниже вывод, но я пытаюсь получить результат из вывода выполнения немедленного оператора.

Текущий выход:

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;

Я ожидаю результат выше 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;
      /

oracle dynamic-sql plsql
person Ganesan VC    schedule 15.11.2019    source источник
comment
Вы пытались установить serveroutput на   -  person Andrew    schedule 15.11.2019
comment
@Андрей да. он также указан в верхней части моего запроса.   -  person Ganesan VC    schedule 15.11.2019
comment
попробуйте использовать v_sql2 := RTRIM(v_sql, 'UNION ALL' || chr(10)) || ';'; ВЫПОЛНИТЬ НЕМЕДЛЕННО v_sql2 МАССОВЫЙ СБОР В MYROW; DBMS_OUTPUT.PUT_LINE(MYROW.XXX); внутри цикла for, потому что вы хотите напечатать результат этого запроса выбора, а не сам запрос выбора...   -  person Andrew    schedule 15.11.2019

Ответы (2)


arrow_upward
1
arrow_downward

В вашем коде есть две явные проблемы:

  1. Проекция целевой коллекции не соответствует проекции динамического запроса. Они должны иметь одинаковое количество (и тип данных) столбцов.
  2. Оператор dbms_output завершится ошибкой, поскольку MYROW является коллекцией, а put_line() принимает только скалярные значения.

Существует также неуклюжесть цикла, который собирает запрос. Мы можем использовать коллекции PL/SQL, чтобы сделать его более аккуратным.

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

Внимание: не тестировалось, потому что у меня нет доступа к многопользовательской среде.

person APC    schedule 15.11.2019

arrow_upward
0
arrow_downward

Ваша переменная MYROW имеет тип T, который равен TABLE OF MYTABLE.ID%TYPE, поэтому она сможет содержать только одно значение для каждой записи типа данных, которое совпадает с типом данных MYTABLE.ID.

В вашем коде вы выбираете "PRJ_ID", EVENT, email, modified_by, modified (5) столбцов и пытаетесь назначить их MYROW, которые не могут содержать столько столбцов.

Вы должны удалить все столбцы, кроме «PRJ_ID», из предложения select, если вас интересует только поле PRJ_ID.

что-то типа:

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

Если вам нужны все данные, вам нужно создать объект, который может содержать соответствующие атрибуты. см. этот форум для реализации таблицы объекта.

Ваше здоровье!!

person Popeye    schedule 15.11.2019