OraFAQ Forum: SQL & PL/SQL » How can i find Oracle Procedure File

Home » SQL & PL/SQL » SQL & PL/SQL » How can i find Oracle Procedure File

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How can i find Oracle Procedure File [message #190503]

Thu, 31 August 2006 02:11

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

Report message to a moderator

Re: How can i find Oracle Procedure File [message #190505 is a reply to message #190503]

Thu, 31 August 2006 02:18

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;

Report message to a moderator

Re: How can i find Oracle Procedure File [message #190513 is a reply to message #190505]

Thu, 31 August 2006 02:40

Maaher
Messages: 7065
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

Report message to a moderator

Re: How can i find Oracle Procedure File [message #190518 is a reply to message #190513]

Thu, 31 August 2006 02:55

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

Report message to a moderator

Re: How can i find Oracle Procedure File [message #190522 is a reply to message #190503]

Thu, 31 August 2006 03:02

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;

Report message to a moderator

Re: How can i find Oracle Procedure File [message #190545 is a reply to message #190503]

Thu, 31 August 2006 04:26

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;

Report message to a moderator

Re: How can i find Oracle Procedure File [message #190781 is a reply to message #190522]

Fri, 01 September 2006 03:36

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

Report message to a moderator

Re: How can i find Oracle Procedure File [message #190785 is a reply to message #190503]

Fri, 01 September 2006 04:01

hobbes
Messages: 173
Registered: January 2006

Senior Member

Looks like you're hitting a bug.
Link

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 14 08:41:34 CDT 2025