difficulty in inserting value into oracle "long" datatype column

[Tapas Chand]

i am having problem with inserting values into "long" datatype column in oracle through java
i am doing this

String query = "INSERT INTO TBL_TEST(COL1,COL2,COL3) VALUES(?,?,?)";
pstmt = con.prepareStatement(query);
pstmt.setString(1, "111");
pstmt.setInt(2, 1212);
pstmt.setAsciiStream(3, bai, bai.available());
int cnt = pstmt.executeUpdate();

where bai is ByteArrayInputStream
when length of bai is less than 4096, data is inserted properly, but when it exceeds 4095 then
im getting error

java.sql.SQLException: Io exception: Invalid character encountered in: failAL32UTF8Conv

please help

[Ireneusz Kordal]

Try to convert long columns to LOB (CLOB, BLOB, NLOB) - Oracle does not recommend using LONG.
LONG datatype is provided only for backward compability and has many limitations.
See here (section "LONG datatype") for details:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements001.htm#i46018

This could be possible reason of your troubles:

If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.

[Fatih Keles]

Hi Tapas,

As Ireneusz said long data type is not supported since Oracle 8, use lobs instead of long type.

But,

Also with long it should be working. There may be a problem with your "bai" stream. Or may be a driver problem.

This is my test:

Oracle 11g R1 database (NLS_CHARACTERSET AL32UTF8)

create table xxcm.xxcm_test_long_table (
       num_col   number(8),
       char_col  varchar2(500),
       long_col  long
);

Java 1.5
ojdbc14dms.jar

public class LongTest {
    public static void pr(Object o){
        System.out.println(o);
    }
    
    public static String getString(long size){
        StringBuilder sb = new StringBuilder();
        for(int i=0;i<size;i++)
            sb.append("*");
        return sb.toString();
    }
    
    public static void main(String[] args) throws SQLException, IOException {
        InputStream stream = null;
        DriverManager.registerDriver(new OracleDriver());
        Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1522:TEST","xxcm","xxcm");
        PreparedStatement ps = c.prepareStatement("insert into xxcm.xxcm_test_long_table(num_col,char_col,long_col) values(?,?,?)");
        ps.setInt(1,1);
        ps.setString(2,"setString");
        ps.setString(3,getString(4000));
        pr(ps.executeUpdate());
        
        ps.setInt(1,2);
        ps.setString(2,"setString");
        ps.setString(3,getString(32000));
        pr(ps.executeUpdate());
        
        ps.setInt(1,3);
        ps.setString(2,"setAsciiStream");
        stream = new ByteArrayInputStream(getString(32768).getBytes());
        ps.setAsciiStream(3,stream,stream.available());
        //ps.setString(3,getString(32767));//wont work for bigger than 32767
        pr(ps.executeUpdate());
        
        ps.setInt(1,4);
        ps.setString(2,"setAsciiStream");
        stream = new ByteArrayInputStream(getString(2><<16).getBytes());
        ps.setAsciiStream(3,stream,stream.available());
        pr(ps.executeUpdate());
        
        ps.close();
        c.close();
    }
}

As you can see, I can use setString method to insert values up to 32767 into a long column.

For checking results, I create a mirror table as long column converted to clob to see length etc.

create table xxcm.xxcm_test_long_table2 as
 select t.num_col,t.char_col,to_lob(t.long_col) as long_col_mir from xxcm.xxcm_test_long_table t

And results:

select t.num_col,t.char_col,
       t.long_col_mir,dbms_lob.getlength(t.long_col_mir) as length
  from xxcm.xxcm_test_long_table2 t
 order by 1 asc

NUM_COL	CHAR_COL	LONG_COL_MIR	LENGTH
1	setString	                <CLOB>	        4000
2	setString	                <CLOB>	        32000
3	setAsciiStream	        <CLOB>	        32768
4	setAsciiStream	        <CLOB>	        131072

Regards,

Fatih.

[Tapas Chand]

many many thanks for your precious instructions
you mentioned you are using
Oracle 11g R1 database (NLS_CHARACTERSET AL32UTF8), Java 1.5 and ojdbc14dms.jar

and im using Oracle 9i enterprize edition release 9.2.0.5.0, Java 1.5 and ojdbc14_g.jar
so may be im facing the problem because of oracle9i or ojdbc14_g.jar

and you are inserting 32000 bytes of data by "setString method"
but when im using

pstmt.setString(1, getString(4001));

to insert into the "long" column, im getting an error

java.sql.SQLException: Data size bigger than max size for this type: 4001

if you got any clue on this, please mention

thanks and regards
tapas chand

[Fatih Keles]

Hi Tapas,

Your driver is supported, you can see it here.
But you should really think of migrating to CLOB/BLOB data types.
Take a look at this:
What is the longest value I can bind?

Regards,

Fatih.