I may able to compile the spec and the body but cannot execute the procedure. Any help will be appreciated.

CREATE OR REPLACE PACKAGE TEST_PKG 
IS
type chef_type is RECORD
(
    p_var1             varchar2(3)
    p_date             vachar2(8)
);
    type chef_array is varray(1000) of chef_type;

PROCEDURE TEST_PROC (   
    p_num_chef                    IN NUMBER,
    p_chef                        IN chef_array, 
    p_erreur                      OUT NUMBER,
    p_message                     OUT VARCHAR2      
);
END TEST_PKG;
/

CREATE OR REPLACE PACKAGE BODY TEST_PKG
IS
   PROCEDURE TEST_PROC (
    p_num_chef                    IN NUMBER,   
    p_chef                        IN chef_array,          
    p_erreur                     OUT NUMBER,
    p_message                    OUT VARCHAR2
   )
IS
v_...
BEGIN
    FOR i IN 1 .. p_num_chef
    LOOP
        v_var1           := p_chef(i).p_var1;
        v_date           := p_chef(i).p_date;
    END LOOP;
 END TEST_PROC;
END TEST_PKG;
/     

--       E X E C U T E   P R O C E D U R E
DECLARE
v_erreur               NUMBER :=0;
v_message              VARCHAR2(256) := ''; 
p_erreur               NUMBER;
p_message              VARCHAR2(256); 


BEGIN
TEST_PROC(
2               -- p_num_chef
,'ABC'          -- p_chef(1).p_var1
,'20241120'     -- p_chef(1).p_date
,'DEF'          -- p_chef(2).p_var1
,'20241120'     -- p_chef(2).p_date
,v_erreur       -- p_erreur
,v_message      -- p_message
);
dbms_output.put_line('p_erreur: ' || p_erreur);
dbms_output.put_line('p_message: ' || p_message);
END;

[Error] Execution (39: 1):
ORA-06550: Line n, column 1:
PLS-00306: wrong number or types of arguments in call to 'TEST_PROC'

Basically I'm trying to call a stored procedure that takes an array as parameter, but I'm getting that error.

I may able to compile the spec and the body but cannot execute the procedure. Any help will be appreciated.

CREATE OR REPLACE PACKAGE TEST_PKG 
IS
type chef_type is RECORD
(
    p_var1             varchar2(3)
    p_date             vachar2(8)
);
    type chef_array is varray(1000) of chef_type;

PROCEDURE TEST_PROC (   
    p_num_chef                    IN NUMBER,
    p_chef                        IN chef_array, 
    p_erreur                      OUT NUMBER,
    p_message                     OUT VARCHAR2      
);
END TEST_PKG;
/

CREATE OR REPLACE PACKAGE BODY TEST_PKG
IS
   PROCEDURE TEST_PROC (
    p_num_chef                    IN NUMBER,   
    p_chef                        IN chef_array,          
    p_erreur                     OUT NUMBER,
    p_message                    OUT VARCHAR2
   )
IS
v_...
BEGIN
    FOR i IN 1 .. p_num_chef
    LOOP
        v_var1           := p_chef(i).p_var1;
        v_date           := p_chef(i).p_date;
    END LOOP;
 END TEST_PROC;
END TEST_PKG;
/     

--       E X E C U T E   P R O C E D U R E
DECLARE
v_erreur               NUMBER :=0;
v_message              VARCHAR2(256) := ''; 
p_erreur               NUMBER;
p_message              VARCHAR2(256); 


BEGIN
TEST_PROC(
2               -- p_num_chef
,'ABC'          -- p_chef(1).p_var1
,'20241120'     -- p_chef(1).p_date
,'DEF'          -- p_chef(2).p_var1
,'20241120'     -- p_chef(2).p_date
,v_erreur       -- p_erreur
,v_message      -- p_message
);
dbms_output.put_line('p_erreur: ' || p_erreur);
dbms_output.put_line('p_message: ' || p_message);
END;

[Error] Execution (39: 1):
ORA-06550: Line n, column 1:
PLS-00306: wrong number or types of arguments in call to 'TEST_PROC'

Basically I'm trying to call a stored procedure that takes an array as parameter, but I'm getting that error.

• plsql
• parameters

2 Answers 2

The main issue here is that you are trying to use the type which is declared within package - outside of the package. If you call the package procedure as in the question then create the types in SQL first, to make them available to both - the package and to calling PLSQL block.

Create or Replace type chef_type is OBJECT( p_var1 varchar2(3), p_date date );
Create or Replace Type chef_tbl is TABLE OF chef_type;

... next create the package ...

create or replace PACKAGE 
  TEST_PKG IS 
