CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Yash Chitroda
Yash Chitroda

Reputation: 661

Procedures giving error : ORA-00955: name is already used by an existing object

CREATE TABLE product
( product_id number(10) NOT NULL,
  product_name varchar2(50) NOT NULL,
  price varchar2(50)
);

CREATE OR REPLACE PROCEDURE p1 (product_id in number,
  product_name in varchar2,
  price in number) 

IS
BEGIN
INSERT INTO product values(1,'yash',100);
DBMS_OUTPUT.PUT_LINE('VALUE INSERTED');

end;

Error: ORA-00955: name is already used by an existing object

Can someone please provide me and suitable example

Upvotes: 1

Views: 2228

Answers (3)

EdStevens
EdStevens

Reputation: 3872

You have twice asked for a 'suitable example' or 'justifying code'. I assume from that you don't understand or don't believe what has been explained about your procedure name conflicting with some other object name. So here's the example that proves it.

SQL> --
SQL> -- check that we don't own anything called MYTEST
SQL> --
SQL> select object_name
  2  from user_objects
  3  where upper(object_name) = 'MYTEST'
  4  ;

no rows selected

SQL> --
SQL> -- create a table MYTEST
SQL> --
SQL> create table mytest (dob date);

Table created.

SQL> --
SQL> -- check objets
SQL> --
SQL> select object_type,
  2         object_name
  3  from user_objects
  4  where upper(object_name) = 'MYTEST'
  5  ;

OBJECT_TYPE         OBJECT_NAME
------------------- --------------------
TABLE               MYTEST

1 row selected.

SQL> --
SQL> -- create a procedure MYTEST
SQL> --
SQL> create or replace procedure mytest
  2  as
  3  begin
  4    dbms_output.put_line('Hello world');
  5  end;
  6  /
create or replace procedure mytest
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> --
SQL> -- now, drop the table and try the procedure again
SQL> --
SQL> drop table mytest purge;

Table dropped.

SQL> create or replace procedure mytest
  2  as
  3  begin
  4    dbms_output.put_line('Hello world');
  5  end;
  6  /

Procedure created.

SQL> --
SQL> -- cleanup
SQL> --
SQL> drop table mytest purge;
drop table mytest purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> drop procedure mytest;

Procedure dropped.

In your case if your procedure is named 'p1' then check:

select object_type,
       object_name
from user_objects
where upper(object_name) = 'P1'
;

Upvotes: 1

user14137003
user14137003

Reputation:

For your this problem the solution is write the following line (EXECUTE p1;) By typing this command you will get your desired output

Upvotes: 1

Mureinik
Mureinik

Reputation: 312116

From the error message, the name p1 is already in use (as an object name whose type is different than a procedure). Just give the procedure a different (and more descriptive!) name:

CREATE OR REPLACE PROCEDURE insert_product 
 (product_id in number,
  product_name in varchar2,
  price in number) 

IS
BEGIN
INSERT INTO product values(1,'yash',100);
DBMS_OUTPUT.PUT_LINE('VALUE INSERTED');
END;

Upvotes: 3

Related Questions