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.
If anyone has other ideas for this problem, either more elegant or more esoteric, I'm all ears!