Chapter 3 Migrating Functions

This chapter explains how to migrate SQL functions.

3.1 CONVERT

Description

CONVERT converts a string from one character set to another.

Functional differences

  • Oracle database
    • The string is converted from the character set identified in the third argument to the character set identified in the second argument.
  • PostgreSQL
    • The string is converted from the character set identified in the second argument to the character set identified in the third argument.

Migration procedure

Use the following procedure to perform migration:

  1. Search for the keyword CONVERT and identify where it is used.
  2. Switch the second and third arguments.
  3. Change the character sets in the second and third arguments to names that are valid under the PostgreSQL encoding system.

Migration example

The example below shows migration when strings are changed to the character set of the target database.

Oracle database PostgreSQL 
SELECT CONVERT( 'abc', 
 'JA16EUC', 
 'AL32UTF8' ) 
 FROM DUAL;
 
SELECT CONVERT( CAST( 'abc' AS BYTEA ), 
  'UTF8', 
 'EUC_JP' ) 
 FROM DUAL;

3.2 EMPTY_BLOB

Description

EMPTY_BLOB initializes BLOB type areas and creates empty data.

Functional differences

  • Oracle database
    • BLOB type areas are initialized and empty data is created.
  • PostgreSQL
    • EMPTY_BLOB cannot be used. Instead, use a zero-length string for initialization.

Migration procedure

Use the following procedure to perform migration:

  1. Search for the keyword EMPTY_BLOB() and identify where it is used.
  2. Change EMPTY_BLOB() to the zero-length string ''.

Migration example

The example below shows migration when empty data is inserted into the BLOB column.

Oracle database PostgreSQL 
CREATE TABLE t1( col1 INTEGER, 
 col2 BLOB ); 


 INSERT INTO t1 VALUES( 11, EMPTY_BLOB() );
 
CREATE TABLE t1( col1 INTEGER, 
 col2 BYTEA ); 


 INSERT INTO t1 VALUES( 11, '' );

The example below shows migration when BLOB column data is updated to empty.

Oracle database PostgreSQL 
UPDATE t1 SET col2 = EMPTY_BLOB() WHERE col1 = 11;
 
UPDATE t1 SET col2 = '' WHERE col1 = 11;

3.3 LEAD

Description

LEAD obtains the value of the column specified in the arguments from the record that is the specified number of lines below.

Functional differences

  • Oracle database
    • A NULL value in the column specified in the arguments can be excluded from the calculation.
  • PostgreSQL
    • A NULL value in the column specified in the arguments cannot be excluded from the calculation.

Migration procedure

Use the following procedure to perform migration:

  1. Search for the keyword LEAD and identify where it is used.
  2. If the IGNORE NULLS clause is specified, check the following values to create a subquery that excludes NULL values:
    • Arguments of LEAD (before IGNORE NULLS)
    • Tables targeted by IGNORE NULLS
    • Columns targeted by IGNORE NULLS
    • Columns to be sorted
  3. Change the table in the FROM clause to a subquery to match the format shown below.
  4. Replace LEAD in the select list with MAX. Specify LEAD_IGNLS in the arguments of MAX, and PARTITION BY CNT in the OVER clause.

~~~ FROM ( SELECT columnBeingUsed, CASE WHEN ignoreNullsTargetColumn IS NOT NULL THEN LEAD( leadFunctionArguments ) OVER( PARTITION BY NVL2( ignoreNullsTargetColumn, '0', '1' ) ORDER BY sortTargetColumn ) END AS LEAD_IGNLS, COUNT( ignoreNullsTargetColumn ) OVER( ORDER BY sortTargetColumn ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) AS CNT FROM ignoreNullsTargetTable ) AS T1; ~~~

Migration example

The example below shows migration when NULL values are not included in the calculation of column values.

Oracle database PostgreSQL 
SELECT staff_id, 
       name, 
       job, 
 LEAD( job, 1 ) IGNORE NULLS 
 OVER( ORDER BY staff_id DESC) 
 AS "LEAD_IGNORE_NULLS" 
 FROM staff_table 
 ORDER BY staff_id DESC;

















SELECT staff_id, 
       name, 
       job, 
 MAX( LEAD_IGNLS ) 
 OVER( PARTITION BY CNT ) 
 AS "LEAD_IGNORE_NULLS" 
 FROM ( SELECT staff_id, 
       name, 
       job, 
 CASE 
 WHEN job IS NOT NULL THEN 
 LEAD( job, 1 ) 
 OVER( PARTITION BY NVL2( 
       job, 
       '0', 
       '1' ) 
 ORDER BY staff_id DESC) 
 END AS LEAD_IGNLS, 
 COUNT( job ) 
 OVER( ORDER BY staff_id DESC 
  ROWS BETWEEN 1 FOLLOWING 
  AND UNBOUNDED FOLLOWING ) 
 AS CNT 
 FROM staff_table ) AS T1 
 ORDER BY staff_id DESC;

Information

If the IGNORE NULLS clause is not specified or if the RESPECT NULLS clause is specified, NULL is included in the calculation, so operation is the same as the LEAD function of PostgreSQL. Therefore, if the IGNORE NULLS clause is not specified, no changes need to be made. If the RESPECT NULLS clause is specified, delete the RESPECT NULLS clause.

