PGXN

Contents

• • Chapter 6 Notes on Using orafce
    • 6.1 Data Types
    • 6.2 Functions
    • 6.3 Standard Packages
    • 6.3.4.8 Example of Migrating DBMS_PIPE

Chapter 6 Notes on Using orafce

This chapter provides notes on using Oracle database compatibility features added by orafce.

6.1 Data Types

This section explains how to migrate data types added by orafce.

6.1.1 Notes on VARCHAR2

This section provides notes on VARCHAR2.

6.1.1.1 Specifying the Maximum Number of Bytes and Maximum Number of Characters

Functional differences

• Oracle database
  • Specifying the keyword BYTE or CHAR after a size enables the size to be indicated in terms of the maximum number of bytes or the maximum number of characters.
• PostgreSQL
  • The keyword BYTE or CHAR cannot be set after the size.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword VARCHAR2 and check if the keyword BYTE or CHAR is specified after the size.
2. If the BYTE keyword is specified, delete it.
3. If the CHAR keyword is specified, delete it and convert the data type to VARCHAR.

Migration example

The example below shows migration when the maximum number of bytes or the maximum number of characters for the VARCHAR2 type is specified.

CREATE TABLE t1( 
  col1 VARCHAR2(5 BYTE), 
  col2 VARCHAR2(5 CHAR) 
 );

CREATE TABLE t1( 
  col1 VARCHAR2(5), 
  col2 VARCHAR(5) 
 );

Note

The VARCHAR2 type does not support collating sequences. Therefore, the following error occurs when a collating sequence like that of an ORDER BY clause is required. At this time, the following HINT will prompt to use a COLLATE clause, however, because collating sequences are not supported, it is not possible to use this clause.

~~~ ERROR: could not determine which collation to use for string comparison HINT: Use the COLLATE clause to set the collation explicitly.

~~~

If the error shown above is displayed, explicitly cast the column to VARCHAR or TEXT type.

6.2 Functions

This section explains how to migrate functions added by orafce.

6.2.1 INSTRB

Description

INSTRB searches for a substring in a string and returns the start position (in bytes) of the first occurrence of the substring.

6.2.1.1 Obtaining the Start Position of a Substring (in Bytes)

Functional differences

• Oracle database
  • INSTRB searches for a substring in a string and returns the start position (in bytes) of the substring.
• PostgreSQL
  • There is no INSTRB function. Use STRPOSB instead. STRPOSB is unique to orafce.

Migration procedure

Use the following procedure to migrate to STRPOSB:

1. Search for the keyword INSTRB and identify where it is used.
2. Confirm that arguments up to the second argument are specified.
3. Change INSTRB to STRPOSB.

Migration example

The example below shows migration when searching for a particular substring in a string, and returning the start position of the substring in bytes.

SELECT c_code, INSTRB( c_address, ',' ) 
  FROM company_table;

SELECT c_code, STRPOSB( c_address, ',' ) 
  FROM company_table;

Note

If the third argument is specified in INSTRB, refer to the conversion example shown below. If the fourth argument is specified, migration is not possible.

Information

The general rules for STRPOSB are as follows:

Description

INSTRB returns the start position (in bytes) of a substring within a string.

Specification format

General rules

• STRPOSB searches for string str2 in str1 and returns the start position it finds in bytes.
• If str2 is not found, 0 is returned.
• The data type of the return value is INTEGER.

6.2.1.2 Obtaining the Start Position of a Substring from a Specified Search Start Position (in Bytes)

Functional differences

• Oracle database
  • The search start position is specified in the third argument of INSTRB.
• PostgreSQL
  • A search start position cannot be specified with STRPOSB.

Migration procedure

A search start position cannot be specified, so truncate the search target string to the start position so that the same result is returned. Use the following procedure to perform migration:

1. Search for the keyword INSTRB and identify where it is used.
2. Confirm that arguments up to the third argument are specified and that a positive number is specified.
3. Enclose the string specified in the first argument with SUBSTRB, and specify the value specified in the third argument of INSTRB as the second argument of SUBSTRB.
4. Change INSTRB to STRPOSB and delete the value specified in the third argument.
5. Enclose the function in a simple CASE expression to evaluate the result of the function changed in step 4.
   Define the selector so that 0 is returned when the result is 0.
   If the result is not 0, specify the same function as in step 4, and add the value obtained by subtracting 1 from the value specified in the second argument of SUBSTRB.

Migration example

The example below shows migration when a search start position is specified and then the start position of a string is found in bytes.

SELECT c_code, INSTRB( c_address, '-', 10 ) 
 FROM company_table; 

SELECT c_code, 
 CASE STRPOSB( SUBSTRB( c_address, 10 ),'-') 
 WHEN 0 THEN 0 
 ELSE STRPOSB( SUBSTRB( c_address, 10 ), '-' ) + 9 
 END 
 FROM company_table;

6.2.2 INSTRC, INSTR2, and INSTR4

Description

INSTRC, INSTR2, and INSTR4 return the start position of a substring in a string using the relevant encoding.

Functional differences

• Oracle database
  • INSTRC, INSTR2, and INSTR4 use the relevant encoding to search for a substring in a string from a specified position and then return the start position of the substring.
• PostgreSQL
  • There are no INSTRC, INSTR2, and INSTR4 functions. Only Unicode encoding is used in PostgreSQL.

Migration procedure

Use the following procedure to migrate to INSTR:

1. Search for the keywords INSTRC, INSTR2, and INSTR4, and identify where they are used.
2. Change those keywords to INSTR.

Migration example

The example below shows migration from INSTRC, INSTR2, and INSTR4.

 SELECT c_name, INSTRC( c_name, 'Corp', 2, 1 ) 
  FROM company_table; 


 SELECT c_name, INSTR2( c_name, 'Corp', 2, 1 ) 
  FROM company_table; 


 SELECT c_name, INSTR4( c_name, 'Corp', 2, 1 ) 
  FROM company_table;

SELECT c_name, INSTR( c_name, 'Corp', 2, 1 ) 
  FROM company_table; 

6.2.3 LENGTHC, LENGTH2, and LENGTH4

Description

LENGTHC, LENGTH2, and LENGTH4 use the relevant encoding to return the length of the specified string.

Functional differences

• Oracle database
  • LENGTHC, LENGTH2, and LENGTH4 use the relevant encoding to return the length of the specified string.
• PostgreSQL
  • There are no LENGTHC, LENGTH2, and LENGTH4 functions. Only Unicode encoding is used in PostgreSQL.

Migration procedure

Use the following procedure to migrate to LENGTH:

1. Search for the keywords LENGTHC, LENGTH2, and LENGTH4, and identify where they are used.
2. Change those keywords to LENGTH.

Migration example

The example below shows migration from LENGTHC, LENGTH2, and LENGTH4.

 SELECT name, LENGTHC( name ) 
  FROM staff_table 
  WHERE job = 'sales member'; 


 SELECT name, LENGTH2( name ) 
  FROM staff_table 
  WHERE job = 'sales member'; 


 SELECT name, LENGTH4( name ) 
  FROM staff_table 
  WHERE job = 'sales member';

 SELECT name, LENGTH( name ) 
  FROM staff_table 
  WHERE job = 'sales member'; 

6.2.4 LISTAGG

Description

LISTAGG returns a concatenated, delimited list of string values.

6.2.4.1 Specifying the Join Sequence for a List

Functional differences

• Oracle database
  • The join sequence for a list is specified using WITHIN GROUP(ORDER BY).
• PostgreSQL
  • WITHIN GROUP(ORDER BY) cannot be used. Instead, a join sequence can be specified using ORDER BY immediately after the value.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword LISTAGG and confirm where it is used.
2. Move the ORDER BY clause of WITHIN GROUP(ORDER BY) immediately after the value of LISTAGG and then delete WITHIN GROUP().

Migration example

The example below shows migration of the join sequence of specified values.

 SELECT manager_id, 
        LISTAGG( name, ', ' ) 
         WITHIN GROUP( ORDER BY staff_id ) 
  FROM staff_table 
  GROUP BY manager_id;

 SELECT manager_id, 
        LISTAGG( name, ', ' ORDER BY staff_id ) 


  FROM staff_table 
  GROUP BY manager_id;

6.2.4.2 Specifying the Join Sequence for a List per Group (Window Functions)

Functional differences

• Oracle database
  • The join sequence for a list per group is specified using WITHIN GROUP(ORDER BY) OVER(PARTITION BY).