PROCEDURE TEST_PROC (   
    p_num_chef                    IN NUMBER,
    p_chef                        IN chef_tbl, 
    p_var1                        OUT VARCHAR2,
    p_date                        OUT DATE      
);
END TEST_PKG;

create or replace PACKAGE BODY TEST_PKG
IS
   PROCEDURE TEST_PROC (
    p_num_chef                    IN NUMBER,   
    p_chef                        IN chef_tbl,          
    p_var1                        OUT VARCHAR2,
    p_date                        OUT DATE
   )
IS
BEGIN
    FOR i IN 1 .. p_num_chef
    LOOP
        p_var1           := p_chef(i).p_var1;
        p_date           := p_chef(i).p_date;
    END LOOP;
 END TEST_PROC;
END TEST_PKG;

... and call the package procedure ...

--       E X E C U T E   P R O C E D U R E
DECLARE
v_var1                 VARCHAR2(3);
v_date                 DATE; 
v_chef_tbl             chef_tbl := chef_tbl();

BEGIN
v_chef_tbl.Extend();
v_chef_tbl(1) := chef_type('ABC', SYSDATE-1);
TEST_PKG.TEST_PROC( p_num_chef => 1, 
           p_chef     => v_chef_tbl,
           p_var1     => v_var1,
           p_date     => v_date );
dbms_output.put_line('p_var1: ' || v_var1);
dbms_output.put_line('p_date: ' || v_date);
--
v_chef_tbl.Extend();
v_chef_tbl(2) := chef_type('DEF', SYSDATE);
TEST_PKG.TEST_PROC( p_num_chef => 2, 
           p_chef     => v_chef_tbl,
           p_var1     => v_var1,
           p_date     => v_date );
dbms_output.put_line('p_var1: ' || v_var1);
dbms_output.put_line('p_date: ' || v_date);
END;
/

R e s u l t :    
    
p_var1: ABC
p_date: 20.11.24
p_var1: DEF
p_date: 21.11.24

The variable p_chef in procedure test_proc is not a scalar value. create a local variable of the same type as p_chef, populate that in your anonymous block and then pass that local variable as argument to the procedure.

The package contains a couple of syntax errors but compiles eventually (you never tested this code did you ? ;) )

Package:

CREATE OR REPLACE PACKAGE TEST_PKG 
IS
    type chef_type is RECORD
    (
         p_var1             varchar2(3)
        ,p_date             varchar2(8)
    );
    type chef_array is varray(1000) of chef_type;

PROCEDURE TEST_PROC (   
    p_num_chef                    IN NUMBER,
    p_chef                        IN chef_array, 
    p_erreur                      OUT NUMBER,
    p_message                     OUT VARCHAR2      
);
END TEST_PKG;
/

CREATE OR REPLACE PACKAGE BODY TEST_PKG
IS
   PROCEDURE TEST_PROC (
    p_num_chef                    IN NUMBER,   
    p_chef                        IN chef_array,          
    p_erreur                     OUT NUMBER,
    p_message                    OUT VARCHAR2
   )
IS
    v_var1             varchar2(3);
    v_date             varchar2(8);
BEGIN
    FOR i IN 1 .. p_num_chef
    LOOP
        v_var1           := p_chef(i).p_var1;
        v_date           := p_chef(i).p_date;
        dbms_output.put_line('test_proc: ' || i ||' ,v_var1: '||v_var1||', v_date '||v_date);

    END LOOP;
    p_erreur := NULL;
    p_message := 'Success';
 END TEST_PROC;
END TEST_PKG;
/       

Invoke Procedure:

set serveroutput on size 999999
clear screen

DECLARE
    v_erreur               NUMBER :=0;
    v_message              VARCHAR2(256) := ''; 
    -- local variable of same type as p_chef
    -- initialized to be empty array
    v_chef                 test_pkg.chef_array := test_pkg.chef_array();
BEGIN
    -- populate local variable
    v_chef.EXTEND (); 
    -- syntax to populate individual elements
    v_chef(1).p_var1 := 'ABC';
    v_chef(1).p_date := '20241120';
    v_chef.EXTEND (); 
    -- syntax using constructor method of the type
    v_chef(2) := test_pkg.chef_type('DEF','20241120');
    
    -- call procedure 
    test_pkg.test_proc
    (
        p_num_chef => 2,
        p_chef => v_chef,
        p_erreur => v_erreur,
        p_message => v_message
    );
    dbms_output.put_line('p_erreur: ' || v_erreur);
    dbms_output.put_line('p_message: ' || v_message);


END;
/

test_proc: 1 ,v_var1: ABC, v_date 20241120
test_proc: 2 ,v_var1: DEF, v_date 20241120
p_erreur: 
p_message: Success


PL/SQL procedure successfully completed.