& Variables and ' & Variables ' in Oracle

Source: Internet
Author: User
Tags character set chr numeric value

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.