• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Oracle Stored Procedure Problem...

 
Ranch Hand
Posts: 449
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am using an oracle driver to connect to Oracle8.1.7 database. I am able to connect to a table, but when I try to connect to stored procedure, it always throws error.
The code that I am using to connect to table is
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
con = DriverManager.getConnection("jdbc racle:thin:@sfddb:1527 EV", "ID", "PWD");
This code works to connect to table :
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from PO.PO_HEADER ");

But this code fails :
CallableStatement cstmt = con.prepareCall("{? = call PO.WFC_GETPO(?)}");
cstmt.clearParameters();
cstmt.registerOutParameter(1, OracleTypes.VARCHAR);
cstmt.setInt(2, 222);
cstmt.execute();
System.out.println(cstmt.getString(1));

Well I have already tried different combination of values to call this stored procedure.
The stored procedure takes and In parameter as PO Number and returns true or false as boolean value. We are able to call this stored procedure from some other external sources.
Are we missing some setting before calling stored procedure?
Well you can consider me a novice in Java Oracle connection with stored procedure. And I have already studied the stored procedure samples available on Oracle site.
Will appreciate any help

Vijay
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Vijay,
I assume you are unfamiliar with the following excerpt from the "Oracle 8i JDBC Developer's Guide and Reference"
http://download-west.oracle.com/docs/cd/A87860_01/doc/java.817/a83724/ref4.htm#1005988


It is not feasible for Oracle JDBC drivers to support calling arguments or return values of the PL/ SQL RECORD, BOOLEAN, or table with non-scalar element types.


Hopefully, this explains the behaviour you are seeing.
Good Luck,
Avi.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic