FETCH BULK COLLECT INTO using Postgresql

Question

We are migrating ORacle to Postgresql and for conversion, I used AWS tool and at FETCH BULK COLLECT INTO clause, it's asking us to do perform manual conversion - not sure how does this work in Postgresql as it's very new to me. Can you please help me out ?

Here is the code:

CREATE OR REPLACE PROCEDURE abc_schemaname.USP_Std_SSIS(p_file_id text)
 LANGUAGE plpgsql
AS $procedure$
/*
PROCEDURE abc_schemaname.USP_Std_SSIS

*/
/* Declare Cursor */
DECLARE
    aws_oracle_ext$array_id$temporary BIGINT;
    activity_cur CURSOR FOR
    /* Fetch records from activity dump ssis  Table Based On file_id */
    SELECT
        *
        FROM abc_schemaname.tbl_activity_dump_ssis
        WHERE file_id = p_file_id;
    l_ACTIVITY VARCHAR(100) := 'l_activity';


BEGIN
    /* Open the Cursor. */
    aws_oracle_ext$array_id$temporary := aws_oracle_ext.array$create_array('l_activity', 'abc_schemaname.USP_Std_SSIS');
    PERFORM aws_oracle_ext.array$add_fields_to_array(aws_oracle_ext$array_id$temporary, '[{"file_id":"CHARACTER VARYING(128)"},{"data_vendor_id":"CHARACTER VARYING(32)"},{"data_vendor_sub_id":"CHARACTER VARYING(32)"},{"base_platform_num":"DOUBLE PRECISION"},{"activity_code":"CHARACTER VARYING(32)"},{"activity_name":"CHARACTER VARYING(64)"},{"effective_date":"TIMESTAMP(0) WITHOUT TIME ZONE"},{"activity_level1":"CHARACTER VARYING(1024)"},{"activity_level2":"CHARACTER VARYING(1024)"},{"activity_level3":"CHARACTER VARYING(1024)"},{"activity_level4":"CHARACTER VARYING(1024)"},{"activity_level5":"CHARACTER VARYING(1024)"},{"participant_id":"CHARACTER VARYING(32)"},{"data_vendor_alt_id":"CHARACTER VARYING(32)"},{"file_creation_date":"TIMESTAMP(0) WITHOUT TIME ZONE"},{"inc_value":"DOUBLE PRECISION"}]');
   OPEN activity_cur;
    /* fill the cursor */
    /*
    [5647 - Severity CRITICAL - FETCH BULK COLLECT INTO is not supported. Perform a manual conversion.]
    FETCH ACTIVITY_CUR
            BULK COLLECT INTO l_ACTIVITY
    */
    /* loop through the cursor */
   fetch  from activity_cur  INTO l_ACTIVITY;
  raise notice 'ACT_CURSOR VALUE - %', l_ACTIVITY;
    FOR INDX IN 1..aws_oracle_ext.array$count('l_activity', 'abc_schemaname.USP_Std_SSIS') 
    loop
   -- raise notice 'base_platform-num - %',l_ACTIVITY[indx].base_platform_num;
        BEGIN
            CALL abc_schemaname.usp_ins_standard_act(l_ACTIVITY[indx].file_id, l_ACTIVITY[indx].base_platform_num, l_ACTIVITY[indx].participant_id /* eci */, l_ACTIVITY[indx].data_vendor_id, l_ACTIVITY[indx].activity_level5, l_ACTIVITY[indx].activity_code, aws_oracle_ext.TRUNC(l_ACTIVITY[indx].effective_date), aws_oracle_ext.TRUNC(l_ACTIVITY[indx].file_creation_date), l_ACTIVITY[indx].inc_value);
           END;
    END LOOP;

*/;
    /* close the cursor */
    CLOSE activity_cur;
    RAISE DEBUG USING MESSAGE = CONCAT_WS('', 'No Of Records Processed :', aws_oracle_ext.array$count('l_ACTIVITY', 'abc_schemaname.USP_Std_SSIS'));
    /* clear the dump table for the file in question */

     DELETE FROM abc_schemaname.tbl_activity_dump_ssis
        WHERE file_id = p_file_id;
    PERFORM aws_oracle_ext.array$clear_procedure('abc_schemaname.USP_Std_SSIS');

END;
$procedure$
;

FETCH BULK COLLECT INTO using Postgresql

0 Answers0