tags:

views:

604

answers:

4

How can I get the scripts of Stored procedures, Views, Functions, Triggers in toad for oracle?

+4  A: 

In general, you should use dbms_metadata to retrieve DDL statements. eg.

select dbms_metadata.get_ddl('VIEW', 'V_MYVIEW') from dual;

This can of course be wrapped in a query over the data dictionary, eg.

select dbms_metadata.get_ddl(object_type, object_name) 
from user_objects
where object_type in ('VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER');
ar
This is not working for me.I am having a database and I want the scripts written for store procedures , views , triggers for backup process. is there anything in toad or any qurey that will give me these scripts
WENzER
+1 ... I have seen (in the past, don't know about the current version of Toad) problems with 3rd-party tools providing an incomplete or incorrect reverse-engineered definition of Oracle objects.@WENzER - what specific problems did you encounter with dbms_metadata?
dpbradley
You should be using RMAN for backups, rather than trying to dump out the scripts and re-run them. This will be a *lot* easier in the long run as there are lots of potential pitfalls that will make this approach difficult.
ar
+1 This worked great for me with the correct privileges.
AieshaDot
A: 

Actually, if you go into the schema browser, you can right-click on any object (or multiple objects) and save it as a script.

moleboy
+1  A: 

Toad has several ways to do this. You can just double-click any code object in the Schema browser and an editor will open, showing you the creation DDL for that object. Or just select the object in the left hand side of the Schema Browser, and select the Script tab on the right hand side (if you don't see the Script tab, check your options).

But if you want to see DDL for a lot of objects, select all the object in the Schema Browser, or search for them in the Object Search window. Then right-click and select Save as Script. Also I think there is an Extract DDL tool which does basically the same thing, but I might be thinking of SQL Navigator. There is also a way to export and entire schema as a creation script. However some of these functions may depend on your license level.

BTW, this isn't a programming question.

Charles
A: 

If we use dba_source table it will give scripts the of Procedures functions and triggers we have to use SELECT TEXT FROM dba_source WHERE TYPE = 'Procedure';

WENzER