#oracle #plsql
#Oracle #plsql
Вопрос:
Я пытаюсь настроить несколько простых утилит в среде PL / SQL. В конце концов, я ожидаю, что жестко закодированная MYTABLE будет заменена переменной bind .
Я начал со следующего, который возвращает ошибку:
DECLARE
TYPE colNames_typ IS TABLE OF all_tab_cols.column_name%type index by PLS_INTEGER;
v_ReturnVal colNames_typ;
v_sql VARCHAR2(32000);
BEGIN
v_sql :='SELECT column_name FROM all_tab_cols WHERE table_name = ''MYTABLE'' ' ;
EXECUTE IMMEDIATE (v_sql)
INTO v_returnVal;
-- Convert assoc array to a comma delimited list.
END;
Возвращенная ошибка:
PLS-00597: expression 'V_RETURNVAL' in the INTO list is of wrong type
Я не могу придумать более «правильный» тип, чем таблица записей с тем же типом переменной, что и у источника.
Любая помощь была бы потрясающей!
Спасибо
Комментарии:
1. Название вашего вопроса и исходный код расходятся. Заголовок запрашивает список, разделенный запятыми (т.Е. Строку), но ваш код генерирует ассоциативный массив (тип данных только для PL / SQL). Что правильно?
2. Хороший момент — спасибо! Немного прояснил в редактировании. Я думаю, что вопрос о том, как преобразовать массив assoc в строку, разделенную запятыми, уже был рассмотрен в Интернете, возможно :-).
Ответ №1:
Существует ли быстрая функция PLSQL для возврата списка имен столбцов, разделенных запятыми, для заданной schema.table?
Использовать LISTAGG
:
DECLARE
v_owner ALL_TAB_COLUMNS.OWNER%TYPE := 'SCHEMA_NAME';
v_table_name ALL_TAB_COLUMNS.TABLE_NAME%TYPE := 'TEST_DATA';
v_columns VARCHAR2(32000);
BEGIN
SELECT LISTAGG( '"' || column_name || '"', ',' )
WITHIN GROUP ( ORDER BY COLUMN_ID )
INTO v_columns
FROM all_tab_columns
WHERE owner = v_owner
AND table_name = v_table_name;
DBMS_OUTPUT.PUT_LINE( v_columns );
END;
/
(Примечание: вам также необходимо передать владельца или, если у вас есть две таблицы с одинаковыми именами в разных схемах, вы получите столбцы для обеих.)
(Примечание 2: я предполагаю, что вы хотите, чтобы список имен столбцов помещался в динамический запрос; если это так, то вы хотите заключить идентификаторы столбцов в двойные кавычки. Если вы этого не сделаете, а идентификатор столбца чувствителен к регистру, вы получите неправильное имя, поскольку Oracle неявно преобразует идентификаторы без кавычек в верхний регистр при их анализе в запросе. Если вам не нужны кавычки, используйте SELECT LISTAGG( column_name, ',' )
.)
Что, если у вас есть таблица:
CREATE TABLE test_data (
A NUMBER,
B DATE,
C VARCHAR2(20),
E TIMESTAMP,
Z INTERVAL DAY TO SECOND,
Q CHAR(5)
);
Выходные данные:
"A","B","C","E","Z","Q"
db<>скрипка здесь
Комментарии:
1. Спасибо — это очень помогает. Ваши заметки об их обобщении для использования в динамических запросах имеют смысл. Для моего собственного изучения, есть ли причина, по которой предыдущий подход не удался?
Ответ №2:
Не уверен, что это то, о чем спрашивают:
create or replace function get_cols_string(target_owner all_tab_cols.owner%type, target_table_name all_tab_cols.table_name%type) return varchar2 is
outputString varchar2(32767);
oneMore boolean := false;
BEGIN
for current_col in
(SELECT column_name FROM all_tab_cols
WHERE owner = target_owner and table_name = target_table_name) loop
if(oneMore) then
outputString := outputString || ', ';
end if;
outputString := outputString || current_col.column_name;
oneMore := TRUE;
end loop;
return outputString;
END;
/
Rem Test the above with simple cases
create table tab1 (c1 number);
create table tab2 (c1 number, c2 number);
set serveroutput on
declare
owner_name varchar2(32767) := 'SYS';
table_name varchar2(32767) := 'TAB1';
begin
dbms_output.put_line('For: ' || owner_name || '.' || table_name);
dbms_output.put_line(get_cols_string(owner_name, table_name));
end;
/
declare
owner_name varchar2(32767) := 'SYS';
table_name varchar2(32767) := 'TAB2';
begin
dbms_output.put_line('For: ' || owner_name || '.' || table_name);
dbms_output.put_line(get_cols_string(owner_name, table_name));
end;
/
declare
owner_name varchar2(32767) := 'SYS';
table_name varchar2(32767) := 'ALL_TAB_COLS';
begin
dbms_output.put_line('For: ' || owner_name || '.' || table_name);
dbms_output.put_line(get_cols_string(owner_name, table_name));
end;
/
Ответ №3:
Вы спросили: «Есть ли причина, по которой предыдущий подход не удался» — ну да. Ошибка связана с тем, что Oracle использует очень строгую типизацию, что делает ваше предположение о том, что не существует «более»правильного» типа, чем таблица записей с тем же типом переменной, что и исходный» false. Коллекция (таблица) type_a не является переменной type_a . Вы пытались сохранить переменную type_a в коллекцию type_a , что привело к неправильному исключению типа.
Теперь это не значит, что вы были на самом деле далеко. Вы хотели сохранить коллекцию переменных type_a, возвращаемых select , в коллекцию type_a . Вы можете это сделать, вам просто нужно сообщить об этом Oracle. Вы выполняете это с помощью BULK COLLECT INTO . Ниже показан этот процесс и создается ваш CSV с именами столбцов.
Примечание: @MTO опубликовал превосходное решение, это просто показывает вам, как можно было выполнить ваш оригинал. Тем не менее, это полезный метод, который нужно держать в своем арсенале.
declare
type colnames_typ is table of all_tab_cols.column_name%type;
k_comma constant varchar2(1) := ',';
v_returnval colnames_typ;
v_sql varchar2(32000);
v_sep varchar2(1) := ' ';
v_csv_names varchar2(512);
begin
v_sql := 'select column_name from all_tab_cols where table_name = ''MYTABLE'' order by column_id';
execute immediate (v_sql)
bulk collect into v_returnval;
-- Convert assoc array to a comma delimited list.
for indx in 1 .. v_returnval.count
loop
v_csv_names := v_csv_names || v_sep || v_returnval(indx);
v_sep :=k_comma;
end loop;
v_csv_names := trim(v_csv_names);
dbms_output.put_line(v_csv_names);
end;
Комментарии:
1. Спасибо, что нашли время для написания этого — различие между коллекцией и отдельными элементами было моей главной проблемой. Абсолютно согласен, что ответ @MTO быстрее и понятнее, чем моя первая попытка.