Suppose you want to write a query like the one in SALES (see Example 5-1, "Using a System Editor to Write a SQL Script") to list the employees with various jobs, not just those whose job is SA_MAN. For example, if you enter: SELECT 'Y' FROM DUAL - TESTING For example: CREATE OR REPLACE PROCEDUREĭo not put comments after statement terminators (period, semicolon or slash). SQL*Plus submits the PL/SQL block to the server when it sees the slash "/" at the beginning of the comment, which it interprets as the "/" statement terminator. The location of the comment prevents SQL*Plus from recognizing the command as a command.
Warning: Procedure created with compilation errors. The following usage notes may help you to use SQL*Plus comments more effectively:ĭo not put comments within the first few keywords of a statement. Comments in some locations can prevent SQL*Plus from correctly identifying the command type, giving unexpected results. It scans the first few keywords of each new statement to determine the command type, SQL, PL/SQL or SQL*Plus. SQL*Plus does not have a SQL or PL/SQL command parser. These are useful if you want to correct or modify a command you have entered.
Table 5-1 SQL*Plus Editing Commands Command Now use your editor's save command to store your query in a file called SALES.SQL.
Format models and the COLUMN command are described in more detail in the COLUMN command and in the Oracle Database SQL Language Reference. 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. Do not forget to include the semicolon at the end of the SQL statement: COLUMN LAST_NAME HEADING 'LAST NAME'ĬOLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999ĬOLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90 To compose and save the query using your system editor, invoke your editor and create a file to hold your script: EDIT SALESĮnter each of the following lines in your editor. You plan to run it once a month to keep track of how well each employee is doing. Suppose you have composed a query to display a list of salespeople and their commissions. Įxample 5-1 Using a System Editor to Write a SQL Script You can include multiple SQL commands and PL/SQL blocks in a script. 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. When you save the script with the text editor, it is saved back into the same file.
SQL to the name unless you specify the file extension. 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ĮDIT adds the filename extension. See SQL*Plus Configuration, and the DEFINE and EDIT commands for more information. You can include an editor definition in your user or site profile so that it is always enabled when you start SQL*Plus. For example, to define the editor used by EDIT to be vi, enter the following command: DEFINE _EDITOR = vi You can use the SQL*Plus DEFINE command to define the variable, _EDITOR, to hold the name of your preferred text editor. You can run your operating system's default text editor without leaving the SQL*Plus command-line by entering the EDIT command. Your operating system may have one or more text editors that you can use to write scripts.