Dear All:
I know this could belong to another space, but it refers to Forms and Apex both.
This document is to demonstrate the difference of dealing with custom data types between Oracle Forms 11g and Oracle Apex 19.2,
The situation is, a project developed with Oracle Forms 11g using some custom user defined data types for specific reason, the project is working in many places.
Now we are planning to migrate the project to Oracle Apex 19.2 and we did a pilot project to enable some customers to access search pages online.
Before we go deep into migration we've gathered some information about the old project and we've found this problem of using custom data types.
I will demonstrate the case by using a demo table with user defined data type as:
create or replace type coordinations as object
(
x number,
y number,
name varchar2(50)
);
/
create table POINTS
(
id NUMBER,
location VARCHAR2(100),
co COORDINATIONS
);
Oracle Forms 11g:
I will create a form module on this table, you will notice that Oracle forms manage to define the type by itself and specify the attributes inside the type,
The data block created successfully and the layout is shown as
After running the form it can successfully manage the insert, update, query and delete on that table without any issue.
After monitoring the application on "Enterprise manager" the insert statement used by Oracle Forms engine was as follow:
You will notice that constructor of the type is used obviously inside the code.
Now let us do the same with Oracle APEX 19.2
Creating Form:
Notice that the type is not recognized at all (Anydata) and it gives the error as
1 error has occurred
- ORA-20001: Unable to create form on table. ORA-20001: Unable to create form on table. ORA-20001: Error page=2 item="P2_CO" id="60140651379268601" ORA-02290: check constraint (APEX_190200.VALID_FSITEM_SOURCE_DATATYPE) violated
Trying to create IG instead of Form:
Here, the type is recognized at the beginning but later when expanding the IG columns you will see the column with type "COORDINATIONS" has "ANYDATA" data type.
The IG is based on the table "POINTS"
And the column CO has "ANYDATA (Invalid)" data type, without showing it's attributes
When trying to run the application and entering data this error appears
And some times it gives (strange behavior)
Another try by creating IG with query:
If you try to base the IG on sql query as:
Now we have all columns plus attributes of the (type) shown individually
(select ID,
LOCATION,
t.CO.X,
t.CO.Y,
t.CO.NAME
from POINTS t)
when running the application and trying to enter data this error appears
Error: ORA-01733: virtual column not allowed here
The debug information is as follow:
- error_statement: insert into (select ID,
LOCATION,
t.CO.X,
t.CO.Y,
t.CO.NAME
from POINTS t
)
("ID","LOCATION","CO.X","CO.Y","CO.NAME")values(:APEX$VAL1,:APEX$VAL2,:APEX$VAL3,:APEX$VAL4,:APEX$VAL5)returning "ID" into :APEX$RET1
The generated insert statement by APEX engine doesn't recognize the type and it's constructor as the Oracle Forms 11g did.
Regards
Motasem Shkeir