Home » SQL & PL/SQL » SQL & PL/SQL » Unable to Drop a Procedure. (Oracle 10g, Windows xp)
Unable to Drop a Procedure. [message #571691] Thu, 29 November 2012 03:43 Go to next message
stalin4d
Messages: 214
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
Hi,

I tried to Drop a procedure we have created, but an error is raised while droping it.

ora-04043 object does not exist


what is the reason?
Re: Unable to Drop a Procedure. [message #571693 is a reply to message #571691] Thu, 29 November 2012 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
- You have not the privilege to do so.
- It's an invalid synonym (no object pointed to).
- It is not a procedure.

Regards
Michel

[Updated on: Thu, 29 November 2012 03:51]

Report message to a moderator

Re: Unable to Drop a Procedure. [message #571694 is a reply to message #571693] Thu, 29 November 2012 03:53 Go to previous messageGo to next message
stalin4d
Messages: 214
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
ok then how to clear that procedure from that area?

we dont want to list that.
Re: Unable to Drop a Procedure. [message #571695 is a reply to message #571694] Thu, 29 November 2012 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From which area? (Note I can't download files).

Regards
Michel
Re: Unable to Drop a Procedure. [message #571701 is a reply to message #571695] Thu, 29 November 2012 04:46 Go to previous messageGo to next message
Littlefoot
Messages: 19609
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you verify all 3 options Michel mentioned? What was the result?
Re: Unable to Drop a Procedure. [message #571704 is a reply to message #571701] Thu, 29 November 2012 05:22 Go to previous messageGo to next message
stalin4d
Messages: 214
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
i have checked with michels options,

that procedure name 'Aim_Sp_login_Check' is found in the All Objects area,
yes we have the rights or privileges to do the action.

then what might be the reason.



Re: Unable to Drop a Procedure. [message #571706 is a reply to message #571704] Thu, 29 November 2012 05:29 Go to previous messageGo to next message
Littlefoot
Messages: 19609
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would you mind and explain how you performed these checks, one by one? Please, post your SQL*Plus session which proves what you claim.
Re: Unable to Drop a Procedure. [message #571708 is a reply to message #571706] Thu, 29 November 2012 06:01 Go to previous messageGo to next message
stalin4d
Messages: 214
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
I have put the screen shots step by step in the pdf attachment.
and the procedure coding below;

create or replace procedure AIM_SP_Login_Check(p_recordset1 OUT SYS_REFCURSOR)
             AS
BEGIN
  OPEN p_recordset1 FOR
  Select T005_CODE, T010_CODE, T010_NAME From AIM_M_DIVISION  Order by T005_CODE, T010_CODE;
END;
Re: Unable to Drop a Procedure. [message #571709 is a reply to message #571708] Thu, 29 November 2012 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 29 November 2012 10:56
From which area? (Note I can't download files).

Regards
Michel

Re: Unable to Drop a Procedure. [message #571711 is a reply to message #571709] Thu, 29 November 2012 06:42 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Query all_objects to find out what type the object is and who it belongs to.
Re: Unable to Drop a Procedure. [message #571712 is a reply to message #571711] Thu, 29 November 2012 06:55 Go to previous messageGo to next message
Littlefoot
Messages: 19609
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You struggle SO HARD, just to make it more difficult for forum members to help you. It appears that you don't really want to drop this procedure, you just enjoy posting messages here.

Screenshots you posted show that it is a procedure. OK. What about your privileges? How did you check that? You claim that you have them. Somehow, I don't trust you.
Re: Unable to Drop a Procedure. [message #571713 is a reply to message #571704] Thu, 29 November 2012 06:55 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
stalin4d wrote on Thu, 29 November 2012 06:22
procedure name 'Aim_Sp_login_Check' is found in the All Objects area,
yes we have the rights or privileges to do the action.


If you see object in ALL_OBJECTS is means you have at least one privilege on it, not necessarily one you need:

SQL> create procedure p1
  2    is
  3    begin
  4        null;
  5  end;
  6  /

Procedure created.

SQL> grant execute on scott.p1 to u1
  2  /

Grant succeeded.

SQL> connect u1@orcl
Enter password: **
Connected.
SQL> select  count(*)
  2    from  all_objects
  3    where owner = 'SCOTT'
  4      and object_name = 'P1'
  5      and object_type = 'PROCEDURE'
  6  /

  COUNT(*)
----------
         1

SQL> exec scott.p1;

PL/SQL procedure successfully completed.

SQL> drop procedure scott.p1
  2  /
drop procedure scott.p1
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> 


You need to check USER_SYS_PRIVS and USER_ROLE_PRIVS and make sure user has DROP ANY PROCEDURE privilege.

SY.
Re: Unable to Drop a Procedure. [message #571714 is a reply to message #571713] Thu, 29 November 2012 06:59 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or more simply, connect as the owner of the object in order to drop it.
Re: Unable to Drop a Procedure. [message #571715 is a reply to message #571714] Thu, 29 November 2012 07:42 Go to previous messageGo to next message
Littlefoot
Messages: 19609
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Or even better, set a filter to your GUI so that it won't display a procedure that pokes your eyes (because, someone else might get hurt if you just drop it).
Re: Unable to Drop a Procedure. [message #571750 is a reply to message #571715] Thu, 29 November 2012 22:38 Go to previous messageGo to next message
stalin4d
Messages: 214
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
Hi Solomon,

I dont get msg like insufficient privileges, just i get like Error Droping procedure
and error number ora-04043.

no one will bother about droping that procedure,
well littlefoot how to set filter to the GUI.
Re: Unable to Drop a Procedure. [message #571751 is a reply to message #571750] Thu, 29 November 2012 22:44 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
oracle is too dumb to lie regarding any error

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.)
Re: Unable to Drop a Procedure. [message #571758 is a reply to message #571750] Fri, 30 November 2012 01:26 Go to previous messageGo to next message
Littlefoot
Messages: 19609
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know how to set a filter in PL/SQL Developer; I never used it, but I hoped that it might have something similar to TOAD:

./fa/10543/0/
Re: Unable to Drop a Procedure. [message #571783 is a reply to message #571758] Fri, 30 November 2012 06:12 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's a drop down at the top of the object list, that currently says all objects, change it to my objects (or equivalent, my version is different to yours).
Re: Unable to Drop a Procedure. [message #571791 is a reply to message #571750] Fri, 30 November 2012 08:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
stalin4d wrote on Thu, 29 November 2012 23:38
I dont get msg like insufficient privileges


Login as same user you were logged in when dropping procedure. Issue:

SELECT  owner
        object_name,
        object_type
  FROM  all_objects
  WHERE upper(object_name) = upper('AIM_SP_Login_Check')
/


and post results.

SY.
Re: Unable to Drop a Procedure. [message #571796 is a reply to message #571791] Fri, 30 November 2012 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you don't understand he is unable to write any SQL statement. Wink
Show him how to get the result of your query without typing anything. Razz

Regards
Michel
Re: Unable to Drop a Procedure. [message #571807 is a reply to message #571796] Fri, 30 November 2012 11:58 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
On closer inspection of the original image the problem is nothing to do with privileges. It's to do with case.
The procedure name is displayed in mixed case. So it was created in mixed case by wrapping the name in double quotes.
It has to be dropped the same way:
drop procedure "AIM_SP_Login_Check";


Next time don't create objects with mixed case names
Re: Unable to Drop a Procedure. [message #571829 is a reply to message #571807] Sat, 01 December 2012 03:29 Go to previous messageGo to next message
Littlefoot
Messages: 19609
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/3971/0/
Re: Unable to Drop a Procedure. [message #572428 is a reply to message #571807] Wed, 12 December 2012 01:16 Go to previous messageGo to next message
stalin4d
Messages: 214
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
Mixed case means Upper and lower case in creating an object are you saying this?
but i dont think so this is not the problem.

littlefoot its not similar like Toad, We use Plsql Developer which does'nt have filter
like toad.

[Updated on: Wed, 12 December 2012 01:20]

Report message to a moderator

Re: Unable to Drop a Procedure. [message #572462 is a reply to message #572428] Wed, 12 December 2012 04:15 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
stalin4d wrote on Wed, 12 December 2012 07:16
Mixed case means Upper and lower case in creating an object are you saying this?

Yes
stalin4d wrote on Wed, 12 December 2012 07:16

but i dont think so this is not the problem.

I guarantee it is the problem. Look:
SQL> create table test (a number);

Table created.

SQL> create table "test" (a number);

Table created.

SQL> create table "Test" (a number);

Table created.

SQL> select table_name from user_tables where upper(table_name) = 'TEST';

TABLE_NAME
--------------------------------------------------------------------------------
TEST
Test
test

SQL> --the one in upper case is the original, oracle automatically stores object names in upper case
SQL> --unless you wrap the object name in double quotes
SQL>
SQL> drop table test;

Table dropped.

SQL>  select table_name from user_tables where upper(table_name) = 'TEST';

TABLE_NAME
--------------------------------------------------------------------------------
Test
test

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


SQL> --doesn't work because oracle uppers the table name which then doesn't match the existing tables.
SQL> --need to wrap in in double quotes
SQL> drop table "Test";

Table dropped.

SQL>  select table_name from user_tables where upper(table_name) = 'TEST';

TABLE_NAME
--------------------------------------------------------------------------------
test

SQL> drop table "test";

Table dropped.

SQL>

Any object that appears in the object lists in mixed case (like your procedure) was created by wrapping the object name in double quotes. In order to be anything with that object you have to wrap the name in double quotes and get the case of all the letters the same every time you want to reference it.

stalin4d wrote on Wed, 12 December 2012 07:16

littlefoot its not similar like Toad, We use Plsql Developer which does'nt have filter
like toad.

Yes it does, I told you so, I use PL/SQL developer so I would know. The filter is irrelevant to this problem though.
Re: Unable to Drop a Procedure. [message #572466 is a reply to message #572462] Wed, 12 December 2012 04:35 Go to previous messageGo to next message
mvmkandan
Messages: 67
Registered: May 2010
Location: Trivendrum
Member
Stalin,

try with this statement by passing your procedure name.. check it will return any records or not.

select * from user_procedures where procedure_name = :proc_name



Veera
Re: Unable to Drop a Procedure. [message #572492 is a reply to message #572466] Wed, 12 December 2012 07:51 Go to previous messageGo to next message
joy_division
Messages: 4513
Registered: February 2005
Location: East Coast USA
Senior Member
mvmkandan wrote on Wed, 12 December 2012 05:35
Stalin,

try with this statement by passing your procedure name.. check it will return any records or not.

select * from user_procedures where procedure_name = :proc_name



This is incorrect. Plus cookiemonster gave the proper advice already.
Re: Unable to Drop a Procedure. [message #572530 is a reply to message #572492] Wed, 12 December 2012 23:39 Go to previous messageGo to next message
mvmkandan
Messages: 67
Registered: May 2010
Location: Trivendrum
Member
joy_division wrote

This is incorrect. Plus cookiemonster gave the proper advice already.


Why is this INCORRECT.....
Re: Unable to Drop a Procedure. [message #572532 is a reply to message #572530] Thu, 13 December 2012 00:24 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
mvmkandan wrote on Wed, 12 December 2012 21:39
joy_division wrote

This is incorrect. Plus cookiemonster gave the proper advice already.


Why is this INCORRECT.....


>select * from user_procedures where procedure_name = :proc_name
defaults to UPPERCASE
Re: Unable to Drop a Procedure. [message #572559 is a reply to message #572532] Thu, 13 December 2012 04:07 Go to previous message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well not exactly.
The procedure name in user_procedures (and user_objects) for the problem procedure will be in mixed case. That's what PL/SQL is displaying in the original screenshot.
Oracle uppers any object name in code before storing it in, or comparing it to, the data dictionary, unless the object name is wrapped in double quotes. In which case oracle leaves it as it is.
Previous Topic: How to display data in a required format by SQL Select Query?
Next Topic: nullifying variables
Goto Forum:
  


Current Time: Thu Sep 18 03:04:51 CDT 2014

Total time taken to generate the page: 0.11644 seconds