To run a sql scripts in Sql*plus use @ or @@ or start command. To run a script named C:\test.sql use,
@c:\test
or,
@@c:\test
or,
start c:\test
Define editor and editing sql scripts on command line
Have a look at
Say yes to Sql*Plus Sql*Plus Tips
In addition,
CLEAR BUFFER (CL BUFF): Will clear buffer, i.e delete all lines from the Sql buffer.
To insert a line before line 1, enter a zero ("0") and follow the zero with text.
0 SELECT Username
Placing comments in Sql Scripts
1)Using Sql*plus REMARK command: If a line starts with REMARK rest words of the line are considered as comment and hence ignored to compile. This is applied to single line only.
Example:
REMARK This script will generate the salary history
REM This script is generated on 11/01/2008
2)Using /*...*/: In order to comment multiple lines start with /* and until it gets */ entire commands inside it are considered as comments. With /*...*/ you can also comment a single line.
You must enter a space after the slash-asterisk(/*).
Example:
/* Column Salary for 9999
Column Name for a30
*/
3)Using --: You can use ANSI/ISO "- -" style comments within SQL statements, PL/SQL blocks, or SQL*Plus commands. This comment cannot span multiple lines.
To span it multiple lines start each line with --.
Example:
--This is a comment.
Issues while placing comments in Sql Scripts
SQL*Plus does not have a SQL or PL/SQL command parser.
It scans the first few keywords of each new statement to determine the command type, whether it is SQL, PL/SQL or SQL*Plus.
Comments in some locations can prevent SQL*Plus from correctly identifying the command type, and thus give unexpected results.
Rules
1)Do not put comments within the first few keywords of a statement. Beow is an example. In first case I commented before procedure keyword and thus PL/SQL block terminates before it is expected and in the second case, it is successfully created because of right select for comment.
SQL> create or replace
2 /* This is a comment */
3 procedure test_proc as
4 begin
5 dbms_output.put_line('Test Comment');
Warning: Procedure created with compilation errors.
SQL> end;
SP2-0042: unknown command "end" - rest of line ignored.
SQL> /
Warning: Procedure created with compilation errors.
SQL> create or replace procedure
2 /* This is a comment */
3 test_proc as
4 begin
5 dbms_output.put_line('Test Comment');
6 end;
7 /
Procedure created.
2)Do not put comments after statement terminators (period, semicolon or slash).
Example:
SQL> select sysdate from dual; --This is a comment
2
SQL> /
select sysdate from dual; --This is a comment
*
ERROR at line 1:
ORA-00911: invalid character
3)Don't put statement termination character at the end of the comment. For example,
select sysdate
--This is comment;
from dual;
statement will fail because statement termination occurs in the comments.
SQL> select sysdate from dual; --This is a comment
2
SQL> /
select sysdate from dual; --This is a comment
*
ERROR at line 1:
ORA-00911: invalid character
SQL> select sysdate
2 --This is comment;
--This is comment
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
SQL> from dual;
SP2-0042: unknown command "from dual" - rest of line ignored.
However, if we omit semicolon from comment then statement successfully executed.
SQL> select sysdate
2 --This is comment
3 from dual;
SYSDATE--
---------
29-NOV-08
4)Do not use ampersand characters '&' in comments in a SQL statement or PL/SQL
block. Because & is identified as substitution variable unless if you set SET DEFINE OFF to prevent scanning for the substitution character.
Example:
SQL> select sysdate
2 --Comment &
3 from dual;
SP2-0317: expected symbol name is missing
SYSDATE--
---------
29-NOV-08
SQL> set define off
SQL> select sysdate
2 --Comment &
3 from dual;
SYSDATE--
---------
29-NOV-08
Related Documents:
-------------------------------------------------------
How to set Environmental variable to SQL*Plus permanently
-------------------------------------------------------
How to set Environmental variable to SQL*Plus permanently
I couldnt suddenly figure out the reason for "missing symbol" error. Thanks to your blog showing up in the first page of google search, I remembered that I added that "&" in one of my comments.
ReplyDelete