ORACLE - 将程序/包导出到文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/710290/
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
ORACLE - Exporting Procedures / Packages to a file
提问by guigui42
I would like to programmatically export my Procedures / Functions and Packages into individual files (as a backup) and using Oracle 9.2.
我想以编程方式将我的程序/函数和包导出到单个文件（作为备份）并使用 Oracle 9.2。
The closest solution i found was using DBMS_METADATA.GET_DDL , but how do i output the CLOB to a text file, without losing any parts (due to length or indentation) ?
我找到的最接近的解决方案是使用 DBMS_METADATA.GET_DDL ，但是如何将 CLOB 输出到文本文件，而不会丢失任何部分（由于长度或缩进）？
Or maybe do you have other solutions to backup packages or other functions individually (only the one i want, not all of them) ?
或者您是否有其他解决方案来单独备份软件包或其他功能（只有我想要的，而不是全部）？
Thanks
谢谢
采纳答案by dpbradley
Trying to get CLOBS (and LONGS) from command line utilities like SQL*Plus always seems to give me formatting/truncation problems. My solution was to write a simple utility in a non- type checking language (Perl) that uses DBMS_METADATAto bring the CLOB back into a string.
尝试从 SQL*Plus 等命令行实用程序获取 CLOBS（和 LONGS）似乎总是给我带来格式/截断问题。我的解决方案是用非类型检查语言 (Perl) 编写一个简单的实用程序，用于DBMS_METADATA将 CLOB 重新转换为字符串。
Snippet:
片段：
...
...
$sthRef = $dbhRef->prepare("select dbms_metadata.get_ddl(?,?) from dual");
$sthRef->execute('PACKAGE', $thisName);
while (($thisDDL) = $sthRef->fetchrow()) {
print $thisDDL;
}
$sthRef->finish;
...
...
回答by guest
SET pages 0
spool proclist.sql
SELECT
CASE line
WHEN 1 THEN
'CREATE OR REPLACE ' || TYPE || ' ' || NAME || CHR(10) || text
ELSE
text
END
FROM user_source
WHERE TYPE IN ( 'PROCEDURE','FUNCTION')
ORDER BY name, line;
spool OFF
exit
回答by andri
If you want to get the DDL, there really is no way except DBMS_METADATAlike you already said.
如果您想获得 DDL，除了DBMS_METADATA您已经说过的之外，真的没有其他办法。
Usually, this kind of a backup is done with exp(or expdp), although this doesn't create a SQL file like you would get with most other DBMS systems.
通常，这种备份是使用exp(或expdp) 完成的，尽管这不会像使用大多数其他 DBMS 系统那样创建 SQL 文件。
回答by Najee Ghanim
Thanks goes for RAS , guest for his answer , I needed to get codes for some procedures only, so I tried the code , to find that this code truncate the code after procedure name in first line of the code and replace it with three dots '...'
感谢RAS，来宾的回答，我只需要获取某些程序的代码，所以我尝试了代码，发现此代码截断了代码第一行中程序名称之后的代码，并将其替换为三个点' ...'
so I changed the code to the following:
所以我将代码更改为以下内容：
SELECT CASE line
WHEN 1 THEN 'CREATE OR REPLACE ' -- || TYPE || ' ' || NAME || --CHR(10) || ' ('
|| text
ELSE
text
END
FROM user_source
WHERE TYPE IN ( 'PROCEDURE') and name like 'SomeThing%'
ORDER BY name, line;
and this page export procedures & triggershave a very usefaul code:
这个页面 导出程序和触发器有一个非常有用的代码：
connect fred/flintstone;
spool procedures_punch.lst
select
dbms_metadata.GET_DDL('PROCEDURE',u.object_name)
from
user_objects u
where
object_type = 'PROCEDURE';
spool off;
Final way to do it by using Toad Schema Browser , then select all the needed procedures and mouse right click then select export from the menu.
使用 Toad Schema Browser 完成此操作的最终方法，然后选择所有需要的程序并单击鼠标右键，然后从菜单中选择导出。
