Last week I was struggling to get the APEXExport utility to work. I was messing around with environment variables, different Java versions, and ugly bash syntax while constantly getting the exact same error message. Anyway, after some frustrating moments, I decided to start all over again and then I came up with a solution pretty fast. Here’s my way to quickly get the APEXExport command-line utility to function properly.
Preparation
1. Download the installation files for your specific APEX version. Unzip and put the resulting folder in an accessible location on your file system.
2. The Java Development Kit (JDK) version 1.5 or greater is required on your machine. Make sure Java is in the PATH environment variable.
>> java -version java version "1.7.0_07" Java(TM) SE Runtime Environment (build 1.7.0_07-b10) Java HotSpot(TM) 64-Bit Server VM (build 23.3-b01, mixed mode)
3. Download the Oracle JDBC driver that is compatible with the version of your Oracle Database. For example, I’m on version 11.2.0.3.0 and I ended up using the ojdbc6.jar file. Execute the below query to find out your database version.
select * from v$version;
Execution
1. Open up a terminal window and change the directory to the utilities folder in the APEX installation folder. It is absolutely mandatory to execute the APEXExport utility starting from this folder.
>> cd Documents/nick/software/apex/utilities/ >> pwd /Users/Nick/Documents/nick/software/apex/utilities
2. You should now be able to invoke the APEXExport utility. Here’s the command I used to export an application.
>> java -cp .:/path/to/ojdbc6.jar oracle.apex.APEXExport -db localhost:1521:apex -user nick_demo -password secret -applicationid 999 Exporting application 999 Completed at Mon Nov 25 20:08:54 CET 2013
Explanation
- cp: set the classpath parameter to the current folder (.) and the path to the Oracle JDBC driver file (/path/to/ojdbc6.jar). You could have used an environment variable instead:
>> export CLASSPATH=.:/path/to/ojdbc6.jar
- db: the database JDBC connection URL
- user: the parsing schema of the application you want to export
- password: the password of the above user
- applicationid: the ID of the application to be exported
Any problems? Post your question in the comments section.
Tried importing the instant row deletion (demo @ http://apex.oracle.com/pls/apex/f?p=19914:10:0::::: Very cool example) and Apex just said there was an error, then tried executing sql via sqldeveloper . . . got . . .
APPLICATION 19914 – APEXPLAINED
Set Credentials…
Check Compatibility…
Set Application ID…
Rollback
Error starting at line : 120 in command –
begin
— Remove Application
wwv_flow_api.remove_flow(nvl(wwv_flow_application_install.get_application_id,19914));
end;
Error report –
ORA-20001: Package variable g_security_group_id must be set.
ORA-06512: at “APEX_040000.WWV_FLOW_API”, line 51
ORA-06512: at “APEX_040000.WWV_FLOW_API”, line 304
ORA-06512: at line 4
Did I do something wrong? I have the latest XE & Apex via download from Oracle.
Can you email me a new export file for this demo? thanks
LikeLike
I don’t think there’s anything wrong with the application export. However, you first need to make some calls to the apex_application_install package before running the export script in SQL Developer. Take a look at the examples here: http://joelkallman.blogspot.be/2010/07/apexapplicationinstall.html
Anyhow, you should be able to import the application via the APEX builder. What error did you get there? I can send you a more recent export file, but then I’ll need your e-mail address.
LikeLike
> It is absolutely mandatory to execute the APEXExport utility starting from this folder.
You can force java to use a directory using -Duser.dir, so you can export files directly into an export directory
Here is a script to export workspaces and applications+files associated
I moved apex/utilities/ into ${REP}/ApexUtilites/
cd ${EXPORTDIR}
export CLASSPATH=.:${ORACLE_HOME}/jdbc/lib/ojdbc6.jar
# Export des workspace
# you need GRANT APEX_ADMINISTRATOR_ROLE TO $USER; to export workspaces
java -Duser.dir=${REP}/ApexUtilities/ oracle.apex.APEXExport -db ${SERVEUR}:1521:$BASE -user $USER -password “$PASSWORD” -expWorkspace > ${EXPORTDIR}/ApexdWorkspace.log
for WORKSPACEID in $( grep “^Exporting Workspace” ApexdWorkspace.log | sed “s/ /:/g” | cut -d’:’ -f3 )
do
echo “On exporte les applications du workspace $WORKSPACEID”
java -Duser.dir=${REP}/ApexUtilities/ oracle.apex.APEXExport -db ${SERVEUR}:1521:$BASE -user $USER -password “$PASSWORD” -workspaceid $WORKSPACEID
echo “On exporte les fichiers du workspace $WORKSPACEID”
java -Duser.dir=${REP}/ApexUtilities/ oracle.apex.APEXExport -db ${SERVEUR}:1521:$BASE -user $USER -password “$PASSWORD” -expFiles -workspaceid $WORKSPACEID
done
LikeLike
Nick – Would you happen to know if this utility works for APEX 4.0?
LikeLike
Hey Bob,
The APEXExport utility definitely works in APEX 4.0. Check your APEX installation files.
LikeLike
Hi Nick,
this blog helped me to solve an issue regarding ORA-28040 after upgrading to 12c.
I just needed to put a most current ojdbc6.jar file (from a sql developer installation) into the classpath.
Thanks!
LikeLike
Great post. Thans Nick!
LikeLike
Thanks, this helped me get my classpath right.
LikeLike