• PostgreSQL
  • The join sequence for a list per group cannot be specified.

Migration procedure

The join sequence for a list per group cannot be specified, so sort the data into the sequence in which it is to be joined and then join it. Use the following procedure to perform migration:

1. Search for the keywords LISTAGG and OVER, and identify where the OVER clause of LISTAGG is used.
2. Convert the table in the FROM clause to a subquery, and move the ORDER BY clause of WITHIN GROUP(ORDER BY) to the subquery.
3. Delete WITHIN GROUP(ORDER BY).

Migration example

The example below shows migration when a join sequence for a list per group is specified.

 SELECT name, 
        manager_id, 
        LISTAGG( name, ', ' ) 
         WITHIN GROUP( ORDER BY staff_id ) 
         OVER( PARTITION BY manager_id )  
  FROM staff_table; 

 SELECT name, 
        manager_id, 
        LISTAGG( name, ', ' ) 


         OVER( PARTITION BY manager_id ) 
  FROM ( SELECT * FROM staff_table 
          ORDER BY staff_id ) st_tbl; 
 

6.2.5 NLSSORT

Description

NLSSORT returns a binary value that denotes the lexical order of the locale (COLLATE).

6.2.5.1 Sorting by the Specified Locale

Functional differences

• Oracle database
  • The locale is specified by NLS_SORT=locale.
    The specifiable locales are provided by the Oracle database.
• PostgreSQL
  • The locale is specified by locale.
    The specifiable locales depend on the operating system.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword NLSSORT and identify where it is used.
2. Delete NLS_SORT= and change the locale to the locale used by the operating system corresponding to the specified collating sequence.

Migration example

The example below shows migration when the specified locale is used for sorting. Note that the example locale in PostgreSQL would be the value specified for Linux.

 SELECT c_code, c_name 
  FROM company_table 
  ORDER BY NLSSORT( c_name, 
                    'NLS_SORT = xDanish' ); 


 SELECT c_code, c_name 
  FROM company_table 
  ORDER BY NLSSORT( c_name, 
                    'NLS_SORT = JAPANESE_M' );

 SELECT c_code, c_name 
  FROM company_table 
  ORDER BY NLSSORT( c_name, 'danish' ); 



 SELECT c_code, c_name 
  FROM company_table 
  ORDER BY NLSSORT( c_name, 'ja_JP.UTF8' ); 
 

6.2.5.2 Sorting by Character Set

Functional differences

• Oracle database
  • NLS_SORT=BINARY is specified in the locale specification for sorting by character set.
• PostgreSQL
  • C is specified in the locale specification for sorting by character set.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword NLSSORT and identify where it is used.
2. If NLS_SORT=BINARY is specified for the locale, change it to C.

Migration example

The example below shows migration when the character set is used for sorting.

 SELECT c_code, c_name 
  FROM company_table 
  ORDER BY NLSSORT( c_name, 'NLS_SORT = BINARY' );

 SELECT c_code, c_name 
  FROM company_table 
  ORDER BY NLSSORT( c_name, 'C' );

6.2.5.3 Case-Insensitive Sorting

Functional differences

• Oracle database
  • Specifying _CI at the end of the locale sets case-insensitive sorting.
• PostgreSQL
  • _CI cannot be specified at the end of the locale.

Migration procedure

There are no features that perform case-insensitive sorting, so make all characters either uppercase or lowercase before starting sorting so that the same result is returned. Use the following procedure to perform migration:

1. Search for the keyword NLSSORT and identify where it is used.
2. If _CI is specified at the end of the specified locale, put the sort column inside the parentheses of LOWER (or UPPER).

Migration example

The example below shows migration when case-insensitive sorting is used.

 SELECT c_code, c_name 
  FROM company_table 
 ORDER BY NLSSORT( c_name, 
                   'NLS_SORT = JAPANESE_M_CI' );

 SELECT c_code, c_name 
  FROM company_table 
  ORDER BY NLSSORT( LOWER( c_name ), 
                    'ja_JP.UTF8' ); 
 

6.2.6 SUBSTRC, SUBSTR2, and SUBSTR4

Description

SUBSTRC, SUBSTR2, and SUBSTR4 extract part of a string in the character unit of the relevant encoding.

Functional differences

• Oracle database
  • SUBSTRC, SUBSTR2, and SUBSTR4 extract part of a string in the character unit of the relevant encoding.
• PostgreSQL
  • There are no SUBSTRC, SUBSTR2, and SUBSTR4 functions. Only Unicode encoding is used in PostgreSQL.

Migration procedure

Use the following procedure to migrate to SUBSTR:

1. Search for the keywords SUBSTRC, SUBSTR2, and SUBSTR4, and identify where they are used.
2. Change those keywords to SUBSTR.

Migration example

The example below shows migration when part of a string is extracted in the character unit of the relevant encoding.

 SELECT SUBSTRC( c_telephone, 5, 8 ) 
  FROM company_table; 


 SELECT SUBSTR2( c_telephone, 5, 8 ) 
 FROM company_table; 


 SELECT SUBSTR4( c_telephone, 5, 8 ) 
  FROM company_table;

 SELECT SUBSTR( c_telephone, 5, 8 ) 
  FROM company_table; 

6.2.7 SUBSTRB

Description

SUBSTRB extracts part of a string in bytes.

6.2.7.1 Specifying Zero as the Start Position

Functional differences

• Oracle database
  • If 0 is specified as the start position, the part of the string is extracted from the first byte.
• PostgreSQL
  • If 0 is specified as the start position, extraction starts at the position found by subtracting 1 from the start position and shifting by that number of positions to the left.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword SUBSTRB and identify where it is used.
2. If 0 is specified as the start position, change it to 1.

Migration example

The example below shows migration when 0 is specified as the start position for SUBSTRB.

 SELECT SUBSTRB( c_telephone, 0, 7 ) || '-xxxx' 
  FROM company_table;

 SELECT SUBSTRB( c_telephone, 1, 7 ) || '-xxxx' 
  FROM company_table;

6.2.7.2 Specifying a Negative Value as the Start Position

Functional differences

• Oracle database
  • If a negative value is specified as the start position, extraction starts at the position found by counting by that number of bytes after the end of the string.
• PostgreSQL
  • If a negative value is specified as the start position, extraction starts at the position found by subtracting 1 from the start position and shifting by that number of positions to the left.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword SUBSTRB and identify where it is used.
2. If a negative value is specified as the start position, add (OCTET_LENGTH(firstArgumentOfSubstrb)+1) before the negative value of the start position parameter.

Migration example

The example below shows migration when a negative value is specified as the start position for SUBSTRB.

 SELECT 'xxx-' || 
        SUBSTRB( c_telephone, -8, 3 ) || 
        '-xxxx' 
 FROM company_table; 

SELECT 'xxx-' || 
        SUBSTRB( c_telephone, 
                 ( OCTET_LENGTH( c_telephone )  
                    +1 ) -8, 
                   3 ) || 
        '-xxxx' 
  FROM company_table;

6.2.7.3 Specifying a Value Less Than One as the String Length

Functional differences

• Oracle database
  • If a value less than 1 is specified as the string length, NULL is returned.
• PostgreSQL
  • If the string length is 0, a null character is returned. A negative value cannot be specified as a string length.

Migration procedure

Use the following procedure to perform migration. Note that the final step depends on whether NULL or a null character is expected as the return value.

• When expecting NULL as the return value
  1. Search for the keyword SUBSTRB and identify where it is used.
  2. Confirm that a value less than 1 is specified in the string length parameter.
  3. Change the string length to NULL.
• When expecting a null character as the return value
  1. Search for the keyword SUBSTRB and identify where it is used.
  2. Confirm that a value less than 1 is specified in the string length parameter.
  3. If a value less than 0 is specified as the string length, change it to 0.

Migration example

The example below shows migration when a value less than 1 is specified as the string length in SUBSTRB. In this example, NULL is expected as the return value.

 SELECT SUBSTRB( c_telephone, 1, -1 ) 
  FROM company_table;

 SELECT SUBSTRB( c_telephone, 1, NULL ) 
  FROM company_table;

6.2.8 TO_CHAR and TO_DATE

Description

TO_CHAR and TO_DATE convert the specified value in accordance with the format.

6.2.8.1 When Only Part of the TO_DATE Datetime Format is Specified

Functional differences

