An alternative way to edit the SQL*Plus buffer is to use the text editor within your operating system. The exact editor used depends on which operating system you are using. For example, if you are using Windows, the editor invoked is Microsoft Notepad or download Notepad++. Invoke the editor by typing the following command and pressing Enter.
EDIT
When the editor is invoked, SQL*Plus creates a file named AFIEDT.BUF and writes the SQL*Plus buffer into the file. After making changes, when you are saving and closing the file, SQL*Plus copies the modified file back into the SQL*Plus buffer and prints it on the screen.
Go through the following series of images for a quick demonstration of invoking and using the text editor.
Using Text Editor with SQL*Plus
SELECT FIRSTNAME, LASTNAME
FROM CUSTOMER C
WHERE C.CUST_ID
IN (1,2,3,4)
1) Here is the query that you created in the simulation in the previous lesson. This time the query will be changed quite a bit, so it is better to use the text editor method.
2) To invoke the text editor, type EDIT and press ENTER
3) SQL*Plus places the current query into a file and opens the file with the text editor.
4) After adding some lines to the query and modifying the SELECT clause, the query is ready to test. Clicking the X in the top right corner of Windows Notepad closes the editor.
5) Notepad displays a window asking you to save changes. Clicking the Yes button saves the changes.
6) SQL*Plus writes the contents of the file into the current buffer and displays it in the SQL* Plus window.
7) Type a forward slash and press ENTER to execute the query.
8) You have an error in your code. Type EDIT and press ENTER to go back to the text editor.
9) The corrections have been made using the text editor. Once again, click the X in the top right corner to close the text editor.
10) Notepad asks about saving your changes. Click the Yes button to save changes and return to SQL* plus.
11) The modified query is now ready to execute in SQL* Plus. Type a forward slash and press ENTER to execute.
12) The query successfully executes and displays the results
To start SQL*Plus and connect to the default database
Open a UNIX or a Windows terminal and enter the SQL*Plus command: sqlplus
When prompted, enter your Oracle Database username and password. If you do not know your Oracle Database username and password, ask your Database Administrator.
Alternatively, enter the SQL*Plus command in the form: sqlplus username
You are prompted to enter your password.
SQL*Plus starts and connects to the default database. Now you can start entering and executing SQL, PL/SQL and SQL*Plus statements and commands at the SQL> prompt.
To start SQL*Plus and connect to a database other than the default
Open a UNIX or a Windows terminal and enter the SQL*Plus command:
sqlplus username@connect_identifier
You are prompted to enter your password.
SQL Style
First, some comments on style. SQL*Plus does not care whether the SQL commands you type are in uppercase or lowercase.For example, the command
SeLect feaTURE, section, PAGE FROM newsPaPeR;
will produce exactly the same result as this one:
select Feature, Section, Page from NEWSPAPER;
Case matters only when SQL*Plus or Oracle is checking an alphanumeric value for equality. Aside from this usage, case is completely irrelevant. As a matter of style, this module follows certain conventions about case to make the text easier to read: select, from, where, order by, having, and group by will always be lowercase and boldface in the body of the text. SQL*Plus commands also will be lowercase and boldface (for example, column, set, save, ttitle, and so on). IN, BETWEEN, UPPER, and other SQL operators and functions will be uppercase and boldface.
Column names will be mixed uppercase and lowercase without boldface (for example, Feature, EastWest, Longitude, and so on). Table names will be uppercase without boldface (for example, NEWSPAPER, WEATHER, LOCATION, and so on). You may want to follow similar conventions in creating your own queries, or your company already may have standards it would like you to use. The goal of any such standards should always be to make your work simple to read and understand. The following section contains information with respect to editing commands for saving and retrieving your own files in SQL*Plus.
Using Line and Text Editors
Your solution should have followed these steps:
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.
You used the line editor to fix the error by typing C/P/P.P/ at the SQL> prompt and pressing Enter.
You executed the query by typing / at the SQL> prompt and pressing Enter.
You started the text editor by typing EDIT at the SQL> prompt and pressing Enter.
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.
You closed the text editor by clicking the X in the top right corner.
You saved your changes by clicking the Yes button.
You executed the revised query by typing / at the SQL> prompt and pressing Enter.
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.
