MySQL equivalent session variable for Oracle

Question

In MySQL, I can create an access a session variable by using a single @. Example initialization:

set @myVar = true;

Some trigger containing this code:

if (@myVar is not true) then
  execute something

What is the equivalent in Oracle 10g?

Answer 1

A package global variable would probably 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;

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

Answer 2

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 3

Why not just use bind variables? In SQL Plus:

variable SOME_NUMBER number
exec :SOME_NUMBER := 10

PL/SQL procedure successfully completed

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

Works for any kind of Oracle datatype.