>While using the SQL*Plus tool to query data I saw on the Menu the following options : File--->Spool---->SpoolFile.../Spool off.
>Does anyone knows the purpose of this items.??
>It looks like they are used to send data to a file. But I am not clear on the Purpose/functionality of Spool off.
>
>Sergio O.
What this lets you do is spool everything echoed to the screen to a file. For example, the following will create a SQL script file that can be run to drop all your tables:
set head off
set feedback off
spool c:\scripts\drop_tables.sql
select 'drop table ' || table_name || ';' from user_tables;
spool off
set head on
set feedback on
It is safe to execute all those lines of code from SQL Plus. If you really wanted to then drop all your tables, execute the following from SQL Plus after running the above commands [assuming you have a C:\Scripts folder]:
@C:\Scripts\Drop_Tables
What the select command does is concatenate the literal string 'drop table ' using the doulble verticle bars || the the value in the table_name column in the user tables. SQL Plus requires each command end with a semi-colon, so I also concatenate a semi-colon at the end of each command.
A drop table command is created for each table found in the user_tables table.
Try the code above to create the script file. Then use Notepad to open the DROP_TABLES.SQL file. It is just a text file. You can then use this technique to crate numerous script files to help you manage your database.
Mark McCasland
Midlothian, TX USA