CODEWITHSUNDEEP
plsqloracle-sqldeveloperplsqldeveloper
AlbertAlex
AlbertAlex

Reputation: 57

How to generate spool file in my local folder

I want to generate a spool file with a file name and store it into my local folder whose path I can provide in the code itself. How to do that? Suppose below is the code where I want script to be generated in the separate SQL file

declare
 lv_str varchar2(1000);
begin
for c in(select distinct a.table_name as table_name, 
                b.table_name as parent_table_name ,
                a.owner
           from all_constraints a, 
                all_constraints b 
          where a.r_constraint_name = b.constraint_name 
            and a.constraint_type   = 'R'
            and b.constraint_type   = 'P'
            and a.owner             = b.owner   -- if parent and child belongs to the same schema
            and a.owner             = 'SCOTT'
       ) loop
 lv_str :='DROP TABLE ' || c.owner || '.' || c.table_name || '; -- ' || c.parent_table_name;
 dbms_output.put_Line (lv_str );
 end loop;
end;

/

Also, one small help If I want to drop the table names would it require deleting the parent table as well. If yes, how to delete table_name and parent_table_name?(as per the code) at once.

Upvotes: 0

Views: 2893

Answers (2)

thatjeffsmith
thatjeffsmith

Reputation: 22412

See the 'cd' command, avail in SQL Developer and it's CLI counterpart, SQLcl. SQLcl is like SQLPlus, but better.

This will also work in SQL Developer, just execute via F5.

SQL> cd c:\users\jdsmith\desktop
SQL> set serveroutput on
SQL> spool so4.txt
SQL> declare
  2   lv_str varchar2(1000);
  3  begin
  4  for c in(select distinct a.table_name as table_name,
  5                  b.table_name as parent_table_name ,
  6                  a.owner
  7             from all_constraints a,
  8                  all_constraints b
  9            where a.r_constraint_name = b.constraint_name
 10              and a.constraint_type   = 'R'
 11              and b.constraint_type   = 'P'
 12              and a.owner             = b.owner   -- if parent and child belongs to the same schema
 13              and a.owner             = 'HR'
 14         ) loop
 15   lv_str :='DROP TABLE ' || c.owner || '.' || c.table_name || '; -- ' || c.parent_table_name;
 16   dbms_output.put_Line (lv_str );
 17   end loop;
 18  end;
 19* /
DROP TABLE HR.EMPLOYEES; -- EMPLOYEES
DROP TABLE HR.DBMSHP_FUNCTION_INFO; -- DBMSHP_RUNS
DROP TABLE HR.JOB_HISTORY; -- EMPLOYEES
DROP TABLE HR.DBMSHP_PARENT_CHILD_INFO; -- DBMSHP_FUNCTION_INFO
DROP TABLE HR.EMPLOYEES; -- JOBS
DROP TABLE HR.JOB_HISTORY; -- JOBS
DROP TABLE HR.JOB_HISTORY; -- DEPARTMENTS
DROP TABLE HR.DEPARTMENTS; -- LOCATIONS
DROP TABLE HR.LOCATIONS; -- COUNTRIES
DROP TABLE HR.WINE_REVIEWS; -- WINE_VARIETIES
DROP TABLE HR.DEPARTMENTS; -- EMPLOYEES
DROP TABLE HR.COUNTRIES; -- REGIONS
DROP TABLE HR.EMPLOYEES; -- DEPARTMENTS


PL/SQL procedure successfully completed.

SQL> spool off;
SQL>

Then if I go look at the so4.txt file on my desktop -- enter image description here

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142720

If you're doing it from PL/SQL, then you could set serveroutput on and spool into a local file (but also get some garbage, i.e. code itself):

SQL> spool a.sql
SQL>
SQL> begin
  2    for cur_r in (select tname from tab) loop
  3      dbms_output.put_line('desc ' || cur_r.tname);
  4    end loop;
  5  end;
  6  /
desc DEPT
desc EMP
desc EMPLOYEES

PL/SQL procedure successfully completed.

SQL> spool off

Result:

SQL> $type a.sql
SQL>
SQL> begin
  2    for cur_r in (select tname from tab) loop
  3      dbms_output.put_line('desc ' || cur_r.tname);
  4    end loop;
  5  end;
  6  /
desc DEPT
desc EMP
desc EMPLOYEES

PL/SQL procedure successfully completed.

SQL> spool off

SQL>

Alternatively, use UTL_FILE package, but it creates a file in a directory on a database server (as you have to use Oracle directory object which usually points there). But then you'd get a nice file.

Upvotes: 1

Related Questions