CODEWITHSUNDEEP
oracle-databaseplsql
Scripta14
Scripta14

Reputation: 463

PLS-00201 – identifier must be declared, passing a collection to procedure

I'm trying to pass a collection to procedure, but when I compile the package, I'll get this message:"PLS-00201 – identifier must be declared". this is my code:

create or replace package PACK_DW_TEMP  
as

procedure A (.......);
--
procedure B (error_list in out l_error);

end PACK_DW_TEMP;

In the package body I've created the collection in the procedure A and passed it to procedure B

create or replace package body PACK_DW_TEMP
as

procedure A ( ........ )
as
begin
declare

     
    
    type error IS RECORD(
            cod_error       NUMBER,
            descr_error     VARCHAR2(100)
            );

    type l_errori is table of error;
    error_list  l_error := l_error(); 
begin
  procedure B(error_list);
end;
end;

Into procedure B:

procedure B ( error_list in out l_error )
as
begin
declare
    i    NUMBER;
    
    type err IS RECORD(
            cod_error       NUMBER,
            descr_error     VARCHAR2(100)
            );

    type l_err is table of err;
    err_list  l_err := l_err(); 

begin
     i := 0;
     for i in 1..5 loop
      err_list(i).cod_err := error_list(i).cod_error;
      err_list(i).descr_err := error_list(i).descr_error;
     end loop;
end;

end;

My target is to pass the collection to procedure B and to assegn values to the new collection.

Upvotes: 0

Views: 1026

Answers (2)

MT0
MT0

Reputation: 168623

A type declared locally to a procedure and another type declared locally to another procedure with exactly the signature are not the same types and you cannot pass one to the other. You need to create the type externally to the procedures rather than internally.

Also, you cannot use the type in the signature for the package when it is not declared except internally to the procedure which is declared in the body of the package.

create or replace package PACK_DW_TEMP  
as
  TYPE error IS RECORD(
    cod_error       NUMBER,
    descr_error     VARCHAR2(100)
  );

  type l_error is table of error;
  procedure A;
  procedure B (error_list in out l_error);
  
end PACK_DW_TEMP;
/

and

create or replace package body PACK_DW_TEMP
as
  procedure A
  as
    error_list  l_error := l_error(); 
  begin
    error_list.EXTEND(2);
    error_list(1).cod_error   := 1;
    error_list(1).descr_error := 'DESCR1';
    error_list(2).cod_error   := 2;
    error_list(2).descr_error := 'DESCR2';
    
    B(error_list);

    FOR i IN 1 .. error_list.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        error_list(i).cod_error || ': ' || error_list(i).descr_error
      );
    END LOOP;
  end;

  procedure B ( error_list in out l_error )
  as
  begin
    error_list.EXTEND;
    error_list(error_list.COUNT).cod_error   := 99;
    error_list(error_list.COUNT).descr_error := 'DESCR99';
  end;
end;    
/

Then you can call it using:

BEGIN
  PACK_DW_TEMP.A();
END;
/

Which outputs:

1: DESCR1
2: DESCR2
99: DESCR99

db<>fiddle here

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

You're defining a type called l_err in the package body but in procedure B you're trying to use a type called l_error. Also, you have to define l_error in the package spec, prior to its first use:

create or replace package PACK_DW_TEMP  
as
  type err IS RECORD(
          cod_error       NUMBER,
          descr_error     VARCHAR2(100)
          );

  type l_error is table of err;

  procedure A (.......);
  --
  procedure B (error_list in out l_error);  
end PACK_DW_TEMP;

and remove the definitions of type_err, l_errori, and l_err from the package body.

Upvotes: 2

Related Questions