1

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

2

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.
  • 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; / – Akash Jun 28 '18 at 19:07
  • You could also call the procedure with double quotes. eg "insert_family" – Michael Kutz Jun 28 '18 at 19:07
  • 1
    You need to avoid using double quotes when you create objects. Then everything will be created as upper case. – Gandolf989 Jun 28 '18 at 19:13
  • @Akash Your welcome. – Gandolf989 Jul 3 '18 at 14:04
1

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.

  • "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. – Gandolf989 Jul 3 '18 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.