如何以编程方式从 Oracle 数据库生成 DDL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4160865/
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
How to programmatically generate DDL from Oracle database?
提问by Sawyer
I've got a monumentally tedious task that is to find several tables from a huge schema, and generate the DDL for these tables.
我有一项非常繁琐的任务，就是从一个巨大的模式中找到几个表，并为这些表生成 DDL。
Say, I've got a schemaAhas 1000 tables, I need to find if a tableAexisted in this schemaA, if it does, generate the DDL and save it to filesystem, if don't, print it's name out or write it to a file. Any ideas?
说，我有一个schemaA有 1000 个表，我需要找出 a 是否tableA存在于此schemaA，如果存在，则生成 DDL 并将其保存到文件系统，如果不存在，则将其名称打印出来或将其写入文件。有任何想法吗？
回答by Justin Cave
The DBMS_METADATA package (assuming you are on a reasonably recent version of Oracle) will generate the DDL for any object in the database. So
DBMS_METADATA 包（假设您使用的是 Oracle 的最新版本）将为数据库中的任何对象生成 DDL。所以
SELECT dbms_metadata.get_ddl( 'TABLE', 'TABLEA', 'SCHEMAA' )
FROM dual;
will return a CLOB with the DDL for SchemaA.TableA. You could catch the exception that is thrown that the object doesn't exist, but I would tend to suggest that you query the data dictionary (i.e. DBA_OBJECTS) to verify that there is a table named TableA in SchemaA, i.e.
将返回一个带有 SchemaA.TableA 的 DDL 的 CLOB。您可以捕获抛出的对象不存在的异常，但我倾向于建议您查询数据字典（即 DBA_OBJECTS）以验证 SchemaA 中是否存在名为 TableA 的表，即
SELECT COUNT(*)
FROM dba_objects
WHERE owner = 'SCHEMAA'
AND object_name = 'TABLEA'
AND object_type = 'TABLE'
Note that if you don't have access to DBA_OBJECTS, you could use ALL_OBJECTS instead. The concern there, however, is that there may be a TableA in SchemaA that you don't have SELECT access on. That table would not appear in ALL_OBJECTS (which has all the objects that you have access to) but it would appear in DBA_OBJECTS (which has all the objects in the database regardless of your ability to access them).
请注意，如果您无权访问 DBA_OBJECTS，则可以改用 ALL_OBJECTS。然而，这里的问题是 SchemaA 中可能存在一个您没有 SELECT 访问权限的 TableA。该表不会出现在 ALL_OBJECTS（其中包含您有权访问的所有对象）中，但会出现在 DBA_OBJECTS（其中包含数据库中的所有对象，而不管您是否能够访问它们）中。
You can then either write the DDL to a file or, if the count is 0, indicate that the object doesn't exist. From a stored procedure, you can use the UTL_FILE package to write to a file on the database server. If you are trying to write to a file on the client file system, you would need to use a language that has access to the client operating system's resources. A small C/ Java/ Perl/ etc. program should be able to select a CLOB and write that data to a file on the client operating system.
然后，您可以将 DDL 写入文件，或者如果计数为 0，则表明该对象不存在。从存储过程中，您可以使用 UTL_FILE 包写入数据库服务器上的文件。如果您尝试写入客户端文件系统上的文件，则需要使用可以访问客户端操作系统资源的语言。一个小的 C/Java/Perl/ 等程序应该能够选择一个 CLOB 并将该数据写入客户端操作系统上的文件。
