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?

You can try execute immediate like shown here: http://stackoverflow.com/questions/4714163/oracle-execute-immediate-into-a-cursor — 6ton, Aug 07 '14 at 12:49

score 14 · Accepted Answer · answered Aug 07 '14 at 12:49

14

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;

answered Aug 07 '14 at 12:49

Klaus Byskov Pedersen

117,245
29
183
222

1

@zaratustra since there is no "selecting into" going on, that is not correct. – Klaus Byskov Pedersen Aug 07 '14 at 12:52
Sorry, I didn't see that, but how is it possible to `select` without `into`? – neshkeev Aug 07 '14 at 12:58
2

@zaratustra - the SELECT is executed but no values are returned. The alternative is to do something like `EXECUTE IMMEDIATE 'select ' || col_n || ' from my_table' INTO SOME_VARIABLE`, or `EXECUTE IMMEDIATE 'select ' || col_n || ' from my_table' BULK COLLECT INTO SOME_COLLECTION`. Share and enjoy. – Bob Jarvis - Слава Україні Aug 07 '14 at 13:37
Interesting fact, I though it was impossible to query without the `into` clause – neshkeev Aug 07 '14 at 14:24

neshkeev · Answer 2 · 2014-08-07T13:03:34.690

3

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

edited Aug 07 '14 at 13:03

answered Aug 07 '14 at 12:50

neshkeev

6,280
3
26
47

1

I'm pretty sure you meant to have a single-quote after `'select ` and before `from my_table'`. – Bob Jarvis - Слава Україні Aug 07 '14 at 12:53
1

You can retrieve column type with package DBMS_SQL, however then you have to write a couple more lines of code. – Wernfried Domscheit Aug 07 '14 at 13:14

score 0 · Answer 3 · edited May 23 '17 at 10:30

0

You will have to use dynamic sql/query to achieve what you are trying to do. Something like below using EXECUTE IMMEDIATE. Concept taken from Here.

DECLARE col_n VARCHAR(100) := 'X';
DECLARE sql VARCHAR(100);
BEGIN
sql := 'select :p1 from my_table;'
EXECUTE IMMEDIATE sql USING col_n;
END;

Like below for SQL *Plus

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

edited May 23 '17 at 10:30

Community

1
1

answered Aug 07 '14 at 12:54

Rahul

76,197
13
71
125

2

This will result as `SELECT 'X' FROM MY_TABLE`, but does not select column 'X' – Wernfried Domscheit Aug 07 '14 at 13:12
In addition, use of parameters as shown above is only valid in SQL*Plus, while the question is tagged for PL/SQL. – Bob Jarvis - Слава Україні Aug 07 '14 at 13:34

Column name as variable in PL/SQL ORACLE

3 Answers3

Linked