Practical application of Oracle sqlplus to format data

The following articles mainly describe the practical application of Oracle sqlplus data formatting. We all know that spool is useful in practical applications, however, it is not familiar with the set command on the formatted data, so I found it online and noted it below:

 
 

  
  
SQL>set colsep' ';  
 
 

Domain output Separator

 
 

  
  
SQL>set newp none  
 
 

Set the number of pages to display the queried data. If continuous data is required, set newp to none if there are no blank rows in the middle, so that the output data rows are continuous, there are no blank lines in the middle.

 
 

  
  
SQL>set echo off;  
 
 

Displays each SQL command in the start script. The default value is on.

 
 

  
  
SQL> set echo on 
 
 

Set whether to display the statement when running the command.

 
 

  
  
SQL> set feedback on; 
 
 

Set to display "XX rows selected"

 
 

  
  
SQL>set feedback off;　  
 
 

Display the number of records processed by this SQL command. By default, on is used to remove the last "10000 rows selected"

 
 

  
  
SQL>set heading off;  
 
 

Output field title. The field name of the select result is removed when the on field is set to off by default. Only the formatted data of Oracle sqlplus is displayed.

 
 

  
  
SQL>set pagesize 0;  
 
 

The number of output lines per page. The default value is 24. To avoid paging, you can set the value to 0.

 
 

  
  
SQL>set linesize 80;  
 
 

Output the number of characters in a row. The default value is 80.

 
 

  
  
SQL>set numwidth 12;　 
 
 

Length of the output number field. The default value is 10.

 
 

  
  
SQL>set termout off; 
 
 

Displays the execution results of commands in the script. The default value is on.

 
 

  
  
SQL>set trimout on;　 
 
 

Removes trailing spaces of each standard output line. The default value is off.

 
 

  
  
SQL>set trimspool on; 
 
 

Remove redirection spool) the trailing space of each row is output. The default value is off.

 
 

  
  
SQL>set serveroutput on; 
 
 

Set to allow display of output similar to dbms_output

 
 

  
  
SQL> set timing on; 
 
 

Set to display "used time: XXXX"

 
 

  
  
SQL> set autotrace on-; 
 
 

Set to allow analysis of executed SQL statements

 
 

  
  
set verify off 
 
 

You can disable or enable the display of the message "old 1" and "new 1.

Oracle export data to a file:

Sqlplus/nolog @ d: \ sp. SQL

D: \ sp. SQL:

 
 

  
  
set head off  
  
  
set headsep off  
  
  
set newp none  
  
  
set linesize 100  
  
  
set pagesize 10000  
  
  
set sqlblanklines OFF  
  
  
set trimspool ON  
  
  
set termout off  
  
  
set feedback off  
  
  
spool d:\export.txt  
  
  
select NODECODE||'&'||NODETYPE||'&'||NODENAME||'&' from "000".tbnode;  
  
  
spool off  
  
  
exit   
 
 

The above content is an introduction to Oracle sqlplus formatting data. I hope you will find some gains.