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
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.col1 := p_col1;
v_bad_row.col2 := p_col2;
( col1, col2 )
values ( v_bad_row.col1, v_bad_row.col2 );
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?
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!