stored procedure with dynamic column name

Question

I'm trying to print the multiple table row where we don't know the column name. I'm trying this from a day and I come up with this procedure with error 'must declare emp_dummy_col'. Please help me out. Thank you.

create or replace procedure sp_display
as
   CURSOR cur_emp is select EMP_ID,EMP_NAME,DEPT_IT,DOJ,LOCATION from employee;
   emp_rows cur_emp%rowtype;
   type emp_table is table of emp_rows%type;
   emp_dummy_table emp_table;
   CURSOR cur_col is select column_name from user_tab_cols where table_name ='EMPLOYEE';
   emp_row cur_col%rowtype;
   type emp_table1 is table of emp_row%type;
   emp_dummy_col emp_table1;
begin
   open cur_emp;
      fetch cur_emp bulk collect into emp_dummy_table;
   close cur_emp;
   open cur_col;
      fetch cur_col bulk COLLECT into emp_dummy_col; 
   close cur_col;
   for i in 1..emp_dummy_table.count
   loop
      for j in 1..emp_dummy_col.count
      loop
         DBMS_OUTPUT.PUT_LINE(emp_dummy_table(i).emp_dummy_col(j));
      end loop;
   end loop;
end;

Answer 1

As mentioned by Justin Cave you can use the package DBMS_SQL with a lot of work to do.

Following is only a simple example.

SET SERVEROUTPUT ON
SET FEEDBACK OFF
CLEAR

DECLARE
  -- The SQL Statement
  V_SQL VARCHAR2(4000) := 'select EMP_ID,EMP_NAME,DEPT_IT,DOJ,LOCATION from employee';
  -- The cursor (number) variable
  V_CURSOR INTEGER;
  -- Variable to hold the return value of DBMS_SQL.EXECUTE (Number of processed rows)
  V_NUM_ROWS NUMBER;
  -- Variable to hold the return value of DBMS_SQL.DESCRIBE_COLUMNS
  V_COL_CNT INTEGER;
  -- Column description table. Outcome from DBMS_SQL.DESCRIBE_COLUMNS
  V_DESC DBMS_SQL.DESC_TAB;
  -- Variable for the current column number when iterating over the column description collection
  V_COL_NUM NUMBER;
  -- (generic) Variable for a VARCHAR2-Column
  V_VARC_COL VARCHAR2(4000);
  -- (generic) Variable for a NUMBER-Column
  V_NUM_COL NUMBER;
