Announcement Announcement Module
Collapse
No announcement yet.
Stored procedure with IN OUT CLOB Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Stored procedure with IN OUT CLOB

    Hello,

    I'm using an Oracle stored procedure with an IN OUT CLOB parameter.

    Code:
      PROCEDURE ping(P_paramSet IN OUT CLOB) IS
      BEGIN
             P_paramSet := 'OK';
      END ping3;
    I've defined my procedure using Spring StoredProcedure class :
    Code:
    private class DemoStoredProcedure extends StoredProcedure {
          public static final String SQL = "inv.ping";
    
          public DemoStoredProcedure(DataSource ds) {
             setDataSource(ds);
             setSql(SQL);
             setFunction(false);
             declareParameter(new SqlOutParameter("request", Types.CLOB, null,
                   new SqlReturnType()
                   {
                      public Object getTypeValue(CallableStatement cs,
                            int paramIndex, int sqlType, String typeName)
                            throws SQLException
                      {
                         try
                         {
                            final Clob aClob = cs.getClob(paramIndex);
                            final Reader clobReader = aClob.getCharacterStream();
                            int length = (int) aClob.length();
                            char[] inputBuffer = new char[1024];
                            final StringBuffer outputBuffer = new StringBuffer();
                            while ((length = clobReader.read(inputBuffer)) != -1)
                            {
                               outputBuffer.append(inputBuffer, 0, length);
                            }
                            return outputBuffer.toString();
                         } catch (IOException e)
                         {
                            throw new SQLException(e.toString());
                         }
                      }
                   }));
             compile();
          }
    }
    When I call the procedure using DefaultLobHandler:
    Code:
    DefaultLobHandler lh = new DefaultLobHandler();
    HashMap map = new HashMap();
    map.put("request", new SqlLobValue("myValue", lh));
    storedProcedure.execute(map);
    I have the following Exception and no result :
    Code:
    org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call InvestorProfile.ping3(?)}]; SQL state [null]; error code [17012]; Parameter Type Conflict; nested exception is java.sql.SQLException: Parameter Type Conflict
    java.sql.SQLException: Parameter Type Conflict
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
        at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2066)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3280)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
        at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
        at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:859)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:824)
        at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:857)
        at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:102)
        at TestStandAloneBlob$DemoStoredProcedure.execute(TestStandAloneBlob.java:97)
        at TestStandAloneBlob.execute(TestStandAloneBlob.java:51)
        at TestStandAloneBlob.main(TestStandAloneBlob.java:37)
    When I call the procedure using OracleLobHandler:
    Code:
    OracleLobHandler lh = new OracleLobHandler();
    HashMap map = new HashMap();
    map.put("request", new SqlLobValue("myValue", lh));
    storedProcedure.execute(map);
    I have the following Exception, but the result is OK (in OracleLobHandler, exception is caught, logged but not thrown) :
    Code:
    2006-01-10 11:50:43,114 [main] [ERROR] org.springframework.jdbc.support.lob.OracleLobHandler.close(409) | Could not free Oracle LOB
    java.sql.SQLException: ORA-22922: nonexistent LOB value
    
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:283)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:278)
        at oracle.jdbc.driver.T4C8TTILob.receiveReply(T4C8TTILob.java:930)
        at oracle.jdbc.driver.T4C8TTILob.freeTemporaryLob(T4C8TTILob.java:411)
        at oracle.jdbc.driver.T4CConnection.freeTemporary(T4CConnection.java:2854)
        at oracle.sql.CLOB.freeTemporary(CLOB.java:815)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:324)
        at org.springframework.jdbc.support.lob.OracleLobHandler$OracleLobCreator.close(OracleLobHandler.java:404)
        at org.springframework.jdbc.core.support.SqlLobValue.cleanup(SqlLobValue.java:223)
        at org.springframework.jdbc.core.StatementCreatorUtils.cleanupParameters(StatementCreatorUtils.java:221)
        at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.cleanupParameters(CallableStatementCreatorFactory.java:227)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:845)
        at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:857)
        at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:102)
        at TestStandAloneBlob$DemoStoredProcedure.execute(TestStandAloneBlob.java:97)
        at TestStandAloneBlob.execute(TestStandAloneBlob.java:51)
        at TestStandAloneBlob.main(TestStandAloneBlob.java:37)
    I'm afraid that, when running in an application server (it's only test now), this SQL exception cause a RollBack...

    So, what is the good LobHandler to use. Did I do something wrong ?


    Thank for your Help.

    For information :
    1. I'm using org.springframework.jdbc.datasource.DriverManagerD ataSource as DataSource
    2. using the following JDBC give the result without exception :
      Code:
      String SQL = "Call InvestorProfile.ping(?)";
      stmt = connection.prepareCall(SQL);
      stmt.setString(1, request);
      stmt.registerOutParameter(1, Types.VARCHAR);
      stmt.execute();
      logger.info(stmt.getString(1));
    3. I'm using Oracle9i Enterprise Edition Release 9.2.0.6.0
    4. I'm using latest oracle Driver : 10.2.0.1.0
    5. I had tried with both THIN and OCI drivers

  • #2
    Has someone already tested Oracle stored procedure with an IN OUT CLOB parameter?
    Last edited by clandestino; Jan 16th, 2006, 07:34 AM.

    Comment


    • #3
      I'm looking at this issue and I was just wondering if you happen to have some plain JDBC code that works with the IN OUT CLOB parameter?

      Comment


      • #4
        "declareParameter(new SqlOutParameter("request", Types.CLOB, null,
        new SqlReturnType()"

        should be

        "declareParameter(new SqlInOutParameter("request", Types.CLOB, null,
        new SqlReturnType()"

        and that's is right.

        Comment


        • #5
          Thank you... I've seen that SqlInOutParameter comes with spring 2.0. I was working with spring 1.2...

          Comment

          Working...
          X