Finally, to the ultimate goal, dynamic session variables. Ideally, these would be repository variables, but since I'm using the row-wise variety, that is not possible (as of 10.1.3.4). Once a day, or some other relatively long time frame would be sufficient.
Anyway, there's an occasion where dynamically creating variables might be needed or necessary.
To recap the problem
Due to certain restrictions in our environment, the tnsnames entries in our separate environments (Dev/QA/Stage/Prod) are exactly the same. I would prefer to have one entry in each environment that points to that environments database, but alas, I cannot. What this does is forces us to change the password in the connection pools when migrating. I know it's not ideal, but it is what it is.A few people did chime in, Pete Scott suggesting using the a command line utility detailed here. Christian chimed in that I should use it at my own risk and rnm1978 said this problem has not been truly solved yet.
The proof of concept I am embarking on will allow us to lookup the tnsname entry and the associated password for that environment, theoretically, requiring no changes between environments.
In that respect, it was decided that we would have a single service account. From that service account, we could look up the values we need for each environment. Here's the other fun problem...what comes first? The chicken or the egg? This service account password will be stored in a simple text file that will be locked down by the system administrators. Don't ask what happens when the password changes either. At this point, it's just a proof of concept. So be gentle. If you have an established way to manage passwords...please share.
The whole issue of migration OBIEE deployments through the environments is a bit of a pig's ear with no foolproof method that I've seenI need to look up that phrase as I'm certain it's peculiar to my brethren across the pond.
Finally, the meat of the post. Here's my table:
OBI@TESTING>@desc obi_variablesI'll throw a few records in there for fun (and to obviously demonstrate).
Name Null? Type
------------------------------ -------- -------------
VARIABLE_NAME NOT NULL VARCHAR2(50)
VARIABLE_VALUE NOT NULL VARCHAR2(250)
VARIABLE_TYPE_CODE NOT NULL VARCHAR2(30)
INSERT INTO obi_variablesFor demonstration purposes, I am going to create a new database in the Physical layer and add a connection pool called obi variables pool. From the previous post, I'll be using the values that I brought in through the text file. The only difference is that I added username...just in case. To sum that up, I'm bringing in the TNSNAME entry, the USERNAME and the PASSWORD.
INSERT INTO obi_variables
INSERT INTO obi_variables
TYPE VARIABLE_NAM VARIABLE_VALUE
---------- ------------ ------------------------------------------
PASSWORD PW_TESTING TESTING
URL URL_TESTING http://localhost:7777/pls/apex/f?p=101:1
TNSNAME TNS_TESTING TESTING
Your connection pool should look like this:
The password column is the same format as the others, VALUEOF( INIT_PASSWORD )
You'll have to re-enter that when you save.
Now I'll be creating an init block using this connection pool. I won't drag you through that entire process again (your welcome). This is a Session Init Block as opposed to the Repository Init Block in the previous example.
The only difference is that I will be using a row-wise variable. I'll get to that in a second.
After selecting the obi variable pool connection pool, entering the following SQL statement into the area provided:
SELECT variable_name, variable_value.In the Edit Data Target area, select the row-wise initialization variable. Select OK.
Test it and voila! You now have dynamic variables in OBIEE.