This content has been marked as final. Show 11 replies
Support for columns with XML types and better support for BLOBs / CLOBs would be great.
We're currently using a workaround with WebUtil and Java to show images which are stored in a BLOB within Forms (i.e. we save the BLOB to a temporary file on the app server and read that file using the READ_IMAGE built-in). It would be much easier if an image item could have a BLOB column as source and one could set a property to specify the content type of the image. The property would have to be changeable dynamically at runtime per record since each record can store images of different content types (we store the content type of the BLOB's image in another column).
Regarding XML types we've observed that the Forms Runtime crashes when accessing a table which has an XML column (even though the Forms app never uses that column!). Would be nice if that "bug" could be fixed - don't know if it is already in 11g - and even better if Forms items could use XML columns (e.g. having such a column as data source for a text item or a bean item and then specifying a filter-like routine which transforms the XML to content suited for presentation to the user).
support for the "new" - well they exists since 9iR1 ... 2001 - data types TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND would be very helpful.
Currently it's not possible to use the Data Block Wizard to create a data block for table/view that contains one of these columns.
(In Forms Builder 10.1.2.3 it fails with a strange error message ... iewbdbc_oracle_to_id ... C:\forms\101220\src\ie\iewbdb.c:724 ...)
So, one has to create the data block and the items manually. The items have to be CHARS.
Then forms is able to fetch and display these columns. The user can edit them as normal.
A WHEN-VALIDATE-ITEM trigger can be used to make sure that the implicit conversion from CHAR to the "new" datatype works.
For example, an INTERVAL YEAR TO MONTH column (b):
This nasty trick works for most of the "new" datatypes except with TIMESTAMP WITH TIME ZONE.
declare l_invalid_interval exception; pragma exception_init(l_invalid_interval, -1867); l_invalid_month exception; pragma exception_init(l_invalid_month, -1843); l_dummy interval year(9) to month; begin l_dummy := to_yminterval(:block2.b); exception when l_invalid_interval then message('invalid interval'); raise form_trigger_failure; when l_invalid_month then message('invalid month'); raise form_trigger_failure; when others then message(error_text); raise form_trigger_failure; end;
In Forms 10.1.2.3 the following WHEN-VALIDATE-ITEM trigger can (incorrectly!) fail:
It fails with "ORA-01804: failure to initialize timezone information", if the time zone is not like "-11:00", but like "EUROPE/PARIS". Don't know why...
declare l_dummy timestamp with time zone; begin l_dummy := to_timestamp_tz(:block2.a); end;
If we recode our trigger to make a round-trip to the database, it works:
We could avoid these tricks, if Forms (and Reports) would support these type natively.
declare l_dummy timestamp with time zone; begin select to_timestamp_tz(:block2.a) into l_dummy from dual; end;
PS: The TIMESTAMP and INTERVAL data types should of course be supported as items, parameters, globals and record groups columns.
Edited by: michael76 on 25.10.2010 01:14
A patch to correct the Assertion error for the Builder has been created for Forms 10.1.2.3 on Windows and is available using Patch ID 8836073 on MyOracleSupport. This patch will prevent the error from occurring. It will NOT, however permit the use of unsupported datatypes. With the patch in place, you will be able to use the Datablock Wizard to create your block, however it will exclude the unsupported columns.
Senior Principal Support Engineer
Forms Global Technical Lead