Expression Conversion Rules

Expression Conversion Rules

RULE-40001

Type

CONVERTED

Description

The built-in package has been converted.

Original SQL Text

CREATE OR REPLACE PROCEDURE proc1 AS
BEGIN
DBMS_OUTPUT.PUT('Hello');
DBMS_OUTPUT.PUT_LINE('world!');
END;

Processed SQL Text

CREATE OR REPLACE PROCEDURE proc1 AS
BEGIN
SYSTEM_.PRINT('Hello');
SYSTEM_.PRINTLN('world!');
END;

RULE-40002

Version Scope: Altibase 6.3.1.0.0 or later

Type

TODO

Description

The user should chek whether the target built-in package is installed in Altibase.

Original SQL Text

CREATE OR REPLACE PROCEDURE proc1 AS
BEGIN
DBMS_OUTPUT.NEW_LINE;
END;

Processed SQL Text

CREATE OR REPLACE PROCEDURE proc1 AS
BEGIN
DBMS_OUTPUT.NEW_LINE /* [TODO] RULE-40002 : Confirm the target built-in package is installed at Altibase */;
END;

RULE-40003

Type

TODO

Description

The target built-in packages should be manually converted.

Original SQL Text

CREATE OR REPLACE PROCEDURE proc1(p_file FILE_TYPE) AS
BEGIN
UTL_FILE.PUTF(p_file, 'Hello %s!', 'world');
END;

Processed SQL Text

CREATE OR REPLACE PROCEDURE proc1(p_file FILE_TYPE) AS
BEGIN
UTL_FILE.PUTF(p_file, 'Hello %s!', 'world') /* [TODO] RULE-40003 : The target built-in package must be converted manually */;
END;

RULE-40004

Type

CONVERTED

Description

The target SQL functions have been converted.

Original SQL Text

CREATE OR REPLACE VIEW v1 AS
SELECT UID FROM dual;

Processed SQL Text

CREATE OR REPLACE VIEW v1 AS
SELECT USER_ID() FROM dual;

RULE-40005

Type

TODO

Description

This function is not supported.

Original SQL Text

CREATE OR REPLACE FUNCTION func1(p1 VARCHAR2)
RETURN NUMBER AS
v1 NUMBER := LENGTHC(p1);
BEGIN
RETURN v1;
END;

Processed SQL Text

CREATE OR REPLACE FUNCTION func1(p1 VARCHAR2)
RETURN NUMBER AS
v1 NUMBER := LENGTHC(p1) /* [TODO] RULE-40005 : Unsupported function */;
BEGIN
RETURN v1;
END;

RULE-40006

Type

CONVERTED

Description

The arguments of TRIM have been converted.

Original SQL Text

CREATE OR REPLACE VIEW stats AS
SELECT TRIM(LEADING 0 FROM total_stats)
FROM test_result WHERE date = SYSDATE;

Processed SQL Text

CREATE OR REPLACE VIEW stats AS
SELECT LTRIM(total_stats, 0)
FROM test_result WHERE date = SYSDATE;

RULE-40007

Type

CONVERTED

Description

The arguments of BIN_TO_NUM function have been converted by being connected with '||'.

Original SQL Text

CREATE OR REPLACE VIEW status_view AS
SELECT BIN_TO_NUM(cp_plan, hp_plan, tv_plan, net_plan) status
FROM service_tbl WHERE ym = TO_CHAR(SYSDATE, 'YYYYMM');

Processed SQL Text

CREATE OR REPLACE VIEW status_view AS
SELECT BIN_TO_NUM(cp_plan || hp_plan || tv_plan || net_plan) status
FROM service_tbl WHERE ym = TO_CHAR(SYSDATE, 'YYYYMM');

RULE-40008

Type

TODO

Description

The CAST function containing a subquery as an argument should be manually converted.

Original SQL Text

CREATE OR REPLACE VIEW v1 AS
SELECT c1, CAST(MULTISET(SELECT c1 FROM t2 ORDER BY c2) AS tmp_tbl)
FROM t1 ORDER BY c1;

Processed SQL Text

CREATE OR REPLACE VIEW v1 AS
SELECT c1, CAST(MULTISET(SELECT c1 FROM t2 ORDER BY c2) AS tmp_tbl) /* [TODO] RULE-40008 : The CAST function containing a subquery as an argument should be manually converted */
FROM t1 ORDER BY c1;

