is it possible to pass arrays as parameters in procedures?

Question

i'm trying to pass an array as a parameter in my procedure but i keep getting a command unknown error

code

    SET SERVEROUTPUT ON;

    TYPE pourcentage_remise IS TABLE OF NUMBER INDEX BY commandeproduit.ref_produit%type;

    CREATE OR REPLACE PROCEDURE remise_produit( pourcent IN pourcentage_remise,
                            ref_comm IN commande.ref_commande%type,
                            c_ht OUT commandeproduit.prix_ht%type,
                            c_ttc OUT commandeproduit.prix_ttc%type)
    IS
    CURSOR p_curs IS 
    SELECT ref_produit, prix_ttc, prix_ht  FROM commandeproduit WHERE concerne = ref_comm ;
    ref commandeproduit.ref_produit%type;
    ttc commandeproduit.prix_ttc%type;
    ht commandeproduit.prix_ht%type;

    BEGIN
        open p_curs;
            LOOP
                FETCH p_curs into ref, ttc, ht;
                EXIT WHEN p_curs%notfound;
                dbms_output.put_line(ref, ' ',ht, ' ', ttc);
                IF pourcent(ref) THEN
                    ttc := ttc - ttc * pourcent(ref);
                    ht := ht - ttc * pourcent(ref);
                    INSERT INTO commandeproduit(prix_ht, prix_ttc) VALUES(ht, ttc) WHERE concerne = ref_comm AND ref_produit = ref;
                END IF;
                dbms_output.put_line(ref, ' ',ht, ' ', ttc);
            END LOOP;
        close p_curs;
    END remise_produit;
    /

procedure call

DECLARE 
pourcentage pourcentage_remise;
reference commande.ref_commande%type :=1;
BEGIN
pourcentage('A01') :=0.15;
pourcentage('B15') :=0.2;
remise_produit(pourcentage, reference);
END;
/

the table

the error in french which means command unknown

please help

Answer 1

Your syntax error is on the declaration of your type so the rest of your code isn't really needed.

TYPE pourcentage_remise IS TABLE OF NUMBER INDEX BY commandeproduit.ref_produit%type;

Several problems

• If you are trying to declare a type in SQL, you'd need to use a CREATE TYPE so you're missing the CREATE.
• If you are trying to declare a table type in SQL, you can't use an associative array. You'd realistically want a nested table instead.
• If you are trying to declare a PL/SQL type, your statement would need to be in a PL/SQL block. You could declare a package that contains an associative array type.

If you want to declare a nested table type in SQL

CREATE TYPE pourcentage_remise IS TABLE OF NUMBER;

If you want to declare an associative array in a PL/SQL package

CREATE OR REPLACE PACKAGE my_collection_pkg
AS
  TYPE pourcentage_remise IS TABLE OF NUMBER INDEX BY commandeproduit.ref_produit%type;
END; 

If you want to use a nested table type, that changes how you need to initialize your associative array. It should change how you reference elements of that array, but I'm confused by your code. Your procedure appears to be using a numeric index to access an element of the associative array which doesn't make sense if the associative array uses a string as the index.