Wednesday, 23 May 2012

Apex collections - small case study

Where do you want to hard-code your information?

Somewhere pieces of data need to be written down for reference. They could be in a table; in some variables; within the query itself... there would be quite a few possibilities, regardless of the scenario.

Consider this statement:
select * from my_table where my_col IN ('CODE_1', 'CODE_2', 'CODE_3')

If it where the SQL source from your Apex application, it's minimal coding on your part - but what if the list of available values changes? You'd need to send your application, or at least certain components through the change control process. At the moment that can be annoying.

We could move this code to a PL/SQL package, perhaps using nested tables. The code is simple enough, I'm sure we've all done something similar before.

First we define a table of records
TYPE sql_rec IS RECORD
  (col1  VARCHAR2(200));

TYPE nt_sql_vc200 IS TABLE OF sql_rec;
Then in this case a simple function to pipe the information back will suffice.
FUNCTION param_list
RETURN nt_sql_vc200 PIPELINED IS
  r1 sql_rec;
  r2 sql_rec;
  r3 sql_rec;
BEGIN
  r1.col1 := 'CODE_1';
  r2.col1 := 'CODE_2';
  r3.col1 := 'CODE_3';

  PIPE ROW(r1);
  PIPE ROW(r2);
  PIPE ROW(r3);

END param_list;
Now we have no hard-coded literals in our SQL source, and it's just like having a sub-query. If we need to change the code list, we can modify the PL/SQL package - a little smoother for deployment.
WHERE my_col IN 
  (SELECT col1
   FROM TABLE(my_pkg.param_list));
However, in the Apex environment it's still not the most elegant. The Apex team have provided us with a wheel called Apex Collections.

In using an Apex Collection, we still need some PL/SQL, but it's all made a little simpler.
First define a process that might be called when rendering the page
DECLARE
  PROCEDURE add_col(pc VARCHAR2) IS
  BEGIN
    apex_collection.add_member
      (p_collection_name => 'PARAM_LIST'
      ,p_c001            => pc);
  END add_col;
BEGIN
  apex_collection.create_or_truncate_collection
    (p_collection_name => 'PARAM_LIST');

  add_col('CODE_1');
  add_col('CODE_2');
  add_col('CODE_3');
END  build_params_collection;
Then modify the where clause a touch
WHERE my_col IN 
 (SELECT c001
  FROM apex_collections
  WHERE collection_name = 'PARAM_LIST');
There are plenty of reasons out there why you'd want to use Apex Collections - either check out the documentation linked above, or install the sample application and check out how it's done.
Here endeth the lesson.

If anyone has other ideas for this problem, either more elegant or more esoteric, I'm all ears!

6 comments:

Peter said...

Hi Scot,
nice article.
you can make the usage of collections even smarter by creating a view. this maps the rather clumsy column names to more developer friendly ones

24-7 said...

I dont completely understand it,...

I thought the meaning was to not have the values hard coded in the select statement.

First you had the values hard coded in a select statement.

Now you still have the values in the Apex application but now in a process.

What is the advantage?

24-7 said...

Im sorry, I was too fast posting this question,..
The collection gets populated in a procedure in the database,
not in an Apex page process.
In the Page Process, I have to do a call to PCK.build_params_collection, so the collection gets build .. then in a select statement in Apex i can use the collection..

@Peter,..
can you explain what you mean by creating a view for the column names?
(small example?)

stewstools said...

@24-7: Thanks for pointing out that you can build the collection in a stored procedure in the database. I'd missed that.

It still seems like a lot of code when you could create a generic reference table and maintain the values in the database (maybe via another Apex page?).

Something like:

EXISTS (SELECT null FROM my_ref_tab rt WHERE rt.ref_type='SOMETYPE' AND my_col = rt.ref_value)

24-7 said...

@stewtools

I think the advantage of using Apex Collections is that the values are available in the session!!..

So you only need to select the values into the collection once,.. and then you dont need to re-query the database everytime you need these values to execute a selection ...

Scott Wesley said...

It's good to come back from holiday to find a little conversation regarding this post!

@peter without a doubt - for repeated use of more involved collections, views are highly appropriate.

@stewstools generic references tables are also useful and I think can have a firm place in Apex applications - but in this particular instance I didn't have a pair of values/descriptions, just a set of enumerated values.