Spool buffer pool technology exports Oracle data as text files

The Spool buffer pool technology in Oracle can be used to export Oracle Data to text files.

1. Enter the buffer start command in Oracle PL/SQL and specify the output file name:

Spool d:/output.txt

 

2,

Set heading off -- remove the field name

Set time off echo off head off -- this row setting does not calculate the time. It does not show the column names of SQL statements that appear in different rows.

Set pagesize 0 -- remove the blank lines or extra spaces when there are few rows.

Set trims on -- remove all spaces. The default length is not enough to fill spaces.

Set feedback off -- the selected number of rows cannot be associated with the previous row

3. Enter your SQL query in the command line:

Select mobile from customer;

Select mobile from client;

......

3. Enter the buffer result command in the command line:

Spool off;

 

The system outputs the results in the buffer pool to the "output.txt" file, separated by the TAB key!

-----------------------------------------------------------------

Ksh:

Runlog = p_divide_001.log

Nohup sqlplus "datatrans/hw @ szdb1" <!> /Dev/null 2> & 1 &

Set serveroutput on

Set timing on

Set time on

Spool $ runlog

Set autocommit on;

Exec P_DIVIDE_INDEX_new (755, 'cm _ SUBS_SERVICE ', 'subsid', 'P _ DEL_DOUBLE_OPEN ');

Commit;

Spool off

!

 

Or:

Sqlplus-s $ v_dest_tns_user/$ v_dest_tns_pswd @ $ v_dest_tns_db <EOF | grep "DATA. VALUE" | while read s1 s2

Set linesize 512 pagesize 512 head off

Select 'data. value' "FIELDNAME ",

Decode (data_type, 'date', column_name | 'date' yyyy-mm-dd hh24: mi: s''' |

Decode (column_id, $ v_max_column_id ,'',','),

'Varchar2', column_name | 'Char ('| to_char (data_length) |') '|

Decode (column_id, $ v_max_column_id ,'',','),

Column_name | decode (column_id, $ v_max_column_id ,'',','))

From all_tab_columns

Where owner = upper ('$ v_dest_tns_user') and table_name = upper ('$ v_table_name ')

Order by column_id;

EOF

Do

Echo $ s2>./$ v_datafile_name.ctl

Done

 

----------------

Sqlplus-s datatrans/hw @ szdb1 <EOF> SQL. log

Set linesize 1024 pagesize 512 head off

Select * from dt_region;

EOF

 

 

SQL> set echo on -- set whether to display the running command

SQL> set feedback on -- set to display "XX rows selected"

SQL> set colsep | -- sets the delimiter between a column and a column.

SQL> set pagesize 10 -- set the number of rows on each page

SQL> set serveroutput on -- set to allow display of output similar to dbms_output

SQL> set heading on -- set the display column name

SQL> set timing on -- set to display "time in use: XXXX"

SQL> set time on -- set display current time

SQL> set autotrace on -- set to allow analysis of executed SQL statements

 

Set verify off -- enable or disable the display of the message "old 1" and "new 1.

Set colsep ''-- domain output Separator

Set linesize 4000 -- output the number of characters in a line. The default value is 80.

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

Set num 16 (or set numwidth 16) -- output the length of the number type domain. The default value is 10.

Set trimspool on -- remove the trailing space of the redirection (spool) output line. The default value is off.

Set heading off -- output domain title, on by default

Set feedback off -- display the number of records processed by this SQL command. The default value is on.

Set termout off; -- displays the execution results of commands in the script. The default value is on.

Set timing off; -- display the time consumed by each SQL command. The default value is off.

Set trimout on; -- Remove trailing spaces of each standard output line. The default value is off.

Set echo off -- display each SQL command in the start script. The default value is on.