Computed database fields: READ-ONLY

Author: elim.de.brock@tui.nl (ErikFiets)

I have created a computed (virtual read-only) field in the (Oracle) database. Reading the entity works fine but if I try to store the entity, Uniface tries to store the field causing a database error: "ORA-54017: UPDATE operation disallowed on virtual columns". Is there any way to define a field as read-only so that the field will not be included in the field list when the entity is stored in the database? I tried to set the field syntax to "DIM" but that does not seem to work.   Example of computed field: ALTER TABLE JOB ADD (salary NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL);

1 Comment

  1. ErikFiets said I have created a computed (virtual read-only) field in the (Oracle) database. Reading the entity works fine but if I try to store the entity, Uniface tries to store the field causing a database error: "ORA-54017: UPDATE operation disallowed on virtual columns". Is there any way to define a field as read-only so that the field will not be included in the field list when the entity is stored in the database? I tried to set the field syntax to "DIM" but that does not seem to work.   Example of computed field: ALTER TABLE JOB ADD (salary NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL);  

    Not that I know off. You only can define an entity as read-only (e.g. by defining the locking as "No Update") and this will mean that nothing can be stored. It might be possible to create a tweaked Stored Procedure for the table(/entity) in question. When reading the data (select ... into ... from ... where ...) you could include the virtual field and exclude when writing (update ... set ... where ...) or deleting (delete from ... where). But this will only work when you use Stored Procedures for all data I/O. In case your using the Oracle connector option dpa then this will not be an option. A more "generic" solution probably requires a bit more work. You could (e.g.) define the virtual field in the entity as a non-database field. The downside is that you have to read the actual value of the virtual field with (e.g.) the SQL statement and assign the value (using /init) to the non-db field. Not really nice, but it would work. One thing to keep in mind here is that after a store you explicitly need to update the value of the non-db field. There (of course) might be other ways to implement this... Hope this helps. Kind regards, Daniel Iseli Uniface Technical Support


    Author: diseli (daniel.iseli@uniface.com)