OBIEE 11g –Use of Dynamic and Static Variables in Connection Pool

Use of dynamic and static variable for stating relational connection pool for data import and access purpose is a common trend used across multiple projects. This blog is an attempt to explain how to use dynamic and static variables in connection pool, and list down its advantages and limitations

Connection Pool with Static Variables

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=VALUEOF(DB_HOST))(PORT=VALUEOF(DB_PORT))))(CONNECT_DATA=(SID=VALUEOF(DB_SID))))

Following STATIC VARIABLES are defined within repository

DB_HOST ='APP-HU9IPS3NTO3'

DB_PORT ='1521'

DB_SID='ORCL'

Connection Pool with Init block and Dynamic Variables

Instead of editing connectivity parameters in each connection pool, update RPD and SESSION VARAIBLE that hold connection information

Initialization Block

Verify DUAL ORCL DSN

Initialization Script  

SELECT '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=VALUEOF(DB_HOST))(PORT=VALUEOF(DB_PORT))))(CONNECT_DATA=(SERVICE_NAME=VALUEOF(DB_SID))))' FROM DUAL

Session Variable

DSN and it is Default Value

'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pdborcl)))'

Connection Pool

"VALUEOF (NQ_SESSION.DSN)"  - uses the SESSION VARIABLE within connection pool

Limitations

Use of static variables within connection pool does not work for IMPOERT METADATA, while it data access from repository and via URL works fine.

Oracle Support Document

OBIEE 11g about the Use of Static Variables in the Connection Pool (Doc ID 1925713.1)

Inference  

By using session variable and default value the import metadata should work as expected. I hope you find this information useful.