Error While Calling A Procedure

oraclestored-procedures

What is wrong in this procedure?

I have a table family:

Name          Null?    Type         
------------- -------- ------------ 
NAME          NOT NULL VARCHAR2(25) 
NICK_NAME              VARCHAR2(15) 
GENDER        NOT NULL CHAR(1)      
AGE           NOT NULL NUMBER(3)    
MARITL_STATUS NOT NULL VARCHAR2(15) 
QUALIFICTN    NOT NULL VARCHAR2(20) 
NO_OF_CHLDRN           NUMBER(2)

I have created a procedure to insert values in this table. The procedure is compiled and created successfully:

create or replace procedure "insert_family"    
(NAME varchar2 ,
NICK_NAME varchar2 ,
GENDER VARCHAR2,
AGE number,
MARITL_STAUS varchar2,
QUALIFICTN varchar2,
NO_OF_CHLDRN number)    
is    
begin    
insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);    
end;

But when I try to call this procedure through:

BEGIN
insert_family('AYUSH','SHUKLA','MALE',12,'UNMARRIED','SCHOOL',0);
END;
/

It gives me this error:

BEGIN
insert_family('AYUSH','SHUKLA','MALE',12,'UNMARRIED','SCHOOL',0);
END;
Error report –
ORA-06550: line 2, column 1:
PLS-00201: identifier 'INSERT_FAMILY' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
06550. 00000 – "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

The problem seems to be in procedure as there is no parameter shown to
have values in debug mode:

procedure_parameters

Best Answer

When you create an object in Oracle with double quotes, Oracle creates that objects with the same case as you entered it. Try dropping the "insert_family" procedure and creating it without the double quotes.

This should help.

SQL> CREATE TABLE family
  2       ( NAME          VARCHAR2(25),
  3         NICK_NAME     VARCHAR2(15),
  4         GENDER        CHAR(1),
  5         AGE           NUMBER(3),
  6         MARITL_STATUS VARCHAR2(15),
  7         QUALIFICTN    VARCHAR2(20),
  8         NO_OF_CHLDRN  NUMBER(2) );

Table created.

SQL>
SQL> create or replace procedure "insert_family"
  2     ( NAME varchar2 ,
  3       NICK_NAME varchar2 ,
  4       GENDER VARCHAR2,
  5       AGE number,
  6       MARITL_STAUS varchar2,
  7       QUALIFICTN varchar2,
  8       NO_OF_CHLDRN number)
  9  is
 10  begin
 11  insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);
 12  end;
 13  /

Procedure created.

SQL>
SQL> create or replace procedure "Insert_family"
  2     ( NAME varchar2 ,
  3       NICK_NAME varchar2 ,
  4       GENDER VARCHAR2,
  5       AGE number,
  6       MARITL_STAUS varchar2,
  7       QUALIFICTN varchar2,
  8       NO_OF_CHLDRN number)
  9  is
 10  begin
 11  insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);
 12  end;
 13  /

Procedure created.

SQL>
SQL> create or replace procedure "iNsert_family"
  2     ( NAME varchar2 ,
  3       NICK_NAME varchar2 ,
  4       GENDER VARCHAR2,
  5       AGE number,
  6       MARITL_STAUS varchar2,
  7       QUALIFICTN varchar2,
  8       NO_OF_CHLDRN number)
  9  is
 10  begin
 11  insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);
 12  end;
 13  /

Procedure created.

SQL>
SQL> create or replace procedure "inSert_family"
  2     ( NAME varchar2 ,
  3       NICK_NAME varchar2 ,
  4       GENDER VARCHAR2,
  5       AGE number,
  6       MARITL_STAUS varchar2,
  7       QUALIFICTN varchar2,
  8       NO_OF_CHLDRN number)
  9  is
 10  begin
 11  insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);
 12  end;
 13  /

Procedure created.

SQL>
SQL> exec insert_family('AYUSH','SHUKLA','MALE',12,'UNMARRIED','SCHOOL',0);
BEGIN insert_family('AYUSH','SHUKLA','MALE',12,'UNMARRIED','SCHOOL',0); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'INSERT_FAMILY' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> select object_name, object_type, status from user_objects where upper(object_name)=upper('inSert_family') order by 1;

OBJECT_NAME                    OBJECT_TYPE          STATUS
------------------------------ -------------------- ------------
Insert_family                  PROCEDURE            VALID
iNsert_family                  PROCEDURE            VALID
inSert_family                  PROCEDURE            VALID
insert_family                  PROCEDURE            VALID

4 rows selected.
Related Question