CODEWITHSUNDEEP
plsql
Codefather
Codefather

Reputation: 27

Why do I get ORA-06531: reference to uninitialized collection while trying to call procedure?

i am trying to call the procedure my_package.procedure_test like bellow:

declare
   v_counter BINARY_INTEGER := 1;
   a  tbl_familles;
   v_mytable tbl_familles;
begin
  
  PK_SOA_Famille_Test.get_famille('zz','zz', v_mytable);

  while v_counter <= v_mytable.count
   loop
      Dbms_Output.Put_Line(v_mytable(v_counter).nom);
      v_counter := v_counter + 1;
   end loop;
  
end;

But i get the exception:

ORA-06531: reference to uninitialized collection

The Package definition:

CREATE OR REPLACE Package PK_SOA_Famille_Test as

PROCEDURE get_famille
(num_ass IN VARCHAR2, num_indd IN VARCHAR2,
OutTableParam OUT tbl_familles);
end PK_SOA_Famille_Test;

The Procedure body:

CREATE OR REPLACE PACKAGE BODY PK_SOA_Famille_Test AS
  PROCEDURE get_famille
  (num_ass IN VARCHAR2, num_indd IN VARCHAR2, OutTableParam OUT tbl_familles) IS
      v_counter number := 0;      
            
      Cursor C_typ_famille
        (
            num_ass varchar2 ,num_indd varchar2
        ) Is
        SELECT  nom, prenom, num_imm ,num_ind ,nat_int ,dat_naiss, num_cin, der_stat, der_sit, datsit, indpere, indmere
           from typ_famille
           where (num_imm = num_ass AND num_ind = num_indd) ;
        C2 C_typ_famille%ROWTYPE;
      
  BEGIN

  IF   num_indd is null then 
  
     for EmpCursor in
         (select nom,prenom,num_imm ,num_ind, nat_int, dat_naiss, num_cin, der_stat, der_sit, datsit, indpere, indmere
           from typ_famille
           where num_imm = num_ass)
           
     loop
       v_counter := v_counter + 1;
       OutTableParam(v_counter).nom := EmpCursor.nom;
       OutTableParam(v_counter).prenom := EmpCursor.prenom;
       OutTableParam(v_counter).num_imm := EmpCursor.num_imm;
       OutTableParam(v_counter).num_ind := EmpCursor.num_ind;
       OutTableParam(v_counter).nat_int  := EmpCursor.nat_int ;
       OutTableParam(v_counter).dat_naiss := EmpCursor.dat_naiss;
       OutTableParam(v_counter).num_cin := EmpCursor.num_cin;
       OutTableParam(v_counter).der_stat := EmpCursor.der_stat;
       OutTableParam(v_counter).der_sit := EmpCursor.der_sit;
       OutTableParam(v_counter).datsit := EmpCursor.datsit;
       OutTableParam(v_counter).indpere := EmpCursor.indpere;
       OutTableParam(v_counter).indmere := EmpCursor.indmere;
    end loop; 
        
END IF ;
  END get_famille;
END PK_SOA_Famille_Test;
/

I have also created the types rec_famille as an object and tbl_familles as a table of rec_famille in schema level.

Upvotes: 0

Views: 897

Answers (2)

Rajesh
Rajesh

Reputation: 692

A dummy code of procedure defination

create or replace PROCEDURE SP_NAME_CALL (
    i_env                IN VARCHAR2,
    i_date               IN DATE,
    i_result             OUT VARCHAR2
) AS
    
    /*
        variables
    */
        
BEGIN
    /*
        code
    */
    
END;
/

While calling the procedure make sure you've specified sufficient variable datatype limit in DECLARE section. It's a one way to avoid the ORA-06531 error.

set SERVEROUTPUT ON
DECLARE
    I_RESULT VARCHAR2(200);  -- var limit should be enough to hold returned data
BEGIN
    SP_NAME_CALL('NAME', 'DD-MON-YY',I_RESULT);
    DBMS_OUTPUT.PUT_LINE('I_RESULT = ' || I_RESULT);
END;
/

Thanks!

Upvotes: 0

Kevin Seymour
Kevin Seymour

Reputation: 807

When num_indd is NOT NULL then PK_SOA_Famille_Test.get_famille effectively does nothing. This leaves OutTableParam uninitialized. You could have an ELSE in your IF-statement with OutTableParam := tbl_familles(); as EJ Egyed suggested.

Additionally, looping through a cursor to fill a collection is a waste: you should use BULK COLLECT instead.

CREATE OR REPLACE PACKAGE BODY pk_soa_famille_test IS
  PROCEDURE get_famille(num_ass       IN VARCHAR2,
                        num_indd      IN VARCHAR2,
                        outtableparam OUT) IS
  
  BEGIN
  
    IF num_indd IS NULL THEN
      SELECT nom,
             prenom,
             num_imm,
             num_ind,
             nat_int,
             dat_naiss,
             num_cin,
             der_stat,
             der_sit,
             datsit,
             indpere,
             indmere
        BULK COLLECT
        INTO outtableparam
        FROM typ_famille
       WHERE num_imm = num_ass;
    ELSE
      outtableparam := tbl_familles;
    END IF;
  END get_famille;
END pk_soa_famille_test;

Upvotes: 1

Related Questions