• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Oracle Record type from java

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have to call a stored procedure in oracle from java for which some of the parameters are pl/sql custom objects.

My test environment is :

Package and type creatin :
=================
Package Body is :
============
for record type object am using jpublisher to generate 'My_Rec' related class.It is giving me :
Testpack.java
TestpackMyRec.java
TestpackMyRecRef.java

so, my target is to use those classes from my java main method


when am executing without using callable stmt object am getting the error as :

Exception in thread "main" java.sql.SQLException: invalid name pattern: SAMPLE.TESTPACK_MY_REC
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:553)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:469)
at oracle.sql.StructDescriptor.initPickler(StructDescriptor.java:390)
at oracle.sql.StructDescriptor.<init>(StructDescriptor.java:320)
at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:201)
at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:169)
at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:138)
at oracle.jdbc.driver.OraclePreparedStatement.setNullCritial(OraclePreparedStatement.java:4284)
at oracle.jdbc.driver.OraclePreparedStatement.setNullInternal(OraclePreparedStatement.java:4242)
at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4218)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull(OraclePreparedStatementWrapper.java:1288)
at sqlj.runtime.profile.ref.OraPreparedStmtWrapper.setNull(OraPreparedStmtWrapper.java:1015)
at com.eidiko.jpubtest.Testpack.testprocedure(Testpack.java:184)
at com.eidiko.jpubtest.calProcedure.main(calProcedure.java:31)



Here i have attached the jpublisher generated classes , please go through it.And please suggest me with the solution.



 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Welcome to the Ranch, Ramesh!

I've added some code tags to your post to make the code more readable. You can do so yourself using the Code button when editing your post.

I believe your problem might be caused by declaring the type inside a package. Packages are PL/SQL constructs and are therefore unavailable in pure SQL, and you can only use pure SQL with JDBC. I'm a bit surprised that JPublisher created Java classes for your PL/SQL type, I'd expect the tool to refuse to do this, since they can't be used with JDBC anyway.

I'd suggest creating the type as an SQL type using the CREATE TYPE statement. You can find more information about accessing SQL types from Java in Oracle in one of our FAQ pages: How to use arrays of Oracle SQL objects. This approach has been tested and is known to work.
 
Ranch Hand
Posts: 50
5
Oracle Postgres Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
While you cannot use PL/SQL RECORD types directly as many others suggest, you can create an anonymous PL/SQL block and use that from JDBC. For instance:



As you can see, this now no longer requires you to pass the record type to the JDBC driver, but you can pass the individual record attributes instead

I've blogged about this more in detail in a blog post:
https://blog.jooq.org/2016/11/08/use-jooq-to-read-write-oracle-plsql-record-types/

The post also explains how you can automate this task, in case you have many of these record types in procedures / functions
 
WHAT is your favorite color? Blue, no yellow, ahhhhhhh! Tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic