PL/SQL Function exists?

Question

I am trying to create a function called add_extra.

CREATE OR REPLACE FUNCTION add_extra(p_price NUMBER)
RETURN NUMBER
IS
BEGIN
  RETURN(9000);
END add_extra;

However, when I run the script, it says:

Error starting at line 1 in command:
CREATE OR REPLACE FUNCTION add_extra(p_price NUMBER)
RETURN NUMBER
IS
BEGIN
  RETURN(9000);
END add_extra;
Error report:
ORA-00955: name is already used by an existing object
00955. 00000 -  "name is already used by an existing object"
*Cause:    
*Action:

And when I try: DROP FUNCTION add_extra; It tells me:

Error starting at line 1 in command:
DROP FUNCTION add_extra
Error report:
SQL Error: ORA-04043: object ADD_EXTRA does not exist
04043. 00000 -  "object %s does not exist"
*Cause:    An object name was specified that was not recognized by the system.
               There are several possible causes:
           - An invalid name for a table, view, sequence, procedure, function,
           package, or package body was entered. Since the system could not
           recognize the invalid name, it responded with the message that the
           named object does not exist.
           - An attempt was made to rename an index or a cluster, or some
           other object that cannot be renamed.
*Action:   Check the spelling of the named object and rerun the code. (Valid
           names of tables, views, functions, etc. can be listed by querying
           the data dictionary.)

Does it exist or does it not exist? What am I doing wrong?

Could it be that the function exists but you dont have the right to see it? Are you trying th create the function in the schema you logged in or did you use "alter session set current_schema..." to work against a different schema? Have you tried to specify the schema explicitly (CREATE OR REPLACE FUNCTION MYUSER.ADD_EXTRA...)? — Codo, Mar 22 '11 at 10:37
I am not sure, but I guess it is in the schema I am logged in to because all other functions, procedures, and so on appear in the list of objects. Also, when I use username.add_extra I get the same error. — whirlwin, Mar 22 '11 at 10:46
This query may help identify which schema (`OWNER`) your object is in: `SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = 'ADD_EXTRA'` — Adam Paynter, Mar 22 '11 at 11:00
Thanks for the tip! It shows `DEGYVI09` as OWNER, however when I try run: `DROP FUNCTION DEGYVI09.ADD_EXTRA`, it tells me it doesn't exist. — whirlwin, Mar 22 '11 at 11:09

score 1 · Answer 1 · edited Nov 14 '11 at 17:13

1

CREATE OR REPLACE FUNCTION add_extra(p_price IN NUMBER)

__IN__ keyword is missing in your query.

edited Nov 14 '11 at 17:13

Felix

88,392
43
149
167

answered Nov 14 '11 at 15:10

Angad Singh

11
1

score 0 · Accepted Answer · answered Mar 22 '11 at 11:21

0

I was almost certain it was a FUNCTION, but it was a PROCEDURE! So, DROP PROCEDURE ADD_EXTRA; worked.

answered Mar 22 '11 at 11:21

whirlwin

16,044
17
67
98

1

describe add_extra; will tell you what you're dealing with for a given object. – tbone Mar 22 '11 at 14:49

PL/SQL Function exists?

2 Answers2