Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

"IN" operator - invalid number error 1

Status
Not open for further replies.

hazelsisson

Programmer
Mar 18, 2002
68
GB
Hello,

I have a stored sql query used in a report. Parameters are passed to it and all work apart from this line:

Code:
and e.org_id IN (:p_orgs)

e.org_id is a numeric column in the table.

:p_orgs is a string that can be blank, or can contain any number of integers separated by commas (e.g. "18, 20, 21, 22, 24, 25").

The problem is that if :p_orgs is not blank, I get the error ORA-01722: invalid number.

I kind of understand that it is because :p_orgs is a string and it doesn't automatically convert the string to a list of integers.

Do you have any ideas on how can I overcome this problem, either by changing the value of :p_orgs (which is generated by a function) or altering the stored query?

Many thanks,
Hazel
 
You can't use bind variables as parameters for IN lists. You will have to generate a dynamic SQL statement and append your list of IN conditions to that.

Code:
declare
   type t_cursor is ref cursor;
   v_cursor t_cursor;
   in_list varchar2(500);
begin
   in_list := '1,2,3,4';
   open v_cursor for 'select numarg from 
   (select 1 as numarg from dual) where numarg in 
   ('||in_list||')';
end;

If you don't want to create a hard-coded list like that, the only alternatives would be to put the list in a temporary table and join back to that.
 
Hi Dagon,

thanks for the advice - I will re-think my approach; the ref cursor looks like the best idea.

Thanks again,
Hazel
 
PS - I will give you a star from home; my work firewall forbids it!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top