Invalid set of rows requested, the source data of the report has been modified. - Explanation

When I first started using APEX I occasionally got the following pagination error message: Invalid set of rows requested, the source data of the report has been modified. reset pagination

At first I had no clue what was causing the error but after a while I finally figured it out. The best way to describe the error is to see exactly what is happening. Full example is here

Create a report

1
2
3
SELECT *
FROM emp
WHERE UPPER (ename) LIKE '%' || UPPER (:P800_LETTER) || '%'

When creating the report, change the number of rows from 15 to 5 for this example. This is important for this demonstration!

Now create a page item called P800_LETTER and a submit button who submits and redirect to P800.

If things went well you’d have a report that looks like this:

To illustrate the problem click on the Next pagination link so that we are seeing rows 6~10.
Now, in the text box enter the letter I (this will restrict to 4 rows) and click submit. You should see the following error message:

To summarize what just happened, you initially requested a report with 14 rows and told APEX to display 5 at a time. When you clicked the Next pagination button you told APEX for the report you’re currently looking at display rows 6~10. So now, if you were to refresh the page APEX would continue to show rows 6~10. When you filtered the report, by restricting to employees with the letter I in their name, you get a result with only 4 rows however APEX is still trying to display rows 6~10. I hope you now see the problem with this.

So now that we’ve demonstrated the problem, how do you fix it? Well there’s several ways. To solve our current issue, on P800 in the submit branch we could check the reset pagination for this page. This means that each time you submit the page APEX will set the display rows back to 1~5.

There are also other ways to reset the pagination automatically. You can reset pagination in the URL: In the Clear Cache section you can enter RP to clear the page’s cache. Please see the APEX documentation for more information. There is also a page process to reset the pagination. This allows you to also set the scope of the current page, multiple pages, or all pages.

I know the issue has come up to reset the pagination for an individual report rather than the entire page. I currently don’t know of any method, however if you do please post!

q Function: Escape Single Quotes

Last year Scott Spendolini gave our company an excellent 3 day training session on APEX. He also showed us a very nifty function (new in 10g I think) to help not having to escape single quotes when defining strings. This is not APEX specific, however it will help you if you ever have to write a block of PL/SQL to return an SQL statement for reports.

Instead of writing out a long description here’s an example:

DECLARE
v_sql VARCHAR2 (255);
v_result VARCHAR2 (255);
BEGIN
v_sql := ‘select ‘’hello’’ into :a from dual’;

EXECUTE IMMEDIATE v_sql
INTO v_result;

DBMS_OUTPUT.put_line (v_result);
END;
Notice how I had to put 2 single quotes around “Hello” to escape the single quote characters?

Now using the q function I don’t need to do that:

DECLARE
v_sql VARCHAR2 (255);
v_result varchar2(255);
BEGIN
v_sql := q’!select ‘hello’ into :a from dual !’;

EXECUTE IMMEDIATE v_sql
INTO v_result;

DBMS_OUTPUT.put_line (v_result);
END;
Notice now how “Hello” is wrapped as it would appear if it were not in variable definition function?

This can save you a lot of time by avoiding having to escape single quotes in strings!

Update:  q Function Inside a q Function

Test Data in Production Applications

Hi,

Well this isn’t an APEX specific post but I thought it should be mentioned either way. It raises a good point about keeping test data out of production.

I got the following email from a colleague today:

I’m a bit of a golf fan, and I like to read up on current events, standings, player profiles, etc. My favorite site to do this is www.pgatour.com

I was recently searching for a player with the surname of Fischer on the website. I had a good laugh when the search results came back. Take a look at the 6th name in the list in the attached image…“

Here’s the screenshot:

  • Martin

"Application ID Reserved" - Fix

When you upgrade an application do you ever get the following Application ID Reserved error message?

Even if you delete your application and try to re-import using the same application ID you’ll get the message.

To view “reserved” application ids you can run the following query (please note you may need to be a dba user):

1
2
select *
from flows_030100.wwv_flows_reserved;

In order to fix this problem you just need to delete the application id from that table:

1
2
3
delete
from flows_030100.wwv_flows_reserved
where id = :p_app_id; -- Your application ID that is locked

APEX Report with checkboxes (advanced).

Note: This solution was updated to account for APEX’s new features and APIs. See the updated post here: http://www.talkapex.com/2015/09/report-with-checkboxes-update.html

A colleague of mine wanted to build a report with check boxes. Once the user selected all the rows, they would click submit and the application would process the rows. Sounds pretty simple and straight forward however he did have some extra requirements:

  • The report is an interactive report
  • There may be up to 10,000 records in the report
  • When the user “scrolls” through the report (i.e. uses pagination), if they checked off a box it should remain checked the entire time (i.e. if they check an row in the 1st 15 rows, then view rows 16~30, then go back to rows 1~15 it should remain checked)

