Error While Calling A Procedure

Question

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

Answer 1

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.

• beskar: Although it did work !! But I am still in doubt as : create or replace procedure "INSERTUSER" (id IN NUMBER, name IN VARCHAR2) is begin insert into user1 values(id,name); end; is working fine with : BEGIN insertuser(101,'Rahul'); END; /
• Michael Kutz: You could also call the procedure with double quotes. eg "insert_family"
• Gandolf989: You need to avoid using double quotes when you create objects. Then everything will be created as upper case.

Answer 2

Technically, it should work irregardless of case-sensitivity.

It appears to be a bug in SQL*Developer.

The SQL Statement SQL*Developer uses is:

SELECT
 position, argument_name, data_level
 ,data_type, in_out,
 CASE
 WHEN char_used = 'C' THEN char_length
 ELSE data_length
 END data_length
 ,data_precision, data_scale
 ,type_owner, type_name, type_subname
 ,pls_type
FROM sys.dba_arguments
WHERE
 owner =:owner
 AND object_id =:id
 AND object_name =:name
 AND nvl( overload,'0' ) = :overload
ORDER BY sequence;

The value for the BIND variable "NAME" is "INSERT_FAMILY" instead of "insert_family".

Object names without double-quotes are case insensitive.

With double-quotes, object names are case sensitive and you can have spaces (and other non-standard characters) in the name.

The search above is doing a case-sensitive search. The value has been UPPER(). Since you defined the procedure as lower-case using double-quotes, there are no parameters.

• Gandolf989: "Technically, it should work irregardless of case-sensitivity." It is not a bug with SQL Developer. sqlplus will yield the same results. It is a "feature" with Oracle.