ORA-01461 for inherited char(1 byte) column - need to make it work using Spring JDBC (extending StoredProcedure)

Question

I have a SP like this

create or replace PROCEDURE myproc
  myvar in out mytable.mycol%TYPE

where mycol is char(1 byte)

From java code,I try to bind a String/Character to this variable and I get

ORA-01461 - can bind a LONG value only for insert into a LONG column

if I replace with

 myvar in out varchar2

then it works

any idea on how to properly bind the value from the Java code?

I really would like to keep using %type for stored procedure input params

ps. this is not dup of ORA-01461: can bind a LONG value only for insert into a LONG column-Occurs when querying because it refers to a char(1) column

UPDATE

Adding more info to this

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();
        }

    }

}

the code above works (however it sounds terribly wrong)

table is

create table testleo (col1 char(1 byte))

SP is

create or replace PROCEDURE testleosp (
    io_col IN OUT testleo.col1%TYPE
)
IS
BEGIN
    INSERT INTO testleo (col1) VALUES (io_col);
    COMMIT WORK;
END;

If I use this

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<String, Object> execute() {

        Map<String, Object> inputs = new HashMap<>();
        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 && !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<String, Object> 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<String, Object> map = t.execute();

        System.out.println(map);
    }

}

it also works HOWEVER it throws an exception like this

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

Info about oracle driver

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

Info about Oracle DB

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

Info about DB charset

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

My java LOCALE info as requested, was generated using this 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"));

    }

}

which prints

English United States en US US en

First I don't want to use any deprecated code.

Second I don't want any de-allocation exception.

Third changing the DB and the SP are not options (why they should anyway?)

ps. I think it may be related to https://support.oracle.com/knowledge/Middleware/370438_1.html unfortunately I don't have access to this repository

Thanks in advance

