Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

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

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
pbarry30
Asked:
pbarry30
  • 3
  • 3
  • 2
2 Solutions
 
CEHJCommented:
>>stmt.setNull(7, java.sql.Types.STRUCT);

Is Struct the correct column type?
0
 
pbarry30Author Commented:
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
 
Mayank SAssociate Director - Product EngineeringCommented:
>> stmt.setNull(7, java.sql.Types.STRUCT);

What if you try: stmt.setNull ( 7,  java.sql.Types.NULL ) ;
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
pbarry30Author Commented:
same result unfortunately
0
 
Mayank SAssociate Director - Product EngineeringCommented:
What about setObject ( 7, ....NULL ) ;
0
 
CEHJCommented:
Try

stmt.setNull ( 7,  java.sql.Types.OTHER ) ;
0
 
pbarry30Author Commented:
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
 
CEHJCommented:
:-)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now