Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Passing Values to Stored Procedure for "IN" Clause

721872Sep 8 2010 — edited Sep 8 2010
Hello All:

I am trying to pass values to a stored procedure to use in an IN clause, and getting an "ORA-01722: invalid number".

I believe this has something to do with how .Net handles strings and how I am trying to pass it to my stored procedure.

The values I know need to be IN (2, 1) for the stored procedure to work, and I built a routine that creates a string value to pass in the format "2, 1", but I believe because this value is defined as a string in the .Net code, the leading and trailing apostrophe characters are causing the problem in the stored procedure.

I have in my .Net code the following:
oCommand.Parameters.Add("groupID_", OracleDbType.Varchar2).Value = GroupID;
Where GroupID is defined as a string character, and has values of the following: 2, 1, but due to how .net handles it, it passes as "2, 1".

So of course, inside my stored procedure,
CREATE OR REPLACE PROCEDURE PAYSOL.sp_ProjectsManAppAndFundRpt(
p_Cursor1 OUT SYS_REFCURSOR,
p_Cursor2 OUT SYS_REFCURSOR,
p_Cursor3 OUT SYS_REFCURSOR,
p_Cursor4 OUT SYS_REFCURSOR,
p_Cursor5 OUT SYS_REFCURSOR,
groupID_ IN VARCHAR2)

where I am defining the groupID_ parameter as a VARCHAR2, it is keeping the apostrophes, causing problems when I use it in my IN clause:
AND S.GroupID IN (groupID_)

What do I need to do to pass this value correctly? Is there something I can do inside the stored procedure to strip those characters, or do I need to pass it differently, or completely alter how I am handling this? I don't know the right path to head down, so wanted to seek some help.

Thanks
Andy
This post has been answered by 319958 on Sep 8 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 6 2010
Added on Sep 8 2010
1 comment
1,940 views