• Oracle database
  • If only part of the TO_DATE datetime format is specified, the omitted portion is set automatically, with the year set to the current year, the month set to the current month, the day set to 1, and the hour, minute, and second set to 0.
• PostgreSQL
  • If only part of the TO_DATE datetime format is specified, the omitted portion is set automatically, with the year, month, and day set to 1, and the hour, minute, and second set to 0.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword TO_DATE and confirm that the year or month is not specified in the datetime format.
2. Use DATE_TRANC to find the year. If the year is omitted, specify SYSDATE to obtain the current year.
3. Multiply the result of DATE_PART by one month indicated in the INTERVAL type to find the month. If the month is omitted, specify SYSDATE to obtain the current month.
4. Add the results found in steps 2 and 3.

Migration example

The example below shows migration when only part of the TO_DATE datetime format is specified.

 SELECT TO_DATE( '04', 'MM' ) 
  FROM DUAL;




 SELECT TO_DATE( '2000', 'YYYY' )  
  FROM DUAL; 

 SELECT DATE_TRUNC( 'YEAR', SYSDATE() ) 
 + ( DATE_PART( 'MONTH', TO_DATE( '04', 'MM' ) ) - 1 ) 
 * INTERVAL '1 MONTH' 
 FROM DUAL; 


 SELECT DATE_TRUNC( 'YEAR', TO_DATE( '2000', 'YYYY' ) ) 
 + ( DATE_PART( 'MONTH', SYSDATE() ) - 1 ) 
 * INTERVAL '1 MONTH' 
 FROM DUAL;

6.2.8.2 Omitting the Data Type Format

Functional differences

• Oracle database
  • If the data type format (datetime format) is omitted from TO_DATE or TO_CHAR, the values are converted in accordance with NLS_DATE_FORMAT.
    Statements such as ALTER SESSION can be used to change NLS_DATE_FORMAT.
• PostgreSQL
  • If the data type format (datetime format) is omitted from TO_DATE or TO_CHAR, the values are converted in accordance with oracle.nls_date_format.
    Statements such as SET can be used to change oracle.nls_date_format.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keywords TO_DATE and TO_CHAR, and check where the data type format (datetime format) is omitted.
2. Check the settings of the NLS_DATE_FORMAT parameter.
3. In oracle.nls_date_format, specify the datetime format specified in the NLS_DATE_FORMAT parameter.

Migration example

The example below shows migration when the date format is specified in the ALTER SESSION statement.

 ALTER SESSION 
  SET NLS_DATE_FORMAT = "yyyy/mm/dd hh24:mi:ss"; 
 SELECT o_code, TO_CHAR( SYSDATE ) 
  FROM ordering_table; 
  

 SET orafce.nls_date_format =  
     'yyyy/mm/dd hh24:mi:ss'; 
 SELECT o_code, 
        TO_CHAR( SYSDATE() ) 
  FROM ordering_table; 
 

See

The scope of supported datetime formats differs between Oracle databases and PostgreSQL. Refer to "Formats" for information on the differences in the supported datetime formats.

6.2.8.3 Setting a Data Type Format Locale (Setting the Third Argument)

Functional differences

• Oracle database
  • The third argument (data type format locale setting) can be specified.
• PostgreSQL
  • The third argument (data type format locale setting) cannot be specified.

Migration procedure

The locale cannot be specified in the data type format, so change the server parameters so that the same result is returned. Use the following procedure to perform migration:

1. Search for the keywords TO_CHAR and TO_DATE, and identify where they are used.
2. If the third argument is specified, use a SET statement to specify the corresponding server parameter to match the string format locale to be converted. The table below shows the correspondence between the parameters for setting a data type format locale and the server parameters.
3. Delete the third argument specified in TO_CHAR and TO_DATE.

Correspondence between the parameters for setting a data type format locale and the server parameters

|Data type format|Parameter for setting data type format locale
(Oracle database)|Server parameter
(PostgreSQL)| |:---|:---|:---| |Number format|NLS_NUMERIC_CHARACTERS|LC_NUMERIC (*1)| |Number format|NLS_CURRENCY|LC_MONETARY (*1)| |Number format|NLS_ISO_CURRENCY|- (Cannot be migrated because there is no corresponding parameter)| |Datetime format|NLS_DATE_LANGUAGE|LC_TIME (*2)(*3)(*4)|

*1: In Oracle databases, the corresponding string is specified directly, but in PostgreSQL, the locale is specified. The string that is set is the value predetermined for each locale.

*2: When a string that is dependent on the specified locale is to be found, the prefix TM must be added at the beginning of the date format. If the TM prefix is not specified, an English-language string will be returned.

*3: When a string that is dependent on a Japanese-language or other character set is to be found, the string including the encoding must be specified. (Example: SET LC_TIME='ja_JP.UTF-8')

*4: Migration is possible only if TO_CHAR is used to find a string from a date. If TO_DATE is used, a locale-dependent string cannot be used as input.

Migration example

