Virtual Column Issue with Oracle Forms

1
votes

I have an Interface which's created with Oracle Forms. It has a base table block in which there's a field (namely col3"has the same name with the table's column that's derived from").

Form has two other fields col1 and col2 as of numeric type, and those fields are also members of the same table having col3 column mentioned above.

I converted col3 to a virtual column as the sum of the columns col1 and col2 in the table's definition( col3 number generated always as (nvl(col1,0)+nvl(col2,0)) virtual )

When I try to commit the changes in the base table it hurls the error message

ORA-54013: INSERT operation disallowed on virtual columns

I know applying a DML to a virtual column has no sense, but even I changed the col3 field's

  • Query Only property from No to Yes

  • Update Allowed property from Yes to No

  • Insert Allowed property from Yes to No

I get the same error message. I don't want to write explicit DML statements, since the form has lots of other fields, and they should also be listed in those statements.

Do you have any idea how I can overcome this problem without giving up base table block structure ? ( I'm using the version Fusion Middleware 11g )

1
oracleoracleformsvirtual-column
Not sure if this will affect inserts, but does setting the block property Update Changed Columns Only to "Yes" make any difference? - Jeffrey Kemp
@JeffreyKemp Even converting Update Only if NULL to "Yes" doesn't make any difference. Thanks. - Barbaros Özhan
Ok - I thought that would probably be the case. Cheers. - Jeffrey Kemp

1 Answers

1
votes

As everything you do with that column is query, how about setting it to be a non-database column? It would require writing a POST-QUERY trigger, though - otherwise you wouldn't see its value after executing a query. 

:block.col3 := :block.col1 + :block.col2;

Put the same code into WHEN-VALIDATE-ITEM triggers on both :block.col1 and :block.col2 items, so that you'd calculate the result when inserting/updating values.

Alternatively, create a procedure (within the form), put the above code into it, and then call the procedure from those triggers - for a long term, that's probably a better choice as you'd have to maintain code changes only in the procedure.