| Dynamically Spool data in a PL/SQL block [message #571250] |
Thu, 22 November 2012 08:15  |
 |
ba236109
Messages: 1
Registered: November 2012
|
Junior Member |
|
|
I have a requirement as follows.
I have a master table A that has 10+ columns along with data and a configuration table B that has only 2 columns i.e table name and column name. For ex:-
Table A:-
C1 C2 C3 C4 C5 C6
-- -- -- -- -- -----
1 2 3 4 3 6
4 5 6 5 5 5
Table B:-
Table_Name Column_Name
--------- ----------
A C1
A C2
A C3
Now I need to write a sql script that should take the table A Columns defined in Table B and then based on the columns it should spool the columns output from Table A.
For above ex:- I need to spool the C1,C2,C3 columns data from Table A in to some Unix path using SQL SCRIPT.
Request someone to provide suggestions or script if you have some idea
[Updated on: Thu, 22 November 2012 08:16] Report message to a moderator |
|
|
|
|
|
| Re: Dynamically Spool data in a PL/SQL block [message #571253 is a reply to message #571250] |
Thu, 22 November 2012 08:24  |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Here's an example of what you can do:
SQL> select decode(column_id, 1, 'select ', ' , ')||
2 column_name||
3 decode(column_id, 3, '
4 from emp;') s
5 from user_tab_columns
6 where table_name = 'EMP' and column_id <= 3
7 order by column_id;
S
-----------------------------------------------
select EMPNO
, ENAME
, JOB
from emp;
Spool this in a file and execute the file.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