RULE-40009

Type

TODO

Description

The DUMP function contains multiple arguments should be manually converted.

Original SQL Text

CREATE OR REPLACE VIEW v1 AS
SELECT DUMP(c3, 8, 3, 2)
FROM t1 WHERE c3 = 100 ORDER BY c2;

Processed SQL Text

CREATE OR REPLACE VIEW v1 AS
SELECT DUMP(c3, 8, 3, 2) /* [TODO] RULE-40009 : The DUMP function contains multiple arguments should be manually converted */
FROM t1 WHERE c3 = 100 ORDER BY c2;

RULE-40010

Type

CONVERTED

Description

The EXTRACT function has been converted.

Original SQL Text

CREATE OR REPLACE VIEW rsvStats_year AS
SELECT EXTRACT(YEAR FROM rsv_date) year, COUNT(*) cnt
FROM rsv_table GROUP BY EXTRACT(YEAR FROM rsv_date);

Processed SQL Text

CREATE OR REPLACE VIEW rsvStats_year AS
SELECT EXTRACT(rsv_date, 'YEAR') year, COUNT(*) cnt
FROM rsv_table GROUP BY EXTRACT(rsv_date, 'YEAR');

RULE-40011

Type

TODO

Description

The datetime field prefixed with 'TIMEZONE' in the EXTRACT function should be manually converted.

Original SQL Text

CREATE VIEW view1 AS
SELECT EXTRACT(TIMEZONE_REGION FROM CURRENT_TIMESTAMP) FROM dual;

Processed SQL Text

CREATE VIEW view1 AS
SELECT EXTRACT(TIMEZONE_REGION /* [TODO] RULE-40011 : The datetime field prefixed 'TIMEZONE' in the EXTRACT function should be manually converted */ FROM CURRENT_TIMESTAMP) FROM dual;

RULE-40012

Type

TODO

Description

The EXTRACT function containing XMLType instance as parameters should be manually converted.

Original SQL Text

CREATE VIEW view1 AS
SELECT EXTRACT(emp_into, 'Employee/Name') emp_name FROM dual;

Processed SQL Text

CREATE VIEW view1 AS
SELECT EXTRACT(emp_info, 'Employee/Name') /* [TODO] RULE-40012 : The EXTRACT function containing XMLType instance as parameters should be manually converted */ emp_name FROM dual;

RULE-40013

Type

CONVERTED

Description

The SYS_CONTEXT function has been converted.

Original SQL Text

CREATE VIEW view1 AS
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual;

Processed SQL Text

CREATE VIEW view1 AS
SELECT USER_NAME() FROM dual;

RULE-40014

Type

TODO

Description

The precision degree, which is an optional argument, of the CURRENT_TIMESTAMP function should be manually converted.

Original SQL Text

CREATE VIEW view1 AS
SELECT CURRENT_TIMESTAMP(0) FROM dual;

Processed SQL Text

CREATE VIEW view1 AS
SELECT CURRENT_TIMESTAMP(0) /* [TODO] RULE-40014 : The optional argument of the function CURRENT_TIMESTAMP, precision must be converted manually */ FROM dual;

RULE-40015

Type

TODO

Description

The nlsparam,which is an optional argument, specifying languages should be manually converted.

Original SQL Text

CREATE VIEW view1 AS
SELECT TO_CHAR(SYSDATE, 'DL', 'NLS_DATE_LANGUAGE = korean') FROM dual;

Processed SQL Text

CREATE VIEW view1 AS
SELECT TO_CHAR(SYSDATE, 'DL', 'NLS_DATE_LANGUAGE = korean' /* [TODO] RULE-40015 : The optional argument, nlsparam must be converted manually */) FROM dual;

RULE-40016

Type

TODO

Description

The optional argument match_param, which may affect operating a function, should be manually converted.

Original SQL Text

CREATE VIEW view1 AS
SELECT REGEXP_SUBSTR(content, '(Name: )(([a-z]+) ([a-z]+))', 1, 1, 'i', 3) "First Name" FROM page_pi;

Processed SQL Text

