Saturday, November 9, 2013

stupid pet tricks with oracle ...make me grumpy

While oracle gave us the ability to define/use virtual columns a while back now I never used them.  We have a couple of tables here now at my new job that do use them.  I was coding away ( ok probably working too fast ) on an insert procedure ... it would be passed in a ROWTYPE variable and would insert all the columns.

OK ... plsql code compiles ... check.

Turn over to developer for testing ... check.

( Well I usually advocate testing anything before giving to a developer ... but busy busy ... and of course turned over with caveat "I have not tested this at all yet" ).

I stumped the developer and me temporarily ... it kind of goes like this:

CREATE TABLE GONNA_MAKE_ME_LOOK_STOOPID
(
  COL1  VARCHAR2(80),
  COL2  VARCHAR2(80)
  Generated Always as (UPPER("COL1"))
);


-- work proc actually received in a row type this is just demo

create or replace procedure this_is_a_big_fail
( p_col1 varchar2, p_col2 varchar2) as
v_bad_row gonna_make_me_look_stoopid%ROWTYPE;
begin
   v_bad_row.col1 := p_col1;
   v_bad_row.col2 := p_col2;
   insert into
               gonna_make_me_look_stoopid
               ( col1, col2 )
        values ( v_bad_row.col1, v_bad_row.col2 );
end this_is_a_big_fail;
/


So Oracle ( tested against 11.2 and 12c ) lets this procedure compile right?  Hmmm ... if the plsql compiles then it should be runnable right?  I mean what can possibly go wrong with a simple insert statement?

BEGIN
   THIS_IS_A_BIG_FAIL ('String','STRING');
END;
Error at line 1
ORA-54013: INSERT operation disallowed on virtual columns
ORA-06512: at "THIS_IS_A_BIG_FAIL", line 6
ORA-06512: at line 1

You know been doing this gig for a long time now and probably forgotten more than I remember but typically I remember most painful error messages.

Never saw this 54013 message before ... wow ouch ... not a pretty look on my face!

Stupid DBA and stupid oracle for allowing me to compile something that could never ever actually run!





No comments:

Post a Comment