Re: LONG to long in Oracle?

Wassili Kazakos wrote:
>
> Craig R. McClanahan wrote:
> >
> > As you might assume (because you got the same error in SQL*Plus), this
> > restriction comes from Oracle, not Java or JDBC. Oracle restricts the
> > length of a VARCHAR2 column to a maximum of 2000 characters. If you want
> > more than that, use a LONG column (or one of the other variants if you want
> > to store binary data) instead.
>
> Hi,
> thanks for the hint, but the column is defind as LONG. The problem is
> not the maximum capacity of the row but that Oracle doesnt accept quoted
> strings longer than 2000 chars. Maybe this is a parser problem..
>
> The test table is
> create table test
> (
> id varchar2(20) not null,
> long_value LONG null
> );
>
> insert into test (id,long_value) values ('xyz', 'enter 2002 chars
> here....');
>
> thanks
> Wassili

Try something like this:

    Connection s = DriverManager.getConnection(

                     "jdbc:oracle:thin:@[host]:[port]:[sid]",
                     "user", "passwrd");

    StringBuffer sb = new StringBuffer("your long string here");     InputStream bs = new StringBufferInputStream( sb.toString() );

    PreparedStatement pstmt = s.prepareStatement(

       "insert into test( id, long_value ) values( 'xyz', ? )" );

    pstmt.clearParameters();
    pstmt.setBinaryStream(1, bs, sb.length());
    pstmt.executeUpdate();

I just did this with a 10 Mb String...

-- 
---------------------------------------------------------------------------
 John Sutton                          New England Development Center
 Oracle System Management Products    internet:  jsutton_at_us.oracle.com  
 One Oracle Drive, Office 2012        voice:     (603) 897-3223         
 Nashua, NH 03062                     fax:       (603) 897-3317         
---------------------------------------------------------------------------