Substitution in Oracle XE

Did any of you guys succeed in getting correct outputs when using the substitution variables?

Like for example, Lesson 2 page 26, the following code read:

Language: SQL
SELECT last_name, department_id, salary*12
FROM employees
WHERE job_id = ';:job_title';;

I get an error until I do this to the WHERE clause:

Language: SQL
WHERE job_id = :';job_title';;

Then on page 27,

Language: SQL
SELECT employee_id, last_name, job_id, :column_name
FROM employees
WHERE :condition
ORDER BY :order_column;

doesn't work as well. I get the screen coming up for inputting the variables, but the output does not come up, because an error relating to the comparison in the condition (WHERE clause).
When I do this

Language: SQL
WHERE salary > 15000

the other two substitution in the SELECT clause and the ORDER BY clause works, except for the fact that the column name doesn't actually appear, instead comes up as :COLUMN_NAME on the heading of the table.

And, lastly, how do we do

Language: SQL
&&column_name

in Oracle XE.

I assumed it would be

Language: SQL
::column_name

which works, but I still don't get any output of table coming up, which I assume is because of the substitution again being in the SELECT clause.

Any ideas on how to overcome this, as I'm stumped.

Ronica

WHERE job_id = :'job_title'; didn't work for me.

this works though...



SELECT employee_id, last_name, first_name, job_id
FROM employees
WHERE job_id = :job_title;

Anyone manage to get substitution of a condition in a WHERE clause right? What about double substitution?

Regarding that double substitution...

Check Practice 6 Question 1. The lecturers solution uses double substitution inside the subquery. You will probably get an error in Oracle XE. I got an error. But if you use a single : inside and outside it actually works and gives you the right values.

SELECT employee_id,last_name,salary
FROM employees
WHERE department_id IN (SELECT department_id
FROM employees
WHERE last_name = :LastName)
AND last_name <> :LastName

The above works in oracle. You can cross check it by doing the same solution with a join instead of a subquery, which I posted here in discussion.