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

HTTP 404/403 executing APEX procedure from URL with Oracle 11g XE

Environment: Oracle Database 11g Express Edition with APEX 4.1, Windows 7 64-bit

I recently helped migrating an APEX application from Oracle 10g EE with GlassFish to a laptop which had Oracle 11g Express Edition running. There were no real issues, except for the fact that procedures called directly from the URL (for example to download documents to the browser) didn’t work. The solution was to first grant EXECUTE rights on these procedures to the user ANONYMOUS, and second to modify the APEX function wwv_flow_epg_include_mod_local.

I will simulate this with an example.

This is the code for my procedure:

CREATE OR REPLACE PROCEDURE MATTHIASH.download_tab_delimited IS
    l_mime_type VARCHAR2(32767) := 'text/plain';
    l_lob_length  NUMBER;
    l_file_name VARCHAR2(32767) := 'tab_delimited.txt';
    l_lob_loc  BLOB;
    l_sql VARCHAR2(32767);
    BEGIN
        l_sql := 'select UTL_RAW.cast_to_raw(''1''||chr(9)||''2''||chr(9)||''3'') from dual';
        execute immediate l_sql into l_lob_loc;
        
        l_lob_length := DBMS_LOB.GETLENGTH(l_lob_loc);
        
        owa_util.mime_header(l_mime_type,FALSE);
        htp.p('Content-length: ' || l_lob_length);
        htp.p('Content-Disposition:  attachment; filename="'||l_file_name|| '"');      
        owa_util.http_header_close;
        wpg_docload.download_file(l_lob_loc);
                
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       htp.p(substr(dbms_utility.format_error_stack()||dbms_utility.format_error_backtrace(), 1 ,4000));
     WHEN OTHERS THEN
       htp.p(substr(dbms_utility.format_error_stack()||dbms_utility.format_error_backtrace(), 1 ,4000));
END;
/

This simple procedure creates a tab delimited text file and presents it to the browser.
I now try to call the procedure by browsing to http://localhost:8080/apex/MATTHIASH.DOWNLOAD_TAB_DELIMITED. The result is a HTTP 404 error (I’m using Google Chrome as web browser):

Not found

The requested URL /apex/MATTHIASH.DOWNLOAD_TAB_DELIMITED was not found on this server
 

To solve this, you need to grant EXECUTE rights to the user ANONYMOUS on this procedure:

grant execute on MATTHIASH.DOWNLOAD_TAB_DELIMITED to ANONYMOUS;

Now try to call this procedure again. You should get a HTTP 403 error:

Forbidden

The requested operation is not allowed

The solution to this is to allow the Embedded PL/SQL Gateway (EPG) to access this procedure. You do this by modifying the APEX function wwv_flow_epg_include_mod_local (which in my case resides in the APEX_040100 schema):

CREATE OR REPLACE function APEX_040100.wwv_flow_epg_include_mod_local(
    procedure_name in varchar2)
return boolean
is
begin
    -- 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 (
          'MATTHIASH.DOWNLOAD_TAB_DELIMITED') then
        return TRUE;
    else
        return FALSE;
    end if;
end wwv_flow_epg_include_mod_local;
/

After this you should be able to call your procedure from the web browser!

HTH,
Matthias

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…

 

Matthias