A bulk collect is a method of fetching data when many rows are placed in a collection at once. This can be a problem when migrating procedures from databases that support this mechanism to others that do not. In this case, Oracle supports the bulk collection, but PostgreSQL does not. The Ispirer toolkit allows you to solve this problem and convert procedures that contain BULK COLLECT in several ways. Let's look at these methods using the examples of converting procedures from Oracle to PostgreSQL.

The Ispirer toolkit converts collection type variables either into a temporary tables or into an array. It depends on the option Table_type_conversion in the [POSTGRE] section of the ini file (General information on how to set options: https://wiki.ispirer.com/sqlways/faq/set-options-sqlways-wizard):

[Postgre]
TABLE_TYPE_CONVERSION

It can take the following values:

• TABLE_TYPE_CONVERSION=Tables

• TABLE_TYPE_CONVERSION=Arrays

The default value is Tables. It is used when option is missing. If it is set to Arrays, then variables of collection type are converted into arrays, otherwise into temporary tables. It seems that converting such variables into arrays is more convinient for further work with them. For more information please read the following article. https://wiki.ispirer.com/sqlways/postgresql/techniques/table-type-conv-using_array

In the case when a variable of the collection type becomes a temporary table, the bulk collect is converted into a simple insertion into the created table.

Oracle code:

 CREATE TYPE type_bulk AS object (p1 integer, p2 varchar2(22)); 
 CREATE TYPE type_bulk_tab AS TABLE OF type_bulk; 
 CREATE FUNCTION func_for_bulk(par IN NUMBER) 
 RETURN type_bulk_tab AS RESULTS type_bulk_tab; 
 BEGIN 
     SELECT type_bulk(p1,p2) 
     BULK COLLECT INTO RESULTS 
     FROM (SELECT c1 as p1, c2 as p2 from table_for_bulk);
     RETURN RESULTS; 
 END;

After converting with our tool, we get the following PostgreSQL code:

 CREATE FUNCTION FUNC_FOR_BULK (IN PAR DOUBLE PRECISION) 
 RETURNS TABLE ( SWC_Index INTEGER, p1 INTEGER, p2 VARCHAR(22) ) 
 LANGUAGE plpgsql AS $$ 
 BEGIN 
     create temporary table if not exists SWT_FUNC_FOR_BULK_RESULTS ( SWC_INDEX INTEGER NOT NULL, P1 INTEGER, P2 
 VARCHAR(22) ); 
     DELETE FROM SWT_FUNC_FOR_BULK_RESULTS; 
     INSERT INTO SWT_FUNC_FOR_BULK_RESULTS SELECT row_number() OVER(), TabAl.P1,TabAl.P2 from(SELECT C1 as P1,C2 
 as P2 from TABLE_FOR_BULK) AS TABAL; 
     RETURN QUERY(SELECT SWT_FUNC_FOR_BULK_RESULTS.SWC_INDEX, SWT_FUNC_FOR_BULK_RESULTS.P1, 
 SWT_FUNC_FOR_BULK_RESULTS.P2 FROM SWT_FUNC_FOR_BULK_RESULTS); 
 END; $$;

If there are more than one variable for bulk collection, temporary tables are created for each of them.

Oracle code:

 SELECT id, full_name 
 BULK COLLECT INTO v_id, v_full_name 
 FROM TABLE_FOR_BULK;

PostgreSQL code:

 create temporary table if not exists SWT_PRC_BULK_COLLECT_V_FULL_NAME ( SWC_Index INTEGER NOT NULL, SWC_Value 
 VARCHAR(30) ); 
 DELETE FROM SWT_PRC_BULK_COLLECT_V_FULL_NAME; 
 create temporary table if not exists SWT_PRC_BULK_COLLECT_V_ID ( SWC_Index INTEGER NOT NULL, SWC_Value DOUBLE 
 PRECISION ); 
 DELETE FROM SWT_PRC_BULK_COLLECT_V_ID; 
 
 INSERT INTO SWT_PRC_BULK_COLLECT_V_ID SELECT row_number() OVER(), id FROM TABLE_FOR_BULK; 
 INSERT INTO SWT_PRC_BULK_COLLECT_V_FULL_NAME SELECT row_number() OVER(), full_name FROM TABLE_FOR_BULK;

If a variable of varray_type type is converted into array due to the option Table_type_conversion=Arrays, instead of bulk collection, a new array is created and then assigned to the variable of an array type.

Oracle code:

 CREATE FUNCTION func_with_varray() 
 RETURN varray_type IS 
     Arr varray_type := varray_type(); 
 BEGIN 
     SELECT name 
     BULK COLLECT INTO Arr 
     FROM TABLE_FOR_BULK; 
     RETURN Arr; 
 END;

PostgreSQL code:

 CREATE OR REPLACE FUNCTION FUNC_WITH_VARRAY() 
 RETURNS VARCHAR(60)[] 
 LANGUAGE plpgsql AS $$ 
 DECLARE 
     ARR VARCHAR(60)[] default array[]::VARCHAR(60)[]; 
 BEGIN 
     ARR := ARRAY(SELECT NAME FROM TABLE_FOR_BULK); 
     RETURN ARR; 
 END; $$;

As you can see, the Ispirer toolkit in these cases can convert BULK COLLECT from Oracle into PostgreSQl correctly.

Official User's Guide: Ispirer Toolkit Official Guide

If you have any questions or face any difficulties, please contact our support team: support@ispirer.com