There have been a few posts on the Application Express forums lately from people who want to either export lots of applications or are looking for a way to automate the task of exporting applications.
Most (if not all) APEX developers are familiar with the application export functionality from within the Application Builder interface itself:
However you may not be aware that tucked away in the Application Express install that you downloaded are a couple of utilities that you can also use to export your applications, infact the utilities can do much more than that, you can use them to export all the applications within a workspace or even all the applications within the instance.
If you have already deleted the Application Express installation files after successfully installing it, then you’ll need to download it again (available from here) because I don’t believe these files are available individually anywhere else.
The files can be found in the utilities subdirectory of the root installation directory, note that I’m using a Unix machine here (well actually an Apple powerbook), however due to the ‘portability’ of Java applications they will also work quite happily on other operating system, assuming of course that you follow the directions in the readme.txt file to setup your Java environment which if you already have Java installed should only involve setting up your Java CLASSPATH environment variable like this -
export CLASSPATH=.:${ORACLE_HOME}/jdbc/lib/classes12.zip
Obviously however you should take the time to read the full readme.txt file.
There are two java utilities available for you to use APEXExport and APEXExportSplitter which both reside in the oracle/apex subdirectory.
Executing the APEXExport program without any additional parameters will show you some default usage help.
As you can see, the usage is pretty intuitive, if I wanted to export application 103, all I would need to do is -
If we now look in the current working directory, you will find a file called f103.sql which you may recognise as being the same type of file which is produced by the export functionality in the Application Builder, in other words you can import this f103.sql file by using the Application Builder tools. You can also import the application by running the SQL script file whilst connected as your FLOWS user if you prefer to perform the import at the command line (or perhaps as part of a batch import).
We can also export all of the applications for a particular workspace, unfortunately we need to do a little bit of work here because the APEXExport utility requires the workspace id rather than the workspace name (it would be a nice feature if we could just pass in the workspace name instead), however it’s quite simple to get your workspace id by running the query
select v(’WORKSPACE_ID’) from dual
in SQL Workshop whilst connected to your Workspace, this query will return a numeric id which you can then use with the APEXExport command, like this -
As you can see, it took around 2 seconds to export all 5 applications from my workspace, which is certainly quicker than doing it via the Application Builder interface. Also note that each application is exported into its own seperate file, rather than a single file.
Lastly, you can also export all the applications in the entire instance, which I won’t show here since it should now be pretty obvious how that works.
You can do so many different things with this sort of commandline export functionality, for example you could quite easily create a Unix cron job to export your applications each night at midnight as an extra backup. Or, if you’re more adventurous you could schedule a job to export the applications, see if they’ve changed (using the Unix ‘diff’ command perhaps) since the last export and if they have then check them into your source control repository, you could also automate an export of your applications and email them automatically to a remote offsite location.
This kind of commandline scripting and control is why I love using Unix so much, with just a few simple commands I can automate so many of the manual tasks to make my life easier and it also means that I can be confident of also having a recent backup of my applications nearby if I should ever need them.
Next post I’ll show the APEXExportSplitter in action.
Comments 22
Hi John,
good intruduction to the export utility. I think there a lot of people who don’t know about that.
BTW, I noticed in your posting that you have several links in your posting, but they don’t render as links as long as you don’t move the mouse over them. So you really have to be lucky to find this links :-)
Greetings from Vienna
Posted 12 Dec 2006 at 11:13 pm ¶Patrick
———————————————————–
Check out my APEX-Blog: http://inside-apex.blogspot.com
Hi Patrick,
Thanks for spotting that, I’ve changed the link colours in the CSS now so that links should be slightly easier to spot.
Thanks,
John.
Posted 12 Dec 2006 at 11:36 pm ¶For the unlucky people who have to work in a windows environment, you can find
Posted 13 Dec 2006 at 9:29 am ¶an example of a .bat file that exports a given ApEx application on following URL:
http://iadvise.blogspot.com/2006/11/automatically-backing-up-your-apex.html
Jan,
Excellent solution, thanks for that.
John.
Posted 13 Dec 2006 at 9:32 am ¶Its quite intresting. I have exported an application, well sort of, just to try out the export function in apex. It does so quite well, yet the location it exports to is a mistery. So with these tools, I could specify that location. Its good to know they exist.
Posted 14 May 2007 at 1:49 pm ¶Hi John,
This is the proof that’s worth to keep old posts available: what a nice tool and clear written explanation.
Posted 16 Jul 2008 at 8:49 am ¶Thanks,
Geert.
Thanks Geert!
John.
Posted 16 Jul 2008 at 9:10 am ¶I put APEXExport.class in the working directory, but when i command “java oracle.apex.APEXExport” i get the following error:
Posted 31 Jul 2008 at 11:41 am ¶Exception in thread “main” java.lang.NoClassDefFoundError: oracle/apex/APEXExpor
t
Caused by: java.lang.ClassNotFoundException: oracle.apex.APEXExport
at java.net.URLClassLoader$1.run(Unknown Source)
at ……
Cazzonio,
Did you remember to do the CLASSPATH part?
John.
Posted 19 Aug 2008 at 3:49 pm ¶I’m trying to run APEXExport on RHEL4. I have the $CLASSPATH set:
.:/home/oracle/oracle/product/10.2.0/db_1/jdbc/lib/classes12.zip
I can run java oracle/apex/APEXExport just fine, and it displays the usage info. If I try to run:
java oracle/apex/APEXExport -db host:port:SID -user apex -password apex -instance (or any other flag), I get:
Exception in thread “main” java.lang.AbstractMethodError
at _Jv_MakeVTable(java.lang.Class) (/usr/lib/libgcj.so.5.0.0)
at _Jv_PrepareClass(java.lang.Class) (/usr/lib/libgcj.so.5.0.0)
at _Jv_WaitForState(java.lang.Class, int) (/usr/lib/libgcj.so.5.0.0)
at java.lang.VMClassLoader.linkClass0(java.lang.Class) (/usr/lib/libgcj.so.5.0.0)
at java.lang.VMClassLoader.resolveClass(java.lang.Class) (/usr/lib/libgcj.so.5.0.0)
at java.lang.Class.initializeClass() (/usr/lib/libgcj.so.5.0.0)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.util.Properties) (Unknown Source)
at oracle.jdbc.driver.OracleDriver.connect(java.lang.String, java.util.Properties) (Unknown Source)
at java.sql.DriverManager.getConnection(java.lang.String, java.util.Properties) (/usr/lib/libgcj.so.5.0.0)
at java.sql.DriverManager.getConnection(java.lang.String, java.lang.String, java.lang.String) (/usr/lib/libgcj.so.5.0.0)
at oracle.apex.APEXExport.main(java.lang.String[]) (Unknown Source)
What am I missing?
Thanks for this excellent tutorial BTW. I hope I can make it work for us, for it will really solve a big problem.
Bill
Posted 02 Oct 2008 at 3:43 pm ¶OK - forget that last post. Discovered I was not using Sun JDK, but rather the open java equivalent. Switched to Sun’s java and I’m getting farther.
Posted 02 Oct 2008 at 4:21 pm ¶Hi John
Thanks for your introduction.I am trying this solution.I believe my CLASSPATH is correct.But sometimes(not always) when I run java oracle.apex.APEXExport -db APEXSERVER:1521:apexdb -user apexusr -password apexpwd -applicationid 1111,I get the following error:
Exception in thread “main” java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:254)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:386)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:419)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:164)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:34)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:752)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:171)
at oracle.apex.APEXExport.main(APEXExport.java:304)
I know the error happens when programa connect database using jdbc .It is not happened all the time,sometimes I got this error and sometimes it runs very well.I know there must be some issues.Could you give me some advices to fix this problems? Thanks.
Posted 26 Nov 2008 at 10:44 am ¶I have used these features to export my application and move it from development to production. I was wondering if you had any ideas about true concurrent development or code merging. We are in the unfortunate position where there could be some bug fixes on production and we want to integrate some additional features that were done on development. From what I remember, doing a code review or merge on the large export files was a little hard to manage. Any tips?
Posted 04 Feb 2009 at 8:03 pm ¶I’m trying to backup my Apex applications through APEXExport utility as given in this article. I set the CLASSPATH correctly as given in the readme.txt documentation and in this article.
FLOWS_030100 account is open.
The database vesion is 10.2.0.3
Apex version is 3.0.1
java -version
java version “1.5.0_19″
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_19-b02)
Java HotSpot(TM) Server VM (build 1.5.0_19-b02, mixed mode)
The errors that I’m encountering while using this utility are given below.
java oracle/apex/APEXExport -db sun-oracle:1521:ORCL -user scott -password tiger -applicationid 100
Exporting application 100
Exception in thread “main” java.sql.SQLException: ORA-20001: Application 100 not found logged in as database user scott
ORA-06512: at “FLOWS_030100.WWV_FLOW_UTILITIES”, line 8014
ORA-06512: at line 1
Another error that I’m encountering when I’m trying with workspaceid is as follows:
java oracle/apex/APEXExport -db sun-oracle:1521:ORCL -user scott -password tiger -workspaceid 1010131433379996
Posted 02 Aug 2009 at 3:08 pm ¶Invalid Workspace ID ‘1010131433379996′ for User ’scott>’
This was a huge help to us. Regards subversion, I found I could simply strip the timestamp from all the exports via
Posted 12 Aug 2009 at 2:58 pm ¶# sed ‘/Date and Time/d’ -i *
Then, I could
# svn add
# svn commit -m “nightly update”
It does what you want: committing just the apps that change - or are new.
Steve
Steve,
I think you’ll need to be more selective than just that sed command alone. For example, what if I added a new page item with a label of ‘Date and Time’…that sed command would strip that out of the export file (and potentially giving me an invalid file which I could not import)?
John.
Posted 17 Aug 2009 at 9:30 am ¶Hi Steve & John,
Thanks for the suggestions, they really help!
After a bit of experimenting I think the following sed command should do the trick:
# sed ‘0,/Date and Time/{//d;}’ -i *
This will only delete the first line that contains “Date and Time” and hence should prevent the problem that John helpfully highlighted.
Andy.
Posted 21 Aug 2009 at 12:41 pm ¶Is there a way to combine the export of the Application with the underlying data?
If not is the appropriate order to import the
database into the schame before importing the apex application?
Off topic: Does anyone know of a good diagramatic presentation for our dba that maps workspace, applications, users (schemas) and tablespaces?
Posted 08 Sep 2009 at 5:30 pm ¶Hi,
We’re using Oracle 11g and ${ORACLE_HOME}/jdbc/lib/classes12.zip does not exist. Do you happen to know what the CLASSPATH should be in 11g?
Thanks!
Posted 14 Oct 2009 at 8:54 pm ¶Hi Jennifer,
Does it not exist anywhere? If you do a ‘find . -name classes12.zip’ (or Windows equivalent) from the top level directory does it not find it at all?
John.
Posted 20 Oct 2009 at 4:44 pm ¶Hi, I am in the same boat as Jennifer. We’re using Oracle 11g and I’m getting the error
“Exception in thread “main” java.lang.NoClassDefFoundError: APEXExport”
When doing a search through root, I do find two instances of classes12, which one do I use?
“./opt/oracle/app/oracle/product/11.2.0/dbhome_1/oui/jlib/classes12.jar”
“./opt/oracle/app/oracle/product/11.2.0/dbhome_1/lib/lclasses12.zip”
The CLASSPATH I’m currently using is CLASSPATH=/opt/oracle/app/oracle/product/11.2.0/dbhome_1/lib/lclasses12.zip
Hope anyone can share some insight regarding Oracle 11g and this awesome feature. Thanks!
Posted 10 Dec 2009 at 5:12 pm ¶In relation to the post above that I just made, I tried setting the CLASSPATH to both locations of classes12 and still get the error Exception in thread “main” java.lang.NoClassDefFoundError: APEXExport”
Also, after I make the changes to the classpath, the “java oracle.apex.APEXExport” command stops working as well and returns the error
Posted 10 Dec 2009 at 5:29 pm ¶“Exception in thread “main” java.lang.NoClassDefFoundError: oracle/apex/APEXExport
Caused by: java.lang.ClassNotFoundException: oracle.apex.APEXExport
at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:252)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)
Could not find the main class: oracle.apex.APEXExport. Program will exit.
“
Post a Comment