The example below shows migration when the data type format locale is set (in the third argument).

 SELECT o_code, 
        TO_CHAR( o_price * o_quantity / 1.2, 
                 'l999g999g999d00', 
                 'NLS_NUMERIC_CHARACTERS = '',.'' 
                 NLS_CURRENCY = ''EUR'' ' ) "MONEY" 
   FROM ordering_table;

 SET LC_MONETARY='de_DE'; 
 SET LC_NUMERIC='de_DE'; 
 SELECT o_code, 
        TO_CHAR( o_price * o_quantity / 1.2, 
                 'l999g999g999d00' ) "MONEY" 
  FROM ordering_table;

Information

If the data type format matches the client locale, simply delete the third argument of TO_CHAR.

See

The values that can be specified in the server parameters depend on the locale of the operating system on the client. Refer to the PostgreSQL Documentation for details.

6.2.9 Functions Requiring Parentheses

Some functions added by orafce do not have arguments. Parentheses must be added to these functions when they are called. The functions to which parentheses must be added are listed below. Functions requiring parentheses:

• SYSDATE
• SESSIONTIMEZONE
• DBTIMEZONE

Migration example

The example below shows migration when a function that has no arguments is called.

 SELECT SYSDATE FROM DUAL;

 SELECT SYSDATE() FROM DUAL;

6.3 Standard Packages

This section explains how to migrate the standard packages added by orafce.

6.3.1 DBMS_ALERT

Description

The DBMS_ALERT package sends alerts from a PL/pgSQL execution session to multiple other PL/pgSQL execution sessions.

6.3.1.1 Set Value of DBMS_ALERT.REGISTER

Functional differences

• Oracle database
  • The second argument of DBMS_ALERT.REGISTER can be specified. The second argument specifies whether to perform a cleanup of the pipe to be used.
    The default is TRUE, which causes a cleanup to be performed.
• PostgreSQL
  • The second argument cannot be specified.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword DBMS_ALERT.REGISTER and identify where it is used.
2. If the second argument is specified, delete it.

Migration example

The example below shows migration when the second argument is specified in DBMS_ALERT.REGISTER.

 DBMS_ALERT.REGISTER( 'SAMPLEALERT', TRUE );

 PERFORM DBMS_ALERT.REGISTER( 'SAMPLEALERT' );

6.3.1.2 Case Sensitivity of Alert Names

Functional differences

• Oracle database
  • Alert names are case-insensitive.
• PostgreSQL
  • Alert names are case-sensitive.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keywords DBMS_ALERT.REGISTER, DBMS_ALERT.SIGNAL, DBMS_ALERT.WAITONE, and DBMS_ALERT.REMOVE, and identify where they are used.
2. If there are alert names in different cases (uppercase and lowercase characters), change them to the same case.

Migration example

The example below shows migration when there is an alert name in uppercase characters and an alert name in lowercase characters. In this example, the alert names are aligned in uppercase.

 DBMS_ALERT.REGISTER( 'SAMPLEALERT', TRUE ); 
 ~ 
 DBMS_ALERT.SIGNAL( 'samplealert', 
                    'TEST MESSAGE 1' );

 PERFORM DBMS_ALERT.REGISTER( 'SAMPLEALERT' ); 
 ~ 
 PERFORM DBMS_ALERT.SIGNAL( 'SAMPLEALERT', 
                            'TEST MESSAGE 1' );

6.3.1.3 Other Notes on Using DBMS_ALERT

This section explains the functional differences to be noted when DBMS_ALERT is used. Note that PL/pgSQL features cannot migrate these functional differences. Consider, for example, changing the application logic.

6.3.1.3.1 Executing DBMS_ALERT.SIGNAL from Multiple PL/pgSQL Sessions

Functional differences

• Oracle database
  • DBMS_ALERT.SIGNAL is serialized according to the execution sequence.
    Therefore, when DBMS_ALERT.SIGNAL is sent from multiple PL/SQL execution sessions to the same alert,
    each DBMS_ALERT.SIGNAL remains in wait state until the preceding DBMS_ALERT.SIGNAL is committed.
• PostgreSQL
  • DBMS_ALERT.SIGNAL is not serialized according to the execution sequence.
    Therefore, even if the preceding DBMS_ALERT.SIGNAL is not yet committed,
    the following DBMS_ALERT.SIGNAL does not enter wait state and the alert that is committed first is reported.

6.3.1.3.2 Message Received when Alert is Reported Multiple Times

Functional differences

• Oracle database
  • If multiple DBMS_ALERT.SIGNAL procedures are executed between the time that DBMS_ALERT.REGISTER is executed and DBMS_ALERT.WAITANY/WAITONE is executed, the message from the DBMS_ALERT.SIGNAL executed last is received. All earlier alert messages are discarded.
• PostgreSQL
  • If multiple DBMS_ALERT.SIGNAL procedures are executed between the time that DBMS_ALERT.REGISTER is executed and DBMS_ALERT.WAITANY/WAITONE is executed, the message from the DBMS_ALERT.SIGNAL executed first is received. Subsequent alert messages are not discarded but retained.

Note

If alerts with the same name are used in multiple sessions, ensure that all alert messages are received or delete alerts from the PL/pgSQL sessions by using DBMS_ALERT.REMOVE/REMOVEALL at the point where alerts no longer need to be received. If alerts remain when the session is closed, other sessions may no longer be able to receive alerts properly.

6.3.1.4 Example of Migrating DBMS_ALERT

The example below shows migration to PL/pgSQL when DBMS_ALERT is used.

(Receiving side) 
 BEGIN 
  DBMS_ALERT.REGISTER( 'SAMPLEALERT', TRUE ); 
 END; 
 / 



 ------------------------------------------------- 
 (Sending side) 


 BEGIN 
   DBMS_ALERT.SIGNAL( 'samplealert', 
                      'TEST MESSAGE 1' ); 
   COMMIT; 
   DBMS_ALERT.SIGNAL( 'samplealert', 
                      'TEST MESSAGE 2' ); 
   COMMIT; 
 END; 
 / 
 ------------------------------------------------- 
 (Receiving side) 
 SET SERVEROUTPUT ON 
 DECLARE 
  alname VARCHAR2(100) := 'SAMPLEALERT'; 
  almess VARCHAR2(1000); 
  alst   NUMBER; 
 BEGIN 
  DBMS_ALERT.WAITONE( alname, almess, alst, 60 ); 
  DBMS_OUTPUT.PUT_LINE( alname ); 
  DBMS_OUTPUT.PUT_LINE( almess ); 
  DBMS_OUTPUT.PUT_LINE( 'alst =' || alst ); 
  DBMS_ALERT.REMOVE( alname ); 
 END; 
 / 

 (Receiving side) 
 DO $$ 
 BEGIN 
  PERFORM DBMS_ALERT.REGISTER( 'SAMPLEALERT' ); 
 END; 
 $$ 
 ; 
 ------------------------------------------------- 
 (Sending side) 
 DO $$ 
 BEGIN 
  PERFORM DBMS_ALERT.SIGNAL( 'SAMPLEALERT', 
                             'TEST MESSAGE 1' ); 
  PERFORM DBMS_ALERT.SIGNAL( 'SAMPLEALERT', 
                             'TEST MESSAGE 2' ); 
 END; 
 $$ 
 ; 


 ------------------------------------------------- 
 (Receiving side) 
 DO $$ 
 DECLARE 
  alname VARCHAR2(100) := 'SAMPLEALERT'; 
  almess VARCHAR2(1000); 
  alst   int; 
 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE ); 
  SELECT message, status INTO almess, alst 
   FROM DBMS_ALERT.WAITONE( alname, 60 ); 
  PERFORM DBMS_OUTPUT.PUT_LINE( alname ); 
  PERFORM DBMS_OUTPUT.PUT_LINE( almess ); 
  PERFORM DBMS_OUTPUT.PUT_LINE( 'alst =' || alst ); 
  PERFORM DBMS_ALERT.REMOVE( alname ); 
 END; 
 $$ 
 ; 
 

6.3.2 DBMS_ASSERT

Description

The DBMS_ASSERT package checks and normalizes SQL syntax elements.

6.3.2.1 DBMS_ASSERT.ENQUOTE_LITERAL

Functional differences

• Oracle database
  • If a string in an argument is already enclosed in single quotation marks, it is not again enclosed in single quotation marks.
• PostgreSQL
  • Even if a string in an argument is already enclosed in single quotation marks, it is again enclosed in single quotation marks.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword DBMS_ASSERT.ENQUOTE_LITERAL and identify where it is used.
2. In the conditions of an IF statement, use LEFT and RIGHT to check the leading and trailing characters.
3. If each result does not match a single quotation mark (E'\x27'), use ENQUOTE_LITERAL to replace it.

Migration example

The example below shows migration when a string is enclosed in single quotation marks.

 DBMS_OUTPUT.PUT_LINE( 
  DBMS_ASSERT.ENQUOTE_LITERAL( en_lit ) ); 

 IF ( LEFT( en_lit, 1 ) = E'\x27' AND 
      RIGHT( en_lit, 1 ) = E'\x27' ) THEN 
   PERFORM DBMS_OUTPUT.PUT_LINE( en_lit ); 
 ELSE 
  PERFORM DBMS_OUTPUT.PUT_LINE( 
   DBMS_ASSERT.ENQUOTE_LITERAL( en_lit ) ); 
 END IF;

Note

PostgreSQL does not verify single quotation marks.

6.3.2.2 DBMS_ASSERT.ENQUOTE_NAME

Functional differences

• Oracle database
  • If the string in the first argument is already enclosed in double quotation marks, it is not again enclosed in double quotation marks.
    In addition, regardless of whether there is a second argument, a string enclosed in double quotation marks is not converted from lowercase to uppercase.
• PostgreSQL
  • Even if the string in the first argument is already enclosed in double quotation marks, it is again enclosed in double quotation marks.
    However, a first argument string that is all in lowercase is not enclosed in double quotation marks.
    In addition, if the second argument is set to TRUE or the default, it is converted from uppercase to lowercase even if it is enclosed in double quotation marks.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword DBMS_ASSERT.ENQUOTE_NAME and identify where it is used.
2. In the conditions of an IF statement, use LEFT and RIGHT to check the leading and trailing characters.
3. If each result does not match a double quotation mark (E'\x27'), use ENQUOTE_NAME to replace it.

Migration example

The example below shows migration when a string is enclosed in double quotation marks.

 DBMS_OUTPUT.PUT_LINE( 
  DBMS_ASSERT.ENQUOTE_NAME( en_nam ) ); 

 IF ( LEFT( en_nam, 1 ) = E'\x22' AND 
      RIGHT( en_nam, 1 ) = E'\x22' ) THEN 
   PERFORM DBMS_OUTPUT.PUT_LINE( en_nam ); 
 ELSE 
  PERFORM DBMS_OUTPUT.PUT_LINE( 
   DBMS_ASSERT.ENQUOTE_NAME( en_nam ) ); 
 END IF;

6.3.2.3 DBMS_ASSERT.SIMPLE_SQL_NAME

Functional differences

• Oracle database
  • If the leading or trailing position of a string in an argument contains a space, the space is deleted before the string is evaluated.
• PostgreSQL
  • If the leading or trailing position of a string in an argument contains a space, the string is evaluated as is, causing an error.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword DBMS_ASSERT.SIMPLE_SQL_NAME and identify where it is used.
2. If the leading or trailing position of a string in an argument contains a space, use TRIM to delete the space immediately preceding or following the argument string.

Migration example

The example below shows migration when the leading or trailing position of a string in an argument contains a space.

DBMS_OUTPUT.PUT_LINE( 
  DBMS_ASSERT.SIMPLE_SQL_NAME( si_nam ) ); 
  

 PERFORM DBMS_OUTPUT.PUT_LINE( 
  DBMS_ASSERT.SIMPLE_SQL_NAME( 
   TRIM( both from si_nam ) ) );

See

