Retrieving Stored procedures, Views, Functions, Triggers using Toad for Oracle

How can I get the scripts of Stored procedures, Views, Functions, Triggers in toad for oracl开发者_JAVA百科e?

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');

In Toad menu, select Database -> Schema Browser. Use the tabs appeared to navigate between views, procedures, tables, trigger, functions...

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.

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

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';

Sorry guys, I came across this thread trying to solve the problem in PL/SQL. The information here actually helped me, Im of the mind it might help someone else.

In PL SQL developer, right click on a view, towards the bottom there is an option DBMS_METADATA. That has a flyout where you can choose DDL.

At my current client , I get an ORA-31603. Object 'objname' of type 'VIEW' not found in schema 'schemaName'

This is a permissions issue, which can technically be solved. Whether or not your organization wants to allow you to do your job is a matter you will have to take up w/ them.

http://www.tek-tips.com/viewthread.cfm?qid=1666882#

Try Ctrl+ LeftMouseClick on object name (function table view ,...) in your query

In toad, try Ctrl+left mouse click on object name (function table view...) in your query.

Select all Stored procedures Click right mouse button Select Send To Next Select Editor to script Save the File with .sql exten DONE

You can query the ALL_SOURCE view to get the data you need. For instance:

select owner, name, type, line, text from all_source
 where name like upper('%database_name%')
 order by owner, name, type, line;

The type column may have one of these: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY.

And if you want only the ones on your schema you can use USER_SOURCE (no owner column in there). For instance:

SELECT * FROM user_source WHERE line = 1;

Toad is confusing enough, for those used to SSMS. Here is the path to take after bringing up the schema browser for the schema (database) you need.

1. Bring up Schema Browser.
2. Set Schema (database)
3. What object do you want to view? Select it.
4. Find the target of what you need to view.
5. View specifics via the tabs.