Re: Strings with more than 2000 characters - problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 22 Jan 1999 19:29:18 GMT
Message-ID: <36abd158.100950519@192.86.155.100>

A copy of this was sent to Hans Rytter <hry_at_post7.tele.dk> (if that email address didn't require changing) On Fri, 22 Jan 1999 15:29:14 +0100, you wrote:

>Hi.
>
>I'm using Delphi 3 C/S version to access a Oracle DB. My problem is that
>I cannot get a long datatype updated if the string I wan't to update
>with exceeds 2000 characters.
>
>The table is defined something like:.
>
>Create Table MyTable
> ( MyTable_Id NUMBER,
> ....
> ....
> MyTableLongStr LONG );
>
>and my sql is something like:
> with Query1 do
> begin
> SQL.Clear;
> SQL.Add(Format('Update MyTable set MyTableLongstr = ''%s'' where
>MyTable_Id = ''%s''',['a very long string .... longer than 2000
>chr.',1]));
> Prepare;
> ExecSql;
> end;
>
>I have read about bind variables, but I don't know what it is and how to
>use them in an application.
>

you need to use bind variables for
larger strings instead of character string constants.

One method that works upto 32k is to use pl/sql as follows:

drop table demo;
create table demo ( x long );

declare

    myvar long default '

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

(lots of stuff here)......
';
begin

   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).

You just need to execute a pl/sql block with your update.

>Please help.
>
>/Hans
>
>hans.rytter_at_dk.pwcglobal.com
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

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 Jan 22 1999 - 13:29:18 CST