3 Replies Latest reply: Jul 24, 2013 10:58 AM by SATim RSS

    Page Process returns ORA-06550: Error

    SATim

      APEX 4.1

      Oracle 11g

       

      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?


      Thanks,

      Tim


      <code>

      Page Process:

      INSERT INTO MDM_DATA.REF_CMR(STORE_CD)

      (SELECT * FROM TABLE(UTIL.SPLIT(:P400_STORE_LIST,':')));

      </code>

       

      <code>

      CREATE OR REPLACE PACKAGE MDM_DATA.UTIL

      AS

      FUNCTION SPLIT(P_LIST VARCHAR2, P_DEL  VARCHAR2 := ',') RETURN SPLIT_TBL PIPELINED;

      Etc...

      End UTIL;

       

      CREATE OR REPLACE PACKAGE BODY MDM_DATA.UTIL

      AS

       

      FUNCTION SPLIT(P_LIST VARCHAR2, P_DEL VARCHAR2 := ',') RETURN SPLIT_TBL PIPELINED

      AS

      L_IDX PLS_INTEGER;

      L_LIST VARCHAR2(32767) := P_LIST;

      L_VALUE VARCHAR2(32767);

      BEGIN

      LOOP

      L_IDX := INSTR(L_LIST, P_DEL);

       

      IF L_IDX > 0

      THEN

      PIPE ROW(SUBSTR(L_LIST, 1, L_IDX - 1));

      L_LIST := SUBSTR(L_LIST, L_IDX + LENGTH(P_DEL));

      ELSE

      PIPE ROW(L_LIST);

      EXIT;

      END IF;

      END LOOP;

       

      RETURN;

       

      EXCEPTION

      WHEN OTHERS THEN

      NULL;

      END SPLIT;


      ETC....

      End util;

      </code>