Hi all ,
From past 1 week i am goggling how to send multi value parameter to oracle store procedure from SSRS 2005.
As we can't send delimiter separated multiple values to oracle store procedure IN CLAUSE. We are left with two option
1st option : use a user define split function which Take a comma delimited list of values in a single string and return it as a table of values to IN clause of ur store procedure
step 1. define the result type to be a TABLE type of the largest possible string.
create or replace type split_tbl as table of varchar2(32767);/show errors;
step 2. Create a function split
create or replace function split( p_list varchar2, p_del varchar2 := ',') return split_tbl pipelinedis l_idx pls_integer; l_list varchar2(32767) := p_list;AA l_value varchar2(32767);begin loop l_idx := instr(l_list,p_del); if l_idx > 0 then pipe row(substr(l_list,1,l_idx-1)); l_list := substr(l_list,l_idx+length(p_del)); else pipe row(l_list); exit; end if; end loop; return;end split;/show errors;
Step 3. use this split( ) in ur store procedure
CREATE OR REPLACE PROCEDURE PR_RANKING_SSRS( multivaluestring IN VARCHAR2,
PO_RANKING OUT SYS_REFCURSOR) IS
BEGIN
OPEN PO_RANKING FOR
SELECT col_namr FROM tbl_name
WHERE (any_Col_name IN ( select * from table(split(( multivaluestring )) ));
END PR_RANKING_SSRS;
2nd option if u don’t have to create table privilege on database then use dynamic SQL query.
Step1: create a store procedure as bellow . and send ur multi value parameter as comma separated string from SSRS 2005
CREATE OR REPLACE PROCEDURE PR_RANKING_SSRS( multivalue_parameter IN VARCHAR2,
PO_RANKING OUT SYS_REFCURSOR) IS
query_str VARCHAR2(1000);
BEGIN
query_str := 'SELECT Col_name
FROM Table_name
WHERE (BRAND_NM IN (' multivalue_parameter '))
GROUP BY XYZ_col_Name
ORDER BY XYZ_xyz_col_name;
OPEN PO_RANKING FOR query_str;
END PR_RANKING_SSRS;
Note : ur parameter string should be like = 'abc’,’def’,’geh’,’ddd'
Not like abc,def,geh,ddd or like abc’,’def’,’geh’,’ddd
