2 Replies Latest reply: Oct 8, 2012 3:19 PM by Ann586341 RSS

    Update APEX collection error ORA-04044: procedure, function, package, type

    Ann586341
      Hello everyone,

      I am trying to update APEX COLLECTION MEMBER and encounter errors:


      1 error has occurred
      ORA-06550: line 9, column 8: PL/SQL: ORA-04044: procedure, function, package, or type is not allowed here ORA-06550: line 7, column 3: PL/SQL: SQL Statement ignored

      The PLSQL code are as below

      DECLARE
      x integer ;
      v_date_started varchar2(255);
      v_date_finished varchar2(255);
      BEGIN
      SELECT c.seq_id
      INTO x
      FROM APEX_COLLECTION c
      WHERE collection_name='PHASE_COLLECTION'
      AND c001 = :P41_MPM_ID
      and rownum = 1;
      v_date_started := :P41_DATE_STARTED;
      v_date_finished := :P41_DATE_FINISHED;

      APEX_COLLECTION.UPDATE_MEMBER(
      p_collection_name => 'PHASE_COLLECTION'
      , p_seq => x
      , p_c004 => v_date_started
      , P_c005 => v_date_finished
      );
      END;


      The process will be run after SUBMIT and validation.

      The code I used to add APEX colletion run fine and is as below

      --Initialize Collection PHASE_COLLECTION
      BEGIN
      IF NOT APEX_COLLECTION.COLLECTION_EXISTS('PHASE_COLLECTION') THEN
      APEX_COLLECTION.CREATE_COLLECTION('PHASE_COLLECTION');
      ELSE
      APEX_COLLECTION.TRUNCATE_COLLECTION('PHASE_COLLECTION');
      END IF;
      --Add member to the PHASE COLLECTION from table PHASE_MEMBERSHIP
      IF :P41_MPM_ENG_ID IS NOT NULL THEN
      FOR x IN (SELECT mpm_id, mpm_eng_id, phase_number, date_started, date_finished, date_created
      FROM phase_membership
      WHERE mpm_eng_id = :P41_MPM_ENG_ID
      AND active = 1
      ORDER BY date_created)
      LOOP
      APEX_COLLECTION.ADD_MEMBER
      (p_collection_name => 'PHASE_COLLECTION'
      ,p_c001 => x.MPM_ID
      ,p_c002 => x.MPM_ENG_ID
      ,p_c003 => x.PHASE_NUMBER
      ,p_c004 => x.DATE_STARTED
      ,p_c005 => x.DATE_FINISHED
      ,p_c006 => x.DATE_CREATED

      );
      END LOOP;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      logger.error(p_message_text => SQLERRM
      ,p_message_code => SQLCODE
      ,p_stack_trace => dbms_utility.format_error_backtrace
      );
      RAISE;
      END;


      What I tried to do is using APEX_COLLETION to check overlap among date_range.
      The requirement is just to display a warning if user adds a new phase record or update current phase record that may cause overlap among the whole phase control.
      PHASE_MEMBERSHIP table script is

      CREATE TABLE "PHASE_MEMBERSHIP"
      (
      "MPM_ID" NUMBER NOT NULL ENABLE,
      "MPM_ENG_ID" NUMBER NOT NULL ENABLE,
      "PHASE_NUMBER" NUMBER(2,0) NOT NULL ENABLE,
      "DATE_STARTED" DATE NOT NULL ENABLE,
      "DATE_FINISHED" DATE,
      "NOTES" VARCHAR2(2000 BYTE),
      "DATE_CREATED" DATE NOT NULL ENABLE,
      "CREATED_BY" VARCHAR2(20 BYTE) NOT NULL ENABLE,
      "ACTIVE" NUMBER(2,0) DEFAULT 1 NOT NULL ENABLE)

      So basically, I want to populate all records related to one particular MPM_ENG_ID to an APEX collection called PHASE_COLLECTION.
      Then when user provide date_started and/or date_finished, it will update/insert the APEX collection first.
      Then do the overlap date range check based on
      http://www.oracle-base.com/articles/misc/overlapping-date-ranges.php
      then display the warning message to ask user to confirm.

      But then I get stuck at APEX COLLECTION update step.

      If anyone has any ideas about what is wrong, please help.

      Many thanks in advance.

      Ann