The strings checked by DBMS_ASSERT.SIMPLE_SQL_NAME correspond to identifiers among the SQL elements. Refer to "The SQL Language" > "Lexical Structure" > "Identifiers and Key Words" in the PostgreSQL Documentation for information on the values that can be used as identifiers in PostgreSQL.

6.3.2.4 DBMS_ASSERT.SQL_OBJECT_NAME

Functional differences

• Oracle database
  • DBMS_ASSERT.SQL_OBJECT_NAME exists.
• PostgreSQL
  • DBMS_ASSERT.SQL_OBJECT_NAME does not exist. Use DBMS_ASSERT.OBJECT_NAME instead.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword DBMS_ASSERT.SQL_OBJECT_NAME and identify where it is used.
2. Change DBMS_ASSERT.SQL_OBJECT_NAME to DBMS_ASSERT.OBJECT_NAME.

Migration example

The example below shows migration when an input value is verified as a qualified SQL identifier of an existing SQL object.

 SELECT 
   DBMS_ASSERT.SQL_OBJECT_NAME( 'inventory_table' ) 
  INTO table_name 
  FROM DUAL;

 SELECT 
   DBMS_ASSERT.OBJECT_NAME( 'inventory_table' ) 
  INTO table_name 
  FROM DUAL;

6.3.2.5 Example of Migrating DBMS_ASSERT

The example below shows migration to PL/pgSQL when DBMS_ASSERT is used.

 SET SERVEROUTPUT ON 
 DECLARE 
  en_lit VARCHAR2(50) := '''ENQUOTE_LITERAL'''; 
  en_nam VARCHAR2(50) := '"enquote_name"'; 
  si_nam VARCHAR2(50) := ' SIMPLE_SQL_NAME   '; 
  table_name VARCHAR2(20); 
 BEGIN 


  DBMS_OUTPUT.PUT_LINE( 
   DBMS_ASSERT.ENQUOTE_LITERAL( en_lit )); 







  DBMS_OUTPUT.PUT_LINE( 
   DBMS_ASSERT.ENQUOTE_NAME( en_nam )); 







  DBMS_OUTPUT.PUT_LINE( 
   DBMS_ASSERT.SIMPLE_SQL_NAME( si_nam )); 



  SELECT DBMS_ASSERT.SQL_OBJECT_NAME( 
                     'inventory_table' ) 
  INTO table_name 
  FROM DUAL; 
  DBMS_OUTPUT.PUT_LINE( 
              'Object is : ' || table_name ); 
 END; 
 / 
 

 DO $$ 
 DECLARE 
  en_lit VARCHAR2(50) := '''ENQUOTE_LITERAL''';  
  en_nam VARCHAR2(50) := '"enquote_name"';  
  si_nam VARCHAR2(50) := ' SIMPLE_SQL_NAME   ';  
  table_name VARCHAR2(20);  
 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE);  
  IF ( LEFT( en_lit, 1 ) = E'\x27' AND 
       RIGHT( en_lit, 1 ) = E'\x27' ) THEN 
   PERFORM DBMS_OUTPUT.PUT_LINE( en_lit );  
  ELSE 
   PERFORM DBMS_OUTPUT.PUT_LINE( 
    DBMS_ASSERT.ENQUOTE_LITERAL( en_lit )); 
  END IF; 

IF ( LEFT( en_nam, 1 ) = E'\x22' AND 
       RIGHT( en_nam, 1 ) = E'\x22' ) THEN 
   PERFORM DBMS_OUTPUT.PUT_LINE( en_nam ); 
  ELSE 
   PERFORM DBMS_OUTPUT.PUT_LINE( 
    DBMS_ASSERT.ENQUOTE_NAME( en_nam ) ); 
  END IF;  


  PERFORM DBMS_OUTPUT.PUT_LINE( 
   DBMS_ASSERT.SIMPLE_SQL_NAME( 
                       TRIM( both from si_nam ) ) ); 


  SELECT DBMS_ASSERT.OBJECT_NAME( 
                     'inventory_table' ) 
   INTO table_name 
   FROM DUAL; 
  PERFORM DBMS_OUTPUT.PUT_LINE( 
                      'Object is : ' || table_name ); 
 END; 
 $$ 
 ;  

6.3.3 DBMS_OUTPUT

Description

The DBMS_OUTPUT package sends messages from PL/pgSQL to clients such as psql.

6.3.3.1 Differences in the Timing of Output Immediately After DBMS_OUTPUT.SERVEROUTPUT Changes from OFF to ON

Functional differences

• Oracle database
  • Messages stored in the buffer while SERVEROUTPUT is OFF are displayed after the execution of the first SQL statement or anonymous PL/SQL after SERVEROUTPUT changes to ON.
• PostgreSQL
  • Messages stored in the buffer while SERVEROUTPUT is FALSE are not displayed even after the execution of the first SQL statement or anonymous block after SERVEROUTPUT changes to TRUE. DBMS_OUT.NEW_LINE must be executed.

Migration procedure

Use the following procedure to perform migration: 1. Search for the keyword SERVEROUTPUT and identify where it changes from OFF to ON. 2. Change the code so that DBMS_OUT.NEW_LINE is executed immediately after the SQL statement or anonymous block that is executed after the SERVEROUTPUT statement is changed to ON.

Migration example

The example below shows migration when the status of SERVEROUTPUT changes.

 SET SERVEROUTPUT OFF; 






 ... 

SET SERVEROUTPUT ON; 
 SELECT * FROM dual; 

 DO $$ 
 BEGIN 
 PERFORM DBMS_OUTPUT.SERVEROUTPUT( FALSE ); 
 END; 
 $$ 
 ; 
 ... 


 SELECT * FROM dual; 
 DO $$ 
 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE );  
  PERFORM DBMS_OUTPUT.NEW_LINE(); 
 END; 
 $$ 
 ; 
 

6.3.3.2 Other Notes on Using DBMS_OUTPUT

This section explains the functional differences to be noted when DBMS_OUTPUT is used. Note that PL/pgSQL features cannot migrate these functional differences. Consider, for example, changing the application logic.

6.3.3.2.1 Differences in the Output Timing of DBMS_OUTPUT.PUT_LINE and DBMS_OUTPUT.NEW_LINE

Functional differences

• Oracle database
  • When SERVEROUTPUT is ON, the outputs of DBMS_OUTPUT.PUT_LINE and DBMS_OUTPUT.NEW_LINE are displayed together after the procedure finishes.
    These outputs are stored in the buffer of the server while the procedure is running.
• PostgreSQL
  • When SERVEROUTPUT is TRUE, the outputs from executing DBMS_OUTPUT.PUT_LINE and DBMS_OUTPUT.NEW_LINE are sent to the client and displayed immediately.
    They are not stored in the buffer of the server.

6.3.3.3 Example of Migrating DBMS_OUTPUT

The example below shows migration to PL/pgSQL when DBMS_OUTPUT is used.

 SET SERVEROUTPUT OFF; 
 BEGIN 


  DBMS_OUTPUT.ENABLE( NULL ); 
  DBMS_OUTPUT.PUT_LINE( '1:Hello World' ); 
 END; 
 / 

SET SERVEROUTPUT ON 
 SELECT * FROM dual; 

 DO $$ 
 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT( FALSE ); 
  PERFORM DBMS_OUTPUT.ENABLE( NULL ); 
  PERFORM DBMS_OUTPUT.PUT_LINE( '1:Hello World' ); 
 END; 
 $$ 
 ; 
 SELECT * FROM dual; 
 DO $$ 
  BEGIN 
   PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE ); 
   PERFORM DBMS_OUTPUT.NEW_LINE(); 
 END; 
 $$ 
 ; 

6.3.4 DBMS_PIPE

Description

The DBMS_PIPE package performs one-to-one communication between PL/pgSQL sessions.

6.3.4.1 Differences from the DBMS_PIPE.CREATE_PIPE Definition

Functional differences

• Oracle database
  • The second argument specifies the maximum size of the pipe in bytes. The default is 8192 bytes.
    The third argument specifies the pipe type. The default is TRUE (private pipe).
• PostgreSQL
  • The second argument specifies the maximum number of messages that the pipe can hold. The default is 0. The specifiable range of numeric values is 1 to 32767.
    The third argument specifies the pipe type. The default is FALSE (public pipe).

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword DBMS_PIPE.CREATE_PIPE and identify where it is used.
2. Change the code so that the maximum number of messages is specified in the second argument.
3. If the third argument is omitted and a private pipe is to be created, specify TRUE in the third argument.

