Tabular Forms - Expert Oracle Application Express - page 184

You will continue with the methods used earlier in this chapter, and keep all of your code in a
package. You will use APEX regions and processes only to make calls into your package code.
First, create a procedure and a view. Listing 3-9 shows a procedure for creating a collection
containing all employees of one department. Also in the listing is a view created upon that collection.
Listing 3-9. Procedure and View for a Tabular Form's Collection
PROCEDURE create emp collection (p deptno IN NUMBER,
p message OUT VARCHAR2);
PROCEDURE create emp collection (p deptno IN NUMBER,
p message OUT VARCHAR2)
IS
v collection VARCHAR2 (40) := 'EMP DEPT';
BEGIN
IF apex collection.collection exists (v collection)
THEN
apex collection.delete collection (v collection);
p message := 'Collection deleted.';
END IF;
apex collection.create collection from query
(v collection,
'SELECT a.*, wwv flow item.md5(empno, ename, job, '
|| 'mgr, hiredate, sal, comm, deptno, valid) '
|| 'FROM EMP a WHERE deptno = '
|| p deptno
);
p message := p message || '<br/>' || 'Collection created.';
p message := LTRIM (p message, '<br/>');
END create emp collection;
CREATE OR REPLACE VIEW emp coll v
AS
SELECT seq id, c001 empno, c002 ename,
c003 job, c004 mgr, c005 hiredate,
c006 sal, c007 comm, c008 deptno,
c009 valid, c010 checksum, c011 delete flag
FROM apex collections
WHERE collection name = 'EMP DEPT';
The procedure will check whether the collection exists. If the collection does exist, the procedure
will delete the existing collection and create a new one based on the input. Otherwise, the procedure
creates a new collection.
The view will make it easier to deal with the collection. You will not need to remember the member
number in order to insert, update, or delete a row. The view takes care of the member number for you.
You can now start creating a new page, which will be page 3. Use a standard Tabular Form based on
the view emp coll v . Include all the columns. The primary key will be the combination of the SEQ_ID
and EMPNO column. Make all columns editable. Make the region title Tabular Form Collection . After
creating the page, change the item type for the columns CHECKSUM and DELETE FLAG to Hidden .
You will also need to edit the generated SQL for the Tabular Form and add a condition as follows: