Home » Other » Client Tools » Dynamically Spool data in a PL/SQL block (oracle 11g)
Dynamically Spool data in a PL/SQL block [message #571250] Thu, 22 November 2012 08:15 Go to next message
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 #571252 is a reply to message #571250] Thu, 22 November 2012 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Dynamically Spool data in a PL/SQL block [message #571253 is a reply to message #571250] Thu, 22 November 2012 08:24 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Not able to see euro symbol in pl/sql developer on windows 7
Next Topic: Execute multiple SQL files in .bat file
Goto Forum:
  


Current Time: Thu Mar 28 12:37:42 CDT 2024