CODEWITHSUNDEEP
javaoracle-databasejdbc
Christoph Pölsterl
Christoph Pölsterl

Reputation: 199

JDBC/Oracle insert NULL without preparedStatement

I have a very confusing problem I don't understand. I am building INSERT statements for different tables and execute them as Batch at the end.

The Statements are build like this:

INSERT INTO table VALUES ('anyValue', 'anyValue', 'anyValue', ...)

The table definition doesn't contain only varchar, there are number, float and timestamp also, but it doesn't matter at all because Oracle makes a type conversion by itself

BUT... If I try to insert NULL in a NUMBER it says invalid number, but for example in PLSQL developer it works like a charm. Looks like this:

INSERT INTO table (varchar, varchar, number, ...) VALUES ('value', 'value', NULL, ...) <- doesnt work, but why?

I am using Oracle11g database with the ojdbc6.jar library. I know the Syntax above is not correct, but it's just to clarify the types and what I try to insert.

Is there any solution to this, without using preparedStatement and setNull(), because I don't know the field types at runtime

EDIT

INSERT ALL  INTO comediatypeadjustments VALUES('1','TEST','1','DEL_COC_361','DEL_COC_361','100','Y','Y','N','GREEN','Y','Y','N','N','LIGHTGREEN','STD','10.65','STD','55.25','N','N',NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,NULL,'Y','WHITE','STD_BOLD_UL','44.25',NULL,NULL,'N','N','N',NULL,'N',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,'Y','N') INTO comediatypeadjustments VALUES('2','TEST','2','DEL_COC_362','DEL_COC_362','100','Y','Y','N','BLUE','Y','Y','N','N','LIGHTGREEN','STD','10.65','STD','55.25','N','N',NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,NULL,'Y','WHITE','STD_BOLD_UL','44.25',NULL,NULL,'N','N','N',NULL,'N',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,'Y','N') SELECT * FROM DUAL

Exception in thread "main" java.sql.BatchUpdateException: Fehler bei Stapelverarbeitung aufgetreten: ORA-01722: invalid number

at oracle.jdbc.driver.OracleStatement.executeBatch(OracleStatement.java:4586) at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:230) at de.draexlmaier.pcm.test.testcase.TestCaseHelper.initializeTestData(TestCaseHelper.java:285) at de.draexlmaier.pcm.test.testcase.TestCaseHelper.setTestName(TestCaseHelper.java:118) at de.draexlmaier.pcm.test.utilities.Main.main(Main.java:19)

Upvotes: 0

Views: 1273

Answers (2)

Avrajit Roy
Avrajit Roy

Reputation: 3303

Since you are using Java, I guess there will be some mapping issues with the columns and the inserted values. Please specify column names first and then put values like 

insert into <table>(column_1,column_2,column_3,...) values(val1,val2,val3...);

Upvotes: 0

Roger Dwan
Roger Dwan

Reputation: 770

You could try empty string '', oracle db will treat it as null.

Upvotes: -1

Related Questions