Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


JDBC not accepting null Oracle object parameters to PL/SQL procedures

Posted on 2007-03-19
Medium Priority
Last Modified: 2013-12-29
I am calling an Oracle Stored Procedure (Oracle 9i), passing in Oracle defined objects.
It is possible that these objects are null. I am using JPublisher to generate the java objects directly based upon the Oracle Object definitions.
Problem is that I am getting the following exception
java.sql.SQLException: Invalid column type

Here is an example of one such parameter
if (convFeeConsPayee == null ){
  stmt.setNull(7, java.sql.Types.STRUCT);
else {
  stmt.setObject(7, convFeeConsPayee);
Question by:pbarry30
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
LVL 86

Accepted Solution

CEHJ earned 375 total points
ID: 18752187
>>stmt.setNull(7, java.sql.Types.STRUCT);

Is Struct the correct column type?

Author Comment

ID: 18752251
Kind of :)
The Java object representing the Oracle object definition implements wraps a MutableStruct but it is not strictly one itself.
These objects are also out parameters and the following works as expected
stmt.registerOutParameter(1, java.sql.Types.STRUCT, SITE_TYPE._SQL_NAME);
where SITE_TYPE._SQL_NAME just resolves to the real Oracle oject name
LVL 30

Assisted Solution

by:Mayank S
Mayank S earned 375 total points
ID: 18756877
>> stmt.setNull(7, java.sql.Types.STRUCT);

What if you try: stmt.setNull ( 7,  java.sql.Types.NULL ) ;
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 18761043
same result unfortunately
LVL 30

Expert Comment

by:Mayank S
ID: 18761534
What about setObject ( 7, ....NULL ) ;
LVL 86

Expert Comment

ID: 18762478

stmt.setNull ( 7,  java.sql.Types.OTHER ) ;

Author Comment

ID: 18804416
The solution is to specifiy it as a STRUCT and to specify the sqlType as well
stmt.setNull(7, java.sql.Types.STRUCT, CONSUMER_PAYEE_TYPE._SQL_NAME);

LVL 86

Expert Comment

ID: 18814465

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a vā€¦
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question