Oracle statements used to create a table in sqlserver

When synchronizing Oracle Data to sqlserver, you must first create a table on sqlserver.

If we do not synchronize complex fields, we only consider the following four data types.

Ing from Oracle to SQLServer: int-> int number-> decimal () number (p, s)-> decimal (p, s) date-> datetime varchar2 (n) -> nvarchar (n)

The following is a plsql script executed from Oracle.

/*
Introduction: Pull the script for table creation in mssql from oracle. This is a script written in PLSQL and executed in Oracle. The following is a non-temporary table under the BOM owner.
Author: DBA _ white boss

Last update date: 20140515
*/

/*
Mappings between Oracle and SQLServer:
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;
-- Does not belong to 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;