如何以编程方式从Oracle数据库生成DDL？ [英] How to programmatically generate DDL from Oracle database?

问题描述

我有一个非常乏味的任务，从一个巨大的模式中找到几个表，并生成这些表的DDL。

I've got a monumentally tedious task that is to find several tables from a huge schema, and generate the DDL for these tables.

得到一个 schemaA 有1000个表，我需要找到 tableA 是否存在于 schemaA ，如果是，生成DDL并将其保存到文件系统，如果没有，打印它的名字或写入一个文件。任何想法？

Say, I've got a schemaA has 1000 tables, I need to find if a tableA existed 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?

推荐答案

DBMS_METADATA包（假设您使用的是Oracle的合理最新版本）会生成任何对象的DDL在数据库中。

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

SELECT dbms_metadata.get_ddl( 'TABLE', 'TABLEA', 'SCHEMAA' )
  FROM dual;

将返回一个CLOB与SchemaA.TableA的DDL。你可以捕获抛出的异常，对象不存在，但我倾向于建议你查询数据字典（即DBA_OBJECTS），以验证在SchemaA中有一个名为TableA的表，即

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.

SELECT COUNT(*) 
  FROM dba_objects
 WHERE owner = 'SCHEMAA'
   AND object_name = 'TABLEA'
   AND object_type = 'TABLE'

请注意，如果您无权访问DBA_OBJECTS ，您可以改用ALL_OBJECTS。然而，关注的是，在SchemaA中可能有一个TableA，您没有SELECT访问。该表不会出现在ALL_OBJECTS（其中包含您有权访问的所有对象），但会出现在DBA_OBJECTS（它拥有数据库中的所有对象，而不管您是否能够访问它们）。

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).

然后可以将DDL写入文件，或者如果计数为0，则指示对象不存在。从存储过程，可以使用UTL_FILE包写入数据库服务器上的文件。如果要尝试写入客户端文件系统上的文件，则需要使用可访问客户端操作系统资源的语言。一个小的C / Java / Perl /等程序应该能够选择一个CLOB并将该数据写入客户端操作系统上的一个文件。

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.

这篇关于如何以编程方式从Oracle数据库生成DDL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！