******************************Page Items Used******************************
P5_FIRST_NAME
P5_LAST_NAME
P5_COLLEGE
P5_COMPANY
P5_SEQ_ID
P5_ACTION
*******************Process to Create Collection/Insert/Update**************************
BEGIN
IF NOT APEX_COLLECTION.COLLECTION_EXISTS('DETAILS') THEN
APEX_COLLECTION.CREATE_COLLECTION('DETAILS');
END IF;
IF :P5_ACTION='A' THEN
declare
v_new_price number;
v_count number:=0;
BEGIN
begin
select count(*) into v_count
from apex_collections
where COLLECTION_NAME = 'DETAILS'
and c001 = :P5_FIRST_NAME;
exception when no_data_found then null;
end;
if nvl(v_count,0) =0 then
APEX_COLLECTION.ADD_MEMBER(P_COLLECTION_NAME =>'DETAILS', //insert in Coll
P_C001 => :P5_FIRST_NAME,
P_C002 => :P5_LAST_NAME,
P_C003 => :P5_COLLEGE,
P_C004 => :P5_COMPANY
);
end if;
END;
ELSE
declare
v_seq varchar2(100);
BEGIN
IF APEX_COLLECTION.COLLECTION_EXISTS('DETAILS') THEN
begin
select Seq_id
into v_seq
from apex_collections
where COLLECTION_NAME = 'DETAILS'
and seq_id = :P5_SEQ_ID
and c001 = :P5_FIRST_NAME;
exception when no_data_found then null;
end;
if nvl(v_seq,'xx') <> 'xx' then
begin
APEX_COLLECTION.UPDATE_MEMBER ( //update in Collection
p_collection_name => 'DETAILS',
p_seq => v_seq,
P_C001 => :P5_FIRST_NAME,
P_C002 => :P5_LAST_NAME,
P_C003 => :P5_COLLEGE,
P_C004 => :P5_COMPANY
);
end;
end if;
END IF;
END;
END IF;
END;
*************************TRUNCATE COLLECTION******************
BEGIN;
APEX_COLLECTION.TRUNCATE_COLLECTION(
p_collection_name => 'DETAILS');
END;
******************FETCH DATA FROM COLLECTION ON UPDATE FORM*********
declare
v_count varchar (10);
v_seq varchar (10);
v_rank number;
begin
IF :P5_ACTION='E' THEN
select
C001,
C002,
C003,
C004
INTO
:P5_FIRST_NAME,
:P5_LAST_NAME,
:P5_COLLEGE,
: P5_COMPANY
from apex_collections
where COLLECTION_NAME = 'DETAILS'
and :P5_SEQ_ID = seq_id;
END IF;
end;
********************show data on report using collection************
SELECT seq_id,
C001 firts_Name,
C002 last_name,
C003 College,
C004 Company
FROM APEX_COLLECTIONS
WHERE COLLECTION_NAME = 'DETAILS';
*******************
Please Feel Free to ask questions in the comment section.
Thanks
Stay Informed: Stay up-to-date with the latest Oracle APEX tips and updates by following my social media profiles.
Follow on YouTube: Dive deeper into Oracle APEX by exploring my YouTube channel for tutorials and insights:
YouTube Channel: YouTube
Connect on LinkedIn: Let's connect on LinkedIn for networking opportunities, discussions, and more:
LinkedIn Profile: Linkedin
Comments
Post a Comment