 |
|
ss
Oracle Tips by Burleson |
Saving
Changes from a Collection to a Table
Although one cannot issue INSERT, UPDATE, or
DELETE statements against collections, it is possible to issue SELECT
statements. The htmldb_collections is a view into the data stored as a
collection. The code that follows selects the C001…C006 columns from
the htmldb_collections view. Then, looping through the collection
starts to check the C006 column for any changes that have taken
place. Members where C006 = ‘I’ indicates a new record and it needs
to be INSERTed into the conference_rsvp table. The U means the member
was updated and an UPDATE is performed. The D is a member that was
deleted, and the record will be deleted from the table. Once the data
has been updated, the collection is deleted and re-created so it can
be displayed in a report.
This code is located in the Save Changes to
CONFERENCE_RSVP process on page 1132 in the EASY Samples application.
--
-- Loop through the collection and update the CONFERNCE_RSVP
-- table according to the c006 column.
--
for r in (
select c001, c002, c003, c004, c005, c006
from htmldb_collections
where collection_name = 'CONF' )
loop
if r.c006 = 'I' then
insert into conference_rsvp(
id, last_name, first_name,
rsvp_date, company )
values(
r.c001, r.c002, r.c003,
r.c004, r.c005 );
elsif r.c006 = 'U' then
update conference_rsvp
set last_name = r.c002,
first_name = r.c003,
rsvp_date = to_date( r.c004, :PICK_DATE_FORMAT_MASK ),
company = r.c005
where id = to_number( r.c001 );
elsif r.c006 = 'D' then
-- don't do the delete while testing for the book.
-- delete from conference_rsvp where id = r.c001;
null;
end if;
end loop;
--
-- Repopulate the collection
--
htmldb_collection.delete_collection( 'CONF' );
htmldb_collection.create_collection_from_query(
p_collection_name => 'CONF',
p_query => 'select id, last_name, first_name, rsvp_date,
company, ''O'' from conference_rsvp where payment = ''Check''',
p_generate_md5 => 'YES');
htmldb_collection.reset_collection_changed( 'CONF' );
The above book excerpt is from:
Easy HTML-DB
Oracle Application Express
Create
Dynamic Web Pages with OAE
ISBN 0-9761573-1-4
Michael Cunningham & Kent Crotty
http://www.rampant-books.com/book_2005_2_html_db.htm
|
