ora-06575 package or function is in an invalid state pl sql

Question

I am new to pl sql and i am trying to call a procedure but i am getting the error ora-06575 package or function is in an invalid state. What do you think i am doing wrong? Thanks

select * from NB_tfr_USERS; --previous table created


create or replace procedure nb_tfr_add_user

(
i_user_name in nb_tfr_users.name%type,
i_address   in nb_tfr_users.address%type,
i_birthdate in nb_tfr_users.birthdate%type,
o_userid   out nb_tfr_users.id%type,
o_errm     out varchar2
) AS 
--Variables
 l_user_id number;

--Parameters invalid exception
  error_params exception;
 BEGIN
 --validate parameters
 --- name can not be empty
 --- birthdate can be empty
   if i_user_name is null or length(i_user_name) = 0 then
    raise error_params;
     end if;

--initialize variables
 l_user_id := nb_seq_tfr_user_id.nextval;

--insert new user
 insert into NB_tfr_USERS (id, name, address, birthdate)
 values (l_user_id, i_user_name, i_address, i_birthdate);


 --deal with exceptions
   exception
   when error_params then
     o_errm := 'Invalid Parameters!';
     dbms_output.put_line(o_errm);

   when others then
   dbms_output.put_line('Error: '||sqlerrm);
    o_errm:=substr(1,150,sqlerrm);
    raise;
     END NB_tfr_ADD_USER;

 call
   nb_tfr_add_user(54,'tf','lx',12121989);

So when i try to call the procedure i get the error.

Answer 1

Well, it looks OK - apart from the fact that you incorrectly called the procedure.

Here's a test case.

SQL> create table nb_tfr_users
  2    (id number, name varchar2(10), address varchar2(10), birthdate date);

Table created.

SQL> create sequence nb_seq_tfr_user_id;

Sequence created.

The procedure:

SQL> create or replace procedure nb_tfr_add_user
  2  (
  3  i_user_name in nb_tfr_users.name%type,
  4  i_address   in nb_tfr_users.address%type,
  5  i_birthdate in nb_tfr_users.birthdate%type,
  6  o_userid   out nb_tfr_users.id%type,
  7  o_errm     out varchar2
  8  ) AS
  9    --Variables
 10    l_user_id number;
 11    --Parameters invalid exception
 12    error_params exception;
 13  BEGIN
 14    --validate parameters
 15    --- name can not be empty
 16    --- birthdate can be empty
 17    if i_user_name is null or length(i_user_name) = 0 then
 18       raise error_params;
 19    end if;
 20
 21    --initialize variables
 22    l_user_id := nb_seq_tfr_user_id.nextval;
 23
 24    --insert new user
 25    insert into NB_tfr_USERS (id, name, address, birthdate)
 26      values (l_user_id, i_user_name, i_address, i_birthdate);
 27
 28   --deal with exceptions
 29    exception
 30      when error_params then
 31        o_errm := 'Invalid Parameters!';
 32        dbms_output.put_line(o_errm);
 33
 34      when others then
 35        dbms_output.put_line('Error: '||sqlerrm);
 36        o_errm:=substr(1,150,sqlerrm);
 37        raise;
 38  END NB_tfr_ADD_USER;
 39  /

Procedure created.

SQL>

Testing - it shows how you should have called it. As there are 3 IN and 2 OUT parameters, that's what you should have done as well.

SQL> set serveroutput on
SQL> declare
  2    l_userid nb_tfr_users.id%type;
  3    l_Errm   varchar2(200);
  4  begin
  5    nb_tfr_add_user(i_user_name => 'tf',
  6                    i_address   => 'lx',
  7                    i_birthdate => sysdate,
  8                    o_userid    => l_userid,
  9                    o_errm      => l_errm);
 10    dbms_output.put_Line(l_userid ||' '||l_errm);
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select * from nb_tfr_users;

        ID NAME       ADDRESS    BIRTHDAT
---------- ---------- ---------- --------
         1 tf         lx         09.09.19

SQL>

If you got the error, it - obviously - means that there's something wrong. I don't know what, but - maybe you forgot to create the sequence.

Anyway: after creating stored procedures, check whether everything is OK by running (in SQL*Plus) show err, e.g.

SQL> create or replace procedure p_test is
  2  begin
  3    insert into abc (id) values (seq_does_not_exist.nextval);
  4  end;
  5  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE P_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PL/SQL: SQL Statement ignored
3/15     PL/SQL: ORA-00942: table or view does not exist
SQL>

Or, query user_source:

SQL> select line, text, attribute from user_errors where name = 'P_TEST';

 LINE TEXT                                               ATTRIBUTE
----- -------------------------------------------------- ---------
    3 PL/SQL: ORA-00942: table or view does not exist    ERROR
    3 PL/SQL: SQL Statement ignored                      ERROR

SQL>

I suggest you do the same and see what's wrong with the procedure.