Re: Update table with a comma seperated string

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
>
> AS
>
> PROCEDURE Update_Route_Camera(
> CSArray IN VARCHAR2)
> IS
> i NUMBER;
> x number;
> y varchar2(1);
> BEGIN
> -- initialise
> i := 0;
> LOOP
> i := i+1;
> EXIT WHEN instr(CSArray, ',', 1, i) = 0;
> -- get first value
> x := to_number(trim(substr(CSArray, instr(CSArray, ',', 1, i)-1, 1)));
> -- get second value
> i := i+1;
> y := trim(substr(CSArray, instr(CSArray, ',', 1, i)-1, 1));
> update rtcamera set rtcamera.ACTIVE = y where id = x;
> END LOOP;
> END Update_Route_Camera;
> END Update_Route_Camera_pkg;

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 Received on Thu May 13 2004 - 03:35:59 CDT