其他函数
本节介绍如下函数:
类型转换
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数据类型。DSC支持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; /
sys_guid( )
不支持类似于SYS_ID VARCHAR2 (32) DEFAULT sys_guid( )的函数名称,因此表列名称显示为默认值。
输入
CREATE TABLE SAD.FND_DATA_CHANGE_LOGS_T ( LOGID NUMBER, TABLE_NAME VARCHAR2(40) NOT NULL ENABLE, TABLE_KEY_COLUMNS VARCHAR2(200), TABLE_KEY_VALUES VARCHAR2(200), COLUMN_NAME VARCHAR2(40) NOT NULL ENABLE, COLUMN_CHANGE_FROM_VALUE VARCHAR2(200), COLUMN_CHANGE_TO_VALUE VARCHAR2(200), DESCRIPTION VARCHAR2(500), SYS_ID VARCHAR2(32) DEFAULT sys_guid() );
输出
CREATE TABLE sad.fnd_data_change_logs_t ( logid NUMBER ,table_name VARCHAR2 (40) NOT NULL /* ENABLE */ ,table_key_columns VARCHAR2 (200) ,table_key_values VARCHAR2 (200) ,column_name VARCHAR2 (40) NOT NULL /* ENABLE */ ,column_change_from_value VARCHAR2 (200) ,column_change_to_value VARCHAR2 (200) ,description VARCHAR2 (500) ,sys_id VARCHAR2 (32) DEFAULT MIG_ORA_EXT.Sys_guid () );
sys.dbms_job.submit
dbms_job函数不支持通过sys来调用,例如,sys.dbms_job.submit(job => v_jobid, what => 'begin eip_htm_integration_pkg.import_instruction_job; end;', next_date => SYSDATE)。
如果从删除sys,则调用生效,例如,dbms_job.submit(job => v_jobid, what => 'begin eip_htm_integration_pkg.import_instruction_job; end;', next_date => SYSDATE)。
脚本:EIP_HTM_INTEGRATION_PKG.SQL, SAD_CALC_CUSTOM_DRAWBACK_PKG.SQL
输入
CREATE OR REPLACE PACKAGE BODY "SAD"."EIP_HTM_INTEGRATION_PKG" IS PROCEDURE greate_import_instruction_job IS v_jobid NUMBER; BEGIN IF bas_lookup_misc_pkg.exits_run_job('eip_htm_integration_pkg.import_instruction_job') = 'N' THEN sys.dbms_job.submit(job => v_jobid, what => 'begin eip_htm_integration_pkg.import_instruction_job; end;', next_date => SYSDATE); COMMIT; END IF; --- END greate_import_instruction_job; END EIP_HTM_INTEGRATION_PKG;
输出
CREATE OR REPLACE PROCEDURE SAD.EIP_HTM_INTEGRATION_PKG#greate_import_instruction_job IS v_jobid NUMBER ; ex_data_error EXCEPTION ; ex_prog_error EXCEPTION ; BEGIN IF BAS_LOOKUP_MISC_PKG#exits_run_job ( 'eip_htm_integration_pkg.import_instruction_job' ) = 'N' THEN sys.dbms_job.submit ( job => v_jobid ,what => 'begin eip_htm_integration_pkg.import_instruction_job; end;' ,next_date => SYSDATE ) ; /* COMMIT; */ NULL ; END IF ; --- END ;
用户环境
在Oracle中,USERENV命名空间返回当前会话信息。DSC可迁移如下示例中的USERENV语句。
不支持以下情况,出现以下情况工具将报异常。
错误原因 |
示例输入 |
示例输出 |
---|---|---|
参数不支持 |
SELECT userenv('LANG') LANG FROM DUAL; |
SELECT userenv('LANG') LANG FROM DUAL; |
参数是变量 |
SELECT userenv(ENTRYID) LANG FROM DUAL; |
SELECT userenv(ENTRYID) LANG FROM DUAL; |
未提供参数 |
SELECT userenv() LANG FROM DUAL; |
SELECT userenv() LANG FROM DUAL; |
空参数 |
SELECT userenv('SID','INSTANCE' ) LANG FROM DUAL; |
SELECT userenv('SID','INSTANCE' ) LANG FROM DUAL; |
提供了多个参数 |
SELECT userenv('' ) LANG FROM DUAL; |
SELECT userenv('' ) LANG FROM DUAL; |
一个语法有效另外一个无效 (不支持的参数) |
SELECT USERENV( 'sid' ) ,USERENV( 'lang' ) FROM dual ; |
SELECT USERENV( 'sid' ) ,USERENV( 'lang' ) FROM dual ; |
输入内容含双引号 |
SELECT USERENV( "sid" ) FROM dual ; |
SELECT USERENV( "sid" ) FROM dual ; |
输入: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应替换为可配置的数据源。
Oracle 语法 |
迁移后语法 |
---|---|
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); |
