ORA-06550 “‘WWV_FLOW_API#G_ID_OFFSET’ must be declared” importing workspace into APEX

Environment: Oracle 11gR2, Application Express

I was trying to export a workspace from Oracle 10g to Oracle 11g (both with APEX installed) when I received the following error:


Execution of the statement was unsuccessful. ORA-06550: line 3, column 4: PLS-00201: identifier

‘WWV_FLOW_API#G_ID_OFFSET’ must be declared ORA-06550: line 3, column 4: PL/SQL: Statement ignored


wwv_flow_api#g_id_offset := 0;

wwv_flow_hint.g_schema := ‘MATTHIASH’;




I checked the OTN forum and I replied to this post. Apparently, it’s a known bug in APEX The bug number is 16846844. For those with access to My Oracle Support, there’s a patch out with patch ID 16781538. This bug fix will be included in the forthcoming APEX 4.2.3 patch set.

However, there is a workaround. Directly executing the sql file from SQL*Plus connected as the APEX schema owner (APEX_040200) works fine!







APEX 4.2.2 released!

Oracle released a new minor version of Application Express: 4.2.2, or to be complete:

Those who are already on version 4.2.x can download a patch from My Oracle Support (if you have a support contract). The patch number is 16277995. Other users can download the full version here.

Interesting documents to read are the known issues and the list of bugs fixed with this release. Unfortunately, the problem with APEX_LDAP.AUTHENTICATE and accented user names (see my earlier blog post) is not mentioned in the list of bugs.




APEX Listener 2.0.1 released

This has escaped my attention: since the beginning of March there is a new bug fix version of the APEX Listener 2.0 available for download. The latest version is now 2.0.1 and it contains the following bug fixes:

  • Support the handling of responses around 32KB in size on Oracle 10.2 database
  • Handling of large identifiers that prevented some RESTful Services being found or dispatched
  • Change the behaviour of BDB storage, each restart of listener gets it’s own folder for BDB storage, allowing multiple (e.g. clustered) nodes to share a single configuration folder, without getting an error
  • JSON streams not being parsed as UTF-8 encoded text
  • Dispatching of RESTful Services was causing a 404 not found exception when more than one database was configured
  • Improved the handling of Media Resources that are generated from large XMLType values
  • Fixed 503 Service Unavailable statuses generated by the PL/SQL Gateway to be mapped to a 404 Not Found status
  • Improve the error message generated when the icap.server/icap.port settings are misconfigured
  • PDF printing not handling results & XSL that totalled over 32766 bytes in size
  • RESTful Service Media Resource source type, that was preventing CLOBs over 4000 bytes in size from being returned without error
  • Handling of the icap.server and icap.port configuration settings
  • Virus scanning of empty and very small files
  • Debug.debugger configuration setting not working properly
  • Class loading in Standalone Mode was preventing FOP (PDF) printing working
  • Reporting of connection pool usage statistics

It’s cool that PDF printing now supports results that are over 32 kB in size, and also that there is now reporting available on the connection pool usage.

Maybe it’s time to upgrade the APEX Listener on our production environment :-)



ORA-02291: integrity constraint (APEX_040200.WWV_FLOW_STEP_UI_FK) violated when exporting/importing single page on APEX 4.2.1

Environment: Application Express, Oracle database

We recently upgraded from APEX 4.1.1 to 4.2.1. The upgrade went fine, no problems were seen in the installation log files.

However, when I tried to export a single application page from one APEX 4.2.1 installation and import it into another APEX 4.2.1 installation, I received the following error:

ORA-20001: Error creating page name=”page01″ ORA-02291: integrity constraint APEX_040200.WWV_FLOW_STEP_UI_FK) violated – parent key not found

The application has the same application ID and workspace name. Individual page exports used to work fine on APEX versions prior to 4.2.

I searched the OTN forum and the Oracle support site and I found that this is a know issue with APEX 4.2.1.

