How to generate spool file in my local folder

Question

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.

Answer 1

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 --