Help Center> Data Warehouse Service> Tool Guide> DSC - SQL Syntax Migration Tool> SQL Syntax Migration Reference> Oracle Syntax Migration (GaussDB A)> Other Functions
View PDF

Other Functions

This section describes the following functions:

Type Casting

The Oracle/PLSQL CAST function converts one data type to another.

CAST ( AS TEXT)

Static values are migrated as CAST AS TEXT. In the PL/SQL storage prcedures and fucntions, static values in the in the SELECT INTO clause are not migrated. The input and output static data are the same.

Input - 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;

Output

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

In Oracle, the CLOB data type stores variable-length character data (character large objects) in the single-byte or multi-byte character set (supports more than 4 GB). The TO_CLOB function is used to convert a string (CHAR, VARCHAR2, NCHAR, NVARHCAR2, CLOB, or NCLOB) to the CLOB data type. DSC supports TO_CLOB in SQL and PL/SQL.

Input - TO_CLOB in SQL

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

Output

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

Input - TO_CLOB in PL/SQL

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;
/

Output

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( )

The table column name like SYS_ID VARCHAR2 (32) DEFAULT sys_guid( ) is not supported. Therefore, the default table column name is displayed.

Input

  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()
   );

Output

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

The dbms_job function cannot be called using sys. For example, sys.dbms_job.submit(job => v_jobid, what => 'begin eip_htm_integration_pkg.import_instruction_job; end;', next_date => SYSDATE);

After sys is removed, the call takes effect. For example, dbms_job.submit(job => v_jobid, what => 'begin eip_htm_integration_pkg.import_instruction_job; end;', next_date => SYSDATE);

SCRIPTS: EIP_HTM_INTEGRATION_PKG.sql, SAD_CALC_CUSTOM_DRAWBACK_PKG.sql

INPUT

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;

OUTPUT :

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 ;

User Environment

In Oracle, USERENV is a namespace that describes the current session, and it is used to obtain information about the current Oracle session. DSC supports the migration of USERENV statements as shown in the examples.

The following scenarios are not supported and DSC will log an exception for them:

Error Cause

Sample Input

Sample Output

Unsupported parameter

SELECT userenv('LANG') LANG FROM DUAL;

SELECT userenv('LANG') LANG FROM DUAL;

Parameter is a variable

SELECT userenv(ENTRYID) LANG FROM DUAL;

SELECT userenv(ENTRYID) LANG FROM DUAL;

No parameter provided

SELECT userenv() LANG FROM DUAL;

SELECT userenv() LANG FROM DUAL;

Empty parameter provided

SELECT userenv('SID','INSTANCE' ) LANG FROM DUAL;

SELECT userenv('SID','INSTANCE' ) LANG FROM DUAL;

More than one parameter provided

SELECT userenv('' ) LANG FROM DUAL;

SELECT userenv('' ) LANG FROM DUAL;

One valid syntax and one invalid syntax (unsupported parameter)

SELECT USERENV( 'sid' ) ,USERENV( 'lang' ) FROM dual ;

SELECT USERENV( 'sid' ) ,USERENV( 'lang' ) FROM dual ;

Double quotes in input

SELECT USERENV( "sid" ) FROM dual ;

SELECT USERENV( "sid" ) FROM dual ;

Before using this function, perform the following operations:

  1. Create and use the MIG_ORA_EXT schema.
  2. Copy the contents of the custom script and execute the script in all target databases for which migration is to be performed. For details, see Migration Process.

Input - USERENV

SELECT  userenv('SID') SID FROM DUAL;

Output

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

Input - 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 ;
/

Output

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 should be replaced with configurable data sources.

Oracle Syntax

Syntac After Migration

 
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);