Monday, 30 June 2008

Sorting with ROWNUM

Creating a query and including the ROWNUM will work only for those reports, with disabled column sorting. Once you enable the column sorting, the ROWNUM will not show the right order. This is because ApEx does the sorting after determining the ROWNUM.

A simple trick can help you to overcome this issue. Just escape the ApEx internal #ROWNUM# like this:

SELECT '#ROWNUM#' SEQUENCE, empno, ename, sal
FROM emp;

and it will give you the right ROWNUM regardless of your sorting.

You can see that working in this example in my Demo Application.

Sunday, 29 June 2008

Pipelined Functions

The question in the forum was:

I need to allow my users to type in a list of names and return the names that are not in a table.

1. User types [ Tom Joe Bob MIKE ] into a Text Area item named :P2_NAME_CHECK

2. A table named CUST contains two rows with TOM and Bob in the
CUST_NAME column.

3. A report returns two rows JOE and MIKE.

Usually, it is oposite. You want to show the list of users that match the criteria.

The solution for this problem is quite easy if you know how to use pipelined functions. This is a case where the pipelined functions shine. I created an example in my Demo Application showing how this can be done.

Basically, the pipelined function will create a row for all substrings returning no rows from the emp table, showing those substrings as rows.

Textfield Item - Submitting the Page

Normaly, if there are page items of type "Text Field" they will not submit the page unless you change them to "Text Field (always submits page when Enter pressed)". If you have only one item on you page of type "Text Field" this behaviour changes and the page will always submit if you press "Enter". Sometimes, you don't want this to happen. Especially, if you have some javascript attached to your field. To avoid that you can use the following trick:

1. create another item on you page of type "Text Field",

2. in the "HTML Form Element Attributes" of that item put


The result is that the second item will not be displayed and the first item will not submit on pressing "Enter".

Two Tabular Forms on one Page

Recently there have been several discussions in the forum regarding multiple tabular forms on one page. I thought it is easy if you know how to create one tabular form manually, you will be able to do it for any other number of those forms. However, many participants of the forum do not share my opinion. This is why I decided to create a very basic example showing how that works. You will find it in my Demo Application together with the required code. If you want to see all the details, you may apply for an account following the instructions on the login page of my Demo Application.

Have fun.