Re: Update table with a comma seperated string

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Thu, 13 May 2004 14:24:06 +0200
Message-ID: <c7vpd7$t8m$1@news.BelWue.DE>

Holger Baer wrote:
> helixpoint wrote:
>

>> I have a string of id's and True or False values. I could have 50000
>> pairs. Like: "2,t,6,t,9,f" I am new to oracle and I am tring to make a
>> package. so basically I need to look threw the string for update
>> active(t or f) where id=number
>>
>> I am new to Oracle. Below does not seem to work. I don't get any
>> errors

>
> Would you care to tell us what's not working? If the data does not enter
> the table, could it be that you did not commit?
>
> And if you have such a large number of pairs, would it be by any chance
> possible to have them in a table like fashion (e.g. textfile) that you
> can use as external table (provided you're on 9i or higher) and then
> insert/update/merge from there? That could be way more efficient.
>
> Regards,
> Holger

As a follow up (I seem to have to much time on my hands), look at this line:

 >> to_number(trim(substr(CSArray, instr(CSArray, ',', 1, i)-1, 1)));

Let me translate this for you (innermost first):

1. get the position-1 of the i-th occurence of a comma, start searching at the first character
2. take the position found in step one and copy out a string of length 1
3. trim the string from step 2
4. make the trimmed string a number

So you expect only numbers with the length 1, and no whitespace between the number and the separator. While this could very well be, how does this match with your statement, that you could have 50000 pairs? I assume they should be different, so maybe what your procedure does is not exactly what you want?

Since you're new to Oracle, there is one site you should put into your browser's bookmarks:

http://tahiti.oracle.com

You'll need to register (but it's free) and all documentation you could possibly need is available for you. I'd suggest out of th list of books the SQL Reference. There is a indepth explanation of every possible parameter of the functions you're using, that should get you on your track in no time.

And I know how it is, because I had a quite similar problem like you just last week, because on parameter of instr did not what I remembered. (If I had looked in the first place, that would have saved me about an hour).

So this 5 minutes to check the documentation really pay off!

HTH
Holger Received on Thu May 13 2004 - 07:24:06 CDT