Home » SQL & PL/SQL » SQL & PL/SQL » How can i find Oracle Procedure File
How can i find Oracle Procedure File [message #190503] Thu, 31 August 2006 02:11 Go to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Hi All,

Can anybody tell me, how can i find the oracle procedure text.

Actually i've created a procedure in oracle using sql plus. i compiled it successfully. After sometime i want to change some parameters of the same, i don't have any tool and i want to change it from sql*plus only, i'm not using OEM, TOAD etc.

Please let me know if someone has the solution for that.

Thanks in Advance.
Amit
Re: How can i find Oracle Procedure File [message #190505 is a reply to message #190503] Thu, 31 August 2006 02:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, in a proper development environment, you'd go to your source control software and get a copy of the procedure code.
You'd then edit the code, compile it into the Db, test the changes, and check the new version back into the source control.

Somehow I'm guessing this isn't the answer you're looking for.

If the only copy of the procedure you've got is in the database then

a) You'd better hope your backups work, or you risk losing everything
b) you can get at the source code with this query:

SELECT text
FROM   user_source
WHERE  name = <procedure name>
AND    type = 'PROCEDURE'
ORDER BY line;
Re: How can i find Oracle Procedure File [message #190513 is a reply to message #190505] Thu, 31 August 2006 02:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
reported

Reported By: amit.pandey On: Thu, 31 August 2006 09:13
In: SQL & PL/SQL SQL Experts How can i find Oracle Procedure File
Reason I've an urgent requirement, i'm implementing this on client side, where thr is no tools available for me. Please help me
Don't report messages to get attention. Only a few people get to see reports. Reporting is meant for inappropriate messages only. Don't do it again.

MHE
Re: How can i find Oracle Procedure File [message #190518 is a reply to message #190513] Thu, 31 August 2006 02:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm sure there's something in the sticky about not reporting messages to try to get attention.

If I had a less idealistic view of mankind, I'd start to think that not everyone had read the sticky before posting

Cool
Re: How can i find Oracle Procedure File [message #190522 is a reply to message #190503] Thu, 31 August 2006 03:02 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
In 9i and above, can also use:
select dbms_metadata.get_ddl('PROCEDURE', <procedure name>) from dual;
Re: How can i find Oracle Procedure File [message #190545 is a reply to message #190503] Thu, 31 August 2006 04:26 Go to previous messageGo to next message
jaydeep mitra
Messages: 20
Registered: August 2006
Location: India
Junior Member
Hi,
Hope this query help you
Select text from all_source where name =procedure name;
Re: How can i find Oracle Procedure File [message #190781 is a reply to message #190522] Fri, 01 September 2006 03:36 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Hi,

I tried this
DBMS_METADATA('PROCEDURE','TXN_ADD','SCY') then it throws an error, which i cannot rectify, the error is
ORA-06502 :-PL/SQL: numeric or value error
ORA-31605 :- the following was returned from LpxXSLResetAllVars in routine kuxsResetPa.

Can u help me out.

Thanks & Regards
Amit
Re: How can i find Oracle Procedure File [message #190785 is a reply to message #190503] Fri, 01 September 2006 04:01 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Looks like you're hitting a bug.
Link

Previous Topic: SQL query
Next Topic: SQL script: loop through a dynamic list of sql scripts?
Goto Forum:
  


Current Time: Tue Dec 06 06:05:08 CST 2016

Total time taken to generate the page: 0.13369 seconds