CREATE VIEW view1 AS
SELECT REGEXP_SUBSTR(content, '(Name: )(([a-z]+) ([a-z]+))', 1, 1, 'i' /* [TODO] RULE-40016 : The optional argument, match_param must be converted manually */, 3) "First Name" FROM page_pi;

RULE-40017

Version Scope: Altibase 6.3.1.0.0 or above

Type

TODO

Description

The optional argument subexpr should be manually converted.

Original SQL Text

CREATE VIEW view1 AS
SELECT REGEXP_SUBSTR(content, '(Name: )(([a-z]+) ([a-z]+))', 1, 1, 'i', 4) "Family Name" FROM page_pi;

Processed SQL Text

CREATE VIEW view1 AS
SELECT REGEXP_SUBSTR(content, '(Name: )(([a-z]+) ([a-z]+))', 1, 1, 'i', 4 /* [TODO] RULE-40017 : The optional argument, subexpr must be converted manually */) "Family Name" FROM page_pi;

RULE-40018

Type

CONVERTED

Description

The MOD operator has been converted as a function.

Original SQL Text

CREATE FUNCTION func1(p1 PLS_INTEGER) RETURN PLS_INTEGER AS
v1 PLS_INTEGER := p1 MOD 2;
BEGIN
RETURN v1;
END;

Processed SQL Text

CREATE FUNCTION func1(p1 PLS_INTEGER) RETURN PLS_INTEGER AS
v1 PLS_INTEGER := MOD(p1, 2);
BEGIN
RETURN v1;
END;

RULE-40019

Type

CONVERTED

Description

The built-in package has been converted.

Original SQL Text

CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV, FWEEK_PSCAT_SALES_MV', 'CF', '', TRUE, FALSE, 0,0,0, FALSE, FALSE);
END;

Processed SQL Text

CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
REFRESH_MATERIALIZED_VIEW(USER_NAME(), 'CAL_MONTH_SALES_MV');
REFRESH_MATERIALIZED_VIEW(USER_NAME(), 'FWEEK_PSCAT_SALES_MV');
END;

RULE-40020

Type

CONVERTED

Description

The WM_CONCAT function has been converted to the LISTAGG function.

Original SQL Text

SELECT WM_CONCAT(val) FROM t1;

Processed SQL Text

SELECT LISTAGG(val, ',') WITHIN GROUP(ORDER BY val) FROM t1;

RULE-40021

Type

TODO

Description

The parameter in the function 'SYS_CONTEXT' should be converted manually.

Original SQL Text

CREATE VIEW v_r40021 AS 
SELECT SYS_CONTEXT('USERENV', 'INSTANCE_NAME') FROM dual;

Processed SQL Text

CREATE VIEW v_r40021 AS 
SELECT SYS_CONTEXT('USERENV', 'INSTANCE_NAME') /* [TODO] RULE-40021 : The parameter in the function 'SYS_CONTEXT' should be converted manually.*/ FROM dual;

RULE-40022

Type

CONVERTED

Description

The third argument of the function 'SYS_CONTEXT', which indicates the length of the return value, is converted to the function 'SUBSTR' surrounding 'SYS_CONTEXT'.

Original SQL Text

CREATE VIEW v_r40022 AS SELECT SYS_CONTEXT('USERENV', 'INSTANCE_NAME', 100) FROM dual;

Processed SQL Text

CREATE VIEW v_r40022 AS SELECT SUBSTR(SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), 0, 100) FROM dual;

RULE-40023

Type

TODO

Description

Since SQLERRM(error_code) is an unsupported function, it should be converted manually.

Original SQL Text

CREATE OR REPLACE PROCEDURE proc1 
AS
BEGIN
FOR curosor1 IN (SELECT c1, c2 FROM t1)
LOOP
UPDATE t2 SET c2 = curosor1 .c2 WHERE c1 = curosor1 .c1;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('SQL ERROR CODE:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE:' || SQLERRM);
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
END;

Processed SQL Text

CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
FOR curosor1 IN (SELECT c1, c2 FROM t1)
LOOP
UPDATE t2 SET c2 = curosor1 .c2 WHERE c1 = curosor1 .c1;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('SQL ERROR CODE:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE:' || SQLERRM);
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE) /* [TODO] RULE-40023 : The SQLERRM(error_code) function should be manually converted */);
END;