Hello List Subscribers,
I’m having trouble utilizing stored procedures which need to be passed a delimited (semi-colon) list of arguments. My report uses a user prompt to get a list of items for an “in list” condition in a normal universe based query, but also passes the value of that prompt to a stored procedure. The semi-colons between the item values seems to cause Oracle to think the semi-colon in the variable is the end of the argument and gets confused about the remaining items in the list. The procedure header is declared as follows: CREATE OR REPLACE PROCEDURE psi_totals
(enter_businesses IN VARCHAR2,
enter_items IN VARCHAR2,
cov_cursor IN OUT bo_pack.coveragecurtype) IS
The package is declared as:
CREATE OR REPLACE PACKAGE BO_Pack IS
TYPE CoverageCurType IS REF CURSOR RETURN psi_sum_total1%ROWTYPE; END BO_Pack;
The procedure is called from BusinessObjects via free hand SQL in the following manner:
begin
psi_totals(@Prompt(‘Enter Businesses separated by semicolons:’,‘A’,multi,free), @Prompt(‘Enter Items separated by semicolons:’,‘A’,multi,free), :cbo);
end;
I ran into exactly that problem a few months ago while migrating a client from 3.1.1 to 4.1.x. They had, somehow, figured out a way to make their stored procedures work with multiple arguments in 3.1.1, but I wasn’t able to find any way to make them work in version 4. The problem lies in the way BO passes the call to the stored procedure.
What we did to get around it was encapsulate the arguments in a different way. I think we used commas instead of semicolons to indicate where different arguments occurred. BO had no problem with that, and passed the whole string off to the database as one argument. We then had to write a wrapper that parsed the string and converted it into a string that used semicolons, then passed the separate arguments off to the original procedure.
Hope that helps,
Paul
On Tuesday, April 18, 2000 12:42 PM, “Sean Teague” [SeanT@DUNNSYS.COM] wrote:
I’m having trouble utilizing stored procedures which need to be passed a delimited (semi-colon) list of arguments. My report uses a user prompt to get a list of items for an “in list” condition in a normal universe based query, but also passes the value of that prompt to a stored procedure. The semi-colons between the item
values
seems to cause Oracle to think the semi-colon in the variable is the end
of
the argument and gets confused about the remaining items in the list.