Hi Nathan, Thank you for your help. I tried a lot to get more than one result from a PL-SQL to a jsp. I don�t know if the class is not prepared to get that or the problem is some incompatability between the types definition in oracle and Java. I finally decided to send the result of the PL-SQL as a string and then use the StringTokenizer() to fill a vector. It is not the best way of doing it but I don�t have more time to investigate what is going on.
Thank you Yacoub >From: "Nathan Wheat" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: JRun-Talk <[EMAIL PROTECTED]> >Subject: RE: Calling an Oracle procedure from a jsp using the >CallableStatement >Date: Fri, 23 Nov 2001 11:04:49 +1100 > >Yacoub, > >I don't know a whole lot about using stored procs, but I've played around. >The only thing that I can pick up is that parameter 6 'out_batch_size' >seems >to be an IN/OUT parameter, and you've only registered it as an OUT >parameter. I'm not sure how you actually setup an IN/OUT parameter - but >it >might be something like the following: > > cstmt.setInt(6, your_out_batch_size); > cstmt.registerOutParameter(6, Types.INTEGER); > >Let me know if this actually works, or if I'm way off the mark. > >Regards, > >Nathan Wheat >Technical Sales Consultant >Firmware Design >Phone: 0401 148 989 >Email: [EMAIL PROTECTED] >http://www.firmware.com.au > >Save thousands on your Web site budget! >Find out how a Content Management System can reduce Web maintenance costs: >http://go.firmware.com.au/cms/ft > > > >-----Original Message----- >From: elias yacoub [mailto:[EMAIL PROTECTED]] >Sent: Friday, 23 November 2001 12:54 AM >To: JRun-Talk >Subject: Calling an Oracle procedure from a jsp using the >CallableStatement > > >Hi everybody, > >I�m trying to call an Oracle procedure (package) from a jsp. I�m using this >code: > >try >{ > int temp_int = 0; > Connection conn = getOracleConnection(); > CallableStatement cstmt = conn.prepareCall("{ call >tepkg_opp_verify.teprc_opp_verify_attribute(?,?,?,?,?,?,?)}"); > cstmt.setInt( 1, temp_int ); > cstmt.setInt( 2, strProdReleaseId ); > cstmt.setInt(3, strQuoteId); > cstmt.registerOutParameter(4, Types.VARCHAR); > cstmt.setInt(5, temp_int); > cstmt.registerOutParameter(6, Types.INTEGER); > cstmt.registerOutParameter(7, Types.INTEGER); > cstmt.executeUpdate(); > conn.close(); >} >catch (SQLException x) >{ > log.addEntry("newAttribVal.jsp", "Error SQLx" + x); >} >catch (Exception x) >{ > log.addEntry("newAttribVal.jsp", "Exception " + x ); >} > >The package I�m trying to call: > >Package TEPKG_OPP_VERIFY AS >TYPE v_value_table_type IS table of Atri.swName%Type index by >binary_integer >(VARCHAR); > >procedure TEPRC_OPP_VERIFY_ATTRIBUTE( >locale IN INT, >gswProdReleaseId IN Prod.swprodreleaseid%TYPE (Number 15,5), >gteQuoteItemId IN Qite.tebquoteitemid%TYPE (Number 15,5), >v_value_table OUT v_value_table_type, >batch_size INT, >out_batch_size IN OUT INT, >status OUT INT); > >I didn�t find lots of references to explain how to use the >CallableStatement >class. I think the problem is in the definition of the parameter no. 4 >(because it is a Table). I don�t know if the registerOutParameter supports >it. > >The error I�m getting is : wrong number or types of arguments in call to >'TEPRC_OPP_VERIFY_ATTRIBUTE' > >Did you have a similar problem? can I use the CallableStatement to get a >set >of results? Is there any other way of doing this? > >Any help would be greatly appreciated. > >Yacoub > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