So here’s the example on how to do it. The working example can be found here: http://apex.oracle.com/pls/otn/f?p=20195:500

Create an application item F_EMPNO_LIST. Note you can use a page item as well…

Setup IR:


SELECT apex_item.checkbox (1,
empno,
‘onchange=”spCheckChange(this);”‘,
:f_empno_list,
‘:’
) checkbox,
empno, ename, job
FROM emp

Once the report is setup set the row display to 5 (you’ll need it for this example)

  • Add an HTML region and add the following code: (Note: the jQuery call is not needed… for now)




    CHECKBOX List:


    &F_EMPNO_LIST.


    Now create an application process (on Demand) called: CHECKBOX_CHANGE


    – Application Process: CHECKBOX_CHANGE
    – On Demand…

    DECLARE
    v_item_val NUMBER := apex_application.g_x01;
    v_checked_flag VARCHAR2 (1) := apex_application.g_x02;
    BEGIN
    IF v_checked_flag = ‘Y’ THEN
    – Add to the list
    IF :f_empno_list IS NULL THEN
    :f_empno_list := ‘:’ || v_item_val || ‘:’;
    ELSE
    :f_empno_list := :f_empno_list || v_item_val || ‘:’;
    END IF;
    ELSE
    – Remove from the list
    :f_empno_list := REPLACE (:f_empno_list, ‘:’ || v_item_val || ‘:’, ‘:’);
    END IF;

    – Just for testing
    HTP.p (:f_empno_list);
    END;

    • On the post page create a query to view data (you can process how you need/want)

      select *
      from emp
      where instr(:F_EMPNO_LIST, ‘:’ || empno || ‘:’) > 0

      Please note there may be better/faster ways to implement the SQL code etc.

    Here are some links on the x01 code that I used:
    http://carlback.blogspot.com/2008/03/new-stuff-2-x01-and-friends.html and http://carlback.blogspot.com/2008/04/new-stuff-q.html

How to access images in APEX on Linux

Hi,

Dietmar has a great post on where the images are located in APEX using webdav: http://daust.blogspot.com/2006/03/where-are-images-of-application.html He also covers how to access them via FTP!

I recently switched from Windows to Linux and was looking for a quick way to access the files as I would with Windows.

Using KDE and Konqueror, you can access them very easily. Use the following in the URL: webdav://<url>

So it would be: webdav://127.0.0.1:8080/i

Log in with system and you should be able to access everything!

Martin

jQuery Calendar and Application Date Format

Hi,

Last week Roel had a posting about jQuery’s Date Picker: http://roelhartman.blogspot.com/2008/12/how-to-replace-default-apex-calendar.html Definitely a great example for people who are looking for an alternative to APEX’s date picker.

We do use the jQuery calendar and have some interesting standards around it (I’ll try to blog about this another day). The most important one, and easiest to implement, is the date format standards.

Javascript/jQuery and Oracle use different date formats. If you are going to use the jQuery date picker I strongly suggest that you standardize your date formats so that you won’t have conflicting results down the road. Here’s what we do:

We have 2 application level items:

F_DATE_FORMAT: is used for the application level date format
F_JQUERY_DATE_FORMAT: format to display the date the same as f_date_format

Both these items are set at login time and the values are defined in our database at the client level. You have to make sure that the date formats produce the same results.

In Roel’s example he uses the jQuery on load function. For the date format just use &F_JQUERY_DATE_FORMAT. instead and this will load set the jQuery date formats accordingly.

Here’s a listing of jQuery’s date formats: http://docs.jquery.com/UI/Datepicker/%24.datepicker.formatDate

And Oracle’s date formats: http://www.techonthenet.com/oracle/functions/to_date.php

Hope this helps,

M

How to list APEX Dictionary views using SQL

Hi,

I hope this isn’t old news but it seems that whenever someone talks about the APEX dictionary they talk about going into the development application and viewing them there. You can also access the dictionary using SQL. Here’s how:


select *
from apex_dictionary
where column_id = 0

or


select distinct apex_view_name
from apex_dictionary

Of course you can remove the “where column_id=0” portion and get a list of the all the columns for the views etc.

CSS Specificity

Most APEX developers will eventually get asked to brand their application or “make it look nice”. If you are like myself, and are not a graphic artist, this can be quite a challenge since it is an art to make a web pages look really good.

I’ve had to do read up on it over the past few months. I finally read an article on CSS specificity (try say that 5 times in a row :-). CSS Specificity is like putting a score or weight on each CSS definition so you know which ones will “override” other definitions.

Here’s the article: http://www.smashingmagazine.com/2007/07/27/css-specificity-things-you-should-know/

There’s also a link to the CSS specificity calculator: http://www.rebelinblue.com/specificity.php

Manually Creating a Tabular Form

