# Oracle轉PostgreSQL【table篇】
###### tags: `PostgreSQL`,`Postgres`,`Oracle`,`轉換資料庫`

將Oracle table、comment、primary key、index,轉換成PostgreSQL能用的語法
文章前半段分項目別,後半段有將這4項合併在一起產出

## 欄位型態對應
以下是建議對應的方式
| Oracle | PostgreSQL | 特別說明 |
| --------- | ---------- | ------- |
| CHAR | VARCHAR | |
| NCHAR | VARCHAR | |
| NVARCHAR2 | VARCHAR | |
| NUMBER | NUMERIC | |
| DATE | DATE | 沒有時分秒|
| DATE | TIMESTAMPTZ</br>等同TIMESTAMP with time zone | 有時分秒,有時區 |
| DATE | TIMESTAMP | 有時分秒,沒時區 |
| TIMESTAMP | TIMESTAMPTZ</br>等同TIMESTAMP with time zone | 有時區 |
| TIMESTAMP | TIMESTAMP | 沒時區 |
| CLOB | TEXT | |
| BLOB | BYTEA | |
| LONG RAW | BYTEA | |
| JSON | JSONB | |
| GUID | UUID | 註1 |

##### 註1: 建立UUID語法

/* Oracle */
create table uuid_table
(
  id NVARCHAR2(64) default RAWTOHEX(SYS_GUID())
);

/* PostgreSQL */
create table uuid_table
(
  id UUID DEFAULT uuid_generate_v4 ()
);

## 產生create postgreSQL table語法
### 儲存語法用的table

create table ORACLE_TO_POSTGRESQL
(
  schema_name varchar2(100),
  table_name varchar2(100),
  create_table varchar2(4000),
  primary_key varchar2(4000),
  index_key  varchar2(4000),
  comments  varchar2(4000)
);

comment on column ORACLE_TO_POSTGRESQL.schema_name is 'schema名稱';
comment on column ORACLE_TO_POSTGRESQL.table_name is 'table名稱';
comment on column ORACLE_TO_POSTGRESQL.create_table is 'Create table語法';
comment on column ORACLE_TO_POSTGRESQL.primary_key is 'Create Primary Key語法';
comment on column ORACLE_TO_POSTGRESQL.index_key is 'Create index語法';
comment on column ORACLE_TO_POSTGRESQL.comments is 'Create 欄位說明語法';

### 將Oracle全部table,轉成create postgreSQL table
語法會入到table ORACLE_TO_POSTGRESQL

declare
  ls_schema_name varchar2(100) := 'DEMO'; /* 產生的table,要在那個schema */
  ls_create_table varchar2(4000);         /* 產生的內容 */
  ls_data_default varchar2(100);          /* Default值 */
  ls_data_type user_tab_columns.data_type%type;  /* 型態 */
