Re: Views and Inserts

Rajesh Patel wrote:

> I have an existing database that i cannot change, unfortunately
> they have used CHAR instead of VARCHAR, so we
> are going to have to code around the right padding
> issues.
>
> I attempted to remedy this by using a view and using
> TRIM to get rid of the padding, however when I do
> an insert on the trimmed fields I get:
>
> [WARN] JDBCExceptionReporter - -SQL Error: 1733, SQLState: 42000
> [ERROR] JDBCExceptionReporter - -ORA-01733: virtual column not allowed here
>
> I take this to mean that when trimmed those fields the database lost
> the link to the underlying table. If I remove the trim statments
> from the view inserts work correctly.
>
> Any Ideas? Strategies to get around this issues?
>
> Raj
>

Look at CAST as well as TRIM ... but the solution is to use an INSTEAD-OF trigger on the view.

http://www.psoug.org/reference/instead_of_trigger.html

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)