Passing a Multi-Value Parameter to a Stored Procedure

[SomeHelp]

Is it possible to pass a multi-value parameter field to an Oracle stored procedure? For example, I have a parameter field called Product that may contain one or more values, depending on what the users selects. I have been able to get single values to work, but multiple values are not working, even using the IN operator as part of the stored procedure. Any feedback would be greatly appreciated. I am using Crystal Reports 8.5, Oracle Client 8.1.7 and DSN is using the CR Oracle8 v3.6 driver.

 

[synapsevampire]

You might address this by passing a string to the SP parm with a delimiter in it and parse it out within the SP.

-k

 

[SomeHelp]

I tried parsing the parameter field in the SP, but that still does not work even though the correct SQL is being generated by my parsing routine.

Thanks...

 

[SomeHelp]

I finally go it to work. With this version the user must enter parameter values as 'name','name2','name3'. However, you should be able to modify this code to have the tick marks put in automatically so user only enters name,name2,name3...

CREATE OR REPLACE PROCEDURE SP_Test(
Cable_Cursor IN OUT PKG_C2P_TOP_RPT_ALL.Top_Type,
p_val IN varchar
)
AS
v_test varchar(100);
BEGIN
v_test := 'select * from test where name in (' || p_val || ')';
OPEN Cable_Cursor FOR
v_test;
END SP_Test;
/

Here is a parsing code sample you can add after the BEGIN block to include ticks around parameter values:

-- This code will work for parsing one or two values and add ticks around each value
if instr (p_val, ',') <> 0 then
v_test := '''' || substr(p_val, 1, instr(p_val, ',',1) - 1) || '''' || ',' || '''' || substr(p_val,instr(p_val, ',',1) + 1 ) || '''';
else
v_test := '''' || p_val || '''';
end if;