I have you access to Oracle support, the problem is registered as bug 15922342.

This is from the bug report:

After upgrading the APEX instances on dev and prod to APEX 4.2 the single 
page export/import from dev into prod doesn’t work anymore. It fails with 
ORA-2291: integrity constraint (APEX_040200.WWV_FLOW_STEP_UI_FK) violated – 
parent key not found

Explain why this is not a bug:
Single component export/import is only supported for application which are 
identical on the source and target system. It relies on the fact that other 
referenced components (for example user interface, templates, …) use the 
same IDs on body systems.

Because of the APEX 4.2 upgrade, that’s not the case anymore. As part of the 
upgrade, several new components had to be created on the fly which do now 
have different IDs on dev and prod. That’s why the single component 
export/import doesn’t work anymore.

If the single component export/import is used to patch applications, it’s 
recommended to do a full export of the app on dev and import it into prod 
after upgrading to a newer version of APEX to make sure that body systems are 
using the same component IDs.”

There are 2 workarounds for this problem:

1) Find an existing ID in the table wwv_flow_user_interfaces on the database where you want to import. Update the export file, so you replace the ID in ‘p_user_interface_id => <id number>’ to the existing number in wwv_flow_user_interfaces.

2) Do a full export/import of the application first. After this, you will again be able to export/import individual pages.

I tried the second workaround, and this works fine! I don’t receive an error anymore when I export a single page to the other installation.






Possible bug when sharing public reports in Team Development TO DO list (APEX 4.2.1)

Environment: confirmed with both APEX 4.1.1 and APEX 4.2.1.

I believe I discovered a bug in the “Team Development” part of Application Express. When you create a report with filters in the “To Do” section, and then save that report as a public report, another user can see the public report in the drop down list, but the filters are not applied when the report is selected.

Let’s illustrate this with an example:

I created a workspace “myworkspace” with 2 users: “myuser” and “anotheruser”.

Now I logged in with “myuser”, I created 2 To-Dos and a public report with the name “TODO myuser”. As filter, I selected “Assigned To=’myuser'”.

To Dos


Now I log out and log in with the user called “anotheruser”, created in the same workspace.

I can see the public report created by user “myuser” in the drop down list, but when I select it, the filter is not applied (which means all to-dos are displayed instead of only the ones for user “myuser”).

To Dos_2


Is this a known bug? Public reports in the applications work fine, but the ones in Team Development seem to be broken…





APEX 4.2.1 and APEX Listener 2.0 released!

First of all: a happy New Year to all my blog readers! I wish you an exciting 2013 with lots of interesting projects, no budget restraints and no unexpected downtime ;-)

When I returned from the Christmas holidays, I noticed that both Application Express 4.2.1 and the APEX Listener 2.0 have been released!

APEX 4.2.1 is actually the first patch set release for APEX 4.2. You can download the full release here (for new installations or upgrades) or, if you have a support contract, you can download the patch only (with number 14732511) here (for upgrades from version 4.2). There are a lot of bugs that have been fixed with this patch set (check for a full list here),  and also some new features have been introduced, for example the inclusion of AnyChart version 6 (which has even more HTML5 support). I’m happy with this patch set release, since this means it’s time to upgrade for us. We had some issues in the past after upgrading to a minor APEX release, so I learned to have patience and wait for the first patch set ;-)

Application Express Listener 2.0 was available as a Early Adaptor version for a while, but now the first production release is available. There are a lot of new features, like the ability to define multiple database connections, and more support for RESTful services. There are now also 2 ways to configure it: using SQL Developer (you need at least version 3.2.2) and using a new command-line interface. The web interface however has been removed, which I find a bit sad because I liked it :( We are using the APEX listener deployed to GlassFish, and I’m not sure if anything has changed in that matter. This is something I need to look into. I also wonder how you would upgrade from a previous version of the APEX Listener to this one. I suppose this involves some redeployment, and so also some (planned) downtime.