Parsing Schema: MDM_APEX
Data tables: MDM_DATA
Creating a page process to take multiple items from a list item and insert into a table.
I am using a function that works in the database (when called by SQL+ and Toad) but get the following error when trying to create the APEX page process:
1 error has occurred
ORA-06550: line 3, column 22: PL/SQL: ORA-00904: "UTIL"."SPLIT": invalid identifier ORA-06550: line 2, column 1: PL/SQL: SQL Statement ignored
I have granted execute privs to APEX_040100 and MDM_APEX on the package. This function works in another application but not recognizing it in this application.
Google search finds no usable solutions for this issue as it relates to APEX. Any suggestions on how to make APEX recognize the function?
INSERT INTO MDM_DATA.REF_CMR(STORE_CD)
(SELECT * FROM TABLE(UTIL.SPLIT(:P400_STORE_LIST,':')));
CREATE OR REPLACE PACKAGE MDM_DATA.UTIL
FUNCTION SPLIT(P_LIST VARCHAR2, P_DEL VARCHAR2 := ',') RETURN SPLIT_TBL PIPELINED;
CREATE OR REPLACE PACKAGE BODY MDM_DATA.UTIL
FUNCTION SPLIT(P_LIST VARCHAR2, P_DEL VARCHAR2 := ',') RETURN SPLIT_TBL PIPELINED
L_LIST VARCHAR2(32767) := P_LIST;
L_IDX := INSTR(L_LIST, P_DEL);
IF L_IDX > 0
PIPE ROW(SUBSTR(L_LIST, 1, L_IDX - 1));
L_LIST := SUBSTR(L_LIST, L_IDX + LENGTH(P_DEL));
WHEN OTHERS THEN
Go to your Application Details and check what your Parsing Schema is set to. If that's the correct schema, then that's the one you want to grant execute to, not to APEX_040100. You rarely need to grant directly to the APEX installation schema because your app runs under the rights of the Parsing Schema.
Hope this helps