其他函数

类型转换

Oracle/PLSQL的CAST函数用于转换数据类型。

CAST ( AS TEXT)

一般情况下，静态值迁移为CAST AS TEXT。但是，在 PL/SQL存储过程和函数中 ，SELECT INTO子句中包的含静态值不迁移，静态值输入和输出完全相同。

输入： CAST（AS TEXT）

BEGIN
               IF
                    V_DETAILSTATUS = '002'
                    THEN
                         meta.p_mt_std_cover_log.r_cover_log ( '384969028' ,'566' ,'35' ) ;
                    SELECT
                    'abc'  INTO V_RECL_USER
               FROM
                    STAT_DETAIL
               WHERE
                    DAPCODE = V_DAPCODE
                    AND DETAIL_ID = V_DETAIL_ID ;
               ELSIF
...
END;
 
 

输出

BEGIN
               IF
                    V_DETAILSTATUS = '002'
                    THEN
                         meta.p_mt_std_cover_log.r_cover_log ( '384969028' ,'566' ,'35' ) ;
                    SELECT
                    'abc'  INTO V_RECL_USER
               FROM
                    STAT_DETAIL
               WHERE
                    DAPCODE = V_DAPCODE
                    AND DETAIL_ID = V_DETAIL_ID ;
               ELSIF
...
END;

TO_CLOB

在Oracle中，CLOB数据类型可存储单字节或多字节字符集（支持超过4 GB）中的变长字符数据（字符型大对象）。TO_CLOB函数用于将字符串（CHAR、VARCHAR2 、NCHAR、NVARHCAR2、CLOB或NCLOB）转换为CLOB数据类型。MigrationTool支持SQL和PL/SQL中的TO_CLOB。

输入：在SQL中使用TO_CLOB

create table clob_t ( c1 int, c2 clob );
insert into clob_t values ( 1, TO_CLOB('abcddedf') );

输出

create table clob_t ( c1 int, c2 clob );
insert into clob_t values ( 1, CAST('abcddedf' AS CLOB) );

输入：在PL/SQL中使用TO_CLOB

declare v_clob clob;
        v_text varchar(1000);
begi  in Sn
    v_clob := TO_CLOB('abcddedf');
    insert into clob_t values ( 2, v_clob );

 v_text := '123454';
 insert into clob_t values ( 3, TO_CLOB(v_text || v_clob) );
end;
/

输出

declare v_clob clob;
        v_text varchar(1000);
begin
    v_clob := CAST('abcddedf' AS CLOB);
    insert into clob_t values ( 2, v_clob );

 v_text := '123454';
 insert into clob_t values ( 3, CAST(v_text || v_clob AS CLOB) );
end;
/

用户环境

在Oracle中，USERENV命名空间返回当前会话信息。DSC可迁移如下示例中的USERENV语句。

不支持以下情况，出现以下情况工具将报异常。

在使用此函数之前：

1. 创建并使用MIG_ORA_EXT模式。
2. 复制customs_scripts文件的内容，并在要执行迁移的所有目标数据库中执行此脚本。详情请参见迁移流程。

输入：USERENV

SELECT  userenv('SID') SID FROM DUAL;

输出

SELECT  mig_ora_ext.userenv('SID') SID FROM DUAL;

输入：USERENV

CREATE
     OR REPLACE PROCEDURE test_userEnvFun AS 
     v_sid NUMBER ( 8 ) := 0 ;
     v_instid NUMBER ( 8 ) := 0 ;
BEGIN
                 SELECT
                USERENV( 'sid' ) ,USERENV( 'instance' ) INTO v_sid ,v_instid
                FROM
               dual ;
end test_userEnvFun ;
/

输出

CREATE
     OR REPLACE PROCEDURE test_userEnvFun  AS 
     v_sid NUMBER ( 8 ) := 0 ;
     v_instid NUMBER ( 8 ) := 0 ;
BEGIN
                 SELECT
                MIG_ORA_EXT.USERENV ( 'sid' ) ,MIG_ORA_EXT.USERENV ( 'instance' ) INTO v_sid ,v_instid
                FROM
               dual ;
  end ;
   /

DBLINK

DBLINK应替换为可配置的数据源。

SELECT col1, col2+col3 AS expcol2, col3 expcol3 
FROM stage.INF_RIM_112_INFO_M@HABIDB1 t
WHERE t.month_id = last_months
AND  DELETE_STATE = '0';

SELECT t.*
FROM (select * from stage.INF_RIM_112_INFO_M@HABIDB1 t 
WHERE t.month_id = last_months
AND  DELETE_STATE = '0') t,
dim.dim_ref_area_no t1
WHERE t1.AREA_ID_PROV = t.area_no
AND t1.area_no = to_char('abc');

SELECT t.*
FROM (select *
from stage.INF_RIM_112_INFO_M@HABIDB1 t) t,
dim.dim_ref_area_no t1
WHERE t1.AREA_ID_PROV = t.area_no
AND t1.area_no = to_char('abc');

SELECT COUNT(1) INTO V_CNT1
FROM ODS.ODS_EXECUTE_LOG@HABIDB1
WHERE MONTH_ID = LAST_MONTHS
AND PROCNAME IN ('P_INF_RIM_112_INFO_M')
AND RESULT = 'SUCCESS';
SELECT COUNT(1) AS cnt INTO V_CNT1
FROM ODS.ODS_EXECUTE_LOG@HABIDB1
WHERE MONTH_ID = LAST_MONTHS
AND PROCNAME IN ('P_INF_RIM_112_INFO_M')
AND RESULT = 'SUCCESS';

SELECT *
FROM exec_on_extension('ds_ora_habidb1', $q$SELECT col1, col2+col3 AS expcol2, col3 expcol3 
FROM stage.INF_RIM_112_INFO_M t
WHERE t.month_id = last_months
AND  DELETE_STATE = '0'$q$) 
AS (col1 TEXT, expcol2 TEXT, expcol3 TEXT);

SELECT t.*
FROM (SELECT * FROM exec_on_extension('ds_ora_habidb1', $q$select * 
from stage.INF_RIM_112_INFO_M t
WHERE t.month_id = last_months
AND DELETE_STATE = '0' 
$q$) AS (month_id TEXT, delete_state TEXT) ) t,
dim.dim_ref_area_no t1
WHERE t1.AREA_ID_PROV = t.area_no
AND t1.area_no = to_char('abc');

SELECT t.*
FROM (SELECT * FROM exec_on_extension('ds_ora_habidb1', $q$select * 
from stage.INF_RIM_112_INFO_M t
$q$) AS (a TEXT) ) t,
dim.dim_ref_area_no t1
WHERE t1.AREA_ID_PROV = t.area_no
AND t1.area_no = to_char('abc');

SELECT aggfn 
INTO V_CNT1 FROM exec_on_extension('ds_ora_habidb1', $q$SELECT COUNT(1)
FROM ODS.ODS_EXECUTE_LOG
WHERE MONTH_ID = LAST_MONTHS
AND PROCNAME IN ('P_INF_RIM_112_INFO_M')
AND RESULT = 'SUCCESS'$q$) AS (aggfn TEXT);

SELECT cnt 
INTO V_CNT1 FROM exec_on_extension('ds_ora_habidb1', $q$SELECT COUNT(1) AS cnt
FROM ODS.ODS_EXECUTE_LOG
WHERE MONTH_ID = LAST_MONTHS
AND PROCNAME IN ('P_INF_RIM_112_INFO_M')
AND RESULT = 'SUCCESS'$q$) AS (cnt TEXT);