| How can i export outputs from a pl/sql script into a file [message #1908] |
Mon, 10 June 2002 07:00  |
FrAgMaN
Messages: 1
Registered: June 2002
|
Junior Member |
|
|
I am a user of a database and i have a dynamic script which create some tables etc. I want to export this sql orders in a file in order to execute it after.
But it seems that the UTL_FILE package don't work coz UTL_FILE_DIR is not correctly setted in INIT.ORA ( and i am not the administrator )
So, is there a solution to include the sql command 'SPOOL' in a PL/SQL script in order to put outputs in a selected file ?
Thx to answer me :)
|
|
|
|
| Re: How can i export outputs from a pl/sql script into a file [message #1912 is a reply to message #1908] |
Mon, 10 June 2002 09:49  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
you can use something like this. this script creates a drop.sql which drops the objects from the schema.
SQL> get drop
1 set heading off;
2 set feedback off;
3 set termout off;
4 spool c:drop.sql;
5 DECLARE
6 cursor c1 is select table_name,table_type from cat;
7 Str varchar2(100);
8 begin
9 for mag in c1 loop
10 exit when c1%notfound;
11 if mag.table_type='SEQUENCE' Then
12 str:= 'drop sequence '||mag.table_name||';';
13 dbms_output.put_line(str);
14 end if;
15 if mag.table_type='VIEW' Then
16 str:='drop view '||mag.table_name||';';
17 dbms_output.put_line(str);
18 end if;
19 if mag.table_type='TABLE' Then
20 str:='drop table '||mag.table_name||';';
21 dbms_output.put_line(str);
22 end if;
23 end loop;
24 end;
25 /
26 spool off;
27 set heading on;
28 set feedback on;
29* set termout on;
SQL> @drop
and contents of drop.sql are
drop viewAUD;
drop table AUDIT_TABLE;
drop table BONUS;
drop table D;
drop table DEPT;
drop table DEPT_BACK;
drop table EMAILAUDIT;
drop sequence EMAILMSGSEQ;
drop table EMP;
drop table EMP1;
drop table EMP2;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
