| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Specify string > 2000 chars
A copy of this was sent to "Christopher Walls" <cwalls_at_integrationware.X.com>
(if that email address didn't require changing)
On Fri, 7 Aug 1998 07:29:51 -0500, you wrote:
>We've run into a problem with Oracle 7.3. It appears that the longest
>string one can have in an insert statement (or update) is 2000 characters.
>If I have a column defined as Long (analogous to a MS SQL Server Text
>datatype) which holds up to 2 Gig of character data, how do I put more than
>2000 characters in it? We've tried concatenating the string but we end up
>with an error message saying "The result of a string concatenation is too
>long".
>
>Example (Field2 is defined as Long):
>INSERT INTO mytable (Field1, Field2) VALUES ('Record1', A String > 2000
>characters)
>
>It seems the error is being thrown by Oracle ("ORA-01704: string literal too
>long"), not the ODBC driver. I plan on testing with the Intersolv ODBC
>drivers but I don't think it's going to work (we're using an Oracle ODBC
>driver written by Microsoft, we never got the ODBC drivers written by Oracle
>to work).
>
>This isn't a problem in either MS SQL Server 6.5 or Sybase SQL Server System
>11.
>
>Any help would be appreciated.
you need to use bind variables for larger strings instead of character string constants. Can you use a bind variable in you ODBC application?
One other method that doesn't require a host program to use a bind variable can be demonstrated in SQL plus (and ODBC i would guess) that works upto 32k is as such:
drop table demo;
create table demo ( x long );
declare
myvar long default '
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx(lots of stuff here)......
insert into demo values ( myvar );
dbms_output.put_line( length( myvar ) );
end;
The pl/sql variable can be initialized to upto 32k of static text. It can then be inserted into the table (it is a bind variable, not a constant at that point).
For example, I just ran it and it showed me:
Table dropped.
Table created.
24726
PL/SQL procedure successfully completed.
So, that put a 24,726 character string into the table...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Aug 07 1998 - 09:40:52 CDT
 |
 |