Home » SQL & PL/SQL » SQL & PL/SQL » how to view the code written in a procedure
how to view the code written in a procedure [message #298937] Fri, 08 February 2008 05:49 Go to next message
oracleforcog
Messages: 4
Registered: February 2008
Junior Member
How to View the code written in a procedure in pl/sql?
Re: how to view the code written in a procedure [message #298940 is a reply to message #298937] Fri, 08 February 2008 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
user_source

Regards
Michel
Re: how to view the code written in a procedure [message #298954 is a reply to message #298940] Fri, 08 February 2008 06:13 Go to previous messageGo to next message
oracleforcog
Messages: 4
Registered: February 2008
Junior Member
Hi

i tried it.. but its not working. can u give me the entire syntax??
Re: how to view the code written in a procedure [message #298961 is a reply to message #298937] Fri, 08 February 2008 06:28 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:
i tried it.. but its not working

Show us what did you try .
And what error you are getting ??

Thumbs Up
Rajuvan.

Re: how to view the code written in a procedure [message #298962 is a reply to message #298954] Fri, 08 February 2008 06:28 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
oracleforcog wrote on Fri, 08 February 2008 13:13
Hi

i tried it.. but its not working. can u give me the entire syntax??


USER_SOURCE is a data dictonary view (sort of table), so you'll have to SELECT from it.

(and I do hope you have basic SQL skills Wink )
Re: how to view the code written in a procedure [message #298965 is a reply to message #298962] Fri, 08 February 2008 06:37 Go to previous messageGo to next message
oracleforcog
Messages: 4
Registered: February 2008
Junior Member
Hi

this is wat i tried.

select user_source from user_objects
where object_type = 'PROCEDURE'
AND OBJECT_NAME = 'PROC_178478';

and the error i got is:

select user_source from user_objects
*
ERROR at line 1:
ORA-00904: "USER_SOURCE": invalid identifier
Re: how to view the code written in a procedure [message #298968 is a reply to message #298937] Fri, 08 February 2008 06:39 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Check th eprevilages.

I mean you should have select Privilage on that.

Re: how to view the code written in a procedure [message #298969 is a reply to message #298965] Fri, 08 February 2008 06:40 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
User source is the table (view) from which you select. Do a desc on the table (view) to find the columns
Re: how to view the code written in a procedure [message #298970 is a reply to message #298968] Fri, 08 February 2008 06:40 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
ramanajv1968 wrote on Fri, 08 February 2008 12:39
Check th eprevilages.

I mean you should have select Privilage on that.



Read his code again
Re: how to view the code written in a procedure [message #298971 is a reply to message #298965] Fri, 08 February 2008 06:41 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
oracleforcog wrote on Fri, 08 February 2008 13:37
Hi

this is wat i tried.

select user_source from user_objects
where object_type = 'PROCEDURE'
AND OBJECT_NAME = 'PROC_178478';

and the error i got is:

select user_source from user_objects
*
ERROR at line 1:
ORA-00904: "USER_SOURCE": invalid identifier


As pablolee tells you:

SELECT *
FROM USER_SOURCE
WHERE ...


Usually, before I start to write a query, I try to perform a describe to see if I'm dealing with a table.

Running TOAD makes it a bit easier: Schema Browser

Re: how to view the code written in a procedure [message #298976 is a reply to message #298937] Fri, 08 February 2008 06:54 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Oops!!!! Done the same mistake

here it is.

select text from user_source
where type= 'PROCEDURE'
and name ='PROC_178478'
Re: how to view the code written in a procedure [message #298977 is a reply to message #298976] Fri, 08 February 2008 06:58 Go to previous messageGo to next message
oracleforcog
Messages: 4
Registered: February 2008
Junior Member
thank you very much.. it is working..
Re: how to view the code written in a procedure [message #298984 is a reply to message #298937] Fri, 08 February 2008 07:21 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
So far, ALL of the code snippets are missing a vital clause...
Answers on a postcard (or just post here if you want)
Re: how to view the code written in a procedure [message #298985 is a reply to message #298984] Fri, 08 February 2008 07:23 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
pablolee wrote on Fri, 08 February 2008 14:21
So far, ALL of the code snippets are missing a vital clause...
Answers on a postcard (or just post here if you want)


Are you referring to that clause that would make sure the END-clause doesn't appear before the BEGIN-clause Wink
Re: how to view the code written in a procedure [message #298991 is a reply to message #298937] Fri, 08 February 2008 07:40 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Or ,

The clause that would make sure the EXCEPTION-clause appears between the BEGIN-END -clauses Smile

Thumbs Up
Rajuvan.
Re: how to view the code written in a procedure [message #299051 is a reply to message #298985] Fri, 08 February 2008 13:26 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
That'd be the one gents.
Re: how to view the code written in a procedure [message #299286 is a reply to message #298937] Mon, 11 February 2008 03:50 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
That'd be the one gents ?
curous to know what is that one....
Re: how to view the code written in a procedure [message #299292 is a reply to message #299286] Mon, 11 February 2008 04:21 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
I'll give you a hint.
Without this clause, rows will be returned in a random order.
Re: how to view the code written in a procedure [message #299378 is a reply to message #298937] Mon, 11 February 2008 15:09 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
One other hint....

below is a describe of the table

>desc user_source
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                               VARCHAR2(4000)


And text will hold ONE line of the source, how would you tell the query to look at the text in the correct sequence?

[Updated on: Mon, 11 February 2008 15:09]

Report message to a moderator

Previous Topic: Force a Number to 2dp
Next Topic: question for "where x in" SQL
Goto Forum:
  


Current Time: Fri Dec 09 08:15:33 CST 2016

Total time taken to generate the page: 0.15826 seconds