Блог

Я пытаюсь настроить несколько простых утилит в среде 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
  

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

Любая помощь была бы потрясающей!

Спасибо

Существует ли быстрая функция 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<>скрипка здесь

Не уверен, что это то, о чем спрашивают:

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

Вы спросили: «Есть ли причина, по которой предыдущий подход не удался» — ну да. Ошибка связана с тем, что 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;