How to insert Null values Oracle Types

Question

This is my code to call procedure from java.

String DocumentSQL = "{call PKG_CREATE.PROC_CREATE_Claim(?,?)}";
callableStatement = connection.prepareCall(insertDocumentSQL);
callableStatement.setString(1,"Test");
callableStatement.setArray(2,claimArray);

Here I have used Oracle type Array. When claimArray has values its working fine. Sometimes it will be Null. So that I added like this

if(claimArray!=null){
   callableStatement.setArray(2,claimArray);
}
else {                         
   callableStatement.setArray(2,null);
   or 
   callableStatement.setNull(2, OracleTypes.NULL);
}

It shows Error. How I can set Null Array?

if(claimArray!=null){ callableStatement.setArray(2,claimArray); } else callableStatement.setArray(2,null); How exactly do you think this differs from callableStatement.setArray(2,claimArray); Writing more code is not always better. "It shows Error", can you be more specific? check the table design. If it is declared as not nullable, no, you can't add null, otherwise, yes. — Stultuske
– Stultuske, Commented Dec 15, 2015 at 7:33
Did you try callableStatement.setNull(2, java.sql.Types.ARRAY);? — user330315
– user330315, Commented Dec 15, 2015 at 7:41

Abdelhak · Accepted Answer · 2015-12-15 09:23:55Z

1

If you want to se a null array you should create a void array like this:

  callableStatement.setArray(2,new typeArray[size]);

For example:

 public static void main(String[] args) {
    String[] arr = new String[4];
    for (String i : arr) {

        System.out.println(i);
    }

}

Output:

 null
 null
 null
 null

edited Dec 15, 2015 at 9:23

answered Dec 15, 2015 at 7:37

Abdelhak

8,4154 gold badges25 silver badges37 bronze badges

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

1 Comment

KSK Over a year ago

what you mean typeArray[size]. Can you post some example?

John Scattergood · Accepted Answer · 2015-12-15 08:03:17Z

0

You need to use the 3 parameter setNull() function for the null case:

void setNull(int parameterIndex, int sqlType, String sql_type_name)

takes a SQL type name in addition to a parameter index and a SQL type code. You use this method only when the SQL type code is REF, ARRAY, or STRUCT.

https://docs.oracle.com/cd/F49540_01/DOC/java.815/a64685/oraext3.htm

answered Dec 15, 2015 at 8:03

John Scattergood

1,0428 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to insert Null values Oracle Types

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related