Generating Table Collection API for Oracle Apex

...or "The Art of Laziness". The lazy programmer will always strive to get more for less, which is a good thing.

Why?
After looking at the collection API's i Oracle Apex, I could see their usefulness, but also get an idea on how labor intensive the operations were (for the programmer, not the database). I quickly got bored of the c001, c002, etc. coding, so for some time now I have been working on an API generator for creating and manipulating collections based on tables.

What is generated?
You get two packages and a view.

• The table API-package: This is very similar to the API packages generated by Apex, but with some changes to the cursor fetching table rows (all now share a global cursor) and the updates in my version does not attempt to update primary keys. Quite frankly, I could get by with the API's from Apex, but I have a bit of control freakishness about me, and it puts me in a better position to further customize the API when needed. I already have some ideas fro the latter part.
  
  
• The collection API package: This is the package that wraps both the Apex API's, and the table operations. Here you will find create, drop, ins/upd/del collection procedures, all with parameters that match the table, both in name and data type. You also get an apply-procedure to handle the actual dml operations against the actual database table. The package supports "overloading" of collections, so you can have more then one collection based on the same table at the same time. It also handles MD5 if you want it to
  
  
• The view: The view wraps the htmld_collections-view, and casts data types and column names from c### varchar2 format to their actual column names and data types from the database table it was founded on.

Statements for calling the collection API is also generated and included with the view-script.

Installation
Just download the generator package, and compile it into desired schema. Schema must have access to the following views: all_tab_columns, all_cons_columns and all_constraints. I deliberately left out any supporting tables (which certainly could have helped in some areas) to simplify things. This means that if you want to modify some parameters, you will have to do it in the package.

You can download the generator package here.

Generating code
Well, generating the code is quite easy. It is a "fire-and-forget" kind of operation; the packages and view will be generated in a PL/SQL-collection and can be queried and executed at will.

The following section shows how to generate code for an EMP-table located in schema NMS:

begin
gen_coll_p.gen_coll_package (p_owner        => 'NMS',
                          p_table_name   => 'EMP',
                          p_shortname    => 'EMP',
                          p_gen_type     => 'ALL');
end;
/

p_gen_type indicates what part(s) of code to generate, the available values are:

• TAB_API_SPEC for table API package specification
• TAB_API_BODY for table API package body
• COLL_SPEC for table/collection API package specification
• COLL_BODY for table/collection API package body
• COLL_VIEW for table/collection wrapper view
• ALL for all of the above

Viewing the generated code:

select text
from table (gen_coll_p.get_code)
order by line

Compiling generated code:
This part I have put NO amount of work in, after selecting the generated code, you are on your own :-) Well, it's not really that hard to select the code in SQL Developer or TOAD using the query above and copy/paste the result into a new window and choose "Run Script". Or lacking any "sophisticated" tools, you can even revert to spooling and running the result in SQL*Plus . I am sure you will find a way :-)

Using the collection API
Te following examples shows how to use the API from an Oracle Apex application. The API in these examples are generated on the EMP table, and p_shortname parameter during generation is set to EMP as well.

Creating a new collection:

apex_emp_coll_p.create_coll( p_coll_name => 'EMP_COLL'
                           , p_include_md5 => 'Y'
                           , p_where => 'where upper(ename) like ''S%''');

Viewing the contents of the collection:

select seq_id,
    empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    comm,
    deptno
from apex_emp_coll_v
where apex_emp_coll_v.collection_name = 'EMP_COLL'

And please feel free to replace 'EMP_COLL' with a bind variable :-)

Inserting a collection member:

apex_emp_coll_p.ins(p_coll_name => 'EMP_COLL'
                   ,p_empno     => :p_empno
                   ,p_ename     => :p_ename
                   ,p_job       => :p_job
                   ,p_mgr       => :p_mgr
                   ,p_hiredate  => :p_hiredate
                   ,p_sal       => :p_sal
                   ,p_comm      => :p_comm
                   ,p_deptno    => :p_deptno);

Updating a collection member:

apex_emp_coll_p.upd(p_coll_name => 'EMP_COLL'
                   ,p_seq_id    => :p_seq_id
                   ,p_empno     => :p_empno
                   ,p_ename     => :p_ename
                   ,p_job       => :p_job
                   ,p_mgr       => :p_mgr
                   ,p_hiredate  => :p_hiredate
                   ,p_sal       => :p_sal
                   ,p_comm      => :p_comm
                   ,p_deptno    => :p_deptno);

Deleting a collection member:

apex_emp_coll_p.del(p_coll_name => 'EMP_COLL'
                   ,p_seq_id    => :p_seq_id);

Propagating changes to the database table:

apex_emp_coll_p.apply_changes(p_coll_name => 'EMP_COLL',
                              p_refresh   => 'Y');

See it in action
I have made some sample pages to showcase the generator and using the generated code. See http://apex.oracle.com/pls/otn/f?p=28990:8 for the code generator, and http://apex.oracle.com/pls/otn/f?p=28990:4 for viewing the generated code in action.

Some notes on the generator
It did not turn out as smooth as I had hoped. I would really have liked to include an "instead of"-trigger to the view, but this cannot be done without granting rights on WWV_FLOW_COLLECTIONS which I would rather not do. Even though the "instead of"-trigger uses the documented Apex API's, it does not change the "ORA-01031: insufficient privileges" from cropping up. This pretty much rules out the automatic DML process, which is a shame.

When viewing the code in the generator itself, please remember that it has been evolving over time (and in my spare time too :-)), and the code reflects that fact.

I have some plans for features to include in the future, but as of now I do not know what and in what order they will be implemented, so I will let it lie.

Please leave a note if you find this useful or have any comments/questions, etc. :-)