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 / June 2005

Tip: Looking for answers? Try searching our database.

RefCursor works in SQL*Plus but not JDBC

Thread view: 
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 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.