When using a SQL Server stored procedure for a dataset you can supply parameters that have 'Multi-select' property but you need some way to handle them in the SQL Server stored procedure (SP). The general approach is to unravel the comma separated string into a temp table within the SP. Once you've done using loops and various string handling functions then you can join the resulting table to you main table to limit the rows.
In Oracle it is a bit more difficult but the principle is the same. The step by step process I came up with (please correct me if there is a simpler method...)
1. Define a Global type in your Oracle database.
This needs to be an Nested table that can hold the parameters once they have been unravelled.
CREATE TYPE GlobalKeyTableType AS TABLE OF NUMBER;
You need this because the code you are about to write will depend on this - and note it must be a global type.
2. Write a routine to split up the keys and load them into your global table
PROCEDURE spSplit_Up_Key (
pParameterString IN VARCHAR2, pIdTable IN OUT GlobalKeyTableType)
AS
lCommaPos SMALLINT;
lParameterString VARCHAR2(2000) := pParameterString;
lLoopCounter NUMBER:=0;
lBreak BOOLEAN := FALSE;
l_err_num NUMBER;
l_err_msg VARCHAR2(100);
BEGIN
IF (LENGTH (lParameterString) <= 0)
THEN
RETURN;
END IF;
lCommaPos := INSTR ( RTRIM (LTRIM (lParameterString)),',');
IF lCommaPos = 0
THEN
lLoopCounter:=lLoopCounter +1;
pIdTable(lLoopCounter) :=TO_NUMBER ( RTRIM (LTRIM (lParameterString)));
ELSE
WHILE LENGTH (lParameterString) > 1 AND NOT lBreak
LOOP
lCommaPos := INSTR ( RTRIM (LTRIM (lParameterString)),',');
lLoopCounter:=lLoopCounter +1;
-- Extend if bigger than 1
IF lLoopCounter > 1 THEN
pidTable.EXTEND;
END IF;
pIdTable(lLoopCounter) :=
TO_NUMBER (SUBSTR (RTRIM (LTRIM (lParameterString)),1,lCommaPos - 1));
lParameterString :=
SUBSTR (RTRIM (LTRIM (lParameterString)),
lCommaPos + 1,
LENGTH (RTRIM (LTRIM (lParameterString))));
lCommaPos := INSTR ( RTRIM (LTRIM (lParameterString)),',');
IF lCommaPos = 0
THEN
lLoopCounter:=lLoopCounter +1;
-- Extend if bigger than 1
IF lLoopCounter > 1 THEN
pidTable.EXTEND;
END IF;
pIdTable(lLoopCounter) := TO_NUMBER (RTRIM (LTRIM (lParameterString)));
lBREAK:=TRUE;
END IF;
END LOOP;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
l_err_num := SQLCODE;
l_err_msg := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.put_line( TO_CHAR(L_err_num) || ' : ' || l_err_msg);
RAISE;
END spSplit_Up_Key;
This SP is best stored centrally in its own package accessible by various other routines. I actually have another one for splitting up VARCHARs but the principle is the same.
3. Call the SP and use the returned table
In you main SP you'll need to call the spSplit_Up_Key and capture the returned NESTED TABLE.
Note you'll have to declare (and initialise) the table in your calling routine - here is an example of where I've used it to capture a list of teams and am now using it in the query.
AND teamid IN
( select a.column_value val
from THE ( select cast( lTeamKeyTable as GlobalKeyTableType )
from dual ) a)
You may need to define more than 1 depending on how many multi values parameters you are passing in.
4. Hook up Reporting services to call the Oracle SP
Refer to my previous post for this but remember when passing the parameter to pass the joined string using
JOIN(pTeams,",")
Conclusions...
This took me longer than it should have done! There are several small gotchas which I haven't included here as would make the post to long but this is enough to get it working.
