Difficulties with Import/Export Database objects in Apex 5
Well... I was developing with APEX via apex.oracle.com and although it worked fine I ran into space limitations and my internet speed is not the fastest so I decided to run APEX local to my PC...
So I chose to use the Database App Development VM (VirtualBox Image) that already contains a database 12c Enterprise Ed. + Apex 5.0 life was good...
Then I decided to copy the application to apex.oracle.com so I can show it to my client (I don't have any other hosting place) and that's when the problems started...
I used the export utility..
So I followed the Wizard and ensured I exported the Supporting Objects - In my mind this means "pack the-whole-thing together" and be done with it..
Well, that didn't go as planned, the application was imported but not the database objects, huh?
I guess I should've checked the "Readme" file he, he...
So I found the solution, I thought.. and this is when the plot thickened...
I exported the supporting object and created the database script..
Everything made sense, I thought I had figured out the way to create a packaged app that contained application + database objects...
I proceeded to import in apex.oracle.com and the application seemed to worked fine until I tried to add a record: "ORA-xxxx" Something like "cant add the record cause is missing the primary key", huh?... the investigation continued...
And to make this story short...
I have 2 theories:
Turns out that because the source database is Enterprise Edition, objects are inherently created with Enterprise Ed. attributes that are not available or not available to public in the database that apex.oracle.com runs on.
The DDL generator in APEX 5.0.2.00.07 is doing something wrong and describing options not appropriate.
I don't know what I'm talking about and should do more APEX 5 reading on how-to-do-it right. I'm leaning on this one by the way! ;-)
Cause I got errors like this when I ran the DDL statements in the generated script:
CREATE SEQUENCE T_CM_ROOM_SEQ MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOPARTITION ; ORA-00933: SQL command not properly ended
CREATE OR REPLACE EDITIONABLE TRIGGER BI_T_CM_EMP before insert on T_CM_EMP for each row begin if NEW.ID is null then select T_CM_EMP_SEQ.nextval into NEW.ID from sys.dual; end if; end;
ORA-00406: COMPATIBLE parameter needs to be 22.214.171.124.0 or greater ORA-00722: Feature "Editions"
So it means the tables were created but sequences and triggers were not created!!
After further investigation I found a few issues with the script that needed to be fixed:
Remove the "NOPARTITION" argument in the create sequence statements
Remove the "EDITIONABLE" argument in the create trigger statements
Add the missing colon ':' to the triggers so change the code from NEW to :NEW
I ran the commands via copy/paste to the SQLCommands in the SQL Workshop and I was back in the game!
I have no idea if there is a proper way to handle the export of database objects properly but at least now I know what to expect when copying applications to apex.oracle.com
Update from Joel Kallman - January 30, 2016
Oracle has finally turned up the COMPATIBLE initialization parameter to 12.0.0 (or higher) in apex.oracle.com This should alleviate all issues when deploying to apex.oracle.com