Access cursor by column name dynamically

Question

Can I access a cursor's column dynamically? I mean by name? something like this:

declare
 v_cursor := select * from emp;
begin
 FOR reg IN v_cursor LOOP
   dbms_output.put_line(**reg['column_name_as_string']**);
 end loop;
end;

I know the bold part is not PL/SQL, but I'm looking for something like that and can't find it anywhere.

Answer 1

You can use the package DBMS_SQL to create and access cursors with dynamic queries.

However it's not really straightforward to access a column by name because the DBMS_SQL package uses positioning and in a dynamic query we may not know the order of the columns before the execution.

Furthermore, in the context of this question, it appears that we may not know which column we want to display at compile time, we will assume that the column we want to display is given as a parameter.

We can use DBMS_SQL.describe_columns to analyze the columns of a SELECT query after it has been parsed to build a dynamic mapping of the columns. We will assume that all columns can be cast into VARCHAR2 since we want to display them with DBMS_OUTPUT.

Here's an example:

SQL> CREATE OR REPLACE PROCEDURE display_query_column(p_query VARCHAR2,
  2                                                   p_column VARCHAR2) IS
  3     l_cursor            INTEGER;
  4     l_dummy             NUMBER;
  5     l_description_table dbms_sql.desc_tab3;
  6     TYPE column_map_type IS TABLE OF NUMBER INDEX BY VARCHAR2(32767);
  7     l_mapping_table column_map_type;
  8     l_column_value  VARCHAR2(4000);
  9  BEGIN
 10     l_cursor := dbms_sql.open_cursor;
 11     dbms_sql.parse(l_cursor, p_query, dbms_sql.native);
 12     -- we build the column mapping
 13     dbms_sql.describe_columns3(l_cursor, l_dummy, l_description_table);
 14     FOR i IN 1 .. l_description_table.count LOOP
 15        l_mapping_table(l_description_table(i).col_name) := i;
 16        dbms_sql.define_column(l_cursor, i, l_column_value, 4000);
 17     END LOOP;
 18     -- main execution loop
 19     l_dummy := dbms_sql.execute(l_cursor);
 20     LOOP
 21        EXIT WHEN dbms_sql.fetch_rows(l_cursor) <= 0;
 22        dbms_sql.column_value(l_cursor, l_mapping_table(p_column), l_column_value);
 23        dbms_output.put_line(l_column_value);
 24     END LOOP;
 25     dbms_sql.close_cursor(l_cursor);
 26  END;
 27  /

Procedure created

We can call this procedure with a query known only at run-time:

SQL> set serveroutput on
SQL> exec display_query_column('SELECT * FROM scott.emp WHERE rownum < 5', 'ENAME');
SMITH
ALLEN
WARD
JONES

PL/SQL procedure successfully completed

SQL> exec display_query_column('SELECT * FROM scott.emp WHERE rownum < 5', 'EMPNO');
7369
7499
7521
7566

PL/SQL procedure successfully completed

Use caution with dynamic SQL: it has the same privileges as the user and can therefore execute any DML and DDL statement allowed for this schema.

For instance, the above procedure could be used to create or drop a table:

SQL> exec display_query_column('CREATE TABLE foo(id number)', '');
begin display_query_column('CREATE TABLE foo(id number)', ''); end;
ORA-01003: aucune instruction analysée
ORA-06512: à "SYS.DBMS_SQL", ligne 1998
ORA-06512: à "APPS.DISPLAY_QUERY_COLUMN", ligne 13
ORA-06512: à ligne 1

SQL> desc foo
Name Type   Nullable Default Comments 
---- ------ -------- ------- -------- 
ID   NUMBER Y      

Answer 2

It's probably easiest to make the query dynamic if you can.

DECLARE
  v_cursor  SYS_REFCURSOR;
  dynamic_column_name VARCHAR2(30) := 'DUMMY';
  column_value  VARCHAR2(32767);
BEGIN
  OPEN v_cursor FOR 'SELECT ' || dynamic_column_name || ' FROM dual';
  LOOP
    FETCH v_cursor INTO column_value;
    EXIT WHEN v_cursor%NOTFOUND;
    dbms_output.put_line( column_value );
  END LOOP;
  CLOSE v_cursor;
END;

If you really want to have a hardcoded SELECT * and dynamically select a column from that by name, I think you could do that using DBMS_SQL as Vincent suggests, but it will be somewhat more complex.