Shell script to export all APEX applications as SQL scripts

Environment: Oracle 11gR2 EE, Application Express 4.2.2, Oracle Linux 6.4 64-bit, Java JDK 7u21

The APEX software contains a Java class which can be used to script or schedule the export of one or more APEX applications. I will show you in this post how to do this.

First, I unzipped the APEX software to /u01/app/apex. The Java class file is called “APEXExport.class” and can be found in the folder /u01/app/apex/utilities/oracle/apex.

You also need a Java JDK. I installed Java 7 JDK (update 21) under /u01/app/java/java7.

You might need to unlock (and set a password) for the APEX schema owner (in my case, APEX_040200). I’m not sure if it’s possible to export the applications with another user.

This is how my script looks like:

#!/bin/sh
# script to export all ApEx applications as sql scripts

CLASSPATH=$CLASSPATH:/u01/app/oracle/product/11.2.0/db_1/oui/jlib/classes12.jar; export CLASSPATH
BACKUPDIR=/u05/apex_backup

cd /u01/app/apex/utilities

/u01/app/java/java7/bin/java oracle.apex.APEXExport -db localhost:1521:ora11g01 -user apex_040200 -password <password> -instance

mv f*.sql $BACKUPDIR

if [ -f $BACKUPDIR/apex_export.log ]
then
    cat $BACKUPDIR/apex_export.log|mail -s "APEX export - `hostname -s`" matthiash@mydomain.com
fi

You need to include the file classes12.jar in your CLASSPATH or the JDBC connection won’t work.

The -instance setting will export all your applications. To limit the number of applications, you can also use -workspaceid or -applicationid. Check the readme.txt file under the utilities folder for more information.

To schedule the script with cron, I added the following line to my crontab file:

00 22 * * * /home/oracle/scripts/apex_export.sh 1>/u05/apex_backup/apex_export.log 2>&1

This will execute the script each night at 22:00 and mail the log file to the mail address inside the script.

Happy scripting! :-)

Matthias

Advertisements

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

Environment: Oracle 11gR2, Application Express 4.2.2.00.11

I was trying to export a workspace from Oracle 10g to Oracle 11g (both with APEX 4.2.2.00.11 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

begin

wwv_flow_api#g_id_offset := 0;

wwv_flow_hint.g_schema := ‘MATTHIASH’;

wwv_flow_hint.check_schema_privs

end;

 

I checked the OTN forum and I replied to this post. Apparently, it’s a known bug in APEX 4.2.2.00.11. 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!

 

HTH,

Matthias

 

 

Getting APEX version number from data dictionary

Environment: Oracle 10gR2, Oracle 11gR2, APEX 4.2.2.00.11

If you don’t have access to the development environment, and you need to find the version of APEX installed into your system, there are several ways to do that:

 

1) Query APEX_RELEASE

APEX_RELEASE is a public view which is present in each APEX schema.

To query the current enabled version:

SELECT VERSION_NO FROM APEX_RELEASE;

4.2.2.00.11

 

To query other installed versions of APEX:

SELECT VERSION_NO FROM APEX_040100.APEX_RELEASE;

4.1.1.00.23

 

2) Query DBA_REGISTRY

The DBA_REGISTRY keeps track of all components installed into the database.

SELECT VERSION FROM DBA_REGISTRY WHERE COMP_NAME = ‘Oracle Application Express’;

4.2.2.00.11

 

HTH,

Matthias

APEX 4.2.2 released!

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

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.

 

Matthias

 

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 :-)

 

Matthias

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 4.2.1.00.08, Oracle database 10.2.0.5.

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.

 

HTH,

Matthias