Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Oren.

Asked: January 24, 2022 - 6:08 pm UTC

Last updated: January 27, 2022 - 11:10 am UTC

Version: Toad Data Point Using PL/SQL Release 12.1.0.2.0

Viewed 1000+ times

You Asked

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

and Chris said...

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

Rating

  (2 ratings)

Comments

Thanks for the response.

Oren, January 25, 2022 - 5:57 pm UTC

None of the syntax posted is being recognized in my Toad Data Point.

Only

WITH MyTab AS (SELECT 'YourName' as usr FROM DUAL) 
SELECT * FROM Table1 T1 INNER JOIN mytab M ON T1.CreatedBy = M.usr;


works, and I an only use the stored variable name for a single query so it is of no use.

Thanks, Oren
Chris Saxon
January 26, 2022 - 9:17 am UTC

Then you'll need to reach out to Quest for help. Or switch to SQL Developer ;)

accross one session

Predefined context, January 27, 2022 - 10:42 am UTC

SQL> begin dbms_session.set_context('clientcontext', 'usr', 'My username'); end;
  2* /

PL/SQL procedure successfully completed.

SQL> select sys_context('clientcontext','usr') myvar from dual;

         MYVAR
______________
My username

Chris Saxon
January 27, 2022 - 11:10 am UTC

Yes, you could do that too.

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here