Home » SQL & PL/SQL » SQL & PL/SQL » I want to get source of all procedures of database. (oracle 9i, WinXP)
I want to get source of all procedures of database. [message #404837] Sun, 24 May 2009 23:09 Go to next message
achilles78
Messages: 5
Registered: January 2008
Junior Member
Hi,
I am using Oracle 9i. I want to get source of all procedures of database. So please tell me the sql for this

Thanks & Regards
Ach le
Re: I want to get source of all procedures of database. [message #404840 is a reply to message #404837] Sun, 24 May 2009 23:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I want to get source of all procedures of database.
Query DBA_SOURCE

Re: I want to get source of all procedures of database. [message #404841 is a reply to message #404837] Sun, 24 May 2009 23:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Query dba_source.

Regards
Michel
Re: I want to get source of all procedures of database. [message #404846 is a reply to message #404841] Sun, 24 May 2009 23:33 Go to previous messageGo to next message
achilles78
Messages: 5
Registered: January 2008
Junior Member
but I want source of all procedure record to script (example file pro.sql), then this script use to create the procedures same on other database
thank!
Re: I want to get source of all procedures of database. [message #404848 is a reply to message #404837] Sun, 24 May 2009 23:35 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@achilles78,

Do you use any 3rd party tools , such as TOAD or PL/SQL Developer etc, there will be an option to export the Procedure/Function/Package Scripts
Re: I want to get source of all procedures of database. [message #404851 is a reply to message #404846] Sun, 24 May 2009 23:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can use dbms_metadata(.get_ddl) and spool the result into a file.

Regards
Michel
Re: I want to get source of all procedures of database. [message #404858 is a reply to message #404846] Mon, 25 May 2009 01:09 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
achilles78 wrote on Mon, 25 May 2009 06:33
but I want source of all procedure record to script (example file pro.sql), then this script use to create the procedures same on other database
thank!

That is the world turned upside down.
You should have your code stored (and versioned) outside the database. There you keep it in a Source Versioning system.
From there you generate the scripts to alter your database.
Re: I want to get source of all procedures of database. [message #404872 is a reply to message #404858] Mon, 25 May 2009 02:09 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Frank wrote on Mon, 25 May 2009 08:09
achilles78 wrote on Mon, 25 May 2009 06:33
but I want source of all procedure record to script (example file pro.sql), then this script use to create the procedures same on other database
thank!

That is the world turned upside down.
You should have your code stored (and versioned) outside the database. There you keep it in a Source Versioning system.
From there you generate the scripts to alter your database.


You would surprised on the number of sites where this approach is not yet implemented Shocked

I've been on a consultancy job to just do that: get the code out of the database and set up a procedure for versioning & deployment.
Re: I want to get source of all procedures of database. [message #404875 is a reply to message #404872] Mon, 25 May 2009 02:25 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I know that there are places where people use the database as the place to store their code.
I myself (also consultant) refuse to work like that. If I hear beforehand and they refuse to adjust, I won't take the job. If I find out after being started there, I strongly insist on introducing one. If they refuse, it normally won't be a lasting assignment Smile
Previous Topic: Copy data from one database to other
Next Topic: delete query taking lot of time
Goto Forum:
  


Current Time: Mon Feb 10 10:59:25 CST 2025