APEXblog.nl - Tips and Tricks

About This Blog:
I (Richard Weug) started this blog primary for myself to save all kinds of Apex tips and tricks in one place. To use it as a place to write down how I used some coding in my own projects, but also to copy and paste all kinds of articles I find on the Internet. (So I never have to wonder on what website did I read about??? When I see something interesting I collect the content so I have my own archive/knowlegde base.

View my profile on LinkedIn

 

PL/SQL stuff

Print
23
May

Apex Collections

Written by Richard Weug. Posted in PL/SQL

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.

Original article by: Scott Wesley