Note

Preferably, create a public pipe (the default) as the pipe type. If you create a private pipe, internal information (the creator of the private pipe) will remain even after the pipe is removed. Thus repeatedly creating and removing pipes may ultimately cause memory to run out.

Migration example

The example below shows migration of DBMS_PIPE.CREATE_PIPE.

 DBMS_PIPE.CREATE_PIPE( 
           'pipename', 
           2000,  
           TRUE );

 DBMS_PIPE.CREATE_PIPE( 
           'pipename', 
           50, 
           TRUE );

6.3.4.2 Return Values of DBMS_PIPE.CREATE_PIPE and DBMS_PIPE.REMOVE_PIPE

Functional differences

• Oracle database
  • DBMS_PIPE.CREATE_PIPE and DBMS_PIPE.REMOVE_PIPE both return values.
• PostgreSQL
  • DBMS_PIPE.CREATE_PIPE and DBMS_PIPE.REMOVE_PIPE both do not return values.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keywords DBMS_PIPE.CREATE_PIPE and DBMS_PIPE.REMOVE_PIPE, and identify where they are used.
2. Change the code so that the call processing identified in step 1 is called by the PERFORM keyword.
3. If return values are used, replace the target processing with 0.

Migration example

The example below shows migration of DBMS_PIPE.CREATE_PIPE.

 st := DBMS_PIPE.CREATE_PIPE( pipename, 2000 ); 
 DBMS_OUTPUT.PUT_LINE( 'Return Value =' || st ); 

 PERFORM DBMS_PIPE.CREATE_PIPE( pipename, 50 ); 
 st := 0; 
 PERFORM DBMS_OUTPUT.PUT_LINE( 
                  'Return Value =' || st );

6.3.4.3 Creating the Same Pipe Name with DBMS_PIPE.CREATE_PIPE

Functional differences

• Oracle database
  • If a pipe with the same name already exists and can be used, DBMS_PIPE.CREATE_PIPE returns normally.
• PostgreSQL
  • If a pipe with the same name already exists, DBMS_PIPE.CREATE_PIPE returns with an error.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword CREATE_PIPE and identify where it is used.
2. If there may be a pipe with the same name, use the PERFORM statement shown below to check if the same pipe exists.
3. If NOT FOUND returns TRUE, there is no pipe with the same name, so execute CREATE_PIPE.

~~~ PERFORM 1 FROM DBMS_PIPE.DB_PIPES WHERE NAME = nameOfPipeToBeCreated ~~~

Migration example

The example below shows migration of CREATE_PIPE when there may be a pipe with the same name.

  
 DECLARE 
  pipename VARCHAR2(1000) := 'TESTPIPE01'; 
 BEGIN 
  DBMS_OUTPUT.PUT_LINE( 
   'Return = '|| DBMS_PIPE.CREATE_PIPE( 
                           pipename, 
                           2000, 
                           TRUE ) ); 




 END; 
 / 
 

 DO $$ 
 DECLARE 
 pipename VARCHAR2(1000) := 'TESTPIPE01'; 
 BEGIN 
  PERFORM 1 
   FROM DBMS_PIPE.DB_PIPES 
   WHERE NAME = pipename;  
  IF ( NOT FOUND ) THEN 
   PERFORM DBMS_PIPE.CREATE_PIPE( pipename, 
                                  50, 
                                  TRUE ); 
 END IF; 
 END; 
 $$ 
 ;

6.3.4.4 Return Values of DBMS_PIPE.NEXT_ITEM_TYPE

Functional differences

• Oracle database
  • DBMS_PIPE.NEXT_ITEM_TYPE has the following return values:
    0: There is no next item.
    6: NUMBER type
    9: VARCHAR2 type
    11: ROWID type
    12: DATE type
    23: RAW type
• PostgreSQL
  • DBMS_PIPE.NEXT_ITEM_TYPE has the following return values:
    0: There is no next item.
    9: NUMERIC type
    11: TEXT type
    12: DATE type
    13: TIMESTAMP type
    23: BYTEA type
    24: RECORD type

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword NEXT_ITEM_TYPE and identify the variable storing the return value of NEXT_ITEM_TYPE.
2. If the return value of NEXT_ITEM_TYPE is determined, change it to the value in PostgreSQL according to the table below.

Correspondence of return values of DBMS_PIPE.NEXT_ITEM_TYPE

|Oracle database|PostgreSQL| |:---|:---| | NUMBER type | NUMERIC type | | VARCHAR2 type | TEXT type | | ROWID type | | | | DATE type | | DATE type | TIMESTAMP type | | RAW type | BYTEA type | | | RECORD type |

Migration example

The example below shows migration when processing is branched according to the return value of DBMS_PIPE.NEXT_ITEM_TYPE.

 item := DBMS_PIPE.NEXT_ITEM_TYPE; 
 IF ( item = 6 ) THEN  -- NUMBER type 
 ~ 
 ELSIF ( item = 9 ) THEN -- VARCHAR2 type 
 ~ 
 ELSIF ( item = 12 ) THEN  -- DATE type 
 ~

 item := DBMS_PIPE.NEXT_ITEM_TYPE(); 
 IF ( item = 9 ) THEN  -- NUMERIC type 
 ~ 
 ELSIF ( item =11 ) THEN -- TEXT type 
 ~ 
 ELSIF ( item = 13 ) THEN -- TIMESTAMP type 
 ~

6.3.4.5 Data Types That Can be Used in DBMS_PIPE.PACK_MESSAGE and UNPACK_MESSAGE

Functional differences

• Oracle database
  • The data types that can be used are VARCHAR2, NCHAR, NUMBER, DATE, RAW, and ROWID.
    When RAW or ROWID is used, the data type must be specified after UNPACK_MESSAGE.
• PostgreSQL
  • The data types that can be used are TEXT, NUMERIC, INTEGER (Note), BIGINT (Note), DATE, TIMESTAMP, BYTEA, and RECORD.
    All data types require the data type and empty parentheses to be specified after UNPACK_MESSAGE.

Note

• The INTEGER and BIGINT data types can be used with PACK_MESSAGE only.
• The INTEGER and BIGINT types are converted internally to the NUMERIC type. Therefore, use UNPACK_MESSAGE_NUMBER to receive a message.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword UNPACK_MESSAGE and identify where UNPACK_MESSAGE is used.
2. Change the variable specified in the argument to an assignment expression specified on the left-hand side, separately specify each data type after UNPACK_MESSAGE, and delete the variable from the parentheses.

Migration example

The example below shows migration when a message is sent and received.

DBMS_PIPE.UNPACK_MESSAGE( testnum );

 testnum := 
     DBMS_PIPE.UNPACK_MESSAGE_NUMBER();

6.3.4.6 Case Sensitivity of DBMS_PIPE.RECEIVE_MESSAGE and SEND_MESSAGE

Functional differences

• Oracle database
  • Pipe names are case-insensitive.
• PostgreSQL
  • Pipe names are case-sensitive.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keywords RECEIVE_MESSAGE and SEND_MESSAGE, and check the pipe names.
2. If there are pipe names in different cases (uppercase and lowercase characters), change them to the same case.

Migration example

The example below shows migration when uppercase and lowercase characters are used for the pipe names.

 (Sending side) 
 st := DBMS_PIPE.SEND_MESSAGE( 'TESTPIPE01', 
                               10, 
                               8192 ); 
 (Receiving side) 
 st := DBMS_PIPE.RECEIVE_MESSAGE( 'testpipe01' );

 (Sending side) 
 st := DBMS_PIPE.SEND_MESSAGE( 'TESTPIPE01', 
                               10, 
                               100 );  
 (Receiving side) 
 st := DBMS_PIPE.RECEIVE_MESSAGE( 'TESTPIPE01' );

Note

The return values of DBMS_PIPE.RECEIVE_MESSAGE and DBMS_PIPE.SEND_MESSAGE differ as shown below.

• Oracle database
  • There are five return values, as follows:
    0: Completed successfully.
    1: A timeout occurred.
    2: A record in the pipe is too big for the buffer.
    3: An interrupt occurred.
    ORA-23322: The user does not have privileges for reading the pipe.
• PostgreSQL
  • There are two return values, as follows:
    0: Completed successfully.
    1: A timeout occurred.

6.3.4.7 Differences in the DBMS_PIPE.SEND_MESSAGE Feature

