ORACLENERD: Oracle's New INSERT Syntax

Oracle's New INSERT Syntax

Tuesday, June 16, 2009

Not really Oracle's...just mine.

I'm busy debugging

  VALUES
  *
ERROR at line 19:
ORA-00947: not enough values

I manually count the columns and they're the same. Run it again.

  VALUES
  *
ERROR at line 19:
ORA-00947: not enough values

I then cut and paste both "halves" of the INSERT statement into excel so I can get a row-by-row compare.

Everything looks good, go!

  VALUES
  *
ERROR at line 19:
ORA-00947: not enough values

WTF?

I still haven't figured it out, but that excel picture gave me an idea...

I tend to put one column on each line, for large tables, this takes up quite a bit of vertical space. I've seen others put columns (and values) on the same line. Just looks ugly to me. Here's what this table looks like:

INSERT INTO my_table
  ( id,
    create_date,
    update_date,
    col1,
    col2,
    col3,
    col4,
    col5,
    col6,
    col7,
    col8,
    col9,
    col10,
    col11,
    col12,
    col13,
    col14 ) 
VALUES 
    seq.nextval,
    SYSDATE,
    SYSDATE,
    'A',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING' );

Wouldn't it be cool if you could do something like this though?

INSERT INTO my_table
  ( id => seq.nexval,
    create_date => SYSDATE,
    update_date => SYSDATE, 
    col1 => 'A',
    col2 => 'SOMETHING',
    col3 => 'SOMETHING',
    col4 => 'SOMETHING',
    col5 => 'SOMETHING',
    col6 => 'SOMETHING',
    col7 => 'SOMETHING',
    col8 => 'SOMETHING',
    col9 => 'SOMETHING',
    col10 => 'SOMETHING',
    col11 => 'SOMETHING',
    col12 => 'SOMETHING',
    col13 => 'SOMETHING',
    col14 => 'SOMETHING' );

1. You'd save space.
2. It would be easier to read and
3. It would be easier to debug

Thoughts?

update:
I did solve my problem, I was missing the opening parenthesis in the VALUES clause.

update 2:
I created an "Idea" over at Oracle Mix, check it out and vote for it here.

Labels: oracle, sql

¶ 6/16/2009 05:00:00 PM

Comments:

Yes, it'd be great if there could be some sort of named parameter convention for the humble insert statement! I like it.

However, I'm generally one for writing the columns horizontally, which is just as easy to compare in excel *{;-)

(I didn't spot your mistake either, until you pointed it out... sometimes it's the most obvious ones we fail to spot!)

# posted by

Boneist : June 16, 2009 at 5:37 PM

@boneist

Horizontally? You mean you exceed the 80-90 character limit on a page? I'm aghast.

That could work, I've just never found scrolling to the right very easy or very fun. Perhaps it's all in the mouse?

# posted by

oraclenerd : June 16, 2009 at 5:58 PM

That looks like a Ruby hash

# posted by

Rich : June 16, 2009 at 6:14 PM

@rich

I'm not familiar with the Ruby hash...looking at it though, yeah, it sort of does.

I think I had in mind named notation, the PL/SQL construct.

I wouldn't mind seeing it though.

chet

# posted by

oraclenerd : June 16, 2009 at 8:18 PM

Hi Chet,

Well you could always use the ROWTYPE method, like this -

declare
vEmp EMP%ROWTYPE;
begin
vEmp.empno := 99;
vEmp.ename := 'JOHN';
insert into emp values vEmp;
end;

Makes it much easier to get your values parameters right (there's only 1!).

Although there are definitely some reasons why you might not want to use this method for updates etc.

John.

# posted by

John Scott : June 17, 2009 at 5:25 AM

@oraclenerd: nah, only when doing adhoc inserts eg. in release scripts for config tables, etc

Depends on how many columns there are as to whether I split onto different lines in pl/sql...

# posted by

Boneist : June 17, 2009 at 6:45 AM

That's a rockin' great idea!

I can't believe somebody hasn't thought of that.

The whole insert scene is such a pain.

I ended up doing crazy stuff with comments like:
insert into
some_90_column_table
(col1 -- 01.
col2 -- 02.
col3 -- 03.
col4 -- 04.
... -- ...
last_col) -- 90.
values
(val1 -- 01.
val2 -- 02.
val3 -- 03.
val4 -- 04.
... -- ...
last_val) -- 90.

Yeah, I know, that's a bunch of crap that's a pain in the arse.

But it made sense to me for a while.

Then I was doing
insert into
some_90_col_table
(col01,col02,col03,col04,col05
,col06,col07,col08,col09,col10
,...
,col86,col87,col88,col89,col90)
values
(val1,...
,...,val90)

Which, yeah, isn't great either 'cause sometimes 5 column names or values don't fit on a line.

So there's no real way using fancy formatting to organize anything.

Your suggestion though ... freakin' A man, that's the way to go!

Calling Larry Ellison now ... haha

# posted by

Mike : June 19, 2009 at 2:01 PM

Chet, I was just thinking that the named parameter approach would be terrific for INSERT... but google show me that you beat me to it!

# posted by

Duke : September 17, 2009 at 11:07 AM

duke,

I even created an Idea at Oracle Mix, you can see it here. I should update the post with that link as well.

First! ;)

# posted by

oraclenerd : September 17, 2009 at 11:12 AM

Someone else agrees...
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:672724700346558185#1975351500346711870

# posted by

Duke : September 21, 2009 at 11:47 AM

How cool!

We just need to keep drumming up support then...to get everyone to vote for it. Maybe they'll listen!

# posted by

oraclenerd : September 21, 2009 at 11:50 AM

Why don't the little arrows point towards where the value is going to?

(I believe that was my thought when first exposed to the pl syntax).

# posted by

Joel Garry : February 2, 2010 at 2:44 PM

@Joel

Never thought about that...would be very interesting though.

# posted by

oraclenerd : February 7, 2010 at 11:01 PM

The named approach is a GREAT idea. But why bother with an arrow ??? Just an equal sign will do (think assignment).

# posted by

Anonymous : April 20, 2010 at 7:07 PM

@Anonymous,

I agree...but the "=>" follows Oracle's syntax in PL/SQL so it just seemed like a natural fit. I'm sure it could be done either way though...

chet

# posted by

oraclenerd : April 21, 2010 at 2:01 AM