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
x integer ;
FROM APEX_COLLECTION c
AND c001 = :P41_MPM_ID
and rownum = 1;
v_date_started := :P41_DATE_STARTED;
v_date_finished := :P41_DATE_FINISHED;
p_collection_name => 'PHASE_COLLECTION'
, p_seq => x
, p_c004 => v_date_started
, P_c005 => v_date_finished
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
IF NOT APEX_COLLECTION.COLLECTION_EXISTS('PHASE_COLLECTION') THEN
--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
WHERE mpm_eng_id = :P41_MPM_ENG_ID
AND active = 1
ORDER BY date_created)
(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
WHEN OTHERS THEN
logger.error(p_message_text => SQLERRM
,p_message_code => SQLCODE
,p_stack_trace => dbms_utility.format_error_backtrace
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,
"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
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.