CODEWITHSUNDEEP
oracle-databaseplsql
griboedov
griboedov

Reputation: 886

Column name as variable in PL/SQL ORACLE

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?

Upvotes: 8

Views: 40584

Answers (3)

Rahul
Rahul

Reputation: 77926

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;

Upvotes: 0

neshkeev
neshkeev

Reputation: 6486

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

Upvotes: 3

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 121057

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;

Upvotes: 14

Related Questions