BEGIN

  -- Open a cursor
  V_CURSOR := DBMS_SQL.OPEN_CURSOR;
  -- Parse the SQL-Query (without any bindings etc.)
  DBMS_SQL.PARSE(V_CURSOR, V_SQL, DBMS_SQL.NATIVE);

  -- Execute the parsed query and return the number of processed rows. 
  -- May be 0 or undefined on SELECT- and DDL-Statements and should be ignored
  V_NUM_ROWS := DBMS_SQL.EXECUTE(V_CURSOR);

  -- Get the columns description table
  DBMS_SQL.DESCRIBE_COLUMNS(V_CURSOR, V_COL_CNT, V_DESC);

  V_COL_NUM := V_DESC.FIRST;

  IF (V_COL_NUM IS NOT NULL) THEN
    LOOP
      -- Assign variables to column types
      CASE V_DESC(V_COL_NUM).COL_TYPE
        WHEN DBMS_SQL.NUMBER_TYPE THEN
          DBMS_SQL.DEFINE_COLUMN(V_CURSOR, V_COL_NUM, V_NUM_COL);
        WHEN DBMS_SQL.VARCHAR2_TYPE THEN
          DBMS_SQL.DEFINE_COLUMN(V_CURSOR, V_COL_NUM, V_VARC_COL, 4000);
          -- more branches if other column types are expected/supported (Date, LOBs etc.)
        ELSE
          NULL;
      END CASE;
      V_COL_NUM := V_DESC.NEXT(V_COL_NUM);
      EXIT WHEN(V_COL_NUM IS NULL);
    END LOOP;

    -- Fetch the rows
    V_NUM_ROWS := DBMS_SQL.FETCH_ROWS(V_CURSOR);
    IF (V_NUM_ROWS > 0) THEN
      LOOP
        -- Column handling
        FOR L_I IN V_DESC.FIRST .. V_DESC.LAST LOOP
          CASE V_DESC(L_I).COL_TYPE
          -- NUMBER column type value handler
            WHEN DBMS_SQL.NUMBER_TYPE THEN
              DBMS_SQL.COLUMN_VALUE(V_CURSOR, L_I, V_NUM_COL);
              V_VARC_COL := TO_CHAR(V_NUM_COL);
              -- VARCHAR2 column type value handler
            WHEN DBMS_SQL.VARCHAR2_TYPE THEN
              DBMS_SQL.COLUMN_VALUE(V_CURSOR, L_I, V_VARC_COL);
              -- more branches if other column types are expected/supported (Date, LOBs etc.)
            ELSE
              --Handling for unsupported column types (if necessary)
              NULL;
          END CASE;
          DBMS_OUTPUT.PUT(V_DESC(L_I).COL_NAME || '" = "' || V_VARC_COL || '"');
          IF (L_I < V_DESC.COUNT) THEN
            DBMS_OUTPUT.PUT('  |  ');
          END IF;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('');
        -- Fetch the next row
        V_NUM_ROWS := DBMS_SQL.FETCH_ROWS(V_CURSOR);
        -- Exit loop if no more row is fetched
        EXIT WHEN V_NUM_ROWS = 0;
      END LOOP;
    END IF;
  END IF;
  -- Close the cursor (important!)
  DBMS_SQL.CLOSE_CURSOR(V_CURSOR);

EXCEPTION
  WHEN OTHERS THEN
    -- Close the cursor (important!) if any error occurs
    IF (DBMS_SQL.IS_OPEN(V_CURSOR)) THEN
      DBMS_SQL.CLOSE_CURSOR(V_CURSOR);
    END IF;
    -- Rethrow the exception
    RAISE;
END;
/

Detailed/further information about DBMS_SQL:

• https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm or https://docs.oracle.com/database/121/ARPLS/d_sql.htm
• http://www.java2s.com/Code/Oracle/System-Packages/dbms_sql.htm

!!Security warning!!

The use of such dynamic SQL is vulnerable to SQL Injection and other techniques!

Answer 2

Hm, the code example you have is not really doing what you are asking for. It expects you to tell the columns, see:

select EMP_ID,EMP_NAME,DEPT_IT,DOJ,LOCATION from employee;

1) you can get the unknown columns of a table like this (i am using the table NEWS for this example)

begin
    for rec in
           (select column_name from user_tab_cols
           where table_name='NEWS') loop
        dbms_output.put_line(rec.column_name);
    end loop;
end;

2) if you want to print out the data, that is very nasty in PLSQL, see Cursor For Loop with dynamic SQL-Statement

3) if you're good with some compromise, try this approach. I build a dynamic SQL statement from the fields learned from user_tab_cols. The dynamic SQL concatenates the string values of each columns into one line, then finally I fetch a cursor on this.

For convenience, I also collect the column names in the header variable.

declare
    statement varchar2(4000);
    header varchar2(4000);
    TYPE TCur IS REF CURSOR;
    cur TCur;
    TYPE TRec IS RECORD (
      line varchar2(4000)
    );
    rec TRec;
begin

    for rec in
           (select column_name from user_tab_cols
           where table_name='NEWS') loop
        if statement is not null then
            statement:=statement||'||'',''||';
            header:=header||',';
        end if;
        statement:=statement||rec.column_name;
        header:=header||rec.column_name;
    end loop;
    statement:='select '||statement||' as line from '||'NEWS';
    dbms_output.put_line(header);
    open cur for statement;
    loop
        fetch cur into rec;
        exit when cur%notfound;
        dbms_output.put_line(rec.line);
    end loop;
    close cur;
end;

Gives like:

NEWS_ID,NEWS
0,Some new PLSQL hack has been done
1,And the day is not over yet!