Calling or using oracle array package in oracle apex

Question

I want to calling oracle array package in oracle apex page process. My package "CREATE OR REPLACE PACKAGE SBPA.DPG_SBPA_ITEM_SUB_CAT AS

/.......................................................................... Program Purpose : SBPA_ITEM_SUB_CAT_ENTRY Process Execution Time :
Generate By : Morshed Generate Date : 27-Feb-2020 Modifyed Date : ...................................................................../

TYPE RefCursor is REF CURSOR;

TYPE Array_Item_Sub_Id IS TABLE OF SBPA_ITEM_SUB_CAT.ITEM_SUB_ID % TYPE INDEX BY BINARY_INTEGER; TYPE Array_Item_Sub_Code IS TABLE OF SBPA_ITEM_SUB_CAT.ITEM_SUB_CODE % TYPE INDEX BY BINARY_INTEGER; TYPE Array_Item_Sub_Desc IS TABLE OF SBPA_ITEM_SUB_CAT.ITEM_SUB_DESC % TYPE INDEX BY BINARY_INTEGER; TYPE Array_Item_Cat_Code IS TABLE OF SBPA_ITEM_SUB_CAT.ITEM_CAT_CODE % TYPE INDEX BY BINARY_INTEGER; TYPE Array_RowStatus IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;

PROCEDURE DPD_SBPA_ITEM_SUB_CAT (O_Status OUT NUMBER, P_Item_Sub_Id IN Array_Item_Sub_Id, P_Item_Sub_Code IN Array_Item_Sub_Code, P_Item_Sub_Desc IN Array_Item_Sub_Desc, P_Item_Cat_Code IN Array_Item_Cat_Code, P_RowStatus IN Array_RowStatus, P_USER VARCHAR2);

PROCEDURE DPD_SBPA_ITEM_SUB_CAT_GRID (Cur_Data OUT RefCursor);

END DPG_SBPA_ITEM_SUB_CAT; /"

And Package body "CREATE OR REPLACE PACKAGE BODY SBPA.DPG_SBPA_ITEM_SUB_CAT AS

/.......................................................................... Program Purpose : SBPA_ITEM_SUB_CAT_ENTRY Process Execution Time :
Generate By : Morshed Generate Date : 27-Feb-2020 Modifyed Date : ...................................................................../

PROCEDURE DPD_SBPA_ITEM_SUB_CAT (O_Status OUT NUMBER, P_Item_Sub_Id IN Array_Item_Sub_Id, P_Item_Sub_Code IN Array_Item_Sub_Code, P_Item_Sub_Desc IN Array_Item_Sub_Desc, P_Item_Cat_Code IN Array_Item_Cat_Code, P_RowStatus IN Array_RowStatus, P_USER VARCHAR2) IS

V_DataType     VARCHAR2(20) :='ITEM_SUBCAT_SAVE';
V_ErrDesc   VARCHAR2(500);
                                                      

BEGIN

/*O_Status :=1;*/

FOR I IN P_Item_Sub_Desc.FIRST..P_Item_Sub_Desc.LAST
LOOP
  
        IF P_RowStatus(I)=1 THEN
                    
            INSERT INTO SBPA_ITEM_SUB_CAT 
            (ITEM_SUB_ID, ITEM_SUB_CODE, ITEM_SUB_DESC, ITEM_CAT_CODE, STATUS, CREATE_DATE, CREATE_BY) VALUES
            (SBPA_ITEM_SUB_ID_SEQ.NEXTVAL,'ITMSC-'||LPAD(SBPA_ITEM_SUB_CODE_SEQ.NEXTVAL,4,'0'),P_Item_Sub_Desc(I),P_Item_Cat_Code(I),'A',SYSDATE,P_USER);
                        
        ELSIF P_RowStatus(I)=2 THEN
                    
            UPDATE SBPA_ITEM_SUB_CAT SET
            ITEM_SUB_DESC=P_Item_Sub_Desc(I),
            Item_Cat_Code=P_Item_Cat_Code(I),
            UPDATE_BY=P_User, 
            UPDATE_DATE =SYSDATE
            WHERE Item_Sub_Code=P_Item_Sub_Code(I);
                    
        ELSIF P_RowStatus(I)=3 THEN
            
            DELETE FROM SBPA_ITEM_SUB_CAT
            WHERE Item_Sub_Code=P_Item_Sub_Code(I);              
                    
        END IF;
    
END LOOP;

COMMIT;

EXCEPTION WHEN OTHERS THEN ROLLBACK;

/* O_Status :=0;*/ V_ErrDesc:=SUBSTR(SQLERRM,1,500);

INSERT INTO SBPA_ERROR_LOG

(RUN_ID, DATA_TYPE, ERROR_DESC, STATUS, RUN_DATE, RUN_BY) VALUES (SBPA_RUN_ID_SEQ.NEXTVAL,V_DataType,V_ErrDesc,'E',SYSDATE,P_User);

COMMIT; 

END DPD_SBPA_ITEM_SUB_CAT;

PROCEDURE DPD_SBPA_ITEM_SUB_CAT_GRID (Cur_Data OUT RefCursor) IS

BEGIN

OPEN CUR_DATA FOR
SELECT ITEM_SUB_ID, ITEM_SUB_CODE, ITEM_SUB_DESC,S.ITEM_CAT_CODE,C.ITEM_CAT_DESC FROM SBPA_ITEM_SUB_CAT S,SBPA_ITEM_CAT C
WHERE S.ITEM_CAT_CODE=C.ITEM_CAT_CODE
ORDER BY ITEM_SUB_ID DESC;

END DPD_SBPA_ITEM_SUB_CAT_GRID;

END DPG_SBPA_ITEM_SUB_CAT; /" This database code. So how can i call this package in oracle apex tabular form. Please help me..

Answer 1

(1) Create a button, so the processing(DML) will be performed on click of the button.

(2) Button Action -> Submit Page.

(3) Create a page Process

Point -> Processing. 
Tabular Form -> Select your tabular form.
Pl/SQL Code ->  Begin 
                package_name.procedure_name(parameters);  
                END;        

(4) When Button Pressed: Select the created button.

(5) Execution Scope: For Created and Modified rows.