Tuesday, June 16, 2009

Oracle's New INSERT Syntax

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.

15 comments:

Boneist said...

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!)

oraclenerd said...

@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?

Rich said...

That looks like a Ruby hash

oraclenerd said...

@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

John Scott said...

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.

Boneist said...

@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...

Mike said...

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

Duke said...

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!

oraclenerd said...

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! ;)

Duke said...

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

oraclenerd said...

How cool!

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

Joel Garry said...

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).

oraclenerd said...

@Joel

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

Anonymous said...

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

oraclenerd said...

@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