Inserting record with a Blob joined field generates ORA-00904 Invalid Column

[Jon Roberts]

After upgrading to version 4.05 from 3.4.6, I now get an error when attempting to insert a record into a dataset that includes joined blob fields in the sql query. Inspecting the SQL that's being generated via Oracle Monitor, the insert statement includes these blob fields. Other joined fields (non-blobs) are appropriately not included in the insert sql.

My SQL for the dataset is as follows:

select c.*,c.rowid, ut.image, v.image as Vendor_Image,ut.ordering,
v.vendor_description as vendor_description ,
ut.description as utility_description
from billing.utilities c, billing.utility_table ut, billing.vendor_table v
where site_id = :site_id
and c.utility=ut.utility
and c.vendor_type=v.vendor_type
and date_inactivated is null
order by ut.ordering, meter_number

Oracle Monitor shows the SQL that is generated from an insert is as follows:

insert into BILLING.UTILITIES
(SITE_ID, UTILITY, METER_NUMBER, VENDOR_TYPE, IMAGE, VENDOR_IMAGE)
values
v1, :v2, :v3, :v4, :v16, :v17)
returning rowid into :doa__rowid

The error generated is an ORA-00904 Invalid Column. If I delete the two image blob fields from the SQL select statement, the insert works fine.

What changed with version 4? Is there a way to do this that works?

 

[Jon Roberts]

I got it to work by changing the FieldKind property on both the blobs to fkInternalCalc. I noticed this requirement on page 89 of the user manual. Weird that I don't have to change it on the non-blob fields and it worked fine in the old version without changing anything.