Column name as variable in PL/SQL ORACLE

Question

I want to have a code where i would declare a column name as variable and then use this variable to retrieve desired column from a certain table.

DECLARE
col_n VARCHAR (100) := 'X' ;
BEGIN
select col_n from my_table;
END;

What is the most easy and explicit way for that in Oracle?

Answer 1

You can use dynamic sql to execute a query that you construct as a string. It would go something along these lines:

DECLARE
col_n VARCHAR (100) := 'X' ;
plsql_block VARCHAR2(500);
BEGIN
    plsql_block := 'select ' || col_n  || ' from my_table';
    EXECUTE IMMEDIATE plsql_block;
END;

Answer 2

You can use dynamic sql:

DECLARE
  col_n VARCHAR (100) := 'X' ;
  l_cursor sys_refcursor;
  l_temp number(10); -- won't work if the col_n column has different type
BEGIN
  open l_cursor for 'select '|| col_n ||' from my_table';
  loop
    fetch l_cursor into l_temp;
    exit when l_cursor%NOTFOUND;
    ...
  end loop;
END;

The problems is you have to know for sure the type of your column.

Actually, there is one more way to do it, if you use SQL*Plus environment:

SQL> select &&col_n from employees where &&col_n = 199;
Enter value for col_n: employee_id
old   1: select &&col_n from employees where &&col_n = 199
new   1: select employee_id from employees where employee_id = 199

EMPLOYEE_ID
-----------
        199