Collection in pl/sql

Hi all,

I am trying to modify a stored procedure that was written by an old colleague, which currently imports product data changes into a products table from a csv file.

Each product has a flag to say if it's visible on the web site or not. What I need to do is mark any product in the database as 'not visible' if the product is in the Oracle database, but not in the import file.

I have got as far as changing the procedure so I have a collection defined, and the product catalogue numbers get stored as a key of each element, so I can use the EXISTS() function to see later if a product with a particular catalogue number needs to be updated or not.

After the collection is populated, I was hoping to run a query like the following:

UPDATE product SET visible='1' WHERE
products_collection.EXISTS(product.catalogue_number) AND visible='0';

where products_collection is my collection of catalogue numbers.

However, I'm getting an error when I try to import the procedure via SQLPlus via @"C:\path\to\pl-sql.file"; :

PL/SQL: ORA-01747: invalid user.table.column, table.column, or column specification

With a line number that is the same line number of the above SQL.

I'm very new to PL/SQL, so I'm not too surprised my first idea didn't work :) - I would be really grateful if somebody could point me in the right direction of how I can do this however!

Thanks,

Ian. Received on Tue Nov 22 2005 - 10:56:31 CST