On Jun 18, 7:58 pm, Jaden <[email protected]> wrote: > For unit testing purposes, I would like to import the oracle > development database schema structure ONLY into an in-memory H2 > database(oracle compatible).
I've done this by examining the Oracle data dictionary views and generating DDL programatically. (I wish I could share the code; alas, it's confidential.) Start by keeping the Oracle Database Reference guide handy: http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/toc.htm DBA_TAB_COLUMNS will tell you the basic structure of each table. http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/statviews_2091.htm#I1020277 COLUMN_ID gives the ordering of the columns. COLUMN_NAME, DATA_TYPE and NULLABLE ('Y' or 'N') are self- explanatory. Be sure to rename VARCHAR2 to VARCHAR; you might also want to translate DATE to TIMESTAMP in H2 (since Oracle DATEs can have times associated with them). DATA_PRECISION and DATA_SCALE are relevant for NUMBER types. (Some columns show up with DATA_PRECISION==0; in these cases, you should just create the H2 version as NUMBER.) CHAR_LENGTH and CHAR_USED are relevant for CHAR/NCHAR/VARCHAR2/ NVARCHAR2 types. CHAR_USED is 'B' if CHAR_LENGTH is in bytes, 'C' if it's in characters. Then query DBA_CONSTRAINTS to find out what else should be applied to the table. http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/statviews_1044.htm#i1576022 I start by looking at primary keys (CONSTRAINT_TYPE='P'); join it with DBA_CONS_COLUMNS: http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/statviews_1042.htm#i1575870 SELECT cc.column_name FROM dba_constraints c INNER JOIN dba_cons_columns cc ON c.constraint_name = cc.constraint_name WHERE c.owner=? AND c.table_name=? AND c.constraint_type='P' ORDER BY cc.position Do the same for unique keys (CONSTRAINT_TYPE='U'). Foreign keys (CONSTRAINT_TYPE='R') are a pain. You first need to grab DBA_CONS_COLUMNS for the child table. R_CONSTRAINT_NAME then points you at a primary or unique key constraint for the parent table; R_OWNER is the owner of the table. However, you won't know the name of the table until you query DBA_CONS_COLUMNS for R_CONSTRAINT_NAME. I haven't implemented check constraints yet, but you'll look in DBA_CONSTRAINTS' SEARCH_CONDITION column for this information. Hope this gives you a pointer in the right direction. Apologies, again, that I can't provide actual code here. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
