Mixing arrays and queries

Question

I have a set of product that I want to tag to a certain value so what I did this:

declare
   type array_produit_auto is varray(3) of varchar(50);
   array array_produit_auto := array_produit_auto('code_product1', 'code_product2', 'code_product3');

begin
   for i in 1..array.count loop
        update F_PRODUITASS pas
        set PAS_NONGES_IDA = 0
        WHERE PAS.PAS_CODE_PRODUIT = array(i;
   end loop;
end;
commit;

however, the list of these products is too long. Instead I'd like to do this:

declare
   type array_produit_auto is varray(3) of varchar(50);
   array array_produit_auto := array_produit_auto('code_product4', 'code_product5', 'code_product6');

begin
    update F_PRODUITASS pas
    set PAS_NONGES_IDA = 1
    WHERE PAS.PAS_CODE_PRODUIT NOT IN array;
end;
commit;

except this doesn't work since apparently I can't mix a query and an array this way.

Any idea of how I could make this work?

Answer 1

If you used a nested table then you could query from the nested table, something like this:

DECLARE

    v_exclude_list t_array_produit_auto :=
        t_array_produit_auto('code_product4', 'code_product5', 'code_product6');

BEGIN

    UPDATE F_PRODUITASS pas
      SET PAS_NONGES_IDA = 1
      WHERE PAS.PAS_CODE_PRODUIT NOT IN ( SELECT *
                                            FROM TABLE(v_exclude_list) )
    ;
END;
/

Also, you meant varchar2, right?

Update regarding the Opaque error: The type declaration would need to be an object type (create with the CREATE OR REPLACE TYPE syntax rather than a local plsql type as in the DDL below.

CREATE TABLE F_PRODUITASS(PAS_NONGES_IDA number, PAS_CODE_PRODUIT VARCHAR2(50));
INSERT INTO F_PRODUITASS VALUES(3, 'code_product3');
INSERT INTO F_PRODUITASS VALUES(4, 'code_product4');

CREATE OR REPLACE TYPE t_array_produit_auto IS TABLE OF VARCHAR2(50);

If you did not wish to create your own object type, you could use pre-existing varchar2 or number types such as sys.odcivarchar2list as described here: Anonymous TABLE or VARRAY type in Oracle