How to make column name dynamic with spool on ORACLE?

Question

I want to ask, is it possible to use cursor to looping the string value? For example I have 3 title column for my '.txt' report :

COLUMN "DATE" format a15
COLUMN "SUBJECT" format a8
COLUMN "CLASS" format a10

and the result is like:

DATE                       SUBJECT                CLASS
----------------------     ------------------     -----------------
08-Apr-2016                 Science                10  
08-Apr-2016                 Social                 11

Now, the column name is still generic or hardcode. Then, I want to return the string value to replace the "DATE", "SUBJECT" and "CLASS" to be dynamic. So just follow to the data that loaded to database.

For example : I have table that is filled up with these data:

TITLE_COLUMN1               TITLE_COLUMN2                 TITLE_COLUMN3
DATE                        SUBJECT                       CLASS

I want to select the value from TITLE_COLUMN1, TITLE_COLUMN2, TITLE_COLUMN3 to be on my column name. So what is the better way? Using

Cursor

or using

Variable

?

Thanks for the advise and help.

Answer 1

If you are using SQLPlus and you are speaking about substitution variables, you can make this:

column title_column1 new_value vc1
column title_column2 new_value vc2
column title_column3 new_value vc3
select title_column1,title_column2,title_column3 from test_columns;
select &vc1,&vc2,&vc3 from test_data;