Oracle pl / sql preferred way to provide environment specific parameters
What is the preferred way to provide parameters (configuration) for pl / sql packages? With different environments (test, qa, prd), you need to specify values that must be set appropriately for each environment. These values will not change after the package is installed. I'm looking for a way that preferably allows the same code for every environment.
source to share
If you want to change the session level parameters, you can use the construct IF-ELSE
in your PL / SQL code. And then you need to use EXECUTE IMMEDIATE to execute ALTER SESSION statements .
To find the current database name. you can query the name from * v $ database **:
SQL> SELECT name FROM v$database;
NAME
---------
ORCL
You can store this in a local variable and then have logic IF-ELSE
to change the session parameters at runtime.
For example,
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
---------
11-MAY-15
SQL> set serveroutput on
SQL> DECLARE
2 v_db_name VARCHAR2(20);
3 BEGIN
4 SELECT NAME INTO v_db_name FROM v$database;
5 DBMS_OUTPUT.PUT_LINE('Current database is : '||V_DB_NAME);
6 IF v_db_name = 'ORCL' THEN
7 EXECUTE IMMEDIATE q'[alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS']';
8 ELSIF v_db_name = 'QA' THEN
9 EXECUTE IMMEDIATE q'[alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS']';
10 ELSE
11 EXECUTE IMMEDIATE q'[alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS']';
12 END IF;
13 END;
14 /
Current database is : ORCL
PL/SQL procedure successfully completed.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
-------------------
05/11/2015 14:24:26
So, you can add a block IF-ELSE
to your existing PL / SQL code and use the same code in all environments :
IF v_db_name = 'param1' THEN
EXECUTE IMMEDIATE q'[alter session set parameter='value1']';
ELSIF v_db_name = 'param2' THEN
EXECUTE IMMEDIATE q'[alter session set parameter='value2']';
ELSE
EXECUTE IMMEDIATE q'[alter session set parameter='value3']';
END IF;
To use the parameter value globally in all packages, you can look at the global context variable .
source to share
OPTION 1
Usage sys_context
or something similar. This will allow you to use these parameters in SQL without sacrificing performance. For example. see this article to get this functionality:
SELECT sys_context('sample_ctx','email') FROM dual
If you combine it with a trigger logon
, you can set it on every new session.
OPTION 2
Another option is to use a global array of these values in a package specification. The array type should be something like
TYPE population_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(30);
This must be initialized inside the package body (pseudocode):
PACKAGE BODY ...
... -- functions etc
BEGIN
-- initialization of your array
END PACKAGE BODY;
Then you can use it like my_params.my_array ("my_parameter") and access the value directly.
The advantage of option # 2 is that it is stored in RAM and the value is retrieved using the built-in array read function. It also doesn't require any specific privileges (better than OPTION 1), nor does it read the table anytime you want to get the parameter. If you still like storing parameters in some table, you can read them once at session start (this will be done automatically during package initialization).
The downside is that it is arguably harder to use an array in SQL processing.
source to share