37593089_7cd841cd66 Creating tabular forms in Apex is relative straight forward. One gets fully generated, instant insert/update/delete functionality. Unfortunately (for the form developer), tables are usually part of a more or less complex data model, which is optimized for data storage, rather then for form-layout. Well, Apex offers some possibilities to the developer to enable tabular forms beyond 1:1 table based.

Collections

One alternative is to create your tabular form using the Apex Collection API. There is a good article on Martin Giffy D’Souza’s blog describing this technique.
This collection-approach basically pre-fetches data into a (PL/SQL) collection from a query/cursor. The “collected” data will be displayed in the form. An on-submit page process will store the data submitted from the form into the collection. After this you can perform some validation before submitting the data to table(s) in an on-submit page process.

Database Views

Well, I favor an approach that keeps the data-logic in the database, which is (in my opinion) more accessible to developers, more likely to be re-used and less fragmented (code-wise), thus easer to maintain: Database Views.
Views can be based on several tables or other views. Usually views are used to query data only, but in my case, I would like to perform all DML through an Apex form based on a view. As long as my view is “key-preserving” for the joined table I would like to perform the DML on, this is not a problem.

Key preserved means, the row from the base table will appear AT MOST ONCE in the output view on that table.


When my SQL becomes more complex, using outer joints, aggregations or sub-queries, the resulting view will probably not be updateable anymore without the use of INSTEAD OF Triggers. The INSTEAD OF trigger(s) will handle all the DML processing and hide the processing complexity from APEX.
Applying this view-Approach, you can keep the UI layer (APEX) separated from the data model layer (database).
Let`s go through this step-by-step:

  • create a view which will return all the data you would like to be available in your tabular form
  • implement the DML processing using an INSTEAD OF trigger on this view 
  • build the tabular form UI in APEX based on this using standard APEX processing logic

Example

This example will implement a simple timesheet in APEX. The timesheet should show all projects of the (APEX-) user for a given month. The user must be able to enter “times spend” for every project assigned to him, for every day. The form should only contain one submit button (no add or remove rows button):

Project Name 1 2 . . . 30 31
project 1
project 2
project n

I started creating a new workspace and used the EMP table as starting point. I added some table definitions to hold project-, assignment- and timesheet information.
You can download the complete DDL scripts for this example here.

Preparations

  1. You’ll need a workspace associated to a database schema containing the Apex demo objects (DEMO_CUSTOMERS, DEMO_ORDERS, DEMO_STATES, DEMO_USERS, …)
  2. run the timesheet_demo.sql script to create the additional table objects (including some demo data)

Step 1: Create the Pivot View

Have a look at these tables and data I added to the demo application’s model:
Table DEMO_PROJECTS defines project with start- and end-date:

select * from demo_projects;

PROJECT_ID PROJECT_NAME                   START_DAT END_DATE 
---------- ------------------------------ --------- --------- 
0 Marketing Campaign Computers   01-OKT-09 31-DEC-10 
1 Marketing Campaign Software    01-JUL-09 31-JUL-10

2 rows selected.

Table DEMO_TIMESCHEETS holds the time spend on an assignment:

select * from demo_timesheets; 

no rows selected.

Now lets join there tables:

SELECT   pj.project_id,
         pj.project_name,
         am.assignment_id,
         us.user_id,
         us.user_name,
         ts.timesheet_date,
         ts.hours
  FROM   demo_projects pj,
         demo_assignments am,
         demo_users us,
         demo_timesheets ts
 WHERE       pj.project_id = am.project_id
         AND am.user_id = us.user_id
         AND am.assignment_id = ts.assignment_id(+);
   
PROJECT_ID PROJECT_NAME                   ASSIGNMENT_ID    USER_ID USER_NAME  TIMESHEET      HOURS 
---------- ------------------------------ ------------- ---------- ---------- --------- ---------- 
0 Marketing Campaign Computers               3         22 SCOTT                          
1 Marketing Campaign Software                5         22 SCOTT                          
1 Marketing Campaign Software                6         23 FORD                          

3 rows selected.

The timesheet table does not contain any data yet. Even if there would be some timesheet data, there is no guaranty there are no gaps in the dates. I would like to have a result with dense dates for all project assignments:

SELECT   prj.*, ts.hours
  FROM   (SELECT   d.thedate,
                   pj.project_id,
                   pj.project_name,
                   am.assignment_id,
                   us.user_id,
                   us.user_name
            FROM   (    SELECT   mindat + LEVEL - 1 thedate
                          FROM   (SELECT   MIN (start_date) mindat,
                                           MAX (end_date) maxdat
                                    FROM   demo_projects)
                    CONNECT BY   LEVEL <= maxdat - mindat + 1) d,
                   demo_projects pj,
                   demo_assignments am,
                   demo_users us
           WHERE   pj.project_id = am.project_id AND am.user_id = us.user_id)
         prj,
         demo_timesheets ts
 WHERE   prj.assignment_id = ts.assignment_id(+)
         AND prj.thedate = ts.timesheet_date(+);
   
THEDATE   PROJECT_ID PROJECT_NAME                   ASSIGNMENT_ID    USER_ID USER_NAME       HOURS 
--------- ---------- ------------------------------ ------------- ---------- ---------- ---------- 
01-JUL-09          0 Marketing Campaign Computers               3         22 SCOTT                
01-JUL-09          1 Marketing Campaign Software                6         23 FORD                 
01-JUL-09          1 Marketing Campaign Software                5         22 SCOTT                
02-JUL-09          0 Marketing Campaign Computers               3         22 SCOTT                
02-JUL-09          1 Marketing Campaign Software                6         23 FORD                 
02-JUL-09          1 Marketing Campaign Software                5         22 SCOTT                
03-JUL-09          0 Marketing Campaign Computers               3         22 SCOTT                
03-JUL-09          1 Marketing Campaign Software                6         23 FORD                 
03-JUL-09          1 Marketing Campaign Software                5         22 SCOTT                
04-JUL-09          0 Marketing Campaign Computers               3         22 SCOTT                 
. . .  

28-DEC-10          1 Marketing Campaign Software                5         22 SCOTT                
29-DEC-10          0 Marketing Campaign Computers               3         22 SCOTT                
29-DEC-10          1 Marketing Campaign Software                5         22 SCOTT                
29-DEC-10          1 Marketing Campaign Software                6         23 FORD                 
30-DEC-10          0 Marketing Campaign Computers               3         22 SCOTT                
30-DEC-10          1 Marketing Campaign Software                5         22 SCOTT                
30-DEC-10          1 Marketing Campaign Software                6         23 FORD                 
31-DEC-10          0 Marketing Campaign Computers               3         22 SCOTT                
31-DEC-10          1 Marketing Campaign Software                5         22 SCOTT                
31-DEC-10          1 Marketing Campaign Software                6         23 FORD                

1647 rows selected.

Now we got a dense dataset, let’s build the pivot statement. Well, I could use the 11g pivot function, but to make this statement more generic, I’ll use the standard pre-11g way to pivot my timesheet data:
As shown in my lay-out example for the actual form I want to implement, the result of the query should show one row with all the days of a month containing the time entered for a certain project per user. Here it is:

  SELECT   theyear || '-' || themonth year_month,
           theyear,
           themonth,
           project_id,
           project_name,
           assignment_id,
           user_id,
           user_name,
           SUM (d1) d1,
           SUM (d2) d2,
           SUM (d3) d3,
           . . .
           SUM (d28) d28,
           SUM (d29) d29,
           SUM (d30) d30,
           SUM (d31) d31
    FROM   (SELECT   EXTRACT (YEAR FROM thedate) theyear,
                     EXTRACT (MONTH FROM thedate) themonth,
                     project_id,
                     project_name,
                     assignment_id,
                     user_id,
                     user_name,
                     CASE WHEN day_in_month = 1 THEN hours ELSE NULL END d1,
                     CASE WHEN day_in_month = 2 THEN hours ELSE NULL END d2,
                     CASE WHEN day_in_month = 3 THEN hours ELSE NULL END d3,
                     . . .
                     CASE WHEN day_in_month = 28 THEN hours ELSE NULL END d28,
                     CASE WHEN day_in_month = 29 THEN hours ELSE NULL END d29,
                     CASE WHEN day_in_month = 30 THEN hours ELSE NULL END d30,
                     CASE WHEN day_in_month = 31 THEN hours ELSE NULL END d31
              FROM   (SELECT   prj.*,
                               ts.hours,
                               EXTRACT (DAY FROM prj.thedate) day_in_month
                        FROM   (SELECT   d.thedate,
                                         pj.project_id,
                                         pj.project_name,
                                         am.assignment_id,
                                         us.user_id,
                                         us.user_name
                                  FROM   (    SELECT   mindat + LEVEL - 1 thedate
                                                FROM   (SELECT   TRUNC(MIN(start_date))
                                                                    mindat,
                                                                 TRUNC (
                                                                    MAX (end_date)
                                                                 )
                                                                    maxdat
                                                          FROM   demo_projects)
                                          CONNECT BY   LEVEL <=
                                                          maxdat - mindat + 1)
                                         d,
                                         demo_projects pj,
                                         demo_assignments am,
                                         demo_users us
                                 WHERE   pj.project_id = am.project_id
                                         AND am.user_id = us.user_id) prj,
                               demo_timesheets ts
                       WHERE   prj.assignment_id = ts.assignment_id(+)
                               AND prj.thedate = ts.timesheet_date(+)))
GROUP BY   theyear,
           themonth,
           project_id,
           project_name,
           assignment_id,
           user_id,
           user_name;

YEAR_MO THEYEAR THEMONTH PROJECT_ID PROJECT_NAME                 ASSIGNMENT_ID USER_ID USER_NAME  D1  D2  D3 … D29 D30 D31 
------- ------- -------- ---------- ---------------------------- ------------- ------- --------- --- --- --- … --- --- --- 
2009-10    2009       10          1 Marketing Campaign Software              6      23 FORD                             
2009-10    2009       10          0 Marketing Campaign Computers             3      22 SCOTT                            
2009-10    2009       10          1 Marketing Campaign Software              5      22 SCOTT                            
2009-11    2009       11          1 Marketing Campaign Software              6      23 FORD                             
2009-11    2009       11          0 Marketing Campaign Computers             3      22 SCOTT                            
2009-11    2009       11          1 Marketing Campaign Software              5      22 SCOTT                            
2009-12    2009       12          1 Marketing Campaign Software              6      23 FORD                             
2009-12    2009       12          0 Marketing Campaign Computers             3      22 SCOTT                            
2009-12    2009       12          1 Marketing Campaign Software              5      22 SCOTT                            
2009-7     2009        7          1 Marketing Campaign Software              6      23 FORD                             
2009-7     2009        7          0 Marketing Campaign Computers             3      22 SCOTT  
. . .  
2010-8     2010        8          0 Marketing Campaign Computers             3      22 SCOTT                            
2010-8     2010        8          1 Marketing Campaign Software              5      22 SCOTT                            
2010-9     2010        9          1 Marketing Campaign Software              6      23 FORD                             
2010-9     2010        9          0 Marketing Campaign Computers             3      22 SCOTT                            
2010-9     2010        9          1 Marketing Campaign Software              5      22 SCOTT                           

54 rows selected.

OK, still no time data, but exactly the lay-out I was looking for.

Step 2: INSTEAD OF view trigger

Editing timesheet data will only effect the table DEMO_TIMESHEETS. For users there will be no difference between insert, update or delete as all fields in the pivot/matrix form will always be visible.

To handle a DML for a single row of DEMO_TIMESHEETS, I will provide a procedure which will:

  • insert a row if the combination of assignment/date does not exist AND hours is not NULL/0
  • update a row if combination of assignment/day does exist AND hours is not NULL/0
  • delete a row if combination of assignment/day does exist AND hours is NULL/0

CREATE OR REPLACE PROCEDURE write_to_timesheet (p_asgnmt_id   IN number,
                                                p_date        IN date,
                                                p_hours       IN number)
AS
BEGIN
   IF p_hours IS NOT NULL AND p_hours <> 0
   THEN
      UPDATE   demo_timesheets
         SET   hours = p_hours
       WHERE   assignment_id = p_asgnmt_id
               AND TRUNC (timesheet_date) = TRUNC (p_date);

      IF sql%ROWCOUNT = 0
      THEN
         INSERT INTO demo_timesheets (assignment_id, timesheet_date, hours)
           VALUES   (p_asgnmt_id, TRUNC (p_date), p_hours);
      END IF;
   ELSE
      DELETE FROM   demo_timesheets
            WHERE   assignment_id = p_asgnmt_id
                    AND TRUNC (timesheet_date) = TRUNC (p_date);
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (-20001, 'Error updating the timesheet!!!');
END;
/

I used the upper pivot SQL-statement to create the view DEMO_TIMESHEET_V. To be able to use this view as basis for my form, I have to make it updateable. The view DEMO_TIMESHEET_V is not inherently updateable. It requires an INSTEAD OF trigger. With an INSTEAD OF trigger, you can write normal UPDATE, INSERT, and DELETE

statements against the view, and the INSTEAD OF trigger works invisibly in the background to make the right actions take place. In this trigger I will call the procedure write_to_timesheet to perform the DML on the underlying table DEMO_TIMESHEETS. In fact, the procedure write_to_timesheet will be called a maximum of 31 times, depending on the month presented in the timesheet. Well, at this point I have to admit, this is not exactly the sexiest code I have ever written, but it does the job and it’s pretty easy to understand (and I wonder, why I didn’t choose an example with a weekly timesheet form).

CREATE OR REPLACE TRIGGER demo_timesheet_v_io_trg
   INSTEAD OF UPDATE
   ON demo_timesheet_v
   REFERENCING NEW AS new OLD AS old
   FOR EACH ROW
DECLARE
   v_first_day   date;
BEGIN
   v_first_day :=
      TO_DATE ('01' || '-' || :new.themonth || '-' || :new.theyear,
               'DD-MM-YYYY');

   write_to_timesheet (:new.assignment_id, v_first_day + 0, :new.d1);
   write_to_timesheet (:new.assignment_id, v_first_day + 1, :new.d2);
   write_to_timesheet (:new.assignment_id, v_first_day + 2, :new.d3);
   . . .
   write_to_timesheet (:new.assignment_id, v_first_day + 25, :new.d26);
   write_to_timesheet (:new.assignment_id, v_first_day + 26, :new.d27);
   write_to_timesheet (:new.assignment_id, v_first_day + 27, :new.d28);

   -- special treatment for days not valid for some months

   IF EXTRACT (DAY FROM LAST_DAY (v_first_day)) >= 29
   THEN
      write_to_timesheet (:new.assignment_id, v_first_day + 28, :new.d29);
   END IF;

   IF EXTRACT (DAY FROM LAST_DAY (v_first_day)) >= 30
   THEN
      write_to_timesheet (:new.assignment_id, v_first_day + 29, :new.d30);
   END IF;

   IF EXTRACT (DAY FROM LAST_DAY (v_first_day)) >= 31
   THEN
      write_to_timesheet (:new.assignment_id, v_first_day + 30, :new.d31);
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      -- Please, do some error handling and allow me
      -- to skip this part for this time...
      RAISE;
END demo_timesheet_v_io_trg;
/

Now I can issue updates against the view:

UPDATE   demo_timesheet_v 
SET   d1 = 4 
  ,   d10 = 5 
  ,   d28 = 6 
  ,   d31 = 7 
WHERE year_month = '2010-2' 
  AND assignment_id = 5
;

And the data will be written into the time sheet table (except the 31-Feb time, so you can’t fool your manager):

ASSIGNMENT_ID TIMESHEET      HOURS 
------------- --------- ---------- 
5 01-FEB-10          4 
5 10-FEB-10          5 
5 28-FEB-10          6

3 rows selected.

So far I didn’t have to code anything in Apex. In the next step I will build the actual form.

Step 3: build the tabular form in APEX

I promised, that the timesheet form will be completely based on standard Apex functionality (strictly spoken, using the collection approach is standard Apex functionality too), without any additional coding in Apex. Although it’s mostly just clicking through the wizards and property screens, I will walk through it step by step:
Assuming you already have created an application or you are using the demo application, the first step will be to create a new page:

Create Page:

  • page type: Form
  • now choose: Tabular Form

The Create Tabular Form wizard appears:

Step “Table / View Owner”:

  • select the schema owning the demo objects
  • For “Allowed Operations” choose “Update Only“. Remember, the pivot view contains all the rows. There is no need to insert or delete rows.

Step “Table / View Name”:

  • select the view DEMO_TIMESHEET_V from the list

Step “Displayed Columns”:

  • just select all the columns

Step “Primary Key”:

  • Each row of the view can be uniquely identified by the column values of year_month and assignment_id. Select these from the lists.

For those folks, who wondered why I created the year_month column: the reason is, I only can assign two columns as PK columns in the Apex wizard (I remember Oracle Portal had the same restriction. I know there was a workaround in Portal to add additional columns; does anybody know such a “backdoor” in Apex?). To overcome the limitation, you might have to combine two or more columns into one, depending on the complexity of your view.

Important: you won’t get any error message while creating the form, but when submitting changes to the form without the correct key, Apex will throw an error:

Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-20001: ORA-20001: Current version of data in database has changed since user initiated update process. <checksum information>, <your MRU statement>.

Step “Primary Key Source”:

  • Choose “Existing Trigger” for both primary key columns.

Step “Updateable Columns”:

  • Updateable columns will be D1 . . . D31.

Step “Page and Region Attributes”:

  • Give the page an appropriate name (like “Timesheet”) and accept the defaults for the rest of the properties.

Step “Tab”:

  • just accept the default

Step “Button Labels”:

  • just accept the default

Step “Branching”:

  • just accept the default

Step “Confirm”:

  • Click “Finish” and run the page.

The lay-out is not ideal yet. After changing the width of columns D1 … D31 to “1” and, modifying the headers and hiding some of the ID columns my timesheet form look like this:
timesheet1s

Now I will add two parameters (Year and Month) and modify the query, so only the assignments of the current Apex user will be shown. I’m not going to describe this in detail, but eventually I will use these parameters (select lists) in the source query of the form region:

timesheet3 

With as result:

timesheet4

One last thing to do: prevent the columns of days 29/30/31 from being displayed when not valid for a month. I just place conditions on the Column Attributes of column D29 – D31:


timesheet5 

Conclusion

What I wanted to show is: when developing with Apex, keep in mind that you are running Apex on a very powerful environment: the Oracle database. Preferably use Apex to build your user interface and standard transaction form handling. Complex data logic should preferably be coded in the database, not in the Apex Page processing. No, it’s not black & white, eventually you have to find a balance between choosing the database or the Apex approach in individual cases.

PS: if you are getting a “ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table”, read this post by Tobias Arnold.

Source Article from http://rokitta.blogspot.com/2009/12/tabular-forms-on-complex-views-using.html
Tabular Forms on Complex Views – using INSTEAD OF Triggers
http://rokitta.blogspot.com/2009/12/tabular-forms-on-complex-views-using.html
http://rokitta.blogspot.com/feeds/posts/default
Oracle & Apex Geekery
And another thing …

I had some reactions on my post, describing an issue that
occurred when using the DHTMLX library in combination with APEX:

From Bharadwaz (Bharat) Pappu ,
Subject: ODTUD- Oracle APEX + DHTMLX
integration

Hello
Christian,

The
presentation at APEXPOSED will be similar to the one I did at
ODTUG.

There is
one huge change though…Instead of making
changes to the .js files or the DHTMLX APIs I use a very
powerful DHTMLX API to integrate it  with Oracle
APEX.

As promised to you earlier
here is a snippet of the code that will work without changing
any APEX or DHTMLX APIs:

 
<link rel="STYLESHEET" type="text/css" href="/i/javascript/.../dhtmlxSuite/dhtmlxtree/samples/common/style.css">
<link rel="STYLESHEET" type="text/css" href="/i/javascript/.../dhtmlxSuite/dhtmlxtree/codebase/dhtmlxtree.css">
<script  src="/i/javascript/.../dhtmlxSuite/dhtmlxtree/codebase/dhtmlxtree.js"></script>

declare
   lv_url varchar2(1000):='f?p='||v('APP_ID')||':31:'||v('SESSION')||'::::P31_ID,P31_UID:';
BEGIN

   htp.p('
<div id="treeboxbox_tree" 
     style="width:350;
     height:600;background-color:#f5f5f5;border:1px solid Silver;overflow:auto;">
</div>
<script>
   tree=new
   dhtmlXTreeObject("treeboxbox_tree","100%","100%",0);
   tree.setImagePath("/i/javascript/.../dhtmlxSuite/dhtmlxtree/codebase/imgs/csh_bluebooks/");

   tree.setXMLAutoLoadingBehaviour("function");
   tree.setXMLAutoLoading(function(id){
      tree.loadXML("'||lv_url||'"+id+","+(new Date()).valueOf());
   });
   tree.loadXML("'||lv_url||'");

   tree.attachEvent("onOpenStart", function (id, state) {
      tree.setItemImage(id,''ajax-loader.gif'',''ajax-loader.gif'');
      return true
     });

   tree.attachEvent("onOpenEnd", function(id, state) {
      tree.setItemImage(id,''folderOpen.gif'',''folderClosed.gif'');
      });
</script>');
END;
 

The
highlighted piece of code is the substitution for my previous
integration technique (which worked but not a classy way to
do bussiness)…hope you like
it.

Thanks

Bharadwaz (Bharat) Pappu

From Peter Raganitsch,
Subject: Your Blog posting about mod_plsql flexible parameter

Hi Christian,

I found your blog posting and learned something about flexible
parameters.

Very interesting!

I just wanted to tell you, that most dhtmlx-components have a
method called preventIECaching(false); to turn
off sending of the additional parameter in IE.

brgds

Peter

Source Article from http://rokitta.blogspot.com/2009/12/follow-up-on-my-parameter-passing-post.html
Follow-up on my "Flexible Parameter Passing" post
http://rokitta.blogspot.com/2009/12/follow-up-on-my-parameter-passing-post.html
http://rokitta.blogspot.com/feeds/posts/default
Oracle & Apex Geekery
And another thing …

Oracle Application Express Forms Converter
A migration guide using the APEX conversion utility
Convert your Oracle Forms application to Oracle APEX successfully

by Douwe Pieter van den Bos

Packt Publishing 2009
ISBN 1847197760
ISBN 13 978-1-847197-76-4


 

Douwe Pieter van den Bos’s book “Oracle Application Express Forms Converter” covers all the aspects and tasks of your Forms conversion project, step-by-step, well explained and packed with screenshots.

The books tutorial-like organization guides you through the whole conversion process, from planning and preparation, through conversion and customizing your application, to deploying it.

If you are thinking of migrating your Oracle Forms and/or Reports to APEX, this book is a “must‑have” on your project teams reading list.

Nevertheless, you will need some knowledge of Oracle Forms and APEX to make this an easy read. Instead of going into details, in some cases you will find references to the appropriate (Oracle) documentation, which makes this book an easier read, but not the ultimate reference.

A very convenient, more practical aspect of the book is its size and number of pages, which seems to be optimized to fit laptop bags.

Source Article from http://rokitta.blogspot.com/2009/11/book-review-oracle-application-express.html
Book Review: Oracle Application Express Forms Converter
http://rokitta.blogspot.com/2009/11/book-review-oracle-application-express.html
http://rokitta.blogspot.com/feeds/posts/default
Oracle & Apex Geekery
And another thing …

Well, another post on browser behavior. Actually it’s about an Add-on. This time it’s about a bug I ran into using the Firebug (current version: 1.4.3). Firebug is very popular under Apex Developers, so I thought this might interest you.

Firebug actually works fine, but if you are using AJAX calls in your Apex application (or any other web application), you will notice that the AJAX call is made, response received, but not processed. I tried all the other browsers, no problems; just Firefox did not behave correctly. I had no idea. A post on Firefox Support Forum … another week of cluelessness … finally I got a friendly hint, directing me to this blog-post:

Looks like this problem is caused by Firebug and solved in the upcoming release 1.5 (currently alpha).

Update 5-nov-2009: Firebug Add-in update 1.4.4 is available. I installed the update and the mentioned problem does not occur anymore.

Source Article from http://rokitta.blogspot.com/2009/09/ajax-bug-in-firefox-caused-by-firebug.html
AJAX Bug in Firefox caused by Firebug 1.4
http://rokitta.blogspot.com/2009/09/ajax-bug-in-firefox-caused-by-firebug.html
http://rokitta.blogspot.com/feeds/posts/default
Oracle & Apex Geekery
And another thing …

The storm had now definitely abated, and what thunder there was now grumbled over more distant hills, like a man saying “And another thing…” twenty minutes after admitting he’s lost the argument.


Follow crokitta on Twitter

Source Article from http://rokitta.blogspot.com/2009/07/session-management-within-internet.html
Session management within Internet Explorer
http://rokitta.blogspot.com/2009/07/session-management-within-internet.html
http://rokitta.blogspot.com/feeds/posts/default
Oracle & Apex Geekery
And another thing …

url11 Apex is using its own syntax to pass URL parameters, called the f?p syntax. In fact, there is only one standard URL search-path parameter: “p”. “p” accepts a string build as:

App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly

I am not going to explain all the individual arguments. They should be familiar to most APEX developers. I would like to discuss the itemNames:itemValues argument pair, which allows us to pass custom parameters to our page calls.
When using external libraries, like the “Yahoo! User Interface Library” (YUI), JQuery or (in my case) DHTMLX, you might find, that the f?p syntax is not always usable with these libraries. In some cases, URL’s get assembled by those libraries dynamically, expecting the standard search-path syntax ( ?P1=V1&…&Pn=Vn).

I came across this problem when using the xmlLoad functionality in the DHTMLX library. This method adds an additional parameter to the given XML-source URL (a random value to prevent caching, I believe; strange enough only when using IE and not in other browsers).

One could modify these libraries to work with the f?p syntax, and then make the modification every time you will receive an upgrade of the lib. Anyway, you probably loose support (if you have). I rather leave the lib untouched and add functionality to APEX to enable standard search-path syntax.

The Apex itemNames:itemValues argument pair allows us to add custom parameters in a very flexible way. But wait. “Flexible”? There already is a “flexible” way to pass parameters using PL/SQL Gateway (the very fundament of Apex http calls): “Flexible Parameter Passing”.

Usually a PL/SQL Gateway http call has to provide exactly those parameter names of the database procedure handling the call (except those with default values of course). This method is called “Parameter Passing by Name”. Not passing a mandatory parameter will result in an error:

{proc}: SIGNATURE (parameter names) MISMATCHVARIABLES IN FORM NOT IN PROCEDURE:
NON-DEFAULT VARIABLES IN PROCEDURE NOT IN FORM: {param}


Passing a parameter that is not in the procedures parameter list will return a different error (obviously):


{proc}: SIGNATURE (parameter names) MISMATCHVARIABLES IN FORM NOT IN PROCEDURE: {param}
NON-DEFAULT VARIABLES IN PROCEDURE NOT IN FORM:

With the PL/SQL Gateway’s Flexible Parameter Passing mechanism one can pass any number of parameters to a procedure. The called procedure has to be defined with a specific interface to handle these calls:

procedure [proc_name] is(name_array IN [array_type],value_array IN array_type])

Example:

If you send the following URL:
http://www.acme.com/pls/myDAD/!scott.my_proc?x=john&y=10&z=doe

The exclamation mark prefix (!) instructs the PL/SQL Gateway to use flexible parameter passing. It invokes procedure scott.myproc and passes it the following two arguments:


name_array ==> (‘x’, ‘y’, ‘z’)
values_array ==> (‘john’, ’10’, ‘doe’)

Using Flexible Parameter Passing, I wrote a new “f” procedure supporting:

  • the f?p syntax arguments
  • passing of (custom) parameters the standard way

I called this procedure “ff” (flexible “f”). Here is the code:

CREATE OR REPLACE PROCEDURE apex_030200.ff (name_array IN OWA.vc_arr, value_array IN OWA.vc_arr) IS
   /******************************************************************************
   NAME:       ff
   PURPOSE:    "flexible parameter passing" enabled APEX f function

   num_entries   The number of name_value pairs in the query string
   name_array    The names from the query string (indexed from 1) in the
   order submitted.
   value_array   The values from the query string (indexed from 1) in the
   order submitted.
   reserved      Not used. It is reserved for future use.

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        15-7-2009   C. Rokitta       Created this procedure.

   NOTE:  All existing f?p syntax parameters are mapped into f?p format
   All other name/value pairs are translated into APEX f call
   syntax itemNames:itemValues:

   APEX f?p syntax:
   ----------------
   f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly

   This procedures ff syntax:
   ---------------------------
   !ff?App=1&Page=2&Session=12345&...&P1=V1&P2=V2 ... &Pn=Vn

   results in:

   f?p=1:2:12345::::P1,P2,...,Pn:V1,V2,...,Vn:

   ******************************************************************************
   C. Rokitta - christian[at]rokitta.nl
   ******************************************************************************/

   TYPE f_param_array IS TABLE OF VARCHAR2 (32767)
                            INDEX BY VARCHAR2 (20);

   v_f_p_arr   f_param_array;
   v_f_p       VARCHAR2 (32767);
   v_inames    VARCHAR2 (32767);
   v_ivalues   VARCHAR2 (32767);
BEGIN
   v_f_p_arr ('app') := '';
   v_f_p_arr ('page') := '';
   v_f_p_arr ('session') := '';
   v_f_p_arr ('request') := '';
   v_f_p_arr ('debug') := '';
   v_f_p_arr ('clearcache') := '';
   v_f_p_arr ('printerfriendly') := '';

   FOR i IN 1 .. name_array.COUNT LOOP
      IF LOWER (name_array (i)) IN
            ('app', 'page', 'session', 'request', 'debug', 'clearcache', 'printerfriendly') THEN
         v_f_p_arr (LOWER (name_array (i))) := value_array (i);
      ELSE
         IF LENGTH (v_inames) > 0 THEN
            v_inames := v_inames || ',';
            v_ivalues := v_ivalues || ',';
         END IF;

         v_inames := v_inames || name_array (i);
         v_ivalues := v_ivalues || value_array (i);
      END IF;
   END LOOP;

   f (   v_f_p_arr ('app')
      || ':'
      || v_f_p_arr ('page')
      || ':'
      || v_f_p_arr ('session')
      || ':'
      || v_f_p_arr ('request')
      || ':'
      || v_f_p_arr ('debug')
      || ':'
      || v_f_p_arr ('clearcache')
      || ':'
      || v_inames
      || ':'
      || v_ivalues
      || ':'
      || v_f_p_arr ('printerfriendly'));
EXCEPTION
   WHEN OTHERS THEN
      RAISE;
END ff;
/


This procedure is a wrapper/translater for the Apex f procedure. All f?p syntax arguments can be posted as named parameter (and then mapped into the f?p syntax) and all non f?p arguments will be placed into the itemNames:itemValues arguments. So basically the ff procedure does exact the same as the f procedure, but then with standard URL search-path syntax.

You have might noticed, that I created the procedure in the APEX schema. I thought this is a natural place to put, and I don’t have to worry about access right for the f procedure. Grant execute rights to PUBLIC and create a PUBLIC SYNONYM for “ff” , just like it is done for “f”.

When testing “ff” I found, that I could not call my new procedure through the apex DAD. The DAD is secured to only allow certain (Apex) procedures to be called. Luckily one can register his own procedure in Apex, by customizing the Apex function WWV_FLOW_EPG_INCLUDE_MOD_LOCAL:

CREATE OR REPLACE FUNCTION wwv_flow_epg_include_mod_local (procedure_name IN VARCHAR2)
   RETURN BOOLEAN IS
BEGIN
   --return false; -- remove this statement when you modify this function
   --
   -- Administrator note: the procedure_name input parameter may be in the format:
   --
   --    procedure
   --    schema.procedure
   --    package.procedure
   --    schema.package.procedure
   --
   -- If the expected input parameter is a procedure name only, the IN list code shown below
   -- can be modified to itemize the expected procedure names. Otherwise you must parse the
   -- procedure_name parameter and replace the simple code below with code that will evaluate
   -- all of the cases listed above.
   --
   IF UPPER (procedure_name) IN ('FF') THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END wwv_flow_epg_include_mod_local;
/

Source Article from http://rokitta.blogspot.com/2009/07/apex-fp-syntax-with-flexible-parameter.html
Apex f?p syntax with Flexible Parameter Passing enabled
http://rokitta.blogspot.com/2009/07/apex-fp-syntax-with-flexible-parameter.html
http://rokitta.blogspot.com/feeds/posts/default
Oracle & Apex Geekery
And another thing …

In my current assignment I face users who would like to have their data and graphs in PowerPoint. How do you do that with Oracle tools when you do not want to spend a lot of money on a BI Publisher license? The answer is simple, by using Apache POI , an Java open source library for MS Office integration.
With the examples supplied with the software I was able to generate a prototype servlet in on afternoon! The result was a servlet that accepts a title and (the URL to) an image which it places in a PowerPoint slide. With some extra hours of effort I expanded the prototype to accept an array of URLs and titles. In combination with an Apex that generates parameter driven images the user can fill his shopping basket with images and generate a PowerPoint presentation by pressing a button.
When you want to generate PowerPoint output, do have a look at Apache POI!

Happy Apexing

Source Article from http://dickdral.blogspot.com/2008/11/power-of-apache-poi.html

As I am starting my own business I am building a new site (www.detora.nl). My hosting provider provides a web site with static files, PHP and MySQL. The last two are not my cup of tea, so I decided to create a site with static HTML files for a start. I took my ASCII editor and set out to create the files. A large part of the pages show more or less then same on every page. Do I have to copy this theme on each page? How do I cope with changes in the design, that cann’t be implemented in CSS?

There must be tools available for this. But I am not in to Dream Weaver, Frontpage and I don’t want to invest time in a new tool if I don’t have to. So I decide to use Application Express to build the site. And it works!

I have implemented the basic theme in a page template. Most of the site is formatted using CSS so I have created a very basic templates for a Region (just showing a title and the HTML content) and some list template for the various menus througout the site. The general menus are placed on page zero.

This way I had my site up and running quite fast. There is only one problem. It’s run in Application Express and it should be static files. This can be solved by saving the webpages to files (use Show source and save the content of that!) and changing the links. Normally a Apex link has the format f?p=&APP_ID.:200:&APP_SESSION.. This is not very useful, because you cannot extract anything like a filename from this. So the links I created use application and page aliases resulting in a link like f?p=detora:about_detora:&APP_SESSION.. If you clip the part around the page alias and augment it with .html you have a perfectly usable link! Make sure you save the pages under there page alias name!

Off course this work is tedious and error prone, so I would not be an IT professional if I would not set out to automate this. It must be possible to write a PL/SQL procedure that reads the pages from the Apex dictionary, accesses each page through UTL_HTTP, replaces the links and writes the static file for the page. To be continued…

One nice thing about using Apex is the possibility to show objects conditionally. I use this feature more and more. It can be used for example to show an “under construction” image on pages that have not been filled yet:

  • create a region on page 0 containing a nice “Under construction” image
  • put a condition on it of type “NOT exists (SQL query returns no rows)”
  • add a query to this condition that returns the regions of the current page:

select p.name, r.plug_name
from flows_020200.wwv_flow_page_plugs r
, flows_020200.wwv_flow_steps p
where p.flow_id = &APP_ID.
and r.flow_id = &APP_ID.
and p.id = r.page_id
and p.id = &APP_PAGE_ID.

In this way the “under construction” region will only be shown on a page that does not have any regions.Just make sure you create empty pages for all the links on the site!

Have fun creating your own site with Apex.

Source Article from http://dickdral.blogspot.com/2008/06/pages-under-construction.html

Laltely I have done some projects that needed the implementation of Oracle pipelined functions. You would typically use pipelined functions for performance or flexibility (for example when using dynamic SQL).
It works great once you have got the job done, but before that there is a lot of typing involved. I myself always have to Google for an example of which objects to create and how it should be done. Then at least an hour further I have got my pipelined function running. This, as a real developer, made me wonder whether is would be possible to automate this process. And after some hours of thinking, I found a solution.

Each pipelined function is based on a table like structure, so we can start with a table or a view, say the known table DEPT. With the definition of this table we can build the rowtype object.


create or replace type dept_rowtype
as object
( deptno number(2,0)
, dname varchar2(14)
, loc varchar2(13)
);

On the rowtype object we need to define a tabletype object, that easy (and short):


create or replace type dept_table_type as table of dept_rowtype;

Then we need to write our function, that is declared returning the tabletype by in fact returning a lot of rowtypes. This is, even for a small table like DEPT quite a lot of typing.


create or replace function dept_pf return dept_table_type pipelined is
cursor c_cur is
select deptno
, dname
, loc
from dept;
r_rec dept_rowtype;
l_deptno number(2,0) := null;
l_dname varchar2(14) := null;
l_loc varchar2(13) := null;
begin
open c_cur;
loop
fetch c_cur into l_deptno
, l_dname
, l_loc
;
exit when c_cur%notfound;
r_rec := new dept_rowtype ( l_deptno
, l_dname
, l_loc
);
pipe row (r_rec);
end loop;
return;
end;
/

At last we need a view to show the data:


create or replace view dept_view as select * from table(dept_pf());

This code is all be generated automatically on the basis of a table definition. You can reach the Apex application to generate all this code with this button:

It’s really easy and fast!
Enter the name of the table, which is used to generate the names of the other objects. Then go to the Object Browser and download the column definitions. Cut the Name and Datatype columns in Excel and Paste them in Column Definition. Now hit the Generate button and presto you have got your scripts. Run them in SQL*Plus or TOAD and you have got your pipelined function working within a few minutes. Now you can query the view based on the function.
Off course you would like to perform some changes on the function itself, because you did not do all this just to get the same result as querying the table directly ;-).
But you did not have to do all the typing,

Hope you enjoy the generator, let me know what you think of it!

Dick Dral

Source Article from http://dickdral.blogspot.com/2008/07/automatic-generation-of-oracle.html