begin
  FOR c_table in (SELECT table_name, decode(temporary,'Y','GLOBAL TEMPORARY ','') temporary, decode(duration,'SYS$SESSION',' ON COMMIT PRESERVE ROWS','SYS$TRANSACTION',' ON COMMIT DELETE ROWS','') duration FROM user_tables) /* 這個user之下,所有table */
  LOOP                                    
    ls_create_table := 'create ' || c_table.temporary || 'table ' || ls_schema_name || '.' || c_table.table_name || '(';
    /* 將型態做對應 */
    FOR c_column in (select column_id, column_name,decode(data_type,'NVARCHAR2',' VARCHAR('||to_char(char_length)||')',
                                                                    'VARCHAR2',' VARCHAR('||to_char(char_length)||')',
                                                                    'NCHAR',' VARCHAR('||to_char(char_length)||')',
                                                                    'CHAR',' VARCHAR('||to_char(char_length)||')',
                                                                    'NUMBER',case when data_precision > 0 then ' NUMERIC('||to_char(data_precision)||','||to_char( data_scale)||')'
                                                                             else ' NUMERIC' end,
                                                                    'DATE',' DATE',
                                                                    'TIMESTAMP(6)',' TIMESTAMPTZ',
                                                                    'LONG RAW',' BYTEA',
                                                                    'BLOB',' BYTEA',
                                                                    'CLOB',' TEXT',
                                                                    'JSON',' JSONB',
                                                                    '對應不到') data_type,
                     decode(nullable,'N',' not null','Y',' ') nullable,
                     case when default_length > 0 then data_default end as data_default
                     from user_tab_columns where table_name = c_table.table_name order by column_id)
    LOOP    
      ls_data_type := c_column.data_type;
      /* 如果有時分秒,要用TIMESTAMPTZ */
      IF ls_data_type = ' DATE' THEN
        /* 判斷欄位資料有沒有時分秒,例如先用『where trunc(欄位) <> 欄位』,檢查有沒有時分秒
        select count(*) into ln_is_datetime_column from datetime_column where table_name = c_table.table_name and column_name = c_column.column_name and is_datetime_column = 'Y';
        IF ln_is_datetime_column > 0 THEN   
          ls_data_type := ' TIMESTAMPTZ';          
        ELSE
          ls_data_type := ' DATE';
        END IF;
        */
        null;
      END IF;                                               
        
      /* 自定義特殊欄位要用的日期型態
      IF c_column.column_name in ('INSERT_TIME','INSERT_DATE') THEN
        ls_data_type := ' TIMESTAMPTZ';
      END IF;
      */      
      
      ls_data_default := c_column.data_default;
      IF ls_data_default = 'null' THEN  /* 測試時遇到看不到但取到的值是null */
        ls_data_default := '';
      END IF;
      /* Default值 */
      IF ls_data_default is not null THEN
        IF trim(upper(ls_data_default)) = 'SYSDATE' THEN
          ls_data_default := 'NOW()';
        ELSIF trim(upper(ls_data_default)) = 'TRUNC(SYSDATE)' THEN
          ls_data_default := 'DATE_TRUNC(''day'',NOW())';
        ELSIF trim(upper(ls_data_default)) = 'RAWTOHEX(SYS_GUID())' THEN
          ls_data_default := 'UUID_GENERATE_V4()';
        ELSIF trim(upper(ls_data_default)) = 'SYS_GUID()' THEN
          ls_data_default := 'UUID_GENERATE_V4()';
        END IF;                   
        ls_data_default := ' default ' || ls_data_default;
      END IF;
      
      ls_create_table := ls_create_table || case when c_column.column_id = 1 then '' else ',' end  || chr(13) || '  ' || c_column.column_name || ls_data_type || c_column.nullable || ls_data_default;
    END LOOP;
    ls_create_table := ls_create_table || chr(13) || ')' || c_table.duration || ';' || chr(13);
 
    insert into ORACLE_TO_POSTGRESQL (schema_name, table_name, create_table) values (ls_schema_name, c_table.table_name, ls_create_table);  
  END LOOP;      
end;

### 將Oracle全部comment,轉成create postgreSQL comment
語法會入到table ORACLE_TO_POSTGRESQL

declare
  ls_schema_name varchar2(100) := 'DEMO'; /* 產生的table,要在那個schema */
  ls_comments varchar2(10000);            /* 產生的內容 */
