Home » SQL & PL/SQL » SQL & PL/SQL » Need Command
Need Command [message #184598] Thu, 27 July 2006 03:32 Go to next message
Splinter2010
Messages: 20
Registered: July 2006
Location: EGYPT
Junior Member

can you tell me when i need to show all procdures in my database user what command i shoul use??
thax Razz
Re: Need Command [message #184609 is a reply to message #184598] Thu, 27 July 2006 04:04 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


 select * from user_objects where OBJECT_TYPE='PROCEDURE'


Thumbs Up
Rajuvan.
Re: Need Command [message #184614 is a reply to message #184609] Thu, 27 July 2006 04:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I was going to suggest just that, but I was beaten to it, so I' had to come up with a more complex solution Cool

select o.object_type
      ,case when o.object_type in ('PACKAGE','PACKAGE BODY') then p.object_name||'.'||p.procedure_name
            else p.object_name
            end  name
from   user_objects o
      ,user_procedures p
where  o.object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY')
and    o.object_name = p.object_name
order by decode(o.object_type,'FUNCTION',1
                             ,'PROCEDURE',2
                             ,'PACKAGE'  ,3
                             ,'PACKAGE BODY',3)
         ,p.object_name
        ,decode(o.object_type,'PACKAGE'  ,1
                             ,'PACKAGE BODY',2
                             ,3)         
         ,p.procedure_name;
Re: Need Command [message #184619 is a reply to message #184614] Thu, 27 July 2006 04:23 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And then I thought: I wonder if they'd like to see the arguments for hose procedures

select o.object_type
      ,case when o.object_type in ('PACKAGE','PACKAGE BODY') then p.object_name||'.'||p.procedure_name
            else p.object_name
            end  name
      ,nvl(a.argument_name, 'Returns ') argument_name
      ,a.data_type
from   user_objects o
      ,user_procedures p
      ,user_arguments a
where  o.object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY')
and    o.object_name = p.object_name
and    p.object_name||p.procedure_name = a.object_name||a.package_name
order by decode(o.object_type,'FUNCTION',1
                             ,'PROCEDURE',2
                             ,'PACKAGE'  ,3
                             ,'PACKAGE BODY',3)
         ,p.object_name
        ,decode(o.object_type,'PACKAGE'  ,1
                             ,'PACKAGE BODY',2
                             ,3)         
         ,p.procedure_name
         ,decode(a.position,0,99,a.position);
Previous Topic: sending email thru PL/SQL ..
Next Topic: ref cursor
Goto Forum:
  


Current Time: Fri Dec 02 16:45:30 CST 2016

Total time taken to generate the page: 0.24744 seconds