Tuesday, January 27, 2009

Working with Sql*plus Scripts

Running Sql Scripts
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


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

1 comment:

  1. Very informative error.Thank you author for posting this kind of errors .


    http://www.wikitechy.com/fix-error/ORA-00911-invalid-character-xsql-error



    Both are really good.
    Cheers,
    Venkat

    ReplyDelete