aspose file tools*
The moose likes JDBC and the fly likes Oracle Stored Procedure Problem... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle Stored Procedure Problem..." Watch "Oracle Stored Procedure Problem..." New topic
Author

Oracle Stored Procedure Problem...

Vijay S. Rathore
Ranch Hand

Joined: Oct 29, 2001
Posts: 449
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


SCJP, SCJD, SCWCD1.4, IBM486, IBM484, IBM 483, IBM 287, IBM141, IBM Certified Enterprise Developer - WebSphere Studio, V5.0
Author of IBM 287 Simulator Exam
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle Stored Procedure Problem...
 
Similar Threads
Oracle Java Stored Procedure ... Urgent help needed.
Empty SQLException on attempt to run a stored procedure
Global temporary table
Weblogic Connection Pool, Oracle Stored Procedure, Remote Sybase Database
Calling DB2 stored procedures