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

Question

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

Langtry

Answer 1

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

Attribute

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 – Langtry

+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. @Langtry - what specific problems did you encounter with dbms_metadata? – Purpose

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. – Devinna

Answer 2

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

Labana

Answer 3

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.

Scend

Answer 4

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

Loutish

Answer 5

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

Langtry

be sure to capitalize PROCEDURE in this query for CS compares. – Crapshooter

Answer 6

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#

Dermato

Answer 7

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

Aerification

Answer 8

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

Aerification

Answer 9

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

Jebel

Answer 10

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;

Procaine

Answer 11

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.

Sibert