Home » SQL & PL/SQL » SQL & PL/SQL » where are the procedures stored
where are the procedures stored [message #16306] Mon, 22 November 2004 07:51 Go to next message
naina
Messages: 5
Registered: November 2004
Junior Member
where are the procedures stored plz if you know do tell me the table name and how to access the table.plz reply soon its kind of urgent.

 
Re: where are the procedures stored [message #16307 is a reply to message #16306] Mon, 22 November 2004 08:25 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
Check out the user_source view.
----------------------------------------------------------------------
SQL> CREATE OR REPLACE PROCEDURE get_dates (
  2      p_in_date           IN  DATE
  3  ,   p_out_tomorrow      OUT DATE
  4  ,   p_out_end_of_month  OUT DATE
  5  )
  6  IS
  7  BEGIN
  8      p_out_tomorrow     := p_in_date + 1;
  9      p_out_end_of_month := LAST_DAY(p_in_date);
 10  END get_dates;
 11  /
 
Procedure created.
 
SQL> SELECT   text
  2  FROM     sys.user_source
  3  WHERE    type = 
'PROCEDURE'
  4  AND      name = 
'GET_DATES'
  5  ORDER BY line
  6  /
 
TEXT
----------------------------------------------------------------------------
 
PROCEDURE get_dates (
    p_in_date           IN  DATE
,   p_out_tomorrow      OUT DATE
,   p_out_end_of_month  OUT DATE
)
IS
BEGIN
    p_out_tomorrow     := p_in_date + 1;
    p_out_end_of_month := LAST_DAY(p_in_date);
END get_dates;
 
10 rows selected.
 
SQL>

----------------------------------------------------------------------
Re: where are the procedures stored [message #16335 is a reply to message #16306] Tue, 23 November 2004 11:09 Go to previous messageGo to next message
naina
Messages: 5
Registered: November 2004
Junior Member
1 select text
2 from sys.user_source
3 where type='PROCEDURE'
4 and name='GET_DATES'
5 Order by line

no rows selected

1.i made the table and when i executed that query
the result was no row selected ....
2.in this is that "text"(select text)
a keyword or a the column name.
3.do i need any grants for accessing the procedure???
Re: where are the procedures stored [message #16338 is a reply to message #16306] Tue, 23 November 2004 11:44 Go to previous messageGo to next message
naina
Messages: 5
Registered: November 2004
Junior Member
Thanx for such a well explained solution I have got the output with the same query you(Art Metzer) told me thanx again.now theres no problem
Re: where are the procedures stored [message #233677 is a reply to message #16335] Fri, 27 April 2007 02:55 Go to previous messageGo to next message
amarlow
Messages: 3
Registered: April 2007
Junior Member
Can anyone provide a solution that will work for Oracle 8i please? I know it's ancient and I am not using a version this old out of choice. Many thanks....
Re: where are the procedures stored [message #233682 is a reply to message #233677] Fri, 27 April 2007 03:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What happens if you execute the above-mentioned solution?
Re: where are the procedures stored [message #233683 is a reply to message #233677] Fri, 27 April 2007 03:06 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
user_source already existed since PL/SQL was introduced in version 6 almost 20 years ago. Is that old enough?

Regards
Michel

Previous Topic: How to tune the query with BETWEEN but no index
Next Topic: How to look at stored prcedures
Goto Forum:
  


Current Time: Wed Dec 07 06:52:55 CST 2016

Total time taken to generate the page: 0.18821 seconds