How to transfer pop up window functionality into code in Oracle SQL for variable declaration?

Question

I am preparing Oracle SQL program for my team to use. This can eventually used by other teams as well by changing few where conditions. We all are using Toad for Oracle to run the query. So I added variables in the query. See the example code below.

DECLARE  v_lob    VARCHAR(2);BEGIN  v_lob := 't' ;END;
select :v_lob  as Test from dual

My issue is when Toad has a pop-up window option to bind the variable. Since my team is using v_lob := 't' any way, I prefer them not to have enter it each time when they query. How can I remove the pop-up window option and use the value 't' for the variable as in the code?

Answer 1

You might want to use a CONTEXT for holding a large number of session variables. This solution should work with any IDE.

One user, one time, must create the context and create a package for setting the context values (based on this oracle-base article):

--Create a package on a shared schema.
create or replace package context_api is
    procedure set_parameter(p_name varchar2, p_value varchar2);
end;
/

create or replace package body context_api is
    procedure set_parameter(p_name varchar2, p_value varchar2) is
    begin
        dbms_session.set_context('my_context', p_name, p_value);
    end;
end;
/


--Create a context on a shared database.
--(Note that contexts are global objects and don't belong to a specific schema.)
create or replace context my_context using context_api;

Each user then needs code like this in their worksheets:

--At the top of the worksheet, set the variables like this:
begin
    context_api.set_parameter('v_lob', 'asdf');
end;
/


--Call the variables with SYS_CONTEXT:
select sys_context('my_context', 'v_lob') as test from dual;

TEST
----
asdf