Java Forum / Databases / June 2005
RefCursor works in SQL*Plus but not JDBC
dsemmler@gmail.com - 27 Jun 2005 22:15 GMT I have a stored procedure that calls a remote database and returns a refcursor. In SQL*Plus, everything works great and I can print the refcursor and the data is all there. When attempting to call this from a JSP page using JDBC (Oracle thin driver 10.1.0.2 or 9.5.0.2) I get an error.
With the 10 driver, I get this error: java.sql.SQLException: Bigger type length than Maximum
With 9.5 driver, I get this error: ORA-02019: connection description for remote database not found
If I change the stored procedure to call the local database (the one connected to via JDBC) only, the refcursor works fine. It is when I attempt to call the remote DB within the stored procedure that the JDBC call has the errors. Both versions work fine in SQL*Plus.
I have searched all over and have yet to find any possible solutions. The thing that has me stumped is that the refcursor works in SQL*Plus and works via JDBC when calling the local database, but errors when attempting a remote database.
If anyone has any ideas, I would greatly appreciate it.
Thanks, Derek
dsemmler@gmail.com - 28 Jun 2005 15:09 GMT Thought I would post an update. With the 10 driver, even the local database call returning a refcursor does not work properly. It throws the same "Bigger type length than Maximum" exception.
Code snippet:
strSQL = "{ call pkg.func_name(?,?,?,?) }"; // returns refcursor callStmt = dbConn.prepareCall(strSQL); callStmt.setString(1, param1); callStmt.setString(2, param2); callStmt.setString(3, param3); callStmt.registerOutParameter(4, OracleTypes.CURSOR); callStmt.execute(); rsData = ((OracleCallableStatement)callStmt).getCursor(4);
============================
The call is dying on the execute command and has the following trace:
java.sql.SQLException: Bigger type length than Maximum at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162) at oracle.jdbc.driver.DatabaseError.check_error(DatabaseError.java:885) at oracle.jdbc.driver.T4CMAREngine.buffer2Value(T4CMAREngine.java:2231) at oracle.jdbc.driver.T4CMAREngine.unmarshalUB2(T4CMAREngine.java:1048) at oracle.jdbc.driver.T4CTTIdcb.receiveCommon(T4CTTIdcb.java:112) at oracle.jdbc.driver.T4CTTIdcb.receiveFromRefCursor(T4CTTIdcb.java:104) at oracle.jdbc.driver.T4CResultSetAccessor.unmarshalOneRow(T4CResultSetAccessor.java:165) at oracle.jdbc.driver.T4CTTIrxd.unmarshal(T4CTTIrxd.java:785) at oracle.jdbc.driver.T4CTTIrxd.unmarshal(T4CTTIrxd.java:702) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:527) at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:180)at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:783)at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1027)at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2885)at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2926)
Sorry for the formatting but I didn't see a way to mark snippets or alter the size of the text to make it fit better.
joeNOSPAM@BEA.com - 28 Jun 2005 18:08 GMT How big are your string parameter values? The exception seems to have nothing to do with RefCursors so far... Joe Weinstein at BEA Systems
dsemmler@gmail.com - 28 Jun 2005 18:58 GMT The three strings being passed are all short, two under 10 characters and one is an empty string.
Here is the procedure, if that helps:
PROCEDURE SHOW_USERS(server_name VARCHAR2, instance_name VARCHAR2,in_users VARCHAR2, outlist in out rc_t) AS l_rc_t rc_t; alias VARCHAR2(50):= get_tns_alias(server_name,instance_name); sqlstr VARCHAR2(32000); l_os_user VARCHAR2(50) := sys_context('USERENV','OS_USER'); l_user VARCHAR2(50) := '%'||UPPER(TRIM(in_users))||'%';
BEGIN sqlstr:= 'open outlist for SELECT username,account_status' || ' FROM APP_USERS@' || alias || ' WHERE USERNAME like ''' || l_user || '''' ; dbms_output.put_line(substr(sqlstr,1,250)); EXECUTE IMMEDIATE(sqlstr);
EXCEPTION WHEN NO_DATA_FOUND THEN open outlist for SELECT 'No user found for pattern ' || l_user from dual; END;
dsemmler@gmail.com - 28 Jun 2005 19:13 GMT I forgot to mention that there is also a procedure that was created as a test that is nearly identical except that it only returns a single VARCHAR2 value as opposed to a ref_cursor. That procedure works fine with the 10 driver. It is only the procedure quoted above that throws the error.
joeNOSPAM@BEA.com - 28 Jun 2005 23:17 GMT Ok, then it does sound like a driver problem, and the way to get it fixed soonest is to see if the problem shows up with as simple/generic a procedure as you can write (select from DUAL etc), so it can run with minimal DBMS setup or type-definitions etc. If you can, show me and if you don't have a customer ID with Oracle I can file a TAR for the problem. Joe Weinstein at BEA
dsemmler@gmail.com - 30 Jun 2005 04:19 GMT Joe, thanks for the help. Tomorrow morning when I get to the office I will try a very simple procedure and see how that works. I will have to check with one of the DBAs to see if we have an Oracle customer ID, I would imagine we do but I am not sure.
In the end, I may revert back to the 9.2.0.5 driver, but even that has problems unless the database link is created as a public link for the ref_cursor. When returning a VARCHAR2 from a remote DB call, the procedures do not require the public link but as soon as I try to call the procedure that makes a remote DB call and returns a ref_cursor, it only works if we have a public link. Given the nature of the data, the DBA said he does not want a public link, so even in that case I will still need to come up with some workaround.
Thanks again for your help Joe, and for creating the test case.
dsemmler@gmail.com - 01 Jul 2005 00:00 GMT Joe,
Just wanted to follow up that I now have it working. The DBA notified me that the local server did not have db_domain set and the remote servers did have it set. He updated that and now everything is working properly.
I do have to test though because I had also upgraded to the 10.1.0.4 driver and it could have just been coincidence that he updated that at the same time. I want to revert back to the 10.1.0.2 driver to see if that still works with the db_domain now being set to the same on all servers.
Thanks for all of your help.
joeNOSPAM@BEA.com - 28 Jun 2005 23:56 GMT I just ran this code and it went OK...
import oracle.sql.*; import oracle.jdbc.*; import java.sql.*; import java.util.*;
public class oraproc3 { public static void main(String argv[]) throws Exception { java.sql.Connection conn = null; try { java.util.Properties props = new java.util.Properties();
Driver d = new oracle.jdbc.OracleDriver(); props.put("user", "scott"); props.put("password", "tiger");
conn = d.connect("jdbc:oracle:thin:@JOE:1521:S9205", props); System.out.println(conn.getMetaData().getDriverVersion() );
Statement st = conn.createStatement();
try{st.executeUpdate( "drop table joe");}catch(Exception ee){} st.executeUpdate( "create table joe(foo int, bar int)"); st.executeUpdate( "INSERT INTO JOE VALUES (1,1)"); st.executeUpdate( "INSERT INTO JOE VALUES (2,2)");
st.executeUpdate ( " CREATE OR REPLACE PACKAGE test_pkg " + " as " + " TYPE testType is REF CURSOR; " + " PROCEDURE ECOM2 ( " + " outchar1 VARCHAR2, " + " outchar2 VARCHAR2, " + " outchar3 VARCHAR2, " + " data_list IN OUT testType " + " ); " + " end test_pkg; " );
st.executeUpdate ( " CREATE OR REPLACE PACKAGE BODY test_pkg " + " as " + " procedure ECOM2 (outchar1 VARCHAR2, " + " outchar2 VARCHAR2, " + " outchar3 VARCHAR2, " + " data_list IN OUT testType) " + " IS " + " BEGIN " + " OPEN data_list FOR " + " SELECT * FROM JOE " + " where 1 = (SELECT 1 FROM DUAL); " + " END ECOM2; " + " end test_pkg; " );
CallableStatement cs = conn.prepareCall("{ call SCOTT.test_pkg.ECOM2(?,?,?,?)}");
cs.setString(1,"qwe"); cs.setString(2,"qwe"); cs.setString(3,"qwe"); cs.registerOutParameter(4,OracleTypes.CURSOR); cs.execute(); ResultSet rs = (ResultSet)(cs.getObject(4)); } catch (Exception e) { e.printStackTrace(); } finally // Always close connection, no matter what failure may have occurred. { try {conn.close();} catch (Exception e) {} } } }
Free MagazinesGet 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 ...
|
|
|