Re: Inserting Oracle long datatype

Hi Cery,

I am currently working on the same problem, only through Perl or PL/SQL (whichever we can get working sooner), instead of straight SQL.

The error message we get says that we must use a bind variable to insert a string longer than 2000 characters. This implies that we have to use some form of dynamic SQL (which is where the bind variables are used).

I suspect that the problem lies in how Oracle passes values. Small (ie, < 2000 byte) strings can be passed by value, but larger ones must be passed
by reference. Oracle has a limited memory buffer for parameters, which precludes passing of variables of arbitrary size. (Imagine trying to pass
a 2 gig LONG string through the memory buffer!) On the other hand, if your
program already has the variable in memory (virtual is OK), then by passing
a bind variable you pass a reference (like a pointer in C or Pascal), which
Oracle can use to get at the value.

Consult a book on PL/SQL; you can probably write a short procedure to insert
your variable for you.

Regards,
Ken

ceri_at_n-ary.com wrote:
>
> Hi,
>
> I am trying to add a record into a table containing a long datatype,
>
> I concatenate a string together it is 8000 chars long in variable named b
>
> insert statement is this,
>
> insert into literature_page values ('" + litId + "', '" + pgId
> + "', '" + b + "'"
>
> and I get the error :
> oracle.jdbc.oci7.OCIDBException: ORA-01704: string literal too long
>
> I thought I could add up to 2 gig????
>
> What am I doing wrong?
Received on Thu Aug 28 1997 - 00:00:00 CDT