Re: Long Fields

In article <8ckqto$mae$1_at_nnrp1.deja.com>,   tkashin_at_my-deja.com wrote:
> In article <d105dsoftfe564s218mg67p2n4r79p7joo_at_4ax.com>,
> tkyte_at_us.oracle.com wrote:
> > A copy of this was sent to jandrews0919_at_my-deja.com
> > (if that email address didn't require changing)
> > On Thu, 16 Mar 2000 21:31:31 GMT, you wrote:
> >
> > >I can insert a text value > 4000 bytes into a
> > >long field on one table, but a max of 4000 bytes
> > >on another. What simple, stupid thing am I doing
> > >to cause this oddity.
> > >
> > >
> > >Sent via Deja.com http://www.deja.com/
> > >Before you buy.
> >
> > Are you inserting into them differently? character string constants
 in SQL are
> > limited to 4000 bytes in 8.0 and up, 2000 in 7.x. If you are doing
 an
 insert
> > with a constant (and not a bind variable) you'll hit this.
> >
> > see http://osi.oracle.com/~tkyte/Misc/LargeStrings.html
> >
> > --
> > http://osi.oracle.com/~tkyte/
> >
> > Thomas Kyte tkyte_at_us.oracle.com
> > Oracle Service Industries Reston, VA USA
> >
> > Opinions are mine and do not necessarily reflect those of Oracle
 Corporation
> >
>
> Mr. Kyte:
> We, too, are having this problem: we need to insert large strings
 (>4000
> characters) into an Oracle 8 "long" table column. I tried your
 program
> and it returned an error.
>
> declare
> myvar long default
> '012345678901234567890123456789012345678901234567890123456789012345678
 90
> [4200 characters here]';
> begin
> insert into tbk values ( myvar );
> dbms_output.put_line( length( myvar ) );
> end;
>
> I received the following error:
> MGR-11401: input error, unable to read input line
> I was using the SQL Worksheet.
>
> Why this seemingly simple process is so complex is beyond me and all
> these other people who are writing about this issue. Why limit the
> inserted string to 4000 characters? Why make the data type up to 2GB
> when you can't ever get it in there?
> Thanks.
>

character string CONSTANTS (things in quotes) are limited to 4000 bytes in v8 and 2000 bytes in v7 (Ansi standards limit them to 255 bytes actually so we go above and beyond).

The proper, correct, efficient method of inserting anything into the database is never to hard code it into the sql statement but rather to use bind variables. For example NEVER:

insert into t values ( 'Hello World' );

Always:

insert into t values ( :bind_var );

and execute that statement with a host variable 'bind_var' that has the value 'Hello World'. That avoids the hard parse of the insert statement (extremely expensive) and avoids all issues with regards to length, special characters (eg: try to insert the string "How's it going?" without using a bind variable -- you have to double up the single quote first and make it "How''s it going?").

You don't mention what language you are using (sqlplus worksheet is just a tool, it has its own limits, line lengths are one of them -- use the continuation character for long lines). You typically would not use sqlplus as your interface to the database for an application.

Use bind variables and all will work -- it'll be efficient, it won't have issues with quotes and lengths and newlines and so on....

> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.