Re: Can this be done with a trigger?

On Aug 6, 9:49 am, Aya the Vampire Slayer <ry..._at_gatech.rmv.this.part.edu> wrote:
> artme..._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

Ok, WTF? Now it works? Does that have to do with the fact that the default value is a space vs a NULL?

CREATE OR REPLACE VIEW CUSTOMER_CONFIG_VW (CUSTOMER_ID, PRODUCT_ID, FORMAT, STATUS, DATE_ADDED,  DATE_MODIFIED, SOURCE)
AS
SELECT CUSTOMER_ID,PRODUCT_ID,FORMAT,STATUS,DATE_ADDED,DATE_MODIFIED, ' ' source
  FROM customer_config
/

SQL> insert into customer_config_vw
values (12345,'HL','Text','Option',SYSDATE,SYSDATE,'A'); 2

1 row created.

Well, anyways, I appreciate everyones patience and help here. This turned into a longer thread that I had expected. Thanks a lot!

Regards Received on Wed Aug 06 2008 - 10:08:56 CDT