So you want to create some tables in Microsoft SQL based on tables that exist in Oracle? The Microsoft SQL Migration Assistant (MSSMA) can help, but it’s a bit tedious and if you have triggers on your tables, MSSMA will create rowid columns in SQL that don’t really exist in Oracle except as pseudo-columns used to indicate where data is stored in a file on disk which is subject to change. I will cover a few why’s and caveats and then get to the script.
Why in the world…?
First off, why in the world would you want to create tables in SQL that already exist in Oracle? There could be any number of reasons including application testing and replication. In both cases this script will give you the basic table, index and constraint structures. This script will not copy triggers or functions or procedures that exist in Oracle as most of those will require extensive rewriting in order to work on the SQL platform. I have used this script to prepare tables for replication from Oracle to SQL using SQL Replication. If you’re going to use this script as a starting point for replication, take out the referential (foreign key) constraints section at the end of the script. SQL replication does not copy data in a specific order and you may end up with numerous constraint violations if parent keys are not replicated before child data.
This script does use file groups in the table definition. The file group names are based on the tablespace names of the source tables in Oracle. So, in order for this to work, you’ll either need to take out the file group reference in the script or have the file groups already created in SQL. I do not have a script for automatically creating file groups based on tablespaces. Not yet.
Caution
Second, there are a few caveats. As mentioned above, this script will not copy triggers or functions or procedures. Most of these structures do not have a one-to-one translation from Oracle to SQL. If you use MSSMA to copy these structures, be aware that MSSMA may create a rowid column in an attempt to duplicate the Oracle rowid. Unless you’re using replication, your application will have to populate the rowid column. If you are using replication, be aware that the Oracle rowid is subject to change if you move the table to a different tablespace or reorganize the table and that may cause errors in replication.
On the subject of replication, this script includes a section to duplicate foreign keys. Replication does not require a parent key to exist before it copies child data. Therefore, you may see constraint violation errors and lost data during replication if you leave the foreign keys in place. If you intend to use replication, I recommend disabling the foreign key constraints during replication or that you exclude them entirely from the duplication by removing that section of the script.
If you intend to use this script as a starting point for testing the migration of your application data source from Oracle to SQL there are numerous considerations to be made after this starting point. For example, Oracle’s technique for outer joins (+) does not work in MS SQL. Additionally, you will need to consider tuning SQL since there are differences in the way Oracle and SQL use indexes and even the types of indexes available. For example, function-based indexes exist in Oracle and are useful for text searches, but do not exist in SQL.
As mentioned above, this script does create the tables in file groups based on the names of the tablespaces found in the Oracle source. You will need to have the file groups created in advance or edit the script to put the tables in the desired file group in SQL.
The final caveat is a summary of caveats, this is a starting point. It is not guaranteed to work in your environment.
The Script
You’ll find the script below. I have commented it throughout and included some directions for use. There are at least three items you need to change before this will run in your world include: the v_owner in the declare section, the list of tables to create in the v_tableList, the v_targetDB is the SQL database that MS-SQL uses to create tables.
How It Works
Basically, you enable the dbms_output window in Oracle SQL Developer. Then execute this script in SQL Developer as a user with SYSDBA privileges. You then copy the output and paste it into MSSQL Studio and execute it against the target database. You may need to adjust the size of your dbms_output. There is a field at the top of the dbms_output pane which allows you to size the buffer accordingly.
/**
The following script runs in Oracle and creates a script to run in MS-SQL.
The resulting script creates tables, keys and indexes in MS-SQL to reflect what is in Oracle.
One notable exception is the lack of function-based indexes which exist in Oracle but not MS-SQL.
This script should be run with sysdba privs.
This script does not create file groups in MS-SQL based on Oracle tablespaces.
This script does use filegroup names based on the Oracle tablespace names in the resulting create table scripts.
IMPORTANT!!!
The are a few items you need to change before this will run in your world include:
the v_owner in the declare section
the list of tables to create in the v_tableList
the v_targetDB is the database that MS-SQL uses to create tables
https://mark.the-fennells.org
**/
DECLARE
v_owner VARCHAR2(32) := 'ABL'; -- schema name
v_count NUMBER := 1;
v_default VARCHAR2(4000) := NULL;
v_nullable VARCHAR2(12) := 'NULL';
v_datatype VARCHAR2(64) := 'varchar(256)';
v_targetDB VARCHAR2(64) := 'MY_DB_NAME';
type TableList
IS
TABLE OF VARCHAR2(64);
/** Put the list of tables you want to create in the TableList CSV string. **/
v_tableList TableList := TableList('ABL_AGL_ALERTS');
BEGIN
/** Setup a for loop to cycle through the tableList variable **/
FOR v_table IN v_tableList.FIRST .. v_tablelist.LAST
LOOP
/** grab the tablespace name and make any necessary changes to get it into the correct file group on SQL. **/
FOR tbl IN
(SELECT owner,
REPLACE(tablespace_name,'TBSP_','FLGRP_') AS file_group,
table_name
FROM dba_tables
WHERE table_name = v_tableList(v_table)
)
/**
The following is lifted almost directly from MS SQL Migration Assistant.
This section drops existing tables and keys before trying to create them.
**/
LOOP
dbms_output.put_line('USE '||v_targetdb||chr(10)||'GO'||chr(10));
dbms_output.put_line('IF EXISTS (SELECT * FROM sys.objects so JOIN sys.schemas sc ON so.schema_id = sc.schema_id WHERE so.name = N'''||tbl.table_name||''' AND sc.name=N'''|| tbl.owner||''' AND type in (N''U''))');
dbms_output.put_line('BEGIN');
dbms_output.put_line(' DECLARE @drop_statement nvarchar(500)');
dbms_output.put_line(' DECLARE drop_cursor CURSOR FOR');
dbms_output.put_line(' SELECT ''alter table ''+quotename(schema_name(ob.schema_id))+');
dbms_output.put_line(' ''.''+quotename(object_name(ob.object_id))+ '' drop constraint '' + quotename(fk.name) ');
dbms_output.put_line(' FROM sys.objects ob INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = ob.object_id');
dbms_output.put_line(' WHERE fk.referenced_object_id = ');
dbms_output.put_line(' (');
dbms_output.put_line(' SELECT so.object_id ');
dbms_output.put_line(' FROM sys.objects so JOIN sys.schemas sc');
dbms_output.put_line(' ON so.schema_id = sc.schema_id');
dbms_output.put_line(' WHERE so.name = N'''||tbl.table_name||''' AND sc.name=N'''||tbl.owner||''' AND type in (N''U'')');
dbms_output.put_line(' )');
dbms_output.put_line(' OPEN drop_cursor');
dbms_output.put_line(' FETCH NEXT FROM drop_cursor');
dbms_output.put_line(' INTO @drop_statement');
dbms_output.put_line(' WHILE @@FETCH_STATUS = 0');
dbms_output.put_line(' BEGIN');
dbms_output.put_line(' EXEC (@drop_statement)');
dbms_output.put_line(' FETCH NEXT FROM drop_cursor');
dbms_output.put_line(' INTO @drop_statement');
dbms_output.put_line(' END');
dbms_output.put_line(' CLOSE drop_cursor');
dbms_output.put_line(' DEALLOCATE drop_cursor');
dbms_output.put_line(chr(10)||' DROP TABLE ['||tbl.owner||'].['||tbl.table_name||']');
dbms_output.put_line('END');
dbms_output.put_line('GO');
dbms_output.put_line('USE '||v_targetdb||chr(10)||'SET ANSI_NULLS ON'||chr(10)||'GO'||chr(10)||'SET QUOTED_IDENTIFIER ON'||chr(10)||'GO'||chr(10));
dbms_output.put_line(chr(10)||'CREATE TABLE ['||tbl.owner||'].['||tbl.table_name||'] (');
/**
Next, we grab the table definition from Oracle and build the column definitions to meet the datatypes used in MSSQL.
This is also a good place to make datatype revisions based on your application requirements.
**/
SELECT MAX(tc.column_id)
INTO v_count
FROM dba_tables t,
dba_tab_columns tc
WHERE t.owner =tc.owner
AND t.table_name = tc.table_name
AND t.table_name = tbl.table_name;
FOR col IN
(SELECT rownum,
t.owner,
t.table_name,
tc.column_name,
tc.data_default,
tc.nullable,
tc.data_type,
tc.char_length,
tc.data_scale,
tc.data_precision,
tc.column_id
FROM dba_tables t,
dba_tab_columns tc
WHERE t.owner =tc.owner
AND t.table_name = tc.table_name
AND t.table_name = tbl.table_name
ORDER BY t.owner,
t.table_name,
tc.column_id
)
LOOP
-- check and set datatype with precison and scale and convert to
-- mssql datatype equivalent
IF col.data_type = 'NUMBER' THEN
IF col.data_precision IS NULL THEN
v_datatype := ' int ';
ELSE
v_datatype := ' numeric('||col.data_precision||','||col.data_scale||') ';
END IF;
elsif col.data_type = 'FLOAT' THEN
v_datatype := ' float ';
elsif col.data_type = 'RAW' THEN
v_datatype := ' varbinary(2000) ';
elsif col.data_type = 'CLOB' THEN
v_datatype := ' varchar(max) ';
elsif col.data_type = 'BLOB' THEN
v_datatype := ' varbinary(max) ';
elsif col.data_type = 'NVARCHAR2' THEN
v_datatype := ' nvarchar('||col.char_length||') ';
elsif col.data_type = 'DATE' THEN
v_datatype := ' datetime2(7) ';
elsif SUBSTR(col.data_type,1,9) = 'TIMESTAMP' THEN
v_datatype := ' datetime2(7) ';
elsif col.data_type = 'VARCHAR' THEN
IF col.char_length <= 3 THEN
v_datatype := ' char('||col.char_length||') ';
ELSE
v_datatype := ' varchar('||col.char_length||') ';
END IF;
elsif col.data_type = 'VARCHAR2' THEN
IF col.char_length <= 3 THEN
v_datatype := ' char('||col.char_length||') ';
ELSE
v_datatype := ' varchar('||col.char_length||') ';
END IF;
elsif col.data_type = 'CHAR' THEN
v_datatype := ' char('||col.char_length||') ';
ELSE
/** !! this is a WARNING datatype used to flag problems !! **/
v_datatype := ' varchar(1234) ';
END IF;
-- check and set default value
IF SUBSTR(col.data_default,1,7) = 'SYSDATE' THEN
v_default := ' DEFAULT sysdatetime() ';
elsif SUBSTR(col.data_default,1,12) = 'CURRENT_DATE' THEN
v_default := ' DEFAULT getDate() ';
elsif col.data_default IS NULL THEN
v_default := '';
ELSE
v_default := ' DEFAULT '||trim(col.data_default);
END IF;
-- check and set nullability
IF col.nullable = 'Y' THEN
v_nullable := ' NULL';
ELSE
v_nullable := ' NOT NULL';
END IF;
-- create the column definition and append a comma if it's not the last column in the table
/** use this section to create standards for columns with the same names **/
IF col.column_id < v_count THEN
IF col.column_name = 'LAST_UPDATED_BY' OR col.column_name='CREATED_BY' THEN
dbms_output.put(chr(9)||rpad('['||col.column_name||']',32,' ')||' '||'varchar(256)'||v_default||v_nullable||','||chr(10));
ELSE
dbms_output.put(chr(9)||rpad('['||col.column_name||']',32,' ')||' '||v_datatype||v_default||v_nullable||','||chr(10));
END IF;
ELSE
IF col.column_name = 'LAST_UPDATED_BY' OR col.column_name='CREATED_BY' THEN
dbms_output.put(chr(9)||rpad('['||col.column_name||']',32,' ')||' '||'varchar(256)'||v_default||v_nullable||chr(10));
ELSE
dbms_output.put(chr(9)||rpad('['||col.column_name||']',32,' ')||' '||v_datatype||v_default||v_nullable||chr(10));
END IF;
END IF;
END LOOP;
/**
Primary Key Constraints
**/
dbms_output.put_line(chr(10)||'/* PRIMARY KEY CONSTRAINTS */');
BEGIN
FOR con IN
(SELECT owner,
table_name,
constraint_name
FROM dba_constraints
WHERE owner = v_owner
AND table_name = v_tableList(v_table)
AND constraint_type='P'
)
LOOP
dbms_output.put_line(chr(10)||'ALTER TABLE ['||con.OWNER||'].['||con.TABLE_NAME||'] ADD CONSTRAINT ['||con.CONSTRAINT_NAME||'] PRIMARY KEY CLUSTERED (');
SELECT MAX(position)
INTO v_count
FROM dba_cons_columns
WHERE owner =con.owner
AND table_name =con.table_name
AND constraint_name=con.constraint_name;
FOR concol IN
(SELECT rownum,
column_name,
position
FROM dba_cons_columns
WHERE owner =con.owner
AND table_name =con.table_name
AND constraint_name=con.constraint_name
ORDER BY position ASC
)
LOOP
IF concol.rownum < v_count THEN dbms_output.put('['||concol.column_name||'] ASC,'); ELSE dbms_output.put('['||concol.column_name||'] ASC'); END IF; END LOOP; dbms_output.put_line(')'||chr(10)||'GO'); END LOOP; END; /** Unique Constraints **/ dbms_output.put_line(chr(10)||'/* UNIQUE CONSTRAINTS */'); BEGIN FOR con IN (SELECT owner, table_name, constraint_name FROM dba_constraints WHERE owner =v_owner AND table_name = v_tableList(v_table) AND constraint_type='U' ) LOOP SELECT MAX(position) INTO v_count FROM dba_cons_columns WHERE owner =con.owner AND table_name =con.table_name AND constraint_name=con.constraint_name; dbms_output.put_line('ALTER TABLE ['||con.OWNER||'].['||con.TABLE_NAME||'] ADD CONSTRAINT ['||con.CONSTRAINT_NAME||'] UNIQUE NONCLUSTERED ('); FOR concol IN (SELECT rownum, column_name, position FROM dba_cons_columns WHERE owner =con.owner AND table_name =con.table_name AND constraint_name=con.constraint_name ORDER BY position ASC ) LOOP IF v_count > concol.position THEN
dbms_output.put('['||concol.column_name||'],'||chr(10));
ELSE
dbms_output.put('['||concol.column_name||']'||chr(10));
END IF;
END LOOP;
dbms_output.put_line(')'||chr(10)||'GO');
END LOOP;
END;
/**
Check Constraints
**/
dbms_output.put_line(chr(10)||'/* CHECK CONSTRAINTS */');
BEGIN
FOR con IN
(SELECT owner,
table_name,
constraint_name,
search_condition
FROM dba_constraints
WHERE owner =v_owner
AND table_name = v_tableList(v_table)
AND constraint_type='C'
)
LOOP
IF con.search_condition = 'IS NOT NULL' THEN
dbms_output.put_line('ALTER TABLE ['||con.OWNER||'].['||con.TABLE_NAME||'] ADD CONSTRAINT ['||con.CONSTRAINT_NAME||'] CHECK ('||REPLACE(con.search_condition,'"')||');');
END IF;
END LOOP;
dbms_output.put_line(chr(10)||'GO');
END;
/**
Normal Indexes
**/
dbms_output.put_line(chr(10)||'/* NORMAL INDEXES */');
BEGIN
FOR ind IN
(SELECT table_owner,
table_name,
index_name,
REPLACE(tablespace_name,'TBSP_','FLGRP_') AS file_group
FROM dba_indexes
WHERE table_owner =v_owner
AND table_name =v_tableList(v_table)
AND ( index_type = 'NORMAL'
OR index_type = 'DOMAIN' )
AND index_name NOT IN
(SELECT INDEX_NAME
FROM dba_constraints
WHERE owner =v_owner
AND table_name = v_tableList(v_table)
AND (constraint_type='P'
OR constraint_type ='U')
)
)
LOOP
SELECT MAX(column_position)
INTO v_count
FROM dba_ind_columns
WHERE table_owner =ind.table_owner
AND table_name =ind.table_name
AND index_name =ind.index_name;
dbms_output.put('CREATE NONCLUSTERED INDEX ['||ind.index_name||'] on ['||ind.table_owner||'].['||ind.table_name||'] (');
FOR indcol IN
(SELECT column_name,
column_position,
descend
FROM dba_ind_columns
WHERE table_owner=ind.table_owner
AND table_name = ind.table_name
AND index_name = ind.index_name
ORDER BY column_position
)
LOOP
IF v_count > indcol.column_position THEN
dbms_output.put('['||indcol.column_name||'] '||indcol.descend||',');
ELSE
dbms_output.put('['||indcol.column_name||'] '||indcol.descend);
END IF;
END LOOP;
dbms_output.put_line(') ON ['||ind.file_group||'];');
END LOOP;
dbms_output.put_line(chr(10)||'GO');
END;
END LOOP;
/**
Referential Constraints
Foreign Key constraints are created last in a separate loop using the v_TableList
to ensure that primary keys are in place.
**/
dbms_output.put_line(chr(10)||'/* REFERENTIAL CONSTRAINTS */');
FOR v_table IN v_tableList.FIRST .. v_tablelist.LAST
LOOP
FOR tbl IN
(SELECT owner,
REPLACE(tablespace_name,'TS_','FG_') AS file_group,
table_name
FROM dba_tables
WHERE table_name = v_tableList(v_table)
)
LOOP
BEGIN
FOR con IN
(SELECT owner,
table_name,
constraint_name,
search_condition
FROM dba_constraints
WHERE owner =v_owner
AND table_name = v_tableList(v_table)
AND constraint_type='R'
)
LOOP
FOR ref IN
(SELECT ucc1.table_name constraint_table,
ucc1.column_name constraint_column,
ucc2.table_name reference_table,
ucc2.column_name reference_column,
ucc2.owner
FROM dba_constraints uc,
dba_cons_columns ucc1,
dba_cons_columns ucc2
WHERE uc.constraint_name = ucc1.constraint_name
AND uc.r_constraint_name = ucc2.constraint_name
AND uc.constraint_type = 'R'
AND uc.constraint_name = con.constraint_name
)
LOOP
dbms_output.put_line('ALTER TABLE ['||con.OWNER||'].['||con.TABLE_NAME||'] WITH CHECK ADD CONSTRAINT ['||con.CONSTRAINT_NAME||'] FOREIGN KEY (['||ref.constraint_column||']) REFERENCES ['||ref.owner||'].['||ref.reference_table||'] (['||ref.reference_column||'])');
END LOOP;
dbms_output.put_line('GO');
END LOOP;
END;
END LOOP;
END LOOP;
/** End Referential Constraints **/
END LOOP;
END;
Leave a Reply
You must be logged in to post a comment.