CODEWITHSUNDEEP
oracle-databaseplsql
Tim Sands
Tim Sands

Reputation: 1068

Is there a fast PLSQL function for returning a comma-delimited list of column names for a given schema.table?

I'm trying to set up some simple utilities in a PL/SQL environment. Eventually, I'd expect the hardcoded MYTABLE to be replaced by a bind variable.

I've started with the following, which returns an error:

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;

The error returned:

PLS-00597: expression 'V_RETURNVAL' in the INTO list is of wrong type

I cant think of a more 'right' type than a table of entries with the exact same variable type as the source.

Any help would be awesome!

Thanks

Upvotes: 0

Views: 561

Answers (3)

Belayer
Belayer

Reputation: 14886

You asked "is there a reason why the previous approach failed" - well yes. The error stems from Oracle being a very strict typing thus making your assumption that there is not "a more 'right' type than a table of entries with the exact same variable type as the source" false. A collection (table) of type_a is not a variable type_a. You attempted to store a variable of type_a into a collection of type_a, thus giving you the wrong type exception.

Now that does not mean you were actually far off. You wanted to store a collection of type_a variables, returned by the select, into a collection of type_a. You can do that, you just need to let Oracle know. You accomplish it with BULK COLLECT INTO. The following shows that process and creates your CSV of column names.

Note: @MTO posted the superior solution, this just shows you how your original could have been accomplished. Still it is a useful technique to keep in your bag of tricks.

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;

Upvotes: 1

B Cheng
B Cheng

Reputation: 121

Not sure if this is what is being asked:

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

Upvotes: 1

MT0
MT0

Reputation: 168026

Is there a fast PLSQL function for returning a comma-delimited list of column names for a given schema.table?

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

(Note: you also need to pass the owner or, if you have two tables with identical names in different schemas then, you will get columns for both.)

(Note 2: I am assuming you want a list of column names to put into a dynamic query; if so, then you want to surround the column identifiers with double-quotes. If you don't and a column identifier is case-sensitive then you will get an incorrect name as Oracle will implicitly convert unquoted identifiers to upper case when it parses them in a query. If you don't want the quotes then use SELECT LISTAGG( column_name, ',' ).)

Which, if you have the table:

CREATE TABLE test_data (
  A NUMBER,
  B DATE,
  C VARCHAR2(20),
  E TIMESTAMP,
  Z INTERVAL DAY TO SECOND,
  Q CHAR(5)
);

Outputs:

"A","B","C","E","Z","Q"

db<>fiddle here

Upvotes: 3

Related Questions