Ora-01008 with substitution variables

Question

I have an Oracle query which works fine when I use the command window but does not in an SQL window.

I use Pl/SQL Developer so the command window is like the DOS window.

The query is as follows:

SELECT EMPLOYEE_ID, FIRST_NAME, &&COLS 
FROM EMPLOYEE
ORDER BY &COLS;

So is the error prompted by PL/SQL Developer? Should I use another tool rather than PL/SQL Developer?

In the field list you've written &&COLS but in the ORDER BY clause you've written &COLS. Is there some fancy syntax I don't know about here whereby the variables should be different in the 2 different contexts? — DavidHyogo, Dec 13 '12 at 10:44
I tried to edit your question to make it a bit clearer but I couldn't understand what the word "latter" refers to. — DavidHyogo, Dec 13 '12 at 10:51
the double-Ampersand (&&) is used if we want to resuse the variable value without prompting the user each time. So it one time prompts and the use the value each time it finds the &Var. — Blood-HaZaRd, Dec 13 '12 at 10:57
I see. I recommend you delete "related to this latter". The sentence would be much clearer. — DavidHyogo, Dec 13 '12 at 11:00

score 3 · Accepted Answer · answered Dec 13 '12 at 11:32

The variable substition using the ampersand is a mechanism that's neither part of SQL nor of PL/SQL. It's an extension of SQLplus. The same extension are implemented by Oracle's SQL Developer.

As far as I know, these extension are not implemented by Allround Automations' PL/SQL Developer.

Ora-01008 with substitution variables

1 Answers1