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.
Well, I tried removing the COM layer completely ... even if I set a temp variable in the procedure and use that variable in the delete statement, I get the same error.
I think the problem has something to do with the variable being varchar2 and the column I'm using for the "where in (variable)" is a number. But I really don't understand why. My opinion is that
v_temp varchar2(32) default '303, 304';
delete from table where column in (v_temp);
should equate to
delete from table where column in (303, 304);
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!
That's definitely your problem. Oracle is VERY VERY VERY VERY VERY VERY (did I mention VERY?) strict on datatyping. You could try TO_CHAR on the column and see if that would work.
I know that Oracle is a very big pain in the *** for simple tasks! ;)
If it was simply a problem with datatypes, why wouldn't it choke if only one digit and no comma were present? For example
v_temp varchar2(32) default '303';
delete from table where column in (v_temp);
works perfectly. It has to be a combination of things ... I'm just not exactly sure what or why.
I wish I could trace the actual execution ... without spending a day setting up the stuff that would actually do the trace. ;)
I think for now I will have the COM split the string and loop through the array, calling the procedure. I hate doing it, but I can't waste any more time. :(
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?
Humm... I don't know much about Oracle, but I did face similar situation when dealing with SQL before. Maybe that could be a syntax problem in the query.
With SQL server, you have to do something like that :
WHERE field IN ('value1', 'value2')
if you do something like that :
WHERE field IN ('value1, value2')
it won't work. So if your param is '308, 309', you might fall in the latter situation and this might explain why it doesn't work.
I don't know Oracle but I thought I might add my 2 cents just in case... ;)
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.
I think the problem is more HOW you're getting the value into that variable than WHAT is in it. If it's in there the way you posted earlier, that isn't gonna fly.
Oracle is looking at that value as one value, not as an array of values. Big difference in how it's going to be resolved.
I don't have my oracle book in front of me. When I get to work, I'll see if I can work out how to get what you want it to do....
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.
That's what I mean.
Variable1 = "001,002,003,004,005"
is different than
Variable1 = "001","002","003","004","005"
What you probably need do is pass "'380'" into the variable instead of "380" so that Oracle can recognize it's a string value in the array.
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. :)