Finally I had some time to upgrade my Oracle XE database from version 10 to version 11, something I wanted to try out but could never find the time to do it. I wanted to try it out, to see if it all worked as advertised, usually it does. This time however I ran into some problems with the export and import of the APEX applications, good thing it was just on my sandbox database.The first step in the documentation (link at the bottom of this blogpost) is to log in as SYS and run the gen_inst.sql script which comes bundled with the Oracle XE11 database.
Running the gen_inst.sql script creates a number of scripts in the directory, each being an APEX application.
Now the thing to be cautious about: check the generated application install scripts.. or read the rest of this blogpost before running the gen_inst.sql script.I found out the hard way that they don't always work as expected. When following along with the "regular upgrade steps" (export the data, uninstall the Oracle XE10 database, install the Oracle XE11 database, import the data), the final step is to install all the applications into the newly created database. When doing so, the process stopped...
As you can see in the image above, the script halted at line 19 waiting for input... entering a slash (/) resulted in the exception shown, any other input would have resulted in a similar exception. Upon examination of the application scripts, I discovered this:
The gen_inst.sql script didn't generate the complete application script, it ran out of the DBMS_OUTPUT buffer resulting in a ORU-10027 exception (buffer overflow).The cause of this exception is the following code in the gen_inst.sql script
set serveroutput on size 1000000
This instruction limits the output buffer to 1000000 bytes of data.Ever since Oracle 10g Release 2, it is possible to do this
set serveroutput on size unlimited
Changing this line in the gen_inst.sql will generate correct application files. Note that there are multiple place in the script where this command is issued (two if I'm not mistaken).And just to be sure that this command also works in Oracle Express Edition 10g:
Finally, another problem I ran into, if you use the APEXLIB and you created a separate schema with a name like "APEXLIB", this schema is not exported with the EXPDP command. When I exported my APEXLIB schema and imported it into the target database a lot of views, packages and triggers stayed invalid. I decided to download a new version of APEXLIB and install that in the newly created database, and all was fine.Documentation links: