APEX

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