Oracle pulls out statements that build tables in SQL Server

When we synchronize Oracle data to SQL Server, we first have to build the table on the SQL Server side.

Complex fields We do not synchronize, only consider the following four types of data.

Oracle-to-SQL Server mappings:INT- intNumber --Decimal (18,6)Number (p,s), decimal (p,s)Date, DateTimevarchar2 (n), nvarchar (n)

The following is a plsql script executed from the Oracle side.

/*
Summary: Pull a script from Oracle to build a table on MSSQL. This is a script written in the Plsql language that executes in Oracle. The following is the removal of non-temporal tables under the main BOM.
Dba_ White boss

Last Updated on: 20140515
*/

/*
Oracle-to-SQL Server mappings:
INT-INT
Number--Decimal (18,6)
Number (p,s), Decimal (P,s)
Date, DateTime
VARCHAR2 (n), nvarchar (n)
*/

Declare
V_column_name VARCHAR2 (30);
V_data_type VARCHAR2 (106);
V_data_length number;
V_data_precision number;
V_data_scale number;
v_cnt int;

Begin
For I in (
SELECT ' Ggmgr ' as OWNER, ' T6 ' as table_name from DUAL UNION
SELECT ' Ggmgr ' as OWNER, ' T2 ' as table_name from DUAL UNION
SELECT ' Ggmgr ' as OWNER, ' T6 ' as table_name from DUAL
)
Loop
Dbms_output.put_line (' CREATE table ' | | ' ERP ' | | '. ' | | ' dbo. ' | | i.table_name| | ' (‘);
Select COUNT (*)
Into v_cnt
From Dba_tab_columns
WHERE table_name = I.table_name
and owner = I.owner;
For B in 1.. V_CNT Loop
Select column_name,
Data_type,
Data_length,
Data_precision,
Data_scale
Into V_column_name,
V_data_type,
V_data_length,
V_data_precision,
V_data_scale
From Dba_tab_columns t
WHERE table_name = I.table_name
and owner = I.owner
and column_id = b;
--int
IF V_data_type = ' number ' and v_data_precision is null and b! = V_cnt Then
Dbms_output.put_line (v_column_name| | ' Decimal ' | | ', ');
END IF;
IF V_data_type = ' number ' and v_data_precision is null and B = V_cnt Then
Dbms_output.put_line (v_column_name| | ' Decimal ');
END IF;
--number
IF (V_data_type = ' number ') and (v_data_precision is not null) and (b! = v_cnt) Then
Dbms_output.put_line (v_column_name| | ' Decimal ' | | ' (' | | v_data_precision| | ', ' | | v_data_scale| | '), ');
END IF;
IF (V_data_type = ' number ') and (v_data_precision is not null) and (b = v_cnt) Then
Dbms_output.put_line (v_column_name| | ' Decimal ' | | ' (' | | v_data_precision| | ', ' | | v_data_scale| | ');
END IF;
--varchar2
IF (v_data_type = ' VARCHAR2 ') and (v_data_length is not null) and (b! = v_cnt) Then
Dbms_output.put_line (v_column_name| | ' nvarchar ' | | ' (' | | v_data_length| | '), ');
END IF;
IF (v_data_type = ' VARCHAR2 ') and (v_data_length is not null) and (b = v_cnt) Then
Dbms_output.put_line (v_column_name| | ' nvarchar ' | | ' (' | | v_data_length| | ');
END IF;
--date
IF (v_data_type = ' DATE ') and (b! = v_cnt) Then
Dbms_output.put_line (v_column_name| | ' DATETIME, ');
END IF;
IF (v_data_type = ' DATE ') and (b = v_cnt) Then
Dbms_output.put_line (v_column_name| | ' DATETIME ');
END IF;
--Not part of INT,NUMBER,DATE,VARCHAR2
IF (v_column_name! = ' number ') and (v_column_name! = ' DATE ') and (v_column_name! = ' VARCHAR2 ') then
Null
END IF;
End Loop;
Dbms_output.put_line ('); ' | | Chr (10));
End Loop;
End