The example below shows migration when RESPECT NULLS is specified in the Oracle database.

LEAD migration example (when RESPECT NULLS is specified)

Oracle database PostgreSQL 
SELECT staff_id, 
       name, 
       job, 
 LEAD( job, 1 ) 
 RESPECT NULLS 
 OVER( ORDER BY staff_id DESC ) 
 AS "LEAD_RESPECT_NULLS" 
 FROM staff_table 
 ORDER BY staff_id DESC;
 
SELECT staff_id, 
       name, 
       job, 
 LEAD( job, 1 ) 
 OVER( ORDER BY staff_id DESC ) 
 AS "LEAD_RESPECT_NULLS" 
 FROM staff_table 
 ORDER BY staff_id DESC;

3.4 RAWTOHEX

Description

RAWTOHEX converts RAW type data to a hexadecimal string value.

Functional differences

  • Oracle database
    • RAW type data is converted to a hexadecimal string value.
  • PostgreSQL
    • RAWTOHEX cannot be used. Instead, use ENCODE to convert binary data types corresponding to the RAW type.

Migration procedure

Use the following procedure to perform migration:

  1. Search for the keyword RAWTOHEX and identify where it is used.
  2. Change RAWTOHEX to ENCODE and specify HEX in the second argument.

Migration example

The example below shows migration when RAW data types are converted to hexadecimal string values.

Oracle database PostgreSQL 
SELECT RAWTOHEX ( 'ABC' ) FROM DUAL;
 
SELECT ENCODE ( 'ABC', 'HEX' ) FROM DUAL;

Information

A RAWTOHEX function that is used in PL/SQL to take a string as an argument must first be converted to a binary data type using DECODE, and then ENCODE must be used to convert the value to a string.

The example below shows migration of RAWTOHEX when it is used in PL/SQL to take a string as an argument.

ROWTOHEX migration example (when taking a string as an argument in PL/SQL)

Oracle database PostgreSQL 
SET SERVEROUTPUT ON; 


 DECLARE 
 HEX_TEXT VARCHAR2( 100 ); 
 BEGIN 
 HEX_TEXT := RAWTOHEX( '414243' ); 



 DBMS_OUTPUT.PUT_LINE( HEX_TEXT ); 
 END; 
 / 
 
 
 DO $$ 
 DECLARE 
 HEX_TEXT TEXT; 
 BEGIN 
 HEX_TEXT := 
 ENCODE( DECODE( '414243', 'HEX' ), 'HEX' ); 
 PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE ); 
 PERFORM DBMS_OUTPUT.PUT_LINE( HEX_TEXT ); 
 END; 
 $$ LANGUAGE plpgsql;

3.5 REGEXP_REPLACE

Description

REGEXP_REPLACE uses a regular expression pattern to replace a string.

Functional differences

  • Oracle database
    • All strings that match the regular expression pattern are replaced.
  • PostgreSQL
    • The first string that matches the regular expression pattern is replaced.

Migration procedure

The REGEXP_REPLACE function of PostgreSQL can return the same result if the option string is specified in the fourth argument. Use the following procedure to perform migration:

  1. Search for the keyword REGEXP_REPLACE and identify where it is used.
    1. Specify the argument 'g' in the fourth argument of REGEXP_REPLACE.

Migration example

The example below shows migration when a regular expression pattern is used to convert a string.

Oracle database PostgreSQL 
SELECT REGEXP_REPLACE( '2016', 
       '[0-2]', 
       '*' ) AS "REGEXP_REPLACE" 
 FROM DUAL; 
 
SELECT REGEXP_REPLACE( '2016', 
       '[0-2]', 
       '*', 
       'g' ) AS "REGEXP_REPLACE" 
 FROM DUAL;

3.6 TO_TIMESTAMP

Description

TO_TIMESTAMP converts a string value to the TIMESTAMP data type.

Functional differences

  • Oracle database
    • The language to be used for returning the month and day of the week can be specified.
  • PostgreSQL
    • The language to be used for returning the month and day of the week cannot be specified.

Migration procedure

Use the following procedure to perform migration:

  1. Search for the keyword TO_TIMESTAMP and identify where it is used.
  2. If the third argument of TO_TIMESTAMP is specified, delete it.
  3. If the a string in the first argument contains a national character string, it is replaced with a datetime keyword supported by PostgreSQL.

Migration example

The example below shows migration when a string value is converted to the TIMESTAMP data type. One string specifies the month in Japanese as a national character, so it is replaced with the date keyword 'JULY'.

Oracle database PostgreSQL 
SELECT TO_TIMESTAMP('2016/**/21 14:15:30', 
        'YYYY/MONTH/DD HH24:MI:SS', 
        'NLS_DATE_LANGUAGE = Japanese') 
 FROM DUAL;
 
SELECT TO_TIMESTAMP('2016/JULY/21 14:15:30', 
        'YYYY/MONTH/DD HH24:MI:SS') 


 FROM DUAL;

**: The July in Japanese