Re: Can this be done with a trigger?

From: Aya the Vampire Slayer <ryoko_at_gatech.rmv.this.part.edu>
Date: Wed, 6 Aug 2008 14:49:12 +0000 (UTC)
Message-ID: <g7cdl8$nao$1@news-int2.gatech.edu>

artmerar_at_yahoo.com wa:
>This is VERY interesting. If you look above at my table definitions,
>the last column, SOURCE, is not in the original table that the view is
>based on, but is created as a column because it is needed in the
>second view.

>If I perform an insert using a NULL for the value of that column, as
>below, it works fine. But, if I put a value in for that column, it
>aborts:

>SQL> desc customer_config_vw
> Name Null? Type
> ----------------------------------------- --------
>----------------------------
> CUSTOMER_ID NUMBER
> PRODUCT_ID VARCHAR2(10)
> FORMAT VARCHAR2(4)
> STATUS VARCHAR2(15)
> DATE_ADDED DATE
> DATE_MODIFIED DATE
> SOURCE VARCHAR2

>insert into customer_config_vw
> values (1234,'PFP','Text','Active',SYSDATE,SYSDATE,NULL);

>1 row created.

>insert into customer_config_vw
>values (67890,'PPM','HTML','Unsubscribe',SYSDATE,SYSDATE,'A');
>insert into customer_config_vw
>*
>ERROR at line 1:
>ORA-03113: end-of-file on communication channel

>Any thoughts on this???

Okay, now that I'm able to test this at work, apparently I find myself having mislead you. I was able to duplicate the error you got from the null column in the view that you mention above.

I also checked through the pl/sql code I'd written over the past few years here at work and I can't find anywhere where that I used a null column to actually INSERT a passed-in value, even though I do use null columns in views on occasion for various front-end application quirks. So I apologize for suggesting something that doesn't quite work exactly as written.

That said, I tried a few different things and managed to get it to work with something basically similar, but it does feel somewhat hackish. It should get the job done either way.

This time I created my view like this (and kept the trigger the same).

create or replace view &schema..VW_BLAHBLAH2 as

    select bb.*,

           'apples' SOURCE
    from TB_BLAHBLAH2 bb
;

If you pass in something that is the length of 'apples' or less as a varchar2, then it will insert it (otherwise you'll get a "value too large for column" error).

INSERT INTO VW_BLAHBLAH2
( ID, IDENT, NAME, INACTIVE, SOURCE )
VALUES ( sys_guid(), 'foo', 'foobar', 0, 'bananas');

                                         *

ERROR at line 3:
ORA-01401: inserted value too large for column

INSERT INTO VW_BLAHBLAH2
( ID, IDENT, NAME, INACTIVE, SOURCE )
VALUES ( sys_guid(), 'foo', 'foobar', 0, 'banan');

1 row created.

-+-

select * from TB_BLAHBLAH2;
01FE2D5D9C26458BB15DFB7F100738EE foo foobar 0

select * from TB_BLAHBLAH2_HIST;
083D80704D594785A529F7CD861FBA96 01FE2D5D9C26458BB15DFB7F100738EE foo foobar 0 banan

-+-

So um, yeah, in the view, just replace:
'apples' SOURCE

...with something that satisfies your length/char/byte requirement for the SOURCE field. If there is another field in your CUSTOMER_CONFIG that is the same size varchar2 as SOURCE in the HIST table, then just select that column twice and rename the second instance to SOURCE. Or otherwise, you can put a really long varchar2 constant for the SOURCE column, e.g.:

'                                                 ' SOURCE 

Again, sorry for giving you some bad information. I guess oracle interprets a null column as having no length, and gives a confusing and vague "catch all" error instead of something actually useful when you try to INSERT something into it, heh.

-- 
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators

"Keep your fingers off the lens." --Elton Byington, English Translator

Received on Wed Aug 06 2008 - 09:49:12 CDT