SQL/PLUS learning notes ECHO and SPOOL usage

Last time we introduced:SQL/PLUS Study NotesTo edit the current line of commands in the buffer, this article describes the SQL/PLUS learning notesECHOAndSPOOLNext, let's take a look at this part.

ECHO parameter settings:

SQL> show echo -- display the echo status

Echo OFF -- at this time, echo is OFF.

SQL> set echo on -- set it to on

SQL> show echo

Echo ON -- Enabled

When the script is run, each SQL statement or PL/SQL block in the script is displayed on the terminal. Run the test script as follows:

 
 

  
  
SQL> @ test -- the following content is displayed on the terminal.
  
  
SQL> select * from t1 where rownum <2;
  
  
 
  
  
ID NAME
  
  
------------------------------
  
  
1 Testing
  
  
 
  
  
SQL>
  
  
SQL> truncate table t1;
  
  
 
  
  
Table truncated.
  
  
 
  
  
SQL>
  
  
SQL> begin
  
  
2
  
  
3 for I in 1 .. 1000 loop
  
  
4
  
  
5 insert into t1 values (I, 'testing ');
  
  
6 end loop;
  
  
7 commit;
  
  
8
  
  
9 end;
  
  
10/
  
  
 
  
  
PL/SQL procedure successfully completed.
  
  
 
  
  
SQL>
  
  
SQL> select count (*) from t1;
  
  
 
  
  
COUNT (*)
  
  
----------
  
  
1000
 
 

Use SPOOL to save the query result set

 
 

  
  
SQL> spool outputfile -- The outputfile. lst file is generated in the current path by default.
  
  
SQL> select * from t1 where rownum <3;
  
  
 
  
  
ID NAME
  
  
------------------------------
  
  
1 Testing
  
  
2 Testing
  
  
 
  
  
SQL> spool off -- terminate. All the content is written to the file outputfile.
  
  
 
  
  
[Oracle @ localhost ~] $ Cat outputfile. lst -- view the file content as follows:
  
  
SQL> select * from t1 where rownum <3;
  
  
 
  
  
ID NAME
  
  
------------------------------
  
  
1 Testing
  
  
2 Testing
  
  
 
  
  
SQL> spool off
 
 

This function can help generate some dynamic batch processing scripts, such as deleting tables that meet certain conditions in the user's emcd:

 
 

  
  
SQL> show user
  
  
USER is "EMCD"
  
  
SQL> spool droptable. SQL
  
  
SQL> select 'drop table' | objec_name from user_objects where object_type = 'table ';
  
  
Select 'drop table' | objec_name from user_objects where object_type = 'table'
  
  
*
  
  
ERROR at line 1:
  
  
ORA-00904: "OBJEC_NAME": invalid identifier
  
  
 
  
  
SQL> l
  
  
1 * select 'drop table' | objec_name from user_objects where object_type = 'table'
  
  
SQL> c/objec _/object _
  
  
1 * select 'drop table' | object_name from user_objects where object_type = 'table'
  
  
SQL>/
  
  
 
  
  
'Droptable' | OBJECT_NAME
  
  
--------------------------------------------------------------------------------
  
  
Drop tableT1
  
  
Drop tableBIN $ qokoVJ6g4HHgQAB/AQAzMg = $0
  
  
Drop tableTOAD_PLAN_TABLE
  
  
 
  
  
SQL> l
  
  
1 * select 'drop table' | object_name from user_objects where object_type = 'table'
  
  
SQL> c/'drop table'
  
  
1 * select 'drop table' | object_name from user_objects where object_type = 'table'
  
  
SQL>/
  
  
 
  
  
'Droptable' | OBJECT_NAME
  
  
--------------------------------------------------------------------------------
  
  
Drop table T1
  
  
Drop table BIN $ qokoVJ6g4HHgQAB/AQAzMg = $0
  
  
Drop table TOAD_PLAN_TABLE
  
  
 
  
  
SQL> spool off -- end input
  
  
SQL>!
  
  
 
  
  
[Oracle @ localhost ~] $ Cat droptable. SQL -- view the output content, as shown below:
  
  
 
  
  
SQL> select 'drop table' | objec_name from user_objects where object_type = 'table ';
  
  
Select 'drop table' | objec_name from user_objects where object_type = 'table'
  
  
*
  
  
ERROR at line 1:
  
  
ORA-00904: "OBJEC_NAME": invalid identifier
  
  
 
  
  
 
  
  
SQL> l
  
  
1 * select 'drop table' | objec_name from user_objects where object_type = 'table'
  
  
SQL> c/objec _/object _
  
  
1 * select 'drop table' | object_name from user_objects where object_type = 'table'
  
  
SQL>/
  
  
 
  
  
'Droptable' | OBJECT_NAME
  
  
--------------------------------------------------------------------------------
  
  
Drop tableT1
  
  
Drop tableBIN $ qokoVJ6g4HHgQAB/AQAzMg = $0
  
  
Drop tableTOAD_PLAN_TABLE
  
  
 
  
  
SQL> l
  
  
1 * select 'drop table' | object_name from user_objects where object_type = 'table'
  
  
SQL> c/'drop table'
  
  
1 * select 'drop table' | object_name from user_objects where object_type = 'table'
  
  
SQL>/
  
  
 
  
  
'Droptable' | OBJECT_NAME
  
  
--------------------------------------------------------------------------------
  
  
Drop table T1
  
  
Drop table BIN $ qokoVJ6g4HHgQAB/AQAzMg = $0
  
  
Drop table TOAD_PLAN_TABLE
  
  
 
  
  
SQL> spool off
 
 

In this way, the SQL statement for dynamically deleting some tables is generated.

Here is the introduction of ECHO and SPOOL in SQL/PLUS study notes. I hope this introduction will be helpful to you!