title: ORA-01461 for inherited char(1 byte) column - need to make it work using Spring JDBC (extending StoredProcedure)
body: <p>I have a SP like this</p>
  
  <pre><code>create or replace PROCEDURE myproc
    myvar in out mytable.mycol%TYPE
  </code></pre>
  
  <p>where mycol is char(1 byte)</p>
  
  <p>From java code,I try to bind a String/Character to this variable and I get </p>
  
  <blockquote>
    <p>ORA-01461 - can bind a LONG value only for insert into a LONG column</p>
  </blockquote>
  
  <p>if I replace with </p>
  
  <pre><code> myvar in out varchar2
  </code></pre>
  
  <p>then it works</p>
  
  <p>any idea on how to properly bind the value from the Java code?</p>
  
  <p>I really would like to keep using %type for stored procedure input params</p>
  
  <p>ps. this is not dup of <a href="https://stackoverflow.com/questions/9156019/ora-01461-can-bind-a-long-value-only-for-insert-into-a-long-column-occurs-when">ORA-01461: can bind a LONG value only for insert into a LONG column-Occurs when querying</a> because it refers to a char(1) column</p>
  
  <p>UPDATE</p>
  
  <p>Adding more info to this</p>
  
  <pre><code>import java.sql.CallableStatement;
  import java.sql.Clob;
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Types;
  
  import oracle.jdbc.pool.OracleDataSource;
  
  public class SimpleDbStandalonePureJdbcTest {
  
      public static void main(String[] args) throws SQLException {
  
          OracleDataSource ods = new OracleDataSource();
  
          ods.setUser("xxx");
          ods.setPassword("xxx");
          ods.setServerName("xxx");
          ods.setPortNumber(xxx);
          ods.setDriverType("thin");
          ods.setNetworkProtocol("tcp");
          ods.setDatabaseName("xxx");
  
          Connection conn = ods.getConnection();
          CallableStatement sp = conn.prepareCall("{call testleosp(?)} ");
  
          Clob clob = conn.createClob();
          clob.setString(1, "b");
          sp.setClob(1, clob);
          sp.registerOutParameter(1, Types.CLOB);
          boolean hadResults = sp.execute();
  
          while (hadResults) {
              ResultSet rs = sp.getResultSet();
              System.out.println(rs.getClob(1));
              hadResults = sp.getMoreResults();
          }
  
      }
  
  }
  </code></pre>
  
  <p>the code above works (however it sounds terribly wrong)</p>
  
  <p>table is</p>
  
  <pre><code>create table testleo (col1 char(1 byte))
  </code></pre>
  
  <p>SP is</p>
  
  <pre><code>create or replace PROCEDURE testleosp (
      io_col IN OUT testleo.col1%TYPE
  )
  IS
  BEGIN
      INSERT INTO testleo (col1) VALUES (io_col);
      COMMIT WORK;
  END;
  </code></pre>
  
  <p>If I use this</p>
  
  <pre><code>import java.io.IOException;
  import java.io.Reader;
  import java.sql.CallableStatement;
  import java.sql.Clob;
  import java.sql.Connection;
  import java.sql.SQLException;
  import java.sql.Types;
  import java.util.HashMap;
  import java.util.Map;
  
  import javax.sql.DataSource;
  
  import org.jboss.jca.adapters.jdbc.WrappedConnection;
  import org.springframework.jdbc.core.SqlInOutParameter;
  import org.springframework.jdbc.core.SqlReturnType;
  import org.springframework.jdbc.core.support.SqlLobValue;
  import org.springframework.jdbc.object.StoredProcedure;
  import org.springframework.jdbc.support.lob.DefaultLobHandler;
  import org.springframework.jdbc.support.lob.OracleLobHandler;
  import org.springframework.jdbc.support.nativejdbc.SimpleNativeJdbcExtractor;
  
  import oracle.jdbc.pool.OracleDataSource;
  
  public class SimpleDbStandaloneTest2 extends StoredProcedure {
  
      public SimpleDbStandaloneTest2(DataSource ds, String sp) {
  
          super(ds, sp);
          declareParameter(new SqlInOutParameter("io_col", Types.CLOB,null,new CLOBToStringConverter()));
  //      declareParameter(new SqlInOutParameter("io_col", Types.CLOB));
          compile();
      }
  
      class CLOBToStringConverter implements SqlReturnType {
  
          @Override
          public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName)
                  throws SQLException {
              Clob aClob = cs.getClob(paramIndex);
  
              final Reader clobReader = aClob.getCharacterStream();
  
              int length = (int) aClob.length();
              char[] inputBuffer = new char[1024];
              final StringBuilder outputBuffer = new StringBuilder();
              try {
                  while ((length = clobReader.read(inputBuffer)) != -1) {
                      outputBuffer.append(inputBuffer, 0, length);
                  }
              } catch (IOException e) {
                  throw new SQLException(e.getMessage());
              }
  
              return outputBuffer.toString();
          }
      }
  
      public Map&lt;String, Object&gt; execute() {
  
          Map&lt;String, Object&gt; inputs = new HashMap&lt;&gt;();
          Connection conn = null;
          WrappedConnection wc = null;
  
          try {
  
              conn = getJdbcTemplate().getDataSource().getConnection();
  
              if (conn instanceof WrappedConnection) {
  
                  // this runs when the app is running from inside jboss
                  wc = (WrappedConnection) conn;
  
              } else {
  
              }
  
              //https://docs.spring.io/spring/docs/3.2.18.RELEASE/javadoc-api/org/springframework/jdbc/support/lob/OracleLobHandler.html
              OracleLobHandler lh = new OracleLobHandler();
              lh.setNativeJdbcExtractor(new SimpleNativeJdbcExtractor());
              //ERROR org.springframework.jdbc.support.lob.OracleLobHandler - Could not free Oracle LOB              
              //inputs.put("io_col",new SqlLobValue("f",lh));
  
              LobHandler h = new  DefaultLobHandler();    
              inputs.put("io_col",new SqlLobValue("f",h));
  
  
          } catch (Exception e) {
              e.printStackTrace();
          } finally {
              /* Close the connections manually to prevent connection leak */
              try {
                  if (wc != null &amp;&amp; !wc.isClosed())
                      wc.close();
              } catch (SQLException e) {
              }
  
              try {
                  if (conn != null)
                      conn.close();
              } catch (SQLException e) {
              }
              /* Close the connections manually to prevent connection leak */
          }
  
          Map&lt;String, Object&gt; outMap = execute(inputs);
          return outMap;
      }
  
      public static void main(String[] args) throws SQLException {
  
          OracleDataSource ods = new OracleDataSource();
  
          ods.setUser("xxx");
          ods.setPassword("xxx");
          ods.setServerName("xxx");
          ods.setPortNumber(xxx);
          ods.setDriverType("thin");
          ods.setNetworkProtocol("tcp");
          ods.setDatabaseName("xxxx");
  
          SimpleDbStandaloneTest2 t = new SimpleDbStandaloneTest2(ods, "TESTLEOSP");
  
          Map&lt;String, Object&gt; map = t.execute();
  
          System.out.println(map);
      }
  
  }
  </code></pre>
  
  <p>it also works HOWEVER it throws an exception like this</p>
  
  <pre><code>Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call TESTLEOSP(?)}]; SQL state [99999]; error code [17012]; Parameter Type Conflict; nested exception is java.sql.SQLException: Parameter Type Conflict
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1099)
      at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1135)
      at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:142)
  Caused by: java.sql.SQLException: Parameter Type Conflict
  </code></pre>
  
  <p>Info about oracle driver</p>
  
  <pre><code>Manifest-Version: 1.0
  Ant-Version: Apache Ant 1.7.1
  Created-By: 20.12-b01 (Sun Microsystems Inc.)
  Implementation-Vendor: Oracle Corporation
  Implementation-Title: JDBC
  Implementation-Version: 12.1.0.1.0
  Repository-Id: JAVAVM_12.1.0.1.0_LINUX.X64_130403
  Specification-Vendor: Sun Microsystems Inc.
  Specification-Title: JDBC
  Specification-Version: 4.0
  Main-Class: oracle.jdbc.OracleDriver
  sealed: true
  </code></pre>
  
  <p>Info about Oracle DB</p>
  
  <pre><code>SELECT * FROM V$VERSION
  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  PL/SQL Release 11.2.0.4.0 - Production
  "CORE   11.2.0.4.0  Production"
  TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
  NLSRTL Version 11.2.0.4.0 - Production
  </code></pre>
  
  <p>Info about DB charset</p>
  
  <pre><code>SELECT * FROM NLS_DATABASE_PARAMETERS
  NLS_LANGUAGE    AMERICAN
  NLS_TERRITORY   AMERICA
  NLS_CURRENCY    $
  NLS_ISO_CURRENCY    AMERICA
  NLS_NUMERIC_CHARACTERS  .,
  NLS_CHARACTERSET    WE8ISO8859P1
  NLS_CALENDAR    GREGORIAN
  NLS_DATE_FORMAT DD-MON-RR
  NLS_DATE_LANGUAGE   AMERICAN
  NLS_SORT    BINARY
  NLS_TIME_FORMAT HH.MI.SSXFF AM
  NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
  NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR
  NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
  NLS_DUAL_CURRENCY   $
  NLS_COMP    BINARY
  NLS_LENGTH_SEMANTICS    BYTE
  NLS_NCHAR_CONV_EXCP FALSE
  NLS_NCHAR_CHARACTERSET  AL16UTF16
  NLS_RDBMS_VERSION   11.2.0.4.0
  NLS_CSMIG_SCHEMA_VERSION    5
  </code></pre>
  
  <p>My java LOCALE info as requested, was generated using this code</p>
  
  <pre><code>import java.util.Locale;
  
  public class JavaLocale {
  
      public static void main(String[] args) {
          Locale currentLocale = Locale.getDefault();
  
          System.out.println(currentLocale.getDisplayLanguage());
          System.out.println(currentLocale.getDisplayCountry());
  
          System.out.println(currentLocale.getLanguage());
          System.out.println(currentLocale.getCountry());
  
          System.out.println(System.getProperty("user.country"));
          System.out.println(System.getProperty("user.language"));
  
      }
  
  }
  </code></pre>
  
  <p>which prints</p>
  
  <blockquote>
    <p>English United States en US US en</p>
  </blockquote>
  
  <p>First I don't want to use any deprecated code.</p>
  
  <p>Second I don't want any de-allocation exception.</p>
  
  <p>Third changing the DB and the SP are not options (why they should anyway?)</p>
  
  <p>ps. I think it may be related to <a href="https://support.oracle.com/knowledge/Middleware/370438_1.html" rel="noreferrer">https://support.oracle.com/knowledge/Middleware/370438_1.html</a> unfortunately I don't have access to this repository</p>
  
  <p>Thanks in advance</p>