Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsWhite Papers
Discussion GroupsFirst AidDatabasesJavaBeansGUIJava 3DVirtual MachineCORBASecurityToolsGeneral
Java DirectoryOpen Source ProjectsSample Book ChaptersUser GroupsWeb Resources
Related Topics
Databases.NETMore Topics ...

Java Forum / Databases / August 2004

Tip: Looking for answers? Try searching our database.

Resultset from Java Stored Procedure

Thread view: 
MAB - 09 Aug 2004 12:05 GMT
I want my Java stored procedure to return (as an out parameter) a resultset
back to my java program but getting an error

Here is the Java stored procedure ( only relevant parts of code are shown.

public class StrTokenDB2
{
 public static Connection con = null ;
 public static Statement st = null ;
 public static ResultSet rs1 = null ;

    public static void tokenizeString( String str, int[] r, ResultSet[]
rs )
   {
         /* code here */

         ((OracleConnection)con).setCreateStatementAsRefCursor(true);
         st = con.createStatement();
          rs1 = st.executeQuery("select empno, ename from emp");

          rs[0] = rs1 ;

   }

}

Here is how I publish it

CREATE OR REPLACE PACKAGE Emp1 AS

 TYPE rfc IS REF CURSOR;

 PROCEDURE STokenizer( ip_str IN VARCHAR2, op_rows OUT NUMBER, op_rst OUT
rfc ) ;

END Emp1;

CREATE OR REPLACE PACKAGE BODY Emp1 AS

PROCEDURE STokenizer( ip_str IN VARCHAR2, op_rows OUT NUMBER, op_rst OUT
rfc ) AS LANGUAGE java name 'StrTokenDB2.tokenizeString(java.lang.String,
int[], java.sql.ResultSet[] )' ;

END Emp1;

and here is how I call it in my program ( again only relevant parts are
shown)

    cs = con.prepareCall("{call EMP1.STokenizer(?,?,?)}");
    cs.setString(1,str);
// This worked fine
    cs.registerOutParameter(2,Types.INTEGER);
// This also worked fine
    cs.registerOutParameter(3,OracleTypes.CURSOR);
// Until I added this

    cs.executeUpdate() ;
// get an error here

/* This is the error I get

ORA-00932: inconsistent datatypes: expected OUT Conversion failed
ORA-06512: at "SCOTT.EMP1", line 170
ORA-06512: at line 1   */

// Final two statements

    System.out.println( cs.getInt(2) + " Rows inserted") ;               //
This worked fine

    rs = ((OracleCallableStatement)cs).getCursor(3) ;                // It
doesn't even get here so I cant say about this statement :(

Would appreciate any help.

thx
Andy Flowers - 16 Aug 2004 23:45 GMT
Might sound obvious, but where is the out parameter array ResultSet[] rs
initialised ?

How long is the result array ?

What happens if the Java stored procedure is changed to...

    public static void tokenizeString( String str, int[] r, ResultSet[]
rs )
   {
         /* code here */

         ((OracleConnection)con).setCreateStatementAsRefCursor(true);
         st = con.createStatement();
         rs1 = st.executeQuery("select empno, ename from emp");

         rs = new ResultSet[1];    /init result set...
         rs[0] = rs1 ;
   }

>I want my Java stored procedure to return (as an out parameter) a resultset
> back to my java program but getting an error
[quoted text clipped - 73 lines]
>
> thx


Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.