SQL Extensions   «Prev  Next»

Using Line and Text Editors

Your solution should have followed these steps:
  1. You started with a query that had been executed in SQL*Plus and failed. You started to correct this error by making the line with the error the current line for editing. You typed L 5 at the SQL> prompt and pressed Enter.
  2. You used the line editor to fix the error by typing C/P/P.P/ at the SQL> prompt and pressing Enter.
  3. You executed the query by typing / at the SQL> prompt and pressing Enter.
  4. You started the text editor by typing EDIT at the SQL> prompt and pressing Enter.
  5. You added another group function to the query to display the number of times that each product was purchased. You typed , COUNT(P.PRODUCT_ID) at the end of the third line in the file at the SQL> prompt and pressed Enter.
  6. You closed the text editor by clicking the X in the top right corner.
  7. You saved your changes by clicking the Yes button.
  8. You executed the revised query by typing / at the SQL> prompt and pressing Enter.
  9. You saw the results of your query and completed the exercise by clicking Exit.

SQL*Plus Date Format

The default date format in SQL*Plus is determined by the database NLS_DATE_FORMAT parameter and may use a date format displaying two digit years. You can use the SQL TO_CHAR function, or the SQL*Plus COLUMN FORMAT command in your SELECT statements to control the way dates are displayed in your report.


Writing Scripts with System Editor

Your operating system may have one or more text editors that you can use to write scripts. You can run your operating system's default text editor without leaving the SQL*Plus command-line by entering the EDIT command.
You can use the SQL*Plus DEFINE command to define the variable, _EDITOR, to hold the name of your preferred text editor. For example, to define the editor used by EDIT to be vi, enter the following command: 

DEFINE _EDITOR = vi

You can include an editor definition in your user or site profile so that it is always enabled when you start SQL*Plus.
To create a script with a text editor, enter EDIT followed by the name of the file to edit or create, for example: 
EDIT SALES

EDIT adds the filename extension .SQL to the name unless you specify the file extension. When you save the script with the text editor, it is saved back into the same file. EDIT lets you create or modify scripts. You must include a semicolon at the end of each SQL command and a slash (/) on a line by itself after each PL/SQL block in the file. You can include multiple SQL commands and PL/SQL blocks in a script.

Example - Using a System Editor to Write a SQL Script

Suppose you have composed a query to display a list of salespeople and their commissions. You plan to run it once a month to keep track of how well each employee is doing. To compose and save the query using your system editor, invoke your editor and create a file to hold your script: 
EDIT SALES

Enter each of the following lines in your editor. Do not forget to include the semicolon at the end of the SQL statement: 
COLUMN LAST_NAME HEADING 'LAST NAME'
COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999
COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';

The format model for the column COMMISSION_PCT tells SQL*Plus to display an initial zero for decimal values, and a zero instead of a blank when the value of COMMISSION_PCT is zero for a given row.
Now use your editor's save command to store your query in a file called SALES.SQL.