Home » SQL & PL/SQL » SQL & PL/SQL » Spooling Results into Variable File Name.????
Spooling Results into Variable File Name.???? [message #36893] Tue, 01 January 2002 21:44 Go to next message
Prasad
Messages: 104
Registered: October 2000
Senior Member
Hai
I Got a query Which Prints table names, & would like to Utilize these table names as different Spool files.
like t1.lst,t2.lst,t3.lst so on,.....
Please help me in this regards, Quite Urgent.

Thanx in advance
Prasad

----------------------------------------------------------------------
Re: Spooling Results into Variable File Name.???? [message #36898 is a reply to message #36893] Wed, 02 January 2002 04:41 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
I dont know what script you got, script below
spools all your tables data into different files.

step 1)Save script below in .sql file (say data.sql)

set feed off
set head off
set term off
set newpage none
spool tab.sql
select 'spool '||table_name||'.txt'||chr(10)
||'select * from '||table_name||';'||chr(10)||'spool off' from user_tables;
spool off
@tab.sql
set term on
set head on
set feed on
set newp 1

step 2) run at sqlprompt.

SQL > @data.sql

step 3) go and check for data files in default
directory of sqlplus or if you want your data
files in specific directory (say xyz directory),
change script slightly..

set feed off
set head off
set term off
set newpage none
spool tab.sql
select 'spool c:xyz'||table_name||'.txt'||chr(10)
||'select * from '||table_name||';'||chr(10)||'spool off' from user_tables;
spool off
@tab.sql
set term on
set head on
set feed on
set newp 1

Happy new year
Suresh

----------------------------------------------------------------------
Re: Spooling Results into Variable File Name.???? [message #36906 is a reply to message #36893] Wed, 02 January 2002 21:02 Go to previous messageGo to next message
Prasad
Messages: 104
Registered: October 2000
Senior Member
Thanx a Lot Suresh, U'r Logic Helped me a lot to Implement What I planned for,Infact I am using cursors to retrive filenames,I have One more Doubt in Getting hierachry of my database, Can U help me in this aspect, My Scripts is as follows,

cursor hierarcy_tree is
SELECT s_cls_name from s_cls_table where obj_id in
( SELECT s_cll_cls_id_parent FROM s_cll_table
START WITH s_cll_cls_id_child =
( select obj_id from s_cls_table where s_cls_label = class_label )
CONNECT BY s_cll_cls_id_child = PRIOR s_cll_cls_id_parent )
and s_cls_type = 'O'
and s_cls_name <> 'V_ELEC_ELECTRONIC';
UNION
select s_cls_name from s_cls_table where s_cls_label = class_label;
BEGIN

for seq in hierarcy_tree
loop
insert into tmp_hierarchy_table values ( seq.s_cls_name );
-- if seq.s_cls_name = 'V_PRT_CLASS' then
-- insert into tmp_hierarchy_table values ( 'V_ELEC_ELECTRONIC' );
-- end if;
end loop;

insert into tmp_hierarchy_table ( select s_cls_name
from s_cls_table where s_cls_label = (class_label) );
END;

I am getting the Result, But not expected one I am not getting in heirachial fashion,
S_ROT_CLASS
V_DRAM_CLASS
V_ELEC_ELECTRONIC
V_IC_CLASS
V_MEM_CLASS
V_PRT_CLASS

But I wanted in this way
S_ROT_CLASS
V_ELEC_ELECTRONIC
V_PRT_CLASS
V_IC_CLASS
V_MEM_CLASS
V_DRAM_CLASS

Can U just Look into this problem

----------------------------------------------------------------------
Re: Spooling Results into Variable File Name.???? [message #36912 is a reply to message #36893] Thu, 03 January 2002 10:24 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
can u provide sample data of your tables?.

----------------------------------------------------------------------
Previous Topic: Member Functions of Oracle Objects
Next Topic: PLS-00201 error ...
Goto Forum:
  


Current Time: Fri Jun 05 15:19:01 CDT 2020