如何从 Oracle 10 模式导出 ddl 脚本以在 H2 数据库中创建表和约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4184785/
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 export a ddl script from an Oracle 10 schema to create tables and constraints in H2-database?
提问by Igor Mukhin
We would like to use H2 in-memory database for automated testing of our web-applications. We use Oracle 10 for our production and development environments.
我们想使用 H2 内存数据库来自动测试我们的 Web 应用程序。我们将 Oracle 10 用于我们的生产和开发环境。
So the idea is to duplicate the table structure in H2 test-database as it is in our Oracle dev-database.
因此，我们的想法是复制 H2 测试数据库中的表结构，就像在我们的 Oracle 开发数据库中一样。
It there an easy way to extract DDLs from an Oracle 10 schema (tables and constraints) so they could be executed against H2 database?
是否有一种简单的方法可以从 Oracle 10 模式（表和约束）中提取 DDL，以便它们可以针对 H2 数据库执行？
采纳答案by Igor Mukhin
This script helped me:
这个脚本帮助了我：
create or replace function mymetadata return sys.ku$_ddls is
md_handle number;
tr_handle number;
dl_handle number;
result_array sys.ku$_ddls;
begin
md_handle := dbms_metadata.open('TABLE');
tr_handle := dbms_metadata.add_transform(md_handle, 'MODIFY');
dbms_metadata.set_remap_param(tr_handle, 'REMAP_SCHEMA', 'MLIS_DEV', null);
dl_handle := dbms_metadata.add_transform(md_handle, 'DDL');
dbms_metadata.set_transform_param(dl_handle, 'SEGMENT_ATTRIBUTES', false);
dbms_metadata.set_transform_param(dl_handle, 'STORAGE', false);
dbms_metadata.set_transform_param(dl_handle, 'TABLESPACE', false);
dbms_metadata.set_transform_param(dl_handle, 'REF_CONSTRAINTS', false);
dbms_metadata.set_transform_param(dl_handle, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param(dl_handle, 'CONSTRAINTS_AS_ALTER', true);
LOOP
result_array := dbms_metadata.fetch_ddl(md_handle);
EXIT WHEN result_array IS NULL;
FOR i IN result_array.FIRST..result_array.LAST LOOP
dbms_output.put_line(result_array(i).ddltext);
END LOOP;
END LOOP;
dbms_metadata.close(md_handle);
md_handle := dbms_metadata.open('REF_CONSTRAINT');
tr_handle := dbms_metadata.add_transform(md_handle, 'MODIFY');
dbms_metadata.set_remap_param(tr_handle, 'REMAP_SCHEMA', 'MLIS_DEV', null);
dl_handle := dbms_metadata.add_transform(md_handle, 'DDL');
dbms_metadata.set_transform_param(dl_handle, 'SQLTERMINATOR', true);
LOOP
result_array := dbms_metadata.fetch_ddl(md_handle);
EXIT WHEN result_array IS NULL;
FOR i IN result_array.FIRST..result_array.LAST LOOP
dbms_output.put_line(result_array(i).ddltext);
END LOOP;
END LOOP;
dbms_metadata.close(md_handle);
return result_array;
end;
/
select ddltext from table(mymetadata);
回答by Gary Myers
I'd have to ask what are you 'proving' if your test environment is using a different database engine that the actual implementation. For example H2 has a DATE datatype that is just a DATE. In Oracle the DATE datatype stores a time as well.
如果您的测试环境使用与实际实现不同的数据库引擎，我不得不问您“证明”了什么。例如 H2 有一个 DATE 数据类型，它只是一个 DATE。在 Oracle 中，DATE 数据类型也存储时间。
If you do decide to go this route, then rather than trying to convert Oracle DDL syntax to H2 you'd be better off designing the data structures in a modelling tool and using that as your 'source of truth'. The tool should be capable of exporting / creating DDL in both Oracle and H2 formats. Most tools should support Oracle, though H2 might be a little trickier.
如果您决定走这条路，那么与其尝试将 Oracle DDL 语法转换为 H2，您最好在建模工具中设计数据结构并将其用作“事实来源”。该工具应该能够以 Oracle 和 H2 格式导出/创建 DDL。大多数工具都应该支持 Oracle，尽管 H2 可能有点棘手。
回答by Justin Cave
You should be able to use the DBMS_METADATA package to generate DDL for all the objects in your schema. There was a discussion a couple of days ago on How to Programmatically Generate DDL from an Oracle Databasethat would seem to be on point.
您应该能够使用 DBMS_METADATA 包为架构中的所有对象生成 DDL。几天前有一个关于如何从 Oracle 数据库以编程方式生成 DDL的讨论，这似乎很重要。
