Solved

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

Posted on 2007-03-19
8
416 Views
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);
}
0
Comment
Question by:pbarry30
[X]
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
8 Comments
 
LVL 86

Accepted Solution

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

Is Struct the correct column type?
0
 

Author Comment

by:pbarry30
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
0
 
LVL 30

Assisted Solution

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

What if you try: stmt.setNull ( 7,  java.sql.Types.NULL ) ;
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 

Author Comment

by:pbarry30
ID: 18761043
same result unfortunately
0
 
LVL 30

Expert Comment

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

Expert Comment

by:CEHJ
ID: 18762478
Try

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

Author Comment

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

Thanks
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 18814465
:-)
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

688 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