MySQL equivalent session variable for Oracle

Question

In MySQL, I can create access to a session variable with a single @. Initialization Example:

set @myVar = true; 

Some triggers containing this code:

 if (@myVar is not true) then execute something 

What is the equivalent in Oracle 10g?

Answer 1

 SQL> EXEC DBMS_SESSION.SET_CONTEXT('CLIENTCONTEXT', 'myvar', 'myvalue'); PL/SQL procedure successfully completed SQL> SELECT SYS_CONTEXT('CLIENTCONTEXT', 'myvar') FROM dual; SYS_CONTEXT('CLIENTCONTEXT','M -------------------------------------------------------------------------------- myvalue 

Answer 2

The global package variable is likely to do the same trick.

 CREATE OR REPLACE PACKAGE foo as myVar BOOLEAN; END foo; CREATE OR REPLACE PACKAGE BODY foo AS BEGIN MyVar := true; END foo; BEGIN If foo.myVar THEN dbms_output.put_line ('MyVar is True'); end if; END; 

The advantage of using the package over SYS_CONTEXT is that you get some encapsulation.

Answer 3

Why not just use bind variables? In SQL Plus:

 variable SOME_NUMBER number exec :SOME_NUMBER := 10 

PL / SQL procedure completed successfully

 if :SOME_NUMBER = 10 then do something; end if; / 

Works for any type of Oracle data.