I'm busy debugging
VALUESI manually count the columns and they're the same. Run it again.
*
ERROR at line 19:
ORA-00947: not enough values
VALUESI then cut and paste both "halves" of the INSERT statement into excel so I can get a row-by-row compare.
*
ERROR at line 19:
ORA-00947: not enough values
 |
Everything looks good, go!
VALUESWTF?
*
ERROR at line 19:
ORA-00947: not enough values
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_tableWouldn't it be cool if you could do something like this though?
( 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' );
INSERT INTO my_table1. You'd save space.
( 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' );
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.
Yes, it'd be great if there could be some sort of named parameter convention for the humble insert statement! I like it.
ReplyDeleteHowever, 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!)
@boneist
ReplyDeleteHorizontally? 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?
That looks like a Ruby hash
ReplyDelete@rich
ReplyDeleteI'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
Hi Chet,
ReplyDeleteWell 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.
@oraclenerd: nah, only when doing adhoc inserts eg. in release scripts for config tables, etc
ReplyDeleteDepends on how many columns there are as to whether I split onto different lines in pl/sql...
That's a rockin' great idea!
ReplyDeleteI 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
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!
ReplyDeleteduke,
ReplyDeleteI even created an Idea at Oracle Mix, you can see it here. I should update the post with that link as well.
First! ;)
Someone else agrees...
ReplyDeletehttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:672724700346558185#1975351500346711870
How cool!
ReplyDeleteWe just need to keep drumming up support then...to get everyone to vote for it. Maybe they'll listen!
Why don't the little arrows point towards where the value is going to?
ReplyDelete(I believe that was my thought when first exposed to the pl syntax).
@Joel
ReplyDeleteNever thought about that...would be very interesting though.
The named approach is a GREAT idea. But why bother with an arrow ??? Just an equal sign will do (think assignment).
ReplyDelete@Anonymous,
ReplyDeleteI 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