使用 Toad for Oracle 检索存储过程、视图、函数、触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2431565/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Retrieving Stored procedures, Views, Functions, Triggers using Toad for Oracle
提问by WENzER
How can I get the scripts of Stored procedures, Views, Functions, Triggers in toad for oracle?
如何在 toad for oracle 中获取存储过程、视图、函数、触发器的脚本？
回答by a'r
In general, you should use dbms_metadatato retrieve DDL statements. eg.
通常，您应该使用dbms_metadata来检索 DDL 语句。例如。
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');
回答by Nikita Koksharov
In Toad menu, select Database -> Schema Browser. Use the tabs appeared to navigate between views, procedures, tables, trigger, functions...
在 Toad 菜单中，选择数据库 -> 架构浏览器。使用出现的选项卡在视图、过程、表、触发器、函数之间导航...
回答by Igby Largeman
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).
Toad 有几种方法可以做到这一点。您只需双击 Schema 浏览器中的任何代码对象，就会打开一个编辑器，显示该对象的创建 DDL。或者只需选择架构浏览器左侧的对象，然后选择右侧的脚本选项卡（如果您没有看到脚本选项卡，请检查您的选项）。
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.
但是，如果您想查看大量对象的 DDL，请在 Schema Browser 中选择所有对象，或者在 Object Search 窗口中搜索它们。然后右键单击并选择另存为脚本。另外我认为有一个 Extract DDL 工具可以做基本相同的事情，但我可能会考虑 SQL Navigator。还有一种方法可以将整个架构导出为创建脚本。但是，其中一些功能可能取决于您的许可证级别。
BTW, this isn't a programming question.
顺便说一句，这不是编程问题。
回答by WENzER
If we use dba_sourcetable it will give scripts the of Procedures functions and triggers we have to use SELECT TEXT FROM dba_source WHERE TYPE = 'Procedure';
如果我们使用dba_source表，它将为脚本提供程序函数和触发器，我们必须使用SELECT TEXT FROM dba_source WHERE TYPE = 'Procedure';
回答by moleboy
Actually, if you go into the schema browser, you can right-click on any object (or multiple objects) and save it as a script.
实际上，如果您进入模式浏览器，您可以右键单击任何对象（或多个对象）并将其另存为脚本。
回答by greg
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.
对不起，我遇到了这个试图解决 PL/SQL 问题的线程。这里的信息实际上帮助了我，我认为它可能会帮助其他人。
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.
在 PL SQL developer 中，右键单击视图，底部有一个选项 DBMS_METADATA。它有一个弹出窗口，您可以在其中选择 DDL。
At my current client , I get an ORA-31603. Object 'objname' of type 'VIEW' not found in schema 'schemaName'
在我当前的客户端，我收到了一个 ORA-31603。在架构“schemaName”中找不到“VIEW”类型的对象“objname”
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.
这是一个权限问题，技术上可以解决。您的组织是否希望允许您完成工作是您必须与他们一起解决的问题。
回答by va ansari
Try Ctrl+ LeftMouseClick on object name (function table view ,...) in your query
在查询中尝试 Ctrl+ LeftMouseClick 对象名称（函数表视图，...）
回答by va ansari
In toad, try Ctrl+left mouse clickon object name (function table view...) in your query.
在蟾蜍中，尝试Ctrl+left mouse click在查询中使用对象名称（函数表视图...）。
回答by user7319181
Select all Stored procedures Click right mouse button Select Send To Next Select Editor to script Save the File with .sql exten DONE
选择所有存储过程 单击鼠标右键 选择发送到下一个 选择编辑器以编写脚本 使用 .sql exten 保存文件 DONE
回答by Mariano Desanze
You can query the ALL_SOURCEview to get the data you need. For instance:
您可以查询ALL_SOURCE视图以获取您需要的数据。例如：
select owner, name, type, line, text from all_source
where name like upper('%database_name%')
order by owner, name, type, line;
The typecolumn may have one of these: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY.
该type列可能具有以下其中一项：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 ownercolumn in there). For instance:
如果你只想要你可以使用的模式中的那些USER_SOURCE（owner那里没有列）。例如：
SELECT * FROM user_source WHERE line = 1;
