[SOLVED] Fetch table headers in horizontal format | PLSQL |

Issue

I am bit stuck with it not getting where I am suppose to change and how to execute

I need to get specific table header in horizontal format with comma separated

example : header1,header2,header3

Used a sample code and edited according to my need

create or replace getdetails( vtable_name varchar2 , out voutput)
return varchar2
  as 
      v_sql varchar2(40000) :='';
BEGIN
      v_sql ='select listagg(column_name,',') within group (order by column_name) items from user_tab_columns where table_name :=vtable_name;'

EXCEPTION
    when no date_found then retrun '0';
    when others        then retrun '0';
END; 

Need to get output into a variable

Solution

Use select into :

create or replace function getdetails( vtable_name varchar2 , out voutput)
return varchar2
  as 
      v_columns varchar2(4000) :='';
BEGIN
      select listagg(column_name,',') within group (order by column_name) 
        into v_columns
        from user_tab_columns 
       where table_name = vtable_name;
      dbms_output.put_line (v_columns);
      return v_columns;

EXCEPTION
    when no_data_found then retrun '0';
    when others        then return '0';
END;

Answered By – San

Answer Checked By – David Goodson (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.