Home » SQL & PL/SQL » SQL & PL/SQL » unable to view procedure (9i, Win XP)
unable to view procedure [message #338555] Tue, 05 August 2008 06:06 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

I am unable to view a procedure which is within a package, here is what I have done.

SQL> select procedure_name from user_procedures where procedure_name like '%PWD%
';

PROCEDURE_NAME
------------------------------
P_SE_CHKCLOGINPWD
P_SE_CHKLOGINPWD
P_SE_UPDPWD
P_SE_CHKLOGINPWD
P_SE_UPDPWD
P_SE_FETCHLOSTPWD
P_SE_UPDUSRPWD

7 rows selected.

SQL> desc p_se_admin.p_se_chkcloginpwd
ERROR:
ORA-04043: object p_se_admin.p_se_chkcloginpwd does not exist


P_SE_ADMIN is the package and P_SE_CHKCLOGINPWD is a procedure within it. Also, how can we check if the procedure exists in the package when the desc not able to give the desired results due to large number of procedures in the package.
Re: unable to view procedure [message #338557 is a reply to message #338555] Tue, 05 August 2008 06:09 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Try dba_source


Regards,
Oli
Re: unable to view procedure [message #338559 is a reply to message #338557] Tue, 05 August 2008 06:15 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
nope..it is not recognizing the procedure so it does not show its source..but surprisingly it is showing in the user_procedure system table and not when I try to execute this

desc p_se_admin.p_se_chkcloginpwd


any guesses..?
Re: unable to view procedure [message #338562 is a reply to message #338559] Tue, 05 August 2008 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't describe a procedure of a package, you can only describe the whole package.

Regards
Michel
Re: unable to view procedure [message #338564 is a reply to message #338559] Tue, 05 August 2008 06:35 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Check the spelling of the named object and compile the entire package.

Regards,
Oli
Re: unable to view procedure [message #338566 is a reply to message #338562] Tue, 05 August 2008 06:36 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Michel Cadot wrote on Tue, 05 August 2008 06:34
You can't describe a procedure of a package, you can only describe the whole package.

Regards
Michel




Not agree with you Michel. Its possible..Please Try!

DESC <Packagename>.<procedurename>

[Updated on: Tue, 05 August 2008 06:39]

Report message to a moderator

Re: unable to view procedure [message #338574 is a reply to message #338566] Tue, 05 August 2008 06:50 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Then show us how you did it....since I cant do it

Connected to:
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE OR REPLACE PACKAGE pack1
  2  AS
  3     PROCEDURE procedure1 (p1 IN NUMBER);
  4  END pack1;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pack1
  2  AS
  3     PROCEDURE procedure1 (p1 IN NUMBER)
  4     IS
  5     BEGIN
  6        NULL;
  7     END;
  8  END pack1;
  9  /

Package body created.

SQL> desc pack1.procedure1;
ERROR:
ORA-04043: object pack1.procedure1 does not exist
Re: unable to view procedure [message #338577 is a reply to message #338555] Tue, 05 August 2008 06:55 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

Quote:

Also, how can we check if the procedure exists in the package



> SELECT text FROM dba_sourceWHERE LOWER(TEXT) LIKE '%procedure_name_in_lowercase%' AND LOWER(NAME)='package_name_in_lowercase'

Quote:


the desc not able to give the desired results due to large number of procedures in the package.



Not like that.Check the spelling of the named object
Compile the entire package




Re: unable to view procedure [message #338582 is a reply to message #338577] Tue, 05 August 2008 07:03 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
While trying to view wil SQL PLUS it gives the error. But if you try with TOAD it gives the description.


PACKAGE DB0DAFX1.PACK1
PROCEDURE PROCEDURE1
 Argument Name                  Type                    In/Out
 ------------------------------ ----------------------- ------
 P1                             NUMBER                  IN

Re: unable to view procedure [message #338584 is a reply to message #338577] Tue, 05 August 2008 07:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

SELECT text FROM dba_sourceWHERE LOWER(TEXT) LIKE '%procedure_name_in_lowercase%' AND LOWER(NAME)='package_name_in_lowercase'



Of course, that will not only return a row if the procedure exist in the package.

That will also return a row when the procedure is called in the package. Or mentioned in a comment somewhere. Or even if there are other random texts that contain the same substring.
Re: unable to view procedure [message #338588 is a reply to message #338577] Tue, 05 August 2008 07:16 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
ok..got it I was able to see the the source of the procedure but isnt there a single system table that will give me this data.
Re: unable to view procedure [message #338589 is a reply to message #338566] Tue, 05 August 2008 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Not agree with you Michel. Its possible..Please Try!

DESC <Packagename>.<procedurename>

I don't agree with and I checked this for years.
For SQL*Plus itself:
SQL> help desc

 DESCRIBE
 --------

 Lists the column definitions for a table, view, or synonym,
 or the specifications for a function or procedure.

 DESC[RIBE] {[schema.]object[@connect_identifier]}

procedure inside a package is NOT an object.

Regards
Michel
Re: unable to view procedure [message #338590 is a reply to message #338588] Tue, 05 August 2008 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
durgadas.menon wrote on Tue, 05 August 2008 14:16
ok..got it I was able to see the the source of the procedure but isnt there a single system table that will give me this data.

USER_ARGUMENTS

Regards
Michel

Re: unable to view procedure [message #338591 is a reply to message #338584] Tue, 05 August 2008 07:21 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:

That will also return a row when the procedure is called in the package. Or mentioned in a comment somewhere. Or even if there are other random texts that contain the same substring.




Then..
Have to include TYPE='PACKAGE' in query.
Re: unable to view procedure [message #338596 is a reply to message #338589] Tue, 05 August 2008 07:25 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
[quote ]
DESC <Packagename>.<procedurename>[/quote]
I don't agree with and I checked this for years.
For SQL*Plus itself:
SQL> help desc

 DESCRIBE
 --------

 Lists the column definitions for a table, view, or synonym,
 or the specifications for a function or procedure.

 DESC[RIBE] {[schema.]object[@connect_identifier]}

procedure inside a package is NOT an object.

Regards
Michel


[/QUOTE]

@Michel, My apologies. Checked in Toad and it worked fine.
In SQL PLUS, its not possible...
Re: unable to view procedure [message #338603 is a reply to message #338590] Tue, 05 August 2008 07:30 Go to previous message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Thanks..that is exactly what I was searching for..
Previous Topic: Use of REGEXP_LIKE
Next Topic: How to find ALL jobs that have run till date ?
Goto Forum:
  


Current Time: Mon Dec 05 21:31:53 CST 2016

Total time taken to generate the page: 0.06062 seconds