SQL & PL/SQL

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!

CSV string to list of number for IN() statement

794031Aug 26 2010 — edited Aug 27 2010

Here is the problematic part of my function:
<blockquote>cr.client_id in (to_number(:ClientId)</blockquote>
I want my SQL*Plus function to handle a list of ClientIds supplied by the user, but if I enter "20006336, 27340036" I get the following error:
<blockquote>ORA-01722: invalid number
01722. 00000 - "invalid number"</blockquote>

Seems like there should be an easy solution to conversting a string to a list of numbers...
Running 10g. Also, the function works with &ClientId but I would prefer to keep it as a bind variable if possible (large query)
Thanks for the help!

Edited by: 791028 on Aug 26, 2010 8:58 AM

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 24 2010

Added on Aug 26 2010

#sql

5 comments

1,385 views