When I first started developing in APEX (back when it was called HTMLDB) I had a requirement for a tabular form. I tried to use the standard tabular forms but it was very limited and I couldn’t customize it to meet my requirements. I had poked around on the APEX forum, but wasn’t able to find a reasonable solution.

After several iterations, I have come up with a process that works extremely well. It is slightly labor intensive but it has met all the requirements each time. Please note that Patrick Wolf, http://www.inside-oracle-apex.com/, has developed an open source framework to handle customized tabular forms. Though it is very good it does not allow for complete access. Marcie Young did a presentation at ODTUG 2008 which outlined a very similar method, this example is taking it a step further. A working example is available here: http://apex.oracle.com/pls/otn/f?p=20195:200

The process below will not only build a customized tabular form, but also handle the errors etc.

Overview
Here’s an overview of the overall methodology

  • Create 2 collections. One will handle data: DATA_COLLECTION and one will handle the errors: ERROR_COLLECTION.
  • The DATA_COLLECTION will be loaded on the first viewing of the page and will only be refreshed from the database when the changes have been sent to the database.
  • The ERROR_COLLECTION will contain errors specific for the corresponding entry in the DATA_COLLECTION. We could keep this in the same collection, but I like to keep them separate. It makes things easier if we need to add or remove columns.
  • In the ERROR_COLLECTION I keep column 50 reserved for the row error. An example of when you’d need a row error is when you have a start and end date and the end date is before the start date.
  • This application will allow users to modify the emp.ename and emp.salary fields
  • In the data collection I keep column 1 reserved for the sequence id (seq_id). Note though I won’t use it in this example, it has come in handy (especially when hiding/delete rows in the front end then submitting the page)

Getting Started

Step 1: Create a PL/SQL Process On Load Before Header called Create Collection


DECLARE
BEGIN
IF NVL (:p200_reload_flag, ‘N’) = ‘N’
THEN
– IF error collection exists, delete
IF apex_collection.collection_exists
(p_collection_name => ‘ERROR_COLLECTION’)
THEN
apex_collection.delete_collection
(p_collection_name => ‘ERROR_COLLECTION’);
END IF;

-- Create New Collection
apex_collection.create_or_truncate_collection ('DATA_COLLECTION');

FOR rec IN (SELECT   e.empno, e.ename, e.sal
                FROM emp e
            ORDER BY e.ename)
LOOP
   apex_collection.add_member (p_collection_name      => 'DATA_COLLECTION',
                               p_generate_md5         => 'NO',
                               p_c002                 => rec.empno,
                               p_c003                 => rec.ename,
                               p_c004                 => TO_CHAR
                                                                (rec.sal),
                               -- Remember the collection is only text
                               p_c049                 => 'Y',
                               -- Modifiable Flag
                               p_c050                 => 'U'
                              -- SQL Action (Insert, Update, Delete)
                              );
END LOOP;


END IF;

