Как написать динамический SQL для выбора имен столбцов на основе другой таблицы?
У меня есть вариант использования, когда я хочу написать динамический sql, который будет динамически выбирать имена столбцов на основе другой таблицы.
Запрос 1: выберите T_SERIES_VALUE из MONTHS_MAPPING, где TIME_PERIOD = 'JUN-2023'; -- T66 является выходом
Запрос 2: выберите aaaa, bbbb, OUTPUT OF QUERY1 как «Current_Month» из MAIN_TABLE;
Поскольку я не могу обрабатывать эти несколько столбцов в функции/процедуре sql в этом случае использования, я создал sql TYPE объекта/таблицы, а затем использовал массовый сбор, как показано ниже. Проц компилируется. Но когда я выполняю процедуру, я получаю ошибку несогласованных типов данных. Но я вижу, что типы данных типа объекта и значения db из MAIN_TABLE являются одними и теми же.
Я пробовал ниже:
Тип 1:
create or replace type cds_type_o1 as Object
(
aaaa VARCHAR2(26 BYTE)
,bbbb VARCHAR2(256 BYTE)
,Tx NUMBER(38,10)
);
Тип 2: `` создать или заменить тип cds_type_t1 на таблицу cds_type_o1 ;
``
Процесс:
create or replace procedure test_bulk_collect_table (reporting_period IN VARCHAR2)
is
v_cm_sql varchar2(256);
v_cm varchar2(256);
v_output_execute varchar2(256);
v_output cds_type_t1;
begin
v_cm_sql:='select T_SERIES_VALUE from MONTHS_MAPPING where TIME_PERIOD = '||reporting_period; -- T61
execute immediate v_cm_sql into v_cm; --T66
v_output_execute:= 'select aaaa, bbbb, '|| v_cm ||' from MAIN_TABLE';
dbms_output.put_line(v_output_execute); --select aaaa, bbbb, T66 from MAIN_TABLE
*execute immediate v_output_execute bulk collect into v_output;* --Here I am getting the error while executing it
dbms_output.put(v_output(0).scenario);
for i in v_output.first..v_output.last loop
dbms_output.put(v_output(i).aaaa);
end loop;
end test_bulk_collect_table;
Теперь, когда я выполняю этот процесс как УСТАНОВИТЬ ВЫХОД СЕРВЕРА; EXEC test_bulk_collect_table('ИЮНЬ-2023');
Я получаю следующую ошибку:
Сообщение об ошибке -
ORA-00932: несовместимые типы данных: ожидаемый UDT получил CHAR
ORA-06512: в «SCHEMA.TEST_BULK_COLLECT_TABLE», строка 15 (выполнить немедленный массовый сбор v_output_execute в v_output)
ORA-06512: в строке 1
00932. 00000 - "несогласованные типы данных: ожидалось %s, получено %s"
*Причина:
*Действие:
Меня смущает эта ошибка, так как она ожидает %s, а также получает %s, любые рекомендации по этому поводу будут очень признательны.
Ответы 3
Для выборки (или массового сбора) в список объектов необходимо выбирать объекты, а не столбцы. Следовательно, вам просто нужно добавить конструктор объекта в ваш запрос, например
SQL> create or replace
2 type myobj as object ( x int, y int );
3 /
Type created.
SQL>
SQL> create or replace
2 type mylist is table of myobj;
3 /
Type created.
SQL>
SQL> declare
2 v mylist;
3 begin
4 select empno, sal
5 bulk collect into v
6 from emp;
7 end;
8 /
from emp;
*
ERROR at line 6:
ORA-06550: line 6, column 3:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored
SQL>
SQL> declare
2 v mylist;
3 begin
4 select myobj(empno, sal)
5 bulk collect into v
6 from emp;
7 end;
8 /
PL/SQL procedure successfully completed.
Не слишком ли вы все усложняете? Ошибка, которую вы получили, связана с тем, что уже сказал Коннор, но поскольку в опубликованном вами коде нет ничего динамического, почему вы используете динамический SQL? Все можно сделать НАМНОГО проще. Вот демо.
Примеры таблиц:
SQL> create table months_mapping as select 'T66' t_series_value, 'T61' time_period from dual;
Table created.
SQL> create table main_table as select 1 aaaa, 2 bbbb from dual;
Table created.
Типы:
SQL> create or replace type cds_type_o1 as Object
2 (aaaa VARCHAR2(26)
3 ,bbbb VARCHAR2(256)
4 ,Tx VARCHAR2(10)
5 );
6 /
Type created.
SQL> create or replace type cds_type_t1 is table of cds_type_o1;
2 /
Type created.
Процедура:
SQL> create or replace procedure test_bulk_collect_table (reporting_period IN VARCHAR2)
2 is
3 v_output cds_type_t1;
4 begin
5 select cds_type_o1(a.aaaa, a.bbbb, b.t_series_value)
6 bulk collect into v_output
7 from months_mapping b cross join main_table a
8 where b.time_period = reporting_period;
9
10 for i in v_output.first .. v_output.last loop
11 dbms_output.put_line(v_output(i).aaaa ||' - '|| v_output(i).bbbb ||' - '|| v_output(i).tx);
12 end loop;
13 end test_bulk_collect_table;
14 /
Procedure created.
Тестирование:
SQL> set serveroutput on
SQL> exec test_bulk_collect_table('T61');
1 - 2 - T66
PL/SQL procedure successfully completed.
SQL>
Привет @Littlefoot, Очень ценю это. t_series_value носит динамический характер. Основываясь на значении строки, которое оно возвращает из таблицы month_mapping, я выбираю это значение/столбец из main_table. и это значение меняется по своей природе. Поэтому мне пришлось использовать динамический sql. Когда я пытаюсь реализовать предоставленное вами решение, я получаю 03001. 00000 — «нереализованная функция» * Причина: эта функция не реализована. *Действие: Нет... Не могли бы вы проверить, не делаю ли я что-то не так.
размещение моего запроса proc, поскольку приведенный выше комментарий не позволяет использовать более 166 символов. v_output_execute:= 'выбрать cds_type_o1(aaaa, bbbb, T61) массовый сбор в v_output из MONTHS_MAPPING b перекрестное соединение MAIN_TABLE a где b.time_period = ' || отчетный период; dbms_output.put_line (v_output_execute); выполнить немедленно v_output_execute ;
Я упускаю очевидное? Как насчет того, чтобы просто использовать скалярный подзапрос и сделать это в sql?
select
aaaa,
bbbb,
(select T_SERIES_VALUE from MONTHS_MAPPING where TIME_PERIOD = 'JUN-2023') as "Current_Month"
from main_table
Привет @Koen Lostrie, к сожалению, на самом деле это не так просто. Я пытался использовать упомянутый вами запрос, но вместо того, чтобы возвращать фактический вывод запроса внутреннего выбора, он просто продолжал печатать <T_SERIES_VALUE> во всех строках.. например, вывода: aaaa bbbb Current_Month 1111 11112 T61 2222 11112 T61
Пожалуйста, предоставьте достаточно образцов данных, чтобы воспроизвести проблему.
Привет! Большое спасибо, @Connor, за ответ. Теперь я получаю следующую ошибку. Извините, я немного новичок в Oracle, я не объявлял какой-либо тип VARRAY, однако получаю следующую ошибку. Просто в таблице подчеркивания 60 тыс. записей. 06532. 00000 - "Подстрочный индекс вне пределов" *Причина: Подстрочный индекс был больше предела varray или неположителен для varray или вложенной таблицы. *Действие: проверьте логику программы и при необходимости увеличьте лимит varray.