Environment settings for SQL*PLUS
                                                 Last update (2008-04-04 10:30:41)
                                                                                                                   Date added (2008-04-03 19:51:09)

Summary
SQL*Plus environment is controlled a big list of SQL*Plus system settings. You can change them by using the SET command as shown in the following list:
    * SET AUTOCOMMIT OFF - Turns off the auto-commit feature.
    * SET FEEDBACK OFF - Stops displaying the "27 rows selected." message at the end of the query output.
    * SET HEADING OFF - Stops displaying the header line of the query output.
    * SET LINESIZE 256 - Sets the number of characters per line when displaying the query output.
    * SET NEWPAGE 2 - Sets 2 blank lines to be displayed on each page of the query output.
    * SET NEWPAGE NONE - Sets for no blank lines to be displayed on each page of the query output.
    * SET NULL 'null' - Asks SQL*Plus to display 'null' for columns that have null values in the query output.
    * SET PAGESIZE 60 - Sets the number of lines per page when displaying the query output.
    * SET TIMING ON - Asks SQL*Plus to display the command execution timing data.
    * SET WRAP OFF - Turns off the wrapping feature when displaying query output.
How To Look at the Current SQL*Plus System Settings?
For example for one setting
SQL> SHOW TIMING
timing OFF
Or for all settings
SQL> SHOW ALL
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
How To Save Query Output to a Local File?
Normally, when you run a SELECT statement in SQL*Plus, the output will be displayed on your screen. If you want the output to be saved to local file, you can use the "SPOOL fileName" command to specify a local file and start the spooling feature. When you are done with your SELECT statement, you need to close the spool file with the "SPOOL OFF" command. The following gives you a good example:
SQL> connect apps/apps@OTE1
SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SET LINESIZE 1000
SQL> SPOOL /filesystem03/users.txt
SQL> SELECT * FROM FND_USER;
......
SQL> SPOOL OFF
You should get all records in users.txt with fixed length fields.
Reviews
Filters
Search