Есть ли функция чтобы получить лист всех колонок с запятой-разделителем из заданных схемы и таблицы?

Question

Пытаюсь реализовать несколько простых утилит в PL/SQL, одна из которых, функция, которая вернёт лист всех колонок с запятой-разделителем из заданных схемы и таблицы.

Начал с таким кодом:

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

Не могу придумать более "правильного" типа, чем таблица записей с точно таким же типом переменной, как в таблице источнике.

Как это можно исправить, или как это сделать более эффективней?

_{Свободный перевод вопроса Is there a fast PLSQL function for returning a comma-delimited list of column names for a given schema.table? от участника @Tim Sands}

Answer 1

Причина ошибки - v_returnVal это коллекция, вернуть которую можно с BULK COLLECT INTO.

Но наиболее эффективно будет - получить лист колонок в прямо в SQL запросе:

create or replace function getColumnList (o varchar2, t varchar2) return varchar2 is
    cols varchar2 (32767);
begin
    select listagg ('"'||column_name||'"', ',') within group (order by column_id)
    into cols
    from all_tab_columns
    where owner=upper (o) and table_name=upper (t);
    return cols; 
end;
/

Тест и результат:

create table me.t1 (col1 int, col2 int, col3 int);
var cols varchar2 (32) 
exec :cols := getColumnList ('me', 't1')

COLS
--------------------
"COL1","COL2","COL3"

БЛОГ НА HUSL

Есть ли функция чтобы получить лист всех колонок с запятой-разделителем из заданных схемы и таблицы?

Ответы (1 шт):