Functional differences

• Oracle database
  • The third argument specifies the maximum size of the pipe in bytes. The default is 8192 bytes.
• PostgreSQL
  • The third argument specifies the maximum number of messages that the pipe can hold.
    The specifiable range of numeric values is 1 to 32767.
    Note that if the maximum number of messages is omitted for an implicit pipe, the number is unlimited.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword SEND_MESSAGE and identify where the maximum number of bytes is specified.
2. Replace the maximum number of bytes with the maximum number of messages.

Migration example

The example below shows migration when the maximum pipe size is specified.

 DBMS_PIPE.SEND_MESSAGE( 'testPIPE', 10, 200 );

 DBMS_PIPE.SEND_MESSAGE( 'testPIPE', 10, 10 );

6.3.4.8 Example of Migrating DBMS_PIPE

The example below shows migration when one-to-one communication is performed between PL/pgSQL sessions.

 (Sending side) 
 SET SERVEROUTPUT ON; 
 DECLARE 
  testnum NUMBER := 111; 
  testvchar2 VARCHAR2(100) := 'Test Message'; 
  testdate DATE := SYSDATE; 
  testraw RAW(100) := '0101010101'; 
  st INT; 
  pipename VARCHAR2(1000) := 'TESTPIPE01'; 
 BEGIN 

st := DBMS_PIPE.CREATE_PIPE( pipename, 2000 ); 






  DBMS_OUTPUT.PUT_LINE( 'Return Value =' || st ); 



  DBMS_PIPE.PACK_MESSAGE( testnum ); 
  DBMS_PIPE.PACK_MESSAGE( testvchar2 ); 
  DBMS_PIPE.PACK_MESSAGE( testdate ); 
  DBMS_PIPE.PACK_MESSAGE_RAW( testraw ); 
  st := DBMS_PIPE.SEND_MESSAGE( 'TESTPIPE01', 
                                10, 
                                200 ); 
   DBMS_OUTPUT.PUT_LINE( 'Return Value =' || st ); 


 END; 
 / 


 ------------------------------------------------- 
 (Receiving side) 
 SET SERVEROUTPUT ON; 
 DECLARE 
   testnum NUMBER; 
   testvchar2 VARCHAR2(100); 
   testdate DATE; 
   testraw RAW(100); 
   item NUMBER; 
   st INT; 
 BEGIN 


  st := DBMS_PIPE.RECEIVE_MESSAGE( 'testpipe01' ); 
  DBMS_OUTPUT.PUT_LINE( 'Return Value =' || st ); 


  LOOP 
   item := DBMS_PIPE.NEXT_ITEM_TYPE; 
   DBMS_OUTPUT.PUT_LINE( 'Next Item : ' || item ); 


   IF ( item = 6 ) THEN 
    DBMS_PIPE.UNPACK_MESSAGE( testnum ); 


   DBMS_OUTPUT.PUT_LINE( 
    'Get Message : ' || testnum ); 
   ELSIF ( item = 9 ) THEN 
    DBMS_PIPE.UNPACK_MESSAGE( testvchar2 ); 


    DBMS_OUTPUT.PUT_LINE( 
                'Get Message : ' || testvchar2 ); 
   ELSIF ( item = 12 ) THEN 
    DBMS_PIPE.UNPACK_MESSAGE( testdate ); 


    DBMS_OUTPUT.PUT_LINE( 
                'Get Message : ' || testdate ); 
   ELSIF ( item = 23 ) THEN 
    DBMS_PIPE.UNPACK_MESSAGE_RAW( testraw ); 




    DBMS_OUTPUT.PUT_LINE( 
                'Get Message : ' || testraw ); 
   ELSE 
    EXIT; 
   END IF; 
  END LOOP; 
  st := DBMS_PIPE.REMOVE_PIPE( 'testpipe01' ); 


  DBMS_OUTPUT.PUT_LINE( 'Return Value =' || st ); 


 END; 
 / 
 

 (Sending side) 
 DO $$ 
 DECLARE 
  testnum NUMERIC := 111; 
  testtext VARCHAR2(100) := 'Test Message'; 
  testtime TIMESTAMP := current_timestamp; 
  testbytea BYTEA := '0101010101'; 
  st INT; 
  pipename VARCHAR2(1000) := 'TESTPIPE01'; 
 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE ); 
  PERFORM 1 
   FROM DBMS_PIPE.DB_PIPES 
    WHERE NAME = pipename;  
  IF ( NOT FOUND ) THEN 
   PERFORM DBMS_PIPE.CREATE_PIPE( pipename,50 ); 
   st := 0; 
   PERFORM DBMS_OUTPUT.PUT_LINE( 
                    'Return Value =' || st ); 
  END IF; 
  PERFORM DBMS_PIPE.PACK_MESSAGE( testnum ); 
  PERFORM DBMS_PIPE.PACK_MESSAGE( testtext ); 
  PERFORM DBMS_PIPE.PACK_MESSAGE( testtime ); 
  PERFORM DBMS_PIPE.PACK_MESSAGE( testbytea ); 
  st := DBMS_PIPE.SEND_MESSAGE( 'TESTPIPE01', 
                                10, 
                                10 );  
  PERFORM DBMS_OUTPUT.PUT_LINE( 
  'Return Value =' || st ); 
 END; 
 $$ 
 ; 
 ------------------------------------------------- 
 (Receiving side) 
 DO $$ 
 DECLARE 
  testnum NUMERIC; 
  testtext VARCHAR2(100); 
  testtime TIMESTAMP; 
  testbytea BYTEA; 
  item INT; 
  st INT; 
 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE ); 
  st := DBMS_PIPE.RECEIVE_MESSAGE( 'TESTPIPE01' ); 
  PERFORM DBMS_OUTPUT.PUT_LINE( 
                      'Return Value ='|| st ); 
  LOOP 
   item := DBMS_PIPE.NEXT_ITEM_TYPE(); 
   PERFORM DBMS_OUTPUT.PUT_LINE( 
                       'Next Item : ' || item ); 
   IF ( item = 9 ) THEN 
    testnum := 
     DBMS_PIPE.UNPACK_MESSAGE_NUMBER(); 
    PERFORM DBMS_OUTPUT.PUT_LINE( 
                        'Get Message : ' || testnum ); 
   ELSIF ( item =11 ) THEN 
    testtext := 
     DBMS_PIPE.UNPACK_MESSAGE_TEXT(); 
    PERFORM DBMS_OUTPUT.PUT_LINE( 
                        'Get Message : ' || testtext ); 
   ELSIF ( item = 13 ) THEN 
    testtime := 
     DBMS_PIPE.UNPACK_MESSAGE_TIMESTAMP(); 
    PERFORM DBMS_OUTPUT.PUT_LINE( 
                        'Get Message : ' || testtime ); 
   ELSIF ( item = 23 ) THEN 
    testbytea := 
     DBMS_PIPE.UNPACK_MESSAGE_BYTEA(); 
    testtext := CAST( testbytea 
     AS varchar2(100) ); 
        PERFORM DBMS_OUTPUT.PUT_LINE( 
                            'Get Message : ' || testtext ); 
   ELSE 
    EXIT; 
   END IF; 
  END LOOP; 
  PERFORM DBMS_PIPE.REMOVE_PIPE( 'TESTPIPE01' ); 
  st := 0; 
  PERFORM DBMS_OUTPUT.PUT_LINE( 
                      'Return Value ='|| st ); 
 END; 
 $$ 
 ; 
 

6.3.5 UTL_FILE

Description

The UTL_FILE package enables PL/pgSQL to read and write text files.

6.3.5.1 Appending a Newline at File Closure

Functional differences

• Oracle database
  • If data in which no newline is specified remains in the buffer, a newline is appended after the data is output and then the file is closed.
• PostgreSQL
  • If data in which no newline is specified remains in the buffer, the data is output and then the file is closed. A newline is not appended.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keywords UTL_FILE.FCLOSE and UTL_FILE.FCLOSE_ALL, and identify where they are used.
2. If UTL_FILE.PUT is executed and no newline is specified during write processing before the file is closed, change the code so that UTL_FILE.NEW_LINE is executed before the file is closed.

Migration example

The example below shows migration when a file that does not end with a newline is closed.

 UTL_FILE.PUT(v_handle, buff); 
 UTL_FILE.FCLOSE(v_handle); 
 

 PERFORM UTL_FILE.PUT(v_handle, buff); 
 PERFORM UTL_FILE.NEW_LINE(v_handle, 1); 
 s_handle := UTL_FILE.FCLOSE(v_handle);

