Dear TOM
This code is not working for me since my PL/SQL release is too old
-- Since PL/SQL 20.2.0.175
DEFINE usr = 'YourName';
SELECT * FROM Table1 WHERE CreatedBy = '&usr';
SELECT * FROM Table2 WHERE CreatedBy = '&usr';
How can I run these two queries in my version?
I have the following but will only work with one query not multiple queries.
WITH MyTab AS (SELECT 'YourName' as usr FROM DUAL)
SELECT * FROM Table1 T1 INNER JOIN mytab M ON T1.CreatedBy = M.usr;
Thanks,
Oren
You certainly can use substitution variables in Oracle tools like SQL*Plus, SQL Dev, etc. Just ensure you have set define on
SQL> set define on
SQL> define v = 'X'
SQL> select * from dual where dummy = '&v';
old 1: select * from dual where dummy = '&v'
new 1: select * from dual where dummy = 'X'
D
-
X
SQL> set define off
SQL> select * from dual where dummy = '&v';
no rows selected
...though you list the product as Toad Data Point. This is created by Quest; you'll need to contact them for support on this.
That said, it's better to use bind variables or PL/SQL variables instead:
var v varchar2(1);
exec :v := 'X';
select * from dual where dummy = :v;
D
-
X
set serveroutput on
declare
v varchar2(1) := 'X';
c integer;
begin
select count(*) into c
from dual where dummy = v;
dbms_output.put_line ( c );
end;
/
1