Oracle: Insert into table(column_list) values(values_list) with some columns defined as default NULL

Question

Suppose you have this Oracle 11g table:

table sample(
  mykey int, --primary key
  myval varchar2(10 byte) not null,
  col1 varchar2(10 byte),
  col2 varchar2(10 byte) default NULL)

If you execute this statement from sqldeveloper for example:

insert into sample(mykey, myval) values (1, 'test');

You get

mykey    myval   col1    col2
1        test    null    null

Note that it doesn't make a difference for the two nullable columns to specify the 'default null' or not, you'll still have 'null' entered as value for you.

Now, why does the exact same insert from JDBC driver fail with the 'Not enough values' error?

Furthermore, if the column col2 is defined without 'default null', but is still nullable, the JDBC insert works and sets 'null' for both col1 and col2.

Is it just superfluous or plain wrong to specify 'default null' for a column? If Oracle allows me to create the column with that default value and allows me to write SQL code that inserts as in the example correctly, I don't think it's wrong/illegal to do so.

Is this something related to the Oracle DB (or any DB for that matter) or could the application inserting via JDBC driver be issuing a wrong statement?

Thanks in advance

@Joram - sorry, if I had it I'd have posted it. I'm using TIBCO BusinessWorks so I don't have actual Java code in front of me but the end result is a JDBC insert. I'm just interested in knowing if specifying 'default null' is wrong/can lead to issues on Oracle of if the problem lies somewhere else — grog
– grog, Commented Sep 5, 2014 at 8:58

score 1 · Accepted Answer · 2014-09-05 09:00:06Z

1

Is it just superfluous or plain wrong to specify 'default null' for a column?

Yes, it superfluous. DEFAULT NULL is implied if you don't specify a default value.

After all that's the only sensible behavior: if you don't provide a value, the only possible choice is to store "unknown" in that column. And that's precisely what NULL means: it is unknown whether there is a value or not.

Now, why does the exact same insert from JDBC driver fail with the 'Not enough values' error?

I don't believe that you are using the exact same statement in your Java program. The "not enough values" error only appears if you - well - don't supply enough values. E.g. by providing less values in the values clause than you have columns in the insert part. The following statement would cause that error:

insert into sample(mykey, myval, col1) values (1, 'test');

I haven't tried, but it could be that something like the following would also cause this error:

PreparedStatement pstmt = connection.prepareStatement("insert into sample(mykey, myval, col1, col2) values (?,?,?,?)");
pstmt.setInt(1, 1),
pstmt.setString(2, 'test');

Note that setXXX() was not called for the third column.

edited Sep 5, 2014 at 9:00

answered Sep 5, 2014 at 8:54

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

grog Over a year ago

Thank you, this does it. If it's just superfluous, not illegal/wrong, then the issue is not on the Oracle side.

grog Over a year ago

Just to clarify, I am passing enough values; the statement works if I declare the two columns nullable but don't set a default value, while it fails if I add 'default null' to one of them and try passing the exact same values (no duplicate key issues etc as the table is emptied before every retry)

user330315 Over a year ago

@grog: to understand that behavior we would need to see the Java code that generates the INSERT statements. It certainly isn't caused on the Oracle side.

Giannis Paraskevopoulos · Accepted Answer · 2014-09-05 08:47:51Z

0

I believe that the JDBC would create an SQL similar to the following:

insert into sample(mykey, myval, col1, col2 ) values (?, ?, ?, ?);

Just to say, i am not into JAVA so the parameters may be written differently.

Having said that, your client expects 4 values and not just 2. It wouldn't know that you may want to only pass a certain set of columns.

answered Sep 5, 2014 at 8:47

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

1 Comment

grog Over a year ago

Sorry, my client does not expect 4 values, that's why we have nullable columns. The JDBC insert I perform is: insert into sample(mykey, myval) values (?, ?); which is legal and executes correctly. The issue is, it fails if I specify 'default null' for one of the nullable columns. I'm just trying to determine if it's an Oracle issue or not.

Collectives™ on Stack Overflow

Oracle: Insert into table(column_list) values(values_list) with some columns defined as default NULL

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related