Problem and challenge came up recently to pull and set a multiple value parameter from OVD (or LDAP source) and set session variable (row-wise session variable to be specific) to be used by report writing. Well in OBI 10g, I usually have done that by using my custom LDAP table function to use along with setting row-wise variable(s) in an init block. I looked to see if anything has changed along the lines of accessing LDAP data sources and pulling parameters. There is no problem pulling scalar parameters and mapping them to session variables, only when it is comes to trying to pull multi-value parameters and setting a row-wise session variable. I tried the interface in the admin tool to see if I could do it with no avail. So I was going to revert to my old approach of using the table function and setting the row-wise session var using that. Problem is, the client wanted to see if we could do so without using this table function at all. The challenge!!!! So I agreed, lets do it.
First, I would need them to set a scalar parameter in the LDAP source as a delimited string of all the values (We chose the notorious ‘,’ delimiter). And since we will not be dealing with an extensive list of values, I do not foresee length or size issues. It is easy enough to extract this delimited value string now. No problem. Now we could theoretically stop there and use this delimited value in reports; but it is more tedious as users would need to use and perform a LIKE operator on this session variable and the column compared to. Not so eloquent if you ask me. I would prefer the row-wise session variables and let bi server use that to build the physical query proper.
So what now! How do we take a session variable that is a delimited string and get a row-wise session variable out of it!? Well here is my approach and the solution:
1) I set an init block to query against the LDAP data source to extract the delimited value list string. Also if need be make sure there is default value in case the parameter is not found for that user.
2) Create a new init block, used for setting the row-wise session variable. This new init block will need to specify the other init block (in step one above) as a dependent using the execution precedence section of the init block properties. This ensures the proper firing of the init blocks and the dependency between the two. Then also specify it as being row-wise session variable. Lastly, I used a pure SQL solution to parse and pivot the string into rows of the individual elements. Now, there could be many approaches to doing this including create custom functions and looking to see if there is such a function already in another package. But, I just wanted a pure SQL solution. Here is that SQL query solution (using MODEL clause):
SELECT ‘ROW_WISE_SESS_VAR_NAME‘, SUBSTR(str, start_pos, end_pos-start_pos) as value
FROM (
SELECT *
FROM (
SELECT ‘,’||’VALUEOF(NQ_SESSION.LDAP_DELIM_STR)’||’,’ as str,
1 as start_pos,
LENGTH(‘VALUEOF(NQ_SESSION.LDAP_DELIM_STR)’) as end_pos,
LENGTH(‘VALUEOF(NQ_SESSION.LDAP_DELIM_STR)’) – LENGTH(REPLACE(‘VALUEOF(NQ_SESSION.LDAP_DELIM_STR)’,',’))+1 as elem_cnt,
0 AS elem_no,
rownum as rn
FROM dual
)
MODEL
PARTITION BY (rn,str)
DIMENSION BY (elem_no)
MEASURES (start_pos, end_pos, elem_cnt)
RULES
ITERATE (4000)
UNTIL (ITERATION_NUMBER+1 = elem_cnt[0])
(
start_pos[ITERATION_NUMBER+1] = INSTR(CV(str), ‘,’, 1, CV(elem_no)) + 1,
end_pos[ITERATION_NUMBER+1] = INSTR(CV(str), ‘,’, 1, CV(elem_no) + 1)
)
)
WHERE elem_no != 0
ORDER BY str, elem_no
This worked like a charm and can be another solution, if the ldap table function cannot be used in order to get the same results. Which is multiple value session var from an LDAP source. Obviously the SQL query above can be used and modified for any such way to parse and pivot delimited string into multiple values. Note: I am using modeling clause that I believe started to be available in Oracle 10g DB, and this is strictly an Oracle based solution.
I like using the modeling clause as I like to use advanced oracle sql and learn and keep knowledge of all the capabilities available to me. For those not too familiar with these types of clauses I am adding an alternative SQL solution:
SELECT ‘ROW_WISE_SESS_VAR_NAME‘
, SUBSTR(str, start_pos, (next_pos-start_pos)) AS value
FROM
(
SELECT ds.str
, ne.rn AS element_no
, INSTR(ds.str, ‘,’, DECODE(ne.rn, 1, 0, 1) , DECODE(ne.rn, 1, 1, ne.rn-1)) + 1 AS start_pos
, INSTR(ds.str, ‘,’, 1, DECODE(ne.rn, 1, 1, ne.rn)) AS next_pos
FROM
(select ‘VALUEOF(NQ_SESSION.LDAP_DELIM_STR)’||’,’ as str from DUAL) ds
, ( SELECT ROWNUM as rn
FROM dual
CONNECT BY ROWNUM <= (LENGTH(‘VALUEOF(NQ_SESSION.LDAP_DELIM_STR)’) – LENGTH(REPLACE(‘VALUEOF(NQ_SESSION.LDAP_DELIM_STR)’, ‘,’))+1)
) ne
)
