what is the difference between the & and : in PL/SQL in oracle?
plsql, sql, sqldatatypes
Solution
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 `define`d 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.
Problem
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 : ?