– Create Extra rows (if we wanted to add a new employee
FOR i IN 1 .. NVL (:p200_num_extra_rows, 0)
LOOP
apex_collection.add_member (p_collection_name => ‘DATA_COLLECTION’,
p_generate_md5 => ‘NO’,
p_c002 => -1,
– use negative numbers for new employees
p_c049 => ‘Y’,
p_c050 => ‘I’
);
END LOOP;

– Insert seq_id
FOR rec IN (SELECT ac.seq_id
FROM apex_collections ac
WHERE ac.collection_name = ‘DATA_COLLECTION’)
LOOP
apex_collection.update_member_attribute
(p_collection_name => ‘DATA_COLLECTION’,
p_seq => rec.seq_id,
p_attr_number => 1,
p_attr_value => rec.seq_id
);
END LOOP;
END;

Step 2: Create a Report / SQL Report region for our custom tabular form called Employee Data


SELECT
– Notice how I’m keeping the idx value the same as the column value in the collection. This helps to keep things organized
– I also apply an id to each entry
– I append the error value to the empname and sal
– The Seq_id. Usefull when hiding rows (for delete) and then submitting from
apex_item.hidden(1,x.seq_id, null, x.seq_id || ‘_seq_id’) ||
– The Primary Key of the column
apex_item.hidden(2, x.empno, null, x.seq_id || ‘_empno_id’) || x.empno empno,
– Employee Name
apex_item.text(3,x.empname,null, null, null, x.seq_id || ‘_empname_id’) || err.empname employee_name,
– Employee Salary
apex_item.text(4,x.sal, null, null, null, x.seq_id || ‘_sal_id’) || err.sal ||
– Store the sql action type as well.
apex_item.hidden(50,x.sql_action_typ, null, x.seq_id || ‘_sql_action_typ_id’) sal,
– Last but not least the row error
err.row_error
FROM (SELECT ac.c001 seq_id,
ac.c002 empno,
ac.c003 empname,
ac.c004 sal,
ac.c049 modifiable_flag,
ac.c050 sql_action_typ
FROM apex_collections ac
WHERE ac.collection_name = ‘DATA_COLLECTION’
ORDER BY ac.seq_id) x,

-- Error Collection
(SELECT   ac.seq_id seq_id,
          ac.c002 empno,
          ac.c003 empname,
          ac.c004 sal,
          ac.c050 row_error -- Useful when individual data is correct, however the row of data is not. Ex: start/end dates
 FROM     apex_collections ac
 WHERE    ac.collection_name = 'ERROR_COLLECTION'
 ORDER BY ac.seq_id) err

WHERE x.seq_id = err.seq_id(+)

Step 3: Add Region items and buttons

All items/buttons should be added to the report region
Create Buttons:
ADD, submit page. Branch to &APP_PAGE_ID.
SUBMIT, submit page. Branch to 201
Note: Page 201 is a simple sql report for


SELECT *
FROM EMP

Create Hidden and Protected Items:
P200_DISPLAY_ROW_ERROR_FLAG

  • Source value or expression: N
  • Comment: Used to determine if the error column should be displayed
    P200_NUM_EXTRA_ROWS
  • Source value or expression: 1
  • Comment: Number of extra rows to add to the tabular form
    P200_RELOAD_FLAG
  • Comment: If Y then we won’t refresh the collection with database values

Computations (After Submit)
P200_NUM_EXTRA_ROWS
Static: 1
Condition: Request = ADD

P200_NUM_EXTRA_ROWS
Static: 0
Condition: Request != ADD

P200_RELOAD_FLAG
Static: Y
Condition: None

P200_DISPLAY_ROW_ERROR_FLAG
Static: N
Condition: None

Step 4: Store Collection
After submit, this will store the data from the form into the collection. No data validation is performed at this point

Process: Store Collection
Type: PL/SQL Anonymous Block
Processing Point: On Submit: Before Computation and Validations
Source:


DECLARE
BEGIN
FOR i IN 1 .. apex_application.g_f01.COUNT LOOP
apex_collection.update_member (p_collection_name => ‘DATA_COLLECTION’,
– I know some of you are still wondering why were are still wondering why I stored the seq_id as a collection attribute. This is why. If you had hidden the row (i.e. let the user “delete” it) then it would not show up on this and your collection synchronization wouldn’t be correct.
p_seq => apex_application.g_f01 (i), – Sequence ID
p_c001 => apex_application.g_f01 (i), – Sequence ID
p_c002 => apex_application.g_f02 (i), – Empno
p_c003 => apex_application.g_f03 (i), – Empname
p_c004 => apex_application.g_f04 (i), – Sal
p_c049 => ‘Y’, – Modifiable Flag
p_c050 => UPPER(apex_application.g_f50 (i))
);
END LOOP;
END;

Step 5: Validation

Type: Page Level Validation
Type: PL/SQL - Function returning Error Text
Name: Validate Collection
Condition: When Button Pressed - SUBMIT
Validation Expression:


DECLARE
v_err_msg VARCHAR2 (255);
BEGIN
– IF error collection exists, truncate. Else Create
IF apex_collection.collection_exists
(p_collection_name => ‘ERROR_COLLECTION’)
THEN
apex_collection.truncate_collection
(p_collection_name => ‘ERROR_COLLECTION’);
ELSE
– Create Error Collection
apex_collection.create_or_truncate_collection (‘ERROR_COLLECTION’);
END IF;

– Basic Check. Make sure the emp name is more than 5 chars long
FOR x IN (SELECT ac.c003 empname, ac.c004 sal, ac.seq_id
FROM apex_collections ac
WHERE ac.collection_name = ‘DATA_COLLECTION’)
LOOP
– Always add a blank error
apex_collection.add_member (p_collection_name => ‘ERROR_COLLECTION’);

IF LENGTH (x.empname) < 5
THEN
   apex_collection.update_member
      (p_collection_name      => 'ERROR_COLLECTION',
       p_seq                  => x.seq_id,
       p_c003                 => '

Name must be 5 Chars
);
v_err_msg := ‘Error Occured’;
END IF;

-- Add a "row level" check for demo purposes
IF LENGTH (x.empname) = LENGTH (x.sal)
THEN
   apex_collection.update_member
       (p_collection_name      => 'ERROR_COLLECTION',
        p_seq                  => x.seq_id,
        p_c050                 => '

Row Level Error
);
apex_util.set_session_state (p_name => ‘P200_DISPLAY_ROW_ERROR_FLAG’,
p_value => ‘Y’
);
v_err_msg := ‘Error Occured’;
END IF;
END LOOP;

RETURN v_err_msg;
END;

Step 6: Finishing it off

As you notice there’s a column called: row_error. Set the condition where item: P200_DISPLAY_ROW_ERROR_FLAG = Y.