6.3.5.2 Processing UTL_FILE Exceptions

Functional differences

• Oracle database
  • There are exception definitions for the UTL_FILE package. They can be used for determining exceptions in the EXCEPTION clause.
• PostgreSQL
  • There are no exception definitions for the UTL_FILE package.

Migration procedure

There are no exception definitions for the UTL_FILE package, so if they are used for determining exceptions in the EXCEPTION clause, replace them with PostgreSQL error codes. Use the following procedure to perform migration:

1. Search for the keyword UTL_FILE and check if an EXCEPTION clause is specified in the target PL/SQL.
2. If a UTL_FILE exception is used, replace it with a PostgreSQL error code in accordance with the table below.

Correspondence of UTL_FILE exceptions

|UTL_FILE exception definition
(Oracle database)|Migratability|Corresponding PostgreSQL error code| |:---|:---|:---| |INVALID_PATH|Y|RAISE_EXCEPTION| |INVALID_MODE|Y|RAISE_EXCEPTION| |INVALID_FILEHANDLE|Y|RAISE_EXCEPTION| |INVALID_OPERATION|Y|RAISE_EXCEPTION| |READ_ERROR|N|Not generated| |WRITE_ERROR|Y|RAISE_EXCEPTION| |INTERNAL_ERROR|Y|INTERNAL_ERROR| |CHARSETMISMATCH|N|Not generated| |FILE_OPEN|N|Not generated| |INVALID_MAXLINESIZE|Y|RAISE_EXCEPTION| |INVALID_FILENAME|Y|INVALID PARAMETER
NULL VALUE NOT ALLOWED (file name is NULL)| |ACCESS_DENIED|Y|RAISE_EXCEPTION| |INVALID_OFFSET|N|Not generated| |DELETE_FAILED|N|Not generated| |RENAME_FAILED|Y|RAISE_EXCEPTION|

Y: Can be migrated

N: Cannot be migrated

Migration example

The example below shows migration when an error message is displayed during UTL_FILE exception processing.

 EXCEPTION 
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN 
   v_errmsg := SQLERRM; 
   DBMS_OUTPUT.PUT_LINE(v_errmsg); 
 END;

 EXCEPTION 
  WHEN RAISE_EXCEPTION THEN 
   v_errmsg := SQLERRM;  
   PERFORM DBMS_OUTPUT.PUT_LINE(v_errmsg); 
 END;

6.3.5.3 Other Notes on Using UTL_FILE

This section explains the functional differences to be noted when UTL_FILE is used. Note that PL/pgSQL features cannot migrate these functional differences. Consider, for example, changing the application logic.

6.3.5.3.1 Differences in the Open Mode of UTL_FILE.FOPEN

Functional differences

• Oracle database
  • The rb (read byte), wb (write byte), or ab (append byte) open mode can be specified.
• PostgreSQL
  • The rb (read byte), wb (write byte), and ab (append byte) open modes cannot be specified for OPEN_MODE.

6.3.5.3.2 Differences in UTL_FILE.IS_OPEN

Functional differences

• Oracle database
  • Executing UTL_FILE.IS_OPEN after UTL_FILE.FCLOSE_ALL returns TRUE.
• PostgreSQL
  • Executing UTL_FILE.IS_OPEN after UTL_FILE.FCLOSE_ALL returns FALSE.

6.3.5.3.3 Timing of Write by UTL_FILE.FFLUSH

Functional differences

• Oracle database
  • Buffered data up to the newline character is written.
• PostgreSQL
  • All buffered data is written.

6.3.5.4 Example of Migrating UTL_FILE

The example below shows migration to PL/pgSQL when UTL_FILE is used.

 SET SERVEROUTPUT ON 
 DECLARE 
  v_handle   UTL_FILE.FILE_TYPE; 
  v_dirname  VARCHAR2(250); 
  v_filename VARCHAR2(250); 
  v_output   VARCHAR2(250); 
  v_getmsg   VARCHAR2(250); 
  v_errmsg   VARCHAR2(1000); 
  v_opcheck  BOOLEAN; 
 BEGIN 
  v_dirname := '/home/oracle'; 
  v_filename := 'sample.txt'; 
  v_output := 'HELLO WORLD!'; 
  v_handle := UTL_FILE.FOPEN(v_dirname, 
                             v_filename, 
                             'w', 
                             256); 



  UTL_FILE.PUT_LINE(v_handle, v_output); 
  UTL_FILE.FFLUSH(v_handle); 
  UTL_FILE.PUT(v_handle, v_output); 


  UTL_FILE.FCLOSE(v_handle); 
  v_handle := UTL_FILE.FOPEN(v_dirname, 
                             v_filename, 
                             'r', 
                             256); 
  UTL_FILE.GET_LINE(v_handle, v_getmsg); 
  DBMS_OUTPUT.PUT_LINE( 
   'GET_MESSAGE : ' || v_getmsg); 
  UTL_FILE.FCLOSE_ALL; 
  v_opcheck := UTL_FILE.IS_OPEN(v_handle); 
  DBMS_OUTPUT.PUT_LINE(CASE 
   WHEN v_opcheck IS NULL THEN 'UNKNOWN' 
   WHEN v_opcheck THEN 'TRUE' 
   WHEN NOT v_opcheck THEN 'FALSE' 
   END); 


  BEGIN 
   UTL_FILE.PUT_LINE(v_handle, v_output); 
  EXCEPTION 
   WHEN UTL_FILE.INVALID_FILEHANDLE THEN 
    v_errmsg := SQLERRM; 
    DBMS_OUTPUT.PUT_LINE(v_errmsg); 
 END; 


 EXCEPTION WHEN OTHERS THEN 
  UTL_FILE.FCLOSE_ALL; 
  v_errmsg := SQLERRM; 
  DBMS_OUTPUT.PUT_LINE(v_errmsg); 
 END; 
 / 
 

 DO $$ 
 DECLARE 
  v_handle   UTL_FILE.FILE_TYPE; 
  v_dirname  VARCHAR2(250); 
  v_filename VARCHAR2(250); 
  v_output   VARCHAR2(250); 
  v_getmsg   VARCHAR2(250); 
  v_errmsg   VARCHAR2(1000); 
  v_opcheck  BOOLEAN; 
 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE); 
  PERFORM DBMS_OUTPUT.ENABLE(NULL); 
  v_dirname := '/home/pgsql'; 
  v_filename := 'sample.txt'; 
  v_output := 'HELLO WORLD!'; 
  v_handle := UTL_FILE.FOPEN(v_dirname, 
                             v_filename, 
                             'w', 
                             256); 
  PERFORM UTL_FILE.PUT_LINE(v_handle, v_output); 
  PERFORM UTL_FILE.PUT(v_handle, v_output); 
  PERFORM UTL_FILE.FFLUSH(v_handle); 
  PERFORM UTL_FILE.NEW_LINE(v_handle, 1); 
  v_handle := UTL_FILE.FCLOSE(v_handle); 
  v_handle := UTL_FILE.FOPEN(v_dirname, 
                             v_filename, 
                             'r', 
                             256); 
  v_getmsg := UTL_FILE.GET_LINE(v_handle); 
  PERFORM DBMS_OUTPUT.PUT_LINE( 
   'GET_MESSAGE : ' || v_getmsg); 
  PERFORM UTL_FILE.FCLOSE_ALL(); 
  v_opcheck := UTL_FILE.IS_OPEN(v_handle); 
  PERFORM DBMS_OUTPUT.PUT_LINE(CASE 
   WHEN v_opcheck IS NULL THEN 'UNKNOWN' 
   WHEN v_opcheck THEN 'TRUE' 
   WHEN NOT v_opcheck THEN 'FALSE' 
   END); 


  BEGIN 
   PERFORM UTL_FILE.PUT_LINE(v_handle, v_output); 
  EXCEPTION 
   WHEN RAISE_EXCEPTION THEN 
    v_errmsg := SQLERRM; 
    PERFORM DBMS_OUTPUT.PUT_LINE(v_errmsg); 
   END; 


 EXCEPTION WHEN OTHERS THEN 
  PERFORM UTL_FILE.FCLOSE_ALL(); 
  v_errmsg := SQLERRM; 
  PERFORM DBMS_OUTPUT.PUT_LINE(v_errmsg); 
 END; 
 $$ 
 ;