Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Pierre.

Asked: October 30, 2024 - 6:19 pm UTC

Last updated: November 14, 2024 - 1:47 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

I need to pass an array into parameter in a store procedure like this

--##############################################################################################
--                            P A C K A G E   S P E C
--##############################################################################################
CREATE OR REPLACE PACKAGE TEST_PKG"
IS
type chef_type is RECORD
(
    p_numero_chef               varchar2(3),
    p_type_infraction_chef      varchar2(1),
    p_code_infraction_chef      varchar2(4),
    p_modele_chef               varchar2(7),
    p_nature_chef               varchar2(6),
    p_reglement_chef            varchar2(20),
    p_article_chef              varchar2(20),
    p_type_date_chef            varchar2(1),
    p_date_infraction_chef      varchar2(8),
    p_date_infraction_fin_chef  varchar2(8)
);
    type chef_array is varray(100) of chef_type;
      
--##############################################################################################
--        PROC 
--##############################################################################################
PROCEDURE TEST_PROC (
p_var...
p_nombre_chef   in number
p_chef            in chef_array,
p_var...
);
--##############################################################################################
--                            P A C K A G E   B O D Y
--##############################################################################################
CREATE OR REPLACE PACKAGE BODY TEST_PKG"
IS
--##############################################################################################
--    (2)   S I C O U R   M E T   A   J O U R   S I C M A
--##############################################################################################
   PROCEDURE TEST_PROC (
            p_var...
            p_nombre_chef in number,

            p_chef IN chef_array,  
            p_var...
      )
      IS
      v_var...
      BEGIN
      FOR i IN 1 .. p_nombre_chef
    LOOP
            v_numero_chef           := p_chef.p_numero_chef(i);
            v_type_infraction_chef  := p_chef.p_type_infraction_chef(i);
            ...
            insert ...
      END LOOP;
      END SICOUR_MAJ_SICMA;   

error message

[Error] PLS-00302 (382: 30): PLS-00302: The composant 'P_NUMERO_CHEF' must be declared
[Error] PLS-00302 (387: 39): PLS-00302: The composant 'P_TYPE_INFRACTION_CHEF' must be declared


and Connor said...

You were close - just misplaced array index

SQL> CREATE OR REPLACE PACKAGE TEST_PKG IS
  2  type chef_type is RECORD
  3  (
  4      p_numero_chef               varchar2(3),
  5      p_type_infraction_chef      varchar2(1),
  6      p_code_infraction_chef      varchar2(4),
  7      p_modele_chef               varchar2(7),
  8      p_nature_chef               varchar2(6),
  9      p_reglement_chef            varchar2(20),
 10      p_article_chef              varchar2(20),
 11      p_type_date_chef            varchar2(1),
 12      p_date_infraction_chef      varchar2(8),
 13      p_date_infraction_fin_chef  varchar2(8)
 14  );
 15      type chef_array is varray(100) of chef_type;
 16        
 17  PROCEDURE TEST_PROC (
 18  p_nombre_chef   in number,
 19  p_chef            in chef_array
 20  );
 21
 22  end;
 23  /

Package created.

SQL> sho err
No errors.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY TEST_PKG
  2  IS
  3     PROCEDURE TEST_PROC (
  4              p_nombre_chef in number,
  5              p_chef IN chef_array
  6        )
  7        IS
  8              v_numero_chef           varchar2(3);
  9              v_type_infraction_chef  varchar2(1);
 10
 11        BEGIN
 12        FOR i IN 1 .. p_nombre_chef
 13      LOOP
 14              v_numero_chef           := p_chef(i).p_numero_chef;
 15              v_type_infraction_chef  := p_chef(i).p_type_infraction_chef;
 16        END LOOP;
 17        END;   
 18  end;
 19  /

Package body created.


Rating

  (1 rating)

Comments

Very helpfull

A reader, November 13, 2024 - 8:54 pm UTC

Many thanks !
Chris Saxon
November 14, 2024 - 1:47 pm UTC

You're welcome

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library