使用sqlplus时要注意的几个问题

使用sqlplus时要注意的几个问题

by fuyuncat

1.1 ‘&’:

The first thing we should be care when using SQL*Plus or PL/SQL Developer is the start character of substitution variable reference, which default is ‘&’. Its effective scope is whole PL/SQL block, including your defined variable in the PL/SQL block or the comments of PL/SQL block. If the string following ‘&’ is a valid naming-string of PL/SQL, it will be treated as a input variable. For example:

SQL> declare

2 v_aaa varchar2(10):= 'aaa&b.bb'; --this is the comment, but include aaa&ccc also.

3 begin

4 dbms_output.put_line(v_aaa);

5 end;

6 /

Enter value for b:

Enter value for ccc:

old 2: v_aaa varchar2(10):= 'aaa&b.bb'; --this is the comment, but include aaa

&ccc also.

new 2: v_aaa varchar2(10):= 'aaabb'; --this is the comment, but include aaa al

so.

aaabb

PL/SQL procedure successfully completed.

We have many methods to avoid it. Such as using ’set’ command, convert ‘&’ to ASCII code. We recommend just split it isolated, or quote it by ‘”’:

SQL> declare

2 v_aaa varchar2(10):= 'aaa'||'&'||'b.bb';--this is the comment, but include aaa"&"ccc also.

3 begin

4 dbms_output.put_line(v_aaa);

5 end;

6 /

aaa&b.bb

PL/SQL procedure successfully completed.

1.2 ‘/’:

Another thing we should remember when using PL/SQL Developer or SQL*Plus is we should add a ‘/’ at the end of each PL/SQL scripts. Because the character means ask oracle to execute the PL/SQL block. If missing it, SQL*Plus will never execute the script and it will not be ended:

SQL> declare

2 v_aaa varchar2(10):= 'aaa';

3 begin

4 dbms_output.put_line(v_aaa);

5 end;

6

7

8

… …

1.3 Set command:

The ‘set’ command is using to set the environment of SQL*Plus and PL/SQL Developer. It can help us to format the select result. But be careful when using it. Because it will impact the following scripts. For example, when you set scan off in the first script, then you want accept input variable. But the ‘&’ will not effect at that time when executing the second script.

To recover all variables to be default value, we suggest to exit current session and recreate another one.

1.4 Spool

Spool is a very useful command. It can let you get the result report of a set of commands, including SQL commands and SQL*Plus commands. You can use ‘set’ or other commands to customize your report. However, if you use SQL*Plus to get the report, you will not get the result you want if you does not execute the commands in a scripts file. For example, if you execute these commands in SQL*Plus dierectly:

08:58:36 SQL> set echo off

08:58:44 SQL> spool c:test.txt

08:58:51 SQL> select sysdate from dual;

SYSDATE

---------

06-DEC-05

08:59:00 SQL> spool off

You wanna get this result:

SYSDATE

---------

06-DEC-05

But if you open the file ‘c:test.txt’, you will find the content of it is:

08:58:51 SQL> select sysdate from dual;

SYSDATE

---------

06-DEC-05

08:59:00 SQL> spool off

You must write those commands in a script file, e.g. ‘c:test.sql’, and execute it

09:06:07 SQL> @c:test.sql

SYSDATE

---------

06-DEC-05

And you can find its result is what you want.

For PL/SQL developer, it is another fact: you can get the same result whenever execute these commands in command window directly or execute the script file including them.

1.5 Comments in script

When we add comments in a script file, we should very careful. Comments will lead to unexpected result in some special conditions.

1.5.1 An English letter follows closely in comment symbol '/*'

In SQL*Plus, it will lead the SQL statement before the comment to be executed twice. For example, the script file “test.sql” includes follow SQL statements:

select 'aaa' from dual;

/*abc*/

select 'bbb' from dual;

We just want the statement ‘select 'aaa' from dual;’ to be executed once. However, we will get another result:

SQL> @c:test.sql

'AA

---

aaa

'AA

---

aaa

'BB

---

bbb

As you can see, the statement were executed twice.

This bug would not impact PL/SQL developer.

And this is the correct script (there is a blank char between ‘/*’ and ‘abc’):

select 'aaa' from dual;

/* abc */

select 'bbb' from dual;

The right result:

SQL> @c:test.sql

'AA

---

aaa

'BB

---

bbb

1.5.2 Commends follow a SQL statement

If a comment follows a SQL statement (the comments including ‘--’ mode and ‘/**/’ mode), this statement will be treated as comments, and will not be executed. For example, here a ‘test.sql’ script:

select 'aaa' from dual; --aaa

select 'bbb' from dual;

You will get this result when execute it:

SQL> @c:test.sql

'BB

---

bbb

Noted, the first SQL statement was not executed, because it was followed by a comments with ‘--’.

And, if the comment was placed in another line follow closely the SQL statement, it will also raise this bug:

select 'aaa' from dual;

--aaa

select 'bbb' from dual;

Execute the script:

SQL> @c:test.sql

'BB

---

bbb

This bug impact both SQL*Plus and PL/SQL developer.

And this is the correct script:

-- aaa

select 'aaa' from dual;

select 'bbb' from dual;

The right result:

SQL> @c:test.sql

'AA

---

aaa

'BB

---

bbb

1.5.3 Commends following a SQL statement, followed by another SQL statement closely

If a commend follows a SQL statement, and it was followed by another SQL statement closely. It will lead to a execution error. For example, this is the script:

select 'aaa' from dual; --aaa

select 'bbb' from dual;

Execute the script:

SQL> @c:test.sql

select 'aaa' from dual; --aaa

*

ERROR at line 1:

ORA-00911: invalid character

This bug will ignore the comments between them. For example, this is the script:

select 'aaa' from dual;--aaa

/* abc */

select 'bbb' from dual;

It will still raise the error:

SQL> @c:test.sql

select 'aaa' from dual;--aaa

*

ERROR at line 1:

ORA-00911: invalid character

This bug impact both SQL*Plus and PL/SQL developer.

And this is the correct script:

-- aaa

select 'aaa' from dual;

select 'bbb' from dual;

The right result:

SQL> @c:test.sql

'AA

---

aaa

'BB

---

bbb

请教
一个脚本中用了多个替代变量，当在SQL*PLUS命令行执行这个脚本时可不可以像在DOS中的命令行输入，即参数写在脚本文件之后如：
SQL>@xxxx.SQL SEGMENT1 SEGMENT2
我试过不行，有没别的方法？