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 places 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:
Hi Alex,
ReplyDeleteI tried upgrading my 10gXE with APEX 4.1 installed to 11gXE, using the gen_inst approach and ran into some problems, probably regarding version dependency:
First of all, the gen_inst.sql script contains hardcoded references to the APEX_040000 schema, which I found out later, trying to import my workspaces and applications. Well it’s to late now, as my 10gXE is deinstalled. Actually the script contains a “whenever sqlerror exit” with a version check (line 37-47):
whenever sqlerror exit
declare
l_version number;
begin
l_version := to_number(replace(replace('&UFROM','FLOWS_',null),'APEX_',null));
if l_version < 20100 or l_version > 40000 then
dbms_output.put_line('This release of Application Express cannot be upgraded to 11.2 XE using this utility.');
execute immediate 'bogus statement to force exit';
end if;
end;
The generated “install.sql” does contain APEX_040000 references as well, but changing those once the export has been messed up doesn’t help.
The effect is: I have a partial migration of my applications and lots of errors during validation.
Fortunately I have an export of my 10gXE database. Let’s find out how much repair work is left to fix my applications.
Cheers,
Christian