| Spooling Results into Variable File Name.???? [message #36893] |
Tue, 01 January 2002 21:44  |
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  |
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 |
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
----------------------------------------------------------------------
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