begin
  FOR c_table in (SELECT table_name FROM user_tables) 
  LOOP                                    
    /* 產生table comment */
    select comments into ls_comments from user_tab_comments where table_name = c_table.table_name;
    IF ls_comments is not null THEN        
      ls_comments := 'comment on table ' || ls_schema_name || '.' || c_table.table_name || ' is ''' || ls_comments || ''';' || chr(13);
    END IF;             
    /* 產生column comment */     
    FOR c_col_comment in (select column_name,comments from user_col_comments where comments is not null and table_name = c_table.table_name)
    LOOP
      ls_comments := ls_comments || 'comment on column ' || ls_schema_name || '.' || c_table.table_name || '.' || c_col_comment.column_name || ' is ''' || replace(replace(c_col_comment.comments,chr(13),''),'''','''''') || ''';' || chr(13);
    END LOOP;   
    
    IF lengthb(ls_comments) > 4000 THEN
      ls_comments := '/* 備註超過4000字,無法入值,請人工移轉 */';
    END IF;
 
    insert into ORACLE_TO_POSTGRESQL (schema_name, table_name, comments) values (ls_schema_name, c_table.table_name, ls_comments);  
  END LOOP;      
end;

### 將Oracle全部primary key,轉成create postgreSQL primary key
語法會入到table ORACLE_TO_POSTGRESQL

declare
  ls_schema_name varchar2(100) := 'DEMO'; /* 產生的table,要在那個schema */
  ls_create_primary varchar2(4000);       /* 產生的內容 */
begin
  FOR c_table in (SELECT table_name FROM user_tables) 
  LOOP                                    
    /* 產生primary */               
    ls_create_primary := '';
    FOR c_primary in (select constraint_name primary_key from user_constraints a  where a.constraint_type = 'P' and a.table_name = c_table.table_name)
    LOOP     
      ls_create_primary := 'alter table ' || ls_schema_name || '.' || c_table.table_name || ' add primary key (';
      FOR c_primary_column in (select position, column_name from user_cons_columns where constraint_name = c_primary.primary_key order by position)
      LOOP     
        ls_create_primary := ls_create_primary || case when c_primary_column.position = 1 then '' else ', ' end || c_primary_column.column_name;          
      END LOOP;
      ls_create_primary := ls_create_primary || ');' || chr(13);      
    END LOOP;
 
    insert into ORACLE_TO_POSTGRESQL (schema_name, table_name, primary_key) values (ls_schema_name, c_table.table_name, ls_create_primary);  
  END LOOP;      
end; 

### 將Oracle全部index,轉成create postgreSQL index
語法會入到table ORACLE_TO_POSTGRESQL

declare
  ls_schema_name varchar2(100) := 'DEMO'; /* 產生的table,要在那個schema */
  ls_create_index varchar2(4000);         /* 產生的內容 */
  ls_index_column user_ind_columns.column_name%type;
begin
  FOR c_table in (SELECT table_name FROM user_tables) 
  LOOP                                    
    /* 產生index */     
    ls_create_index := '';             
    /* table欄位有clob,就有這類名稱SYS_IL0001350299C00008$$的index,所以必須排除,自行處理 */
    FOR c_index in (select index_name, decode(uniqueness,'UNIQUE','UNIQUE','') uniqueness from user_indexes where table_name = c_table.table_name and index_name not like '%$$' and index_name not in (select constraint_name primary_key from user_constraints a  where a.constraint_type = 'P' and a.table_name = user_indexes.table_name) order by index_name)
    LOOP     
      ls_create_index := ls_create_index || 'create ' || c_index.uniqueness || ' index ' || c_index.index_name || ' on ' || ls_schema_name || '.' || c_table.table_name || ' (';
      FOR c_index_column in (select column_position, column_name, ' ' || descend descend from user_ind_columns where index_name = c_index.index_name order by column_position)
      LOOP  
        /* 如果有欄位名稱出現'$',要到sys.col$,找真正的欄位名稱 */
        IF c_index_column.column_name like '%$' THEN
          select c.default$ into ls_index_column from sys.user$ u, sys.obj$ o, sys.col$ c where u.user# = o.owner# and o.obj# = c.obj# and c.name = c_index_column.column_name and u.name = user;
          ls_index_column := replace(ls_index_column,'"','');
        ELSE
          ls_index_column := c_index_column.column_name;  
        END IF;
        ls_create_index := ls_create_index || case when c_index_column.column_position = 1 then '' else ', ' end || ls_index_column || c_index_column.descend;          
      END LOOP;
      ls_create_index := ls_create_index || ');' || chr(13);      
    END LOOP;
 
    insert into ORACLE_TO_POSTGRESQL (schema_name, table_name, index_key) values (ls_schema_name, c_table.table_name, ls_create_index);  
  END LOOP;      
end;

### 合併以上SQL,將Oracle全部table(table、comment、primary key、index),轉成create postgreSQL
語法會入到table ORACLE_TO_POSTGRESQL

declare
  ls_schema_name varchar2(100) := 'DEMO'; /* 產生的table,要在那個schema */
  ls_data_default varchar2(100);          /* Default值 */
  ls_data_type user_tab_columns.data_type%type;  /* 型態 */
  ls_create_table varchar2(4000);         /* 產生table的內容 */
  ls_comments varchar2(10000);            /* 產生comment的內容 */ 
  ls_create_primary varchar2(4000);       /* 產生primary key的內容  */
  ls_create_index varchar2(4000);         /* 產生index的內容  */
  ls_index_column user_ind_columns.column_name%type;  /* index欄位 */
begin
  FOR c_table in (SELECT table_name, decode(temporary,'Y','GLOBAL TEMPORARY ','') temporary, decode(duration,'SYS$SESSION',' ON COMMIT PRESERVE ROWS','SYS$TRANSACTION',' ON COMMIT DELETE ROWS','') duration FROM user_tables) /* 這個user之下,所有table */
  LOOP                                    
    ls_create_table := 'create ' || c_table.temporary || 'table ' || ls_schema_name || '.' || c_table.table_name || '(';
    /* 將型態做對應 */
    FOR c_column in (select column_id, column_name,decode(data_type,'NVARCHAR2',' VARCHAR('||to_char(char_length)||')',
                                                                    'VARCHAR2',' VARCHAR('||to_char(char_length)||')',
                                                                    'NCHAR',' VARCHAR('||to_char(char_length)||')',
                                                                    'CHAR',' VARCHAR('||to_char(char_length)||')',
                                                                    'NUMBER',case when data_precision > 0 then ' NUMERIC('||to_char(data_precision)||','||to_char( data_scale)||')'
                                                                             else ' NUMERIC' end,
                                                                    'DATE',' DATE',
                                                                    'TIMESTAMP(6)',' TIMESTAMPTZ',
                                                                    'LONG RAW',' BYTEA',
                                                                    'BLOB',' BYTEA',
                                                                    'CLOB',' TEXT',
                                                                    'JSON',' JSONB',
                                                                    '對應不到') data_type,
                     decode(nullable,'N',' not null','Y',' ') nullable,
                     case when default_length > 0 then data_default end as data_default
                     from user_tab_columns where table_name = c_table.table_name order by column_id)
    LOOP    
      ls_data_type := c_column.data_type;
      /* 如果有時分秒,要用TIMESTAMPTZ */
      IF ls_data_type = ' DATE' THEN
        /* 判斷欄位資料有沒有時分秒,例如先用『where trunc(欄位) <> 欄位』,檢查有沒有時分秒
        select count(*) into ln_is_datetime_column from datetime_column where table_name = c_table.table_name and column_name = c_column.column_name and is_datetime_column = 'Y';
        IF ln_is_datetime_column > 0 THEN   
          ls_data_type := ' TIMESTAMPTZ';          
        ELSE
          ls_data_type := ' DATE';
        END IF;
        */
        null;
      END IF;                                               
        
      /* 自定義特殊欄位要用的日期型態
      IF c_column.column_name in ('INSERT_TIME','INSERT_DATE') THEN
        ls_data_type := ' TIMESTAMPTZ';
      END IF;
      */      
      
      ls_data_default := c_column.data_default;
      IF ls_data_default = 'null' THEN  /* 測試時遇到看不到但取到的值是null */
        ls_data_default := '';
      END IF;
      /* Default值 */
      IF ls_data_default is not null THEN
        IF trim(upper(ls_data_default)) = 'SYSDATE' THEN
          ls_data_default := 'NOW()';
        ELSIF trim(upper(ls_data_default)) = 'TRUNC(SYSDATE)' THEN
          ls_data_default := 'DATE_TRUNC(''day'',NOW())';
        ELSIF trim(upper(ls_data_default)) = 'RAWTOHEX(SYS_GUID())' THEN
          ls_data_default := 'UUID_GENERATE_V4()';
        ELSIF trim(upper(ls_data_default)) = 'SYS_GUID()' THEN
          ls_data_default := 'UUID_GENERATE_V4()';
        END IF;                   
        ls_data_default := ' default ' || ls_data_default;
      END IF;
      
      ls_create_table := ls_create_table || case when c_column.column_id = 1 then '' else ',' end  || chr(13) || '  ' || c_column.column_name || ls_data_type || c_column.nullable || ls_data_default;
    END LOOP;
    ls_create_table := ls_create_table || chr(13) || ')' || c_table.duration || ';' || chr(13);
 
    /* 產生table comment */
    select comments into ls_comments from user_tab_comments where table_name = c_table.table_name;
    IF ls_comments is not null THEN        
      ls_comments := 'comment on table ' || ls_schema_name || '.' || c_table.table_name || ' is ''' || ls_comments || ''';' || chr(13);
    END IF;             
    /* 產生column comment */     
    FOR c_col_comment in (select column_name,comments from user_col_comments where comments is not null and table_name = c_table.table_name)
    LOOP
      ls_comments := ls_comments || 'comment on column ' || ls_schema_name || '.' || c_table.table_name || '.' || c_col_comment.column_name || ' is ''' || replace(replace(c_col_comment.comments,chr(13),''),'''','''''') || ''';' || chr(13);
    END LOOP;   
    
    IF lengthb(ls_comments) > 4000 THEN
      ls_comments := '/* 備註超過4000字,無法入值,請人工移轉 */';
    END IF;    
    
    /* 產生primary */               
    ls_create_primary := '';
    FOR c_primary in (select constraint_name primary_key from user_constraints a  where a.constraint_type = 'P' and a.table_name = c_table.table_name)
    LOOP     
      ls_create_primary := 'alter table ' || ls_schema_name || '.' || c_table.table_name || ' add primary key (';
      FOR c_primary_column in (select position, column_name from user_cons_columns where constraint_name = c_primary.primary_key order by position)
      LOOP     
        ls_create_primary := ls_create_primary || case when c_primary_column.position = 1 then '' else ', ' end || c_primary_column.column_name;          
      END LOOP;
      ls_create_primary := ls_create_primary || ');' || chr(13);      
    END LOOP;
    
    /* 產生index */     
    ls_create_index := '';             
    /* table欄位有clob,就有這類名稱SYS_IL0001350299C00008$$的index,所以必須排除,自行處理 */
    FOR c_index in (select index_name, decode(uniqueness,'UNIQUE','UNIQUE','') uniqueness from user_indexes where table_name = c_table.table_name and index_name not like '%$$' and index_name not in (select constraint_name primary_key from user_constraints a  where a.constraint_type = 'P' and a.table_name = user_indexes.table_name) order by index_name)
    LOOP     
      ls_create_index := ls_create_index || 'create ' || c_index.uniqueness || ' index ' || c_index.index_name || ' on ' || ls_schema_name || '.' || c_table.table_name || ' (';
      FOR c_index_column in (select column_position, column_name, ' ' || descend descend from user_ind_columns where index_name = c_index.index_name order by column_position)
      LOOP  
        /* 如果有欄位名稱出現'$',要到sys.col$,找真正的欄位名稱 */
        IF c_index_column.column_name like '%$' THEN
          select c.default$ into ls_index_column from sys.user$ u, sys.obj$ o, sys.col$ c where u.user# = o.owner# and o.obj# = c.obj# and c.name = c_index_column.column_name and u.name = user;
          ls_index_column := replace(ls_index_column,'"','');
        ELSE
          ls_index_column := c_index_column.column_name;  
        END IF;
        ls_create_index := ls_create_index || case when c_index_column.column_position = 1 then '' else ', ' end || ls_index_column || c_index_column.descend;          
      END LOOP;
      ls_create_index := ls_create_index || ');' || chr(13);      
    END LOOP;    

    insert into ORACLE_TO_POSTGRESQL values (ls_schema_name, c_table.table_name, ls_create_table, ls_create_primary, ls_create_index, ls_comments);    
  END LOOP;      
end;