3 Replies Latest reply: Oct 31, 2007 12:18 PM by 807603 RSS

    java.sql.SQLException: ORA-06502 accessing oracle CLOB

    807603
      Hi,

      I am reposting this as I didn't get any response previously. Please suggest.

      I have a table with a column CLOB type. I wrote a procedure to select data from the from CLOB. I am trying to display this on the JSP page using CallableStatement. Its doing good if the data in the CLOB is not long (may be below 32k). But if the data is more than 32k, I am getting the exception

      java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error

      Not sure, why this is happening. Below I have attached some code and environment I am working on.

      Below code may miss bits and pieces but its all tested for less data in CLOB and it was working good. Problem is only with large data in CLOB

      Please suggest me why am i getting the exception if the data is more? I have been fiddling around this since 3 days. Please suggest.

      Like mentioned earlier it works if I execute the oracle procedure directly on oracle. I doubt something is wrong while its getting the data back on jdbc link. (just a guess)

      Environment
      ==========

      Oracle : 10.2.0
      App Server : JBOSS 4.2.0
      Oracle Driver : ojdbc14.jar (downloaded latest from otn)

      Table
      structure : DEEP_CLOB
      ==================
      ID NUMBER
      ID_DESC CLOB

      Procedure
      ========
      PROCEDURE getDesc(
      --* Returned value
      oraValue IN OUT CLOB
      ) IS

      i1 CLOB;

      BEGIN

      SELECT substr(id_desc,1, dbms_lob.getlength(id_desc)) INTO i1
      FROM traqdba.deep_clob
      WHERE id = 65;

      oraValue := i1;


      END getDesc;

      JAVA (This below code may miss some bits and pieces here and there,
      but its working good with low amount of data: TESTED)
      ========================================================
      public void getClob throws SQLException
      {

      try
      {

      // data type that we expect the proc to return
      int returnType = OracleTypes.VARCHAR;
      Object retValue;
      DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
      Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@server:1521:XXXX", "XXXX", "XXXX");

      CallableStatement cs = conn.prepareCall("DeepClob.getDesc()");

      cs.registerOutParameter(1,returnType);
      cs.executeUpdate();

      if (returnType == OracleTypes.CLOB)
      {
      retValue = cstmt.getClob(1);
      }

      //reading as string
      if (retValue instanceof oracle.sql.CLOB)
      {

      oracle.sql.CLOB tempRetVal;
      String strVal = null;
      tempRetVal = (oracle.sql.CLOB) retValue;

      try
      {
      long len = tempRetVal.length();
      strVal = tempRetVal.getSubString(len, 1);
      }catch (Exception e) {log.info("---->getValue():Error in getting clob : " + e);}

      System.out.println(strVal);
      }


      ps.close();
      conn.close();
      }
      catch (Exception e) {
      e.printStrack();
      System.out.println("Java Exception caught, error message="+e.getMessage());
      }
      }

      Here is the Stack Trace.

      NOTE:You may see OraProcValue.java and ShoClob.jsp in the stack trace, I have the getClob method in the OraProc.java class and calling this in ShoClob.jsp.

      It throws an error just after calling the cs.executeUpdate() statement. (fails on calling this statement)



      Thanks

      Stack Trace
      java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error
      ORA-06512: at line 1

      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
      at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
      at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
      at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
      at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
      at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
      at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:736)
      at OraProcValue.getClob(OraProcValue.java:232)
      at org.apache.jsp.xxxxx.ShoClob_jsp._jspService(ShoClob_jsp.java:1496)
      at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
      at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:384)
      at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:320)
      at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:228)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
      at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:175)
      at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
      at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:156)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:216)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:624)
      at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:445)
      at java.lang.Thread.run(Thread.java:595)