Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Introduction to Command-Line SQL*Plus Client Tool

By: FYIcenter.com

Part:   1  2  3  4  5  6   7  8  9 

(Continued from previous part...)

What Is Output Spooling in SQL*Plus?

The output spooling a nice feature of the command-line SQL*Plus tool. If the spooling feature is turned on, SQL*Plus will send a carbon copy of the everything on your screen to a specified local file.

Output spooling is used mostly for quick dump of data to local files. Here are the commands to turn on and off output spooling in SQL*Plus:

  • SPOOL fileName - Turning on output spooling with the specified file.
  • SPOOL OFF - Turning off output spooling and close the spool file.

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 tutorial exercise gives you a good example:

SQL> connect HR/retneciyf

SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SET LINESIZE 1000
SQL> SPOOL \temp\employees.lst
SQL> SELECT * FROM EMPLOYEES;
......
SQL> SPOOL OFF

You should get all records in employees.lst with fixed length fields.

What Is Input Buffer in SQL*Plus?

Input buffer is a nice feature of the command-line SQL*Plus tool. It allows you to revise a multiple-line command and re-run it with a couple of simple commands. By default, input buffer is always turned on in SQL*Plus. The last SQL statement is always stored in the buffer. All you need is to remember to following commonly used commands:

  • LIST - Displays the SQL statement (the last executed SQL statement) in the buffer.
  • RUN - Runs the SQL statement in the buffer again. ";" is a quick command equivalent to RUN.
  • CLEAR BUFFER - Removes the SQL statement in the buffer.
  • INPUT line - Adds a new line into the buffer.
  • APPEND text - Appends more text to the last line in the buffer.
  • DEL - Deletes one line from the buffer.
  • CHANGE /old/new - Replaces 'old' text with 'new' text in the buffer.

How To Revise and Re-Run the Last SQL Command?

If executed a long SQL statement, found a mistake in the statement, and you don't want enter that long statement again, you can use the input buffer commands to the correct last statement and re-run it. The following tutorial exercise gives you a good example:

SQL> connect HR/retneciyf

SQL> SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
  2  FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%';
FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%'
     *
ERROR at line 2:
ORA-00942: table or view does not exist

SQL> LIST
  1  SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
  2* FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%'

SQL> CHANGE /EMPLOYEE/EMPLOYEES/
  2* FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%'

SQL> RUN
(Query output)

SQL> INPUT ORDER BY FIRE_DATE
SQL> LIST
  1  SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
  2  FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%'
  3* ORDER BY HIRE_DATE

SQL> RUN
(Query output)

SQL> CLEAR BUFFER
buffer cleared

SQL> LIST
SP2-0223: No lines in SQL buffer.

(Continued on next part...)

Part:   1  2  3  4  5  6   7  8  9 


Selected Developer Jobs:

More...