Interesting, looks like a bug. Another [reason to avoid `char`](https://stackoverflow.com/a/42165653/230471). — William Robertson, Feb 26 '18 at 13:38
Possible duplicate of [ORA-01461: can bind a LONG value only for insert into a LONG column-Occurs when querying](https://stackoverflow.com/questions/9156019/ora-01461-can-bind-a-long-value-only-for-insert-into-a-long-column-occurs-when) — XING, Feb 26 '18 at 13:53
don't think so. I went to this post before posting mine. it's a completely different issue — Leo, Feb 26 '18 at 14:33
Are you really sure that `mytable.mycol%TYPE` is a `char(1)`? It would be very suspicious when Oracle cannot handle the datatype correctly. — Uwe Plonus, Feb 26 '18 at 15:37
Look at Kiran's answer on the possible duplicate thread - ORA-01461 also comes up when you exceed the column width. You're defining a variable that you can only put ONE BYTE into, and Java is trying to put a larger string in than that. Possibly it's even a single multi-byte character. — kfinity, Feb 26 '18 at 17:09
column is char(1) and I've tried to insert a single char string and a Character object — Leo, Feb 26 '18 at 17:23
You should be able to easily change the data type of the column from char(1 byte) to char(1 char), since there is no loss of information. Something like `alter table your_table modify (your_col char(1 char));` - assuming that won't break other things. — , Feb 26 '18 at 19:19
however, I am still struggling to make it work using Spring JDBC (gonna open another question about this...) — Leo, Feb 27 '18 at 13:58
does it have something to do with the fact that single unicode characters can be as large as 4 bytes... — Palcente, Feb 27 '18 at 14:30
have you tried with the ASCII spin of the setter, also what is your database default characterset — Palcente, Feb 27 '18 at 14:39
added the charset info. Not sure what you mean about "ASCII spin of the setter". What data types would you declare in the SP and what java type would you use in this situation? — Leo, Feb 27 '18 at 15:29
What kind of characters are you inserting into the table? Is there a chance that they fall outside of the DB's charset encoding (`WE8ISO8859P1`)? — Mick Mnemonic, Feb 28 '18 at 18:49
How were you actually binding the value in the code that threw the original error - you don't seem to have included that? Did you get the error only through Spring, or with the standalone Java code too? I haven't been able to reproduce yet modifying the CLOB back to String/char. And what is your Java locale (and/or OS :LANG)? — Alex Poole, Mar 01 '18 at 12:36
Hi Alex. I've included both the example using vanilla jdbc and spring jdbc above. The first one is the class SimpleDbStandalonePureJdbcTest and the second one is the class SimpleDbStandaloneTest2. I am adding my locale info in the question itself — Leo, Mar 01 '18 at 13:30

score 4 · Accepted Answer · answered Mar 02 '18 at 04:59

Here's how I've solved this issue. I could not avoid CLOBs unfortunately.

import java.io.IOException;
import java.io.Reader;
import java.io.StringReader;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
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.object.StoredProcedure;
import org.springframework.jdbc.support.SqlValue;

import oracle.jdbc.pool.OracleDataSource;

public class SimpleDbStandaloneTest extends StoredProcedure {

    public SimpleDbStandaloneTest(DataSource ds, String sp) {

        super(ds, sp);
        declareParameter(new SqlInOutParameter("io_col", Types.CLOB, null, new SqlReturnType() {

            @Override
            public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName)
                    throws SQLException {

                //not all methods work for both CLOB and Clob but at least getCharacterStream() does
                java.sql.Clob clob = (java.sql.Clob) cs.getObject(paramIndex,java.sql.Clob.class);

//              oracle.sql.CLOB clob = (oracle.sql.CLOB) cs.getObject(paramIndex,oracle.sql.CLOB.class);

                System.out.println(clob); //just checking, the jdbc driver returns the deprecated CLOB class

                Reader r = clob.getCharacterStream();
                char[] cbuf = new char[1];
                try {
                    r.read(cbuf);
                } catch (IOException e) {
                    e.printStackTrace();
                }
                return new String(cbuf);
            }

        }));
        compile();
    }


    public Map<String, Object> execute() {

        Map<String, Object> inputs = new HashMap<>();
        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 {

            }

            inputs.put("io_col",new SqlValue() {

                @Override
                public void setValue(PreparedStatement ps, int paramIndex) throws SQLException {

                    Reader r = new StringReader("z");
                    ps.setClob(paramIndex, r);

                }

                @Override
                public void cleanup() {

                }

            });

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            /* Close the connections manually to prevent connection leak */
            try {
                if (wc != null && !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<String, Object> outMap = execute(inputs);
        return outMap;
    }

    public static void main(String[] args) throws SQLException {

        OracleDataSource ods = getDataSource();

        SimpleDbStandaloneTest t = new SimpleDbStandaloneTest(ods, "TESTLEOSP");

        Map<String, Object> map = t.execute();

        System.out.println(map);
    }


    private static OracleDataSource getDataSource() throws SQLException {
        OracleDataSource ods = new OracleDataSource();
        (...)
        return ods;
    }

}

Some observations

It's a pity that Oracle JDBC driver can't handle that.
Also, I don't know why they keep using the deprecated oracle.sql.CLOB class.
I really would like not to use CLOB and believe me, I've tried many things.
I think this is still a sub-optimal solution, so I will be happy if someone else find a better solution and post here
I am keeping this solution here for 2 reasons: first because it shows that this question IS NOT A DUPLICATED ONE. Second because I believe that good stack overflow answers provide objective and ready to use answers for this kind of issue. It's obvious that the problem is related to the charset translation. However, what matters here is a sample of code that works IMO.

score 0 · Answer 2 · answered Mar 06 '18 at 16:28

I am not a Java expert. But a bit of googling shows the following. See if it helps.

Using the docID from the MyOracleSupport link you shared, could find some explanation/reference to the Oracle Document text at this link.

Also found the following in OracleDocumentation for datatypes under Oracle Built-in datatypes. The datatype CHAR can be declared as CHAR(Size in number of Bytes) or CHAR(Size in number of Characters). Your NLS_LENGTH_SEMANTICS is BYTE. Your column MYCOL is declared with length of 1 byte. One difference that I note between specifying mutable.mycol%type and varchar2 is that the length is limited to 1 byte when the datatype is anchored.

The support document text suggests using the right driver for your situation. I suspect that the text passed other than as CLOB do not implicitly get converted to the destination datatype CHAR but to LONG datatype. Hence the parameter type conflict in the second example. I would try the following.

Declare mytable.mycol as CHAR(1 CHAR) instead of CHAR(1 BYTE) and see what happens.
Increase the size of mycol and test again to see what happens.
If nothing works, check if the driver is the correct/required version.

ORA-01461 for inherited char(1 byte) column - need to make it work using Spring JDBC (extending StoredProcedure)

2 Answers2