CODEWITHSUNDEEP
sqlplsqlsqldatatypes
user1252398
user1252398

Reputation: 1149

what is the difference between the & and : in PL/SQL in oracle?

I am stuying PL/SQL & I am going through the bind variable . I understood that the bind variable we used to pass RUN-TIME values.

but what is the difference between & and :(colon) in PL/SQL? both are same or is their any difference between this two? when should I use & and : ?

Upvotes: 5

Views: 16181

Answers (2)

René Nyffenegger
René Nyffenegger

Reputation: 40499

The & is used in SQL*Plus only, it has no meaning outside of it.

While SQL*Plus "parses" the input buffer, it replaces &variables with what they were defined to. See also this link and this link. The technical term is substitution variable.

On the other hand :variable are real bind variables. They are used when Oracle's SQL engine "parses" and executes the SQL statement. See for example this link or this link.

So, in short, &variables are replaced by SQL*Plus and then passed to Oracle's SQL engine, while :variables are left untouched by SQLPlus and passed to Oracle's SQL engine as they appear. Outside fo SQLPlus, & makes no sense.

Upvotes: 13

Tony Andrews
Tony Andrews

Reputation: 132580

& has no meaning in PL/SQL, it is actually a SQL Plus feature (that has been copied in Toad, SQL Developer etc. for compatibility). In SQL Plus, & is used to define a substitution variable, which is replaced with specified replacement text by SQL Plus before the code is sent to the server to be processed.

The correct syntax for a SQL Plus substitution variable is &name. i.e. is begins with a & and ends with a ., but in most situations the . is optional. So when you run this code in SQL Plus:

select * from emp where ename = '&name.';

SQL Plus prompts the user for a value for name and substitues this into the query before it is run. So if the user enters KING then the SQL actually run is:

select * from emp where ename = 'KING';

Upvotes: 4

Related Questions