The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Oracle Procedure - Weird

I'll give a general overview of what is happening and if you need the code, just let me know.

I have an Oracle 8.1.7 database with a procedure contained within a package. I have a VB COM object calling this procedure with one input parameter (string in VB, varchar2 in oracle). The COM object receives the string from an html form ... there are form elements with the same name, so the value of the element is delimited by commas (ie, 308, 309, etc). The purpose is to use a delete statement that looks like this:

delete from table where column in (param);

Where the param = 308, 309

Unfortunately, I get an invalid number error whenever there are multiple numbers (it works fine if I only send 308). There is no trace available on the database, so I can't see what's actually being executed, but does anyone have any ideas why this wouldn't work? If I skip the procedure and execute the same sql statement from ASP, it works fine ... and I know for sure the concept is valid.

If this is not possible (for whatever reason), my options are to split the comma delimited value into multiple numbers and call the delete statement multiple times, or to only allow deleting one record at a time. I would like to keep the functionality the same, so if anyone can tell me of a "split" function in PL/SQL, I would greatly appreciate it. so far I have not found one.

Hmm.. Almost gave a stupid answer because I didn't read the whole post at one time.

Make sure the datatype is correct, and also make sure you have the right number of commas (ie. the last number in the list doesn't have a comma after it). Oracle and vb/vbscript don't play well together when dealing with datatypes, so I would start there.

It works if I hardcode it, but not if I use a variable. But if I only have 303, it works fine. Very strange!! I think I might just loop from my COM object and call the procedure multiple times ... not pretty, but it should only be 1-2 calls anyway. If anyone has an explanation on why the above did not work, PLEASE fill me in!

I did some reading on TO_CHAR and I think I might have an explanation.

First, when I use TO_CHAR on a varchar2, it tells me I have too many declarations of 'TO_CHAR' ... I didn't play around with it too much, but I'm going to assume I can't use TO_CHAR on a varchar2.

Second, I think the problem is a result of Oracle trying to convert 303, 304 to a number automatically (because the column is a number), but it thinks the comma from the variable is a group separator. Maybe. Make sense?

I understand what you are saying, but the variable in question *should* equate to: value1, value2 and not 'value1, value2' ... because when you print a varchar2 variable, it doesn't have quotes. I still feel the problem has something to do with the auto conversion oracle tries to make on the variable and gets confused with the comma.

Well, I could be wrong, because I'm just using my imagination, but I honestly believe Oracle will try to convert the variable to match the column in the where clause. In this case, Oracle tries to convert a varchar2 to a number - which works when there is only one number in the varchar2. But when there is a comma in the varchar2 variable and it tries to convert it to a number, it becomes an invalid number.

That's my theory, but if someone has any info to prove it wrong, please post! I am using a work around for now, but I am very curious about this.

You're right, Oracle is actually 'smart' enough to treat it as an array, then convert each element to a number. However, the string is created at the html level and when I force single quotes around the value so the string ends up like '303', '304', '305' the single quotes are lost by the time it reaches the procedure and an invalid number is the result. I'm sure there's a way to force the single quotes to stay, but at this point I'm not worried about it. If I have time to go back and make it more elegant, I will. I'm just happy to understand what was happening.