& Variables and ' & Variables ' in Oracle

We have a problem with & variable assignment today.

Declare

UID number (10);

Begin

uid:=&x;

End

When &x entered as the empty times the following error

ORA-06550: Line 5th, column 9th:

Pls-00103:encountered the symbol ";" When expecting one of the following:

(-+ case mod new NOT null <an identifier>

<a double-quoted delimited-identifier> <a bind variable>

Continue AVG count current exists max min prior SQL StdDev

Sum Variance execute forall merge time timestamp interval

Date <a string literal with character set specification>

<a number> <a single-quoted SQL string> Pipe

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

< a quoted string literal with Character set description >

< A quotation-able SQL

When &x entered as the character times the following error

ORA-06550: Line 6th, column 25th:

Pls-00306:wrong number or types of arguments in call to ' V '

ORA-06550: Line 6th, column 4th:

Pl/sql:statement ignored

And when the &x is changed to ' &x ',

uid:= ' &x ';

Both the input null and the character can be assigned to the normal value.

So the experiment proves that the & variable is required to enter a numeric value and cannot be null.

Add single quote ' & variable ' to enter any character.

Another record of how to deal with & characters if encountered

1. If encountered in Query column,

Select ' B&r/sc ' from dual;

can be replaced with Chr (38)

Select ' B ' | | Chr (38) | | R/sc ' from dual;

2. If you encounter & characters in a Where condition

SELECT * FROM test where id = ' b&c ';

Can now turn off the & symbol function

Set define off;

Then execute the query statement again.

This article is from "Richard's notes-accumulate micro Cheng" blog, please be sure to keep this source http://zxf261.blog.51cto.com/701797/722326