Re: Collection in pl/sql

On 2005-11-22, ian <mx3_at_tesco.net> wrote:
> 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.

update product set visible=1 where catalogue_number in (select * from table(products_collection));

should do the trick.

hth,
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/