Updating CLOB in Oracle

Jim Poe

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Updating CLOB in Oracle

I'm using Delphi 4.2, BDE 5.1, and Oracle 8.0.5 on NT.

I'm getting an 'ORA-01461: can bind a LONG value only for insert into a
LONG column' error when I try to update a CLOB field. I'm using the
following syntax:

with qryStandardUpdate do begin
{UPDATE FIT_DBA.STANDARD
SET TYPE_CD=:TYPE_CD,
PARENT_ID=:PARENT_ID,
DSCR=:DSCR,
DOC=:DOC
WHERE KEY_ID=:KEY_ID }

Params[0].Value := DeltaDS.FieldByName( 'TYPE_CD' ).Value;
Params[1].Value := DeltaDS.FieldByName( 'PARENT_ID'
).Value;
Params[2].Value := DeltaDS.FieldByName( 'DSCR' ).Value;}
Params[0].Value := DeltaDS.FieldByName( 'DOC' ).Value;
Params[1].Value := DeltaDS.FieldByName( 'KEY_ID' ).Value;
try
ExecSQL;
except
end;
end;

Does anyone have any advice on how to get this update to work?

--
Jim Poe
<j...@fulcrumit.com>
Fulcrum InteTech, Inc.

Ginger Wilsbache

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Updating CLOB in Oracle

Hi Jim -

There's a not-very-well-documented workaround that you can set a global
variable called Oracle8Blobs=true. If you've got the vcl source, check out
dbtables.pas. Here's a blub from that file.
{ Global variable for parameter binding to Oracle8 CLOB & BLOB field
types.
Set this to True before executing a query on that uses these types.
Assign the data using TParam.AsMemo for CLOB and TParam.AsBlob for
BLOB.
Note you cannot mix CLOB/BLOB and RAW/LONG RAW in the same query. }
Oracle8Blobs: Boolean;

Good luck,
Ginger

Quote

Jim Poe wrote:
> I'm using Delphi 4.2, BDE 5.1, and Oracle 8.0.5 on NT.

> I'm getting an 'ORA-01461: can bind a LONG value only for insert into a
> LONG column' error when I try to update a CLOB field. I'm using the
> following syntax:

> with qryStandardUpdate do begin
> {UPDATE FIT_DBA.STANDARD
> SET TYPE_CD=:TYPE_CD,
> PARENT_ID=:PARENT_ID,
> DSCR=:DSCR,
> DOC=:DOC
> WHERE KEY_ID=:KEY_ID }

> Params[0].Value := DeltaDS.FieldByName( 'TYPE_CD' ).Value;
> Params[1].Value := DeltaDS.FieldByName( 'PARENT_ID'
> ).Value;
> Params[2].Value := DeltaDS.FieldByName( 'DSCR' ).Value;}
> Params[0].Value := DeltaDS.FieldByName( 'DOC' ).Value;
> Params[1].Value := DeltaDS.FieldByName( 'KEY_ID' ).Value;
> try
> ExecSQL;
> except
> end;
> end;

> Does anyone have any advice on how to get this update to work?

> --
> Jim Poe
> <j...@fulcrumit.com>
> Fulcrum InteTech, Inc.

Jim Po

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Updating CLOB in Oracle

Ginger,

Thanks for your response. I set the global variable as you suggested. It
does make a difference. I don't get the ORA-01461 error anymore. It's been
replaced with 'ORA-01036: illegal variable name/num'. Any ideas on what
causes this?

Quote

Ginger Wilsbacher wrote:
> Hi Jim -

> There's a not-very-well-documented workaround that you can set a global
> variable called Oracle8Blobs=true. If you've got the vcl source, check out
> dbtables.pas. Here's a blub from that file.
> { Global variable for parameter binding to Oracle8 CLOB & BLOB field
> types.
> Set this to True before executing a query on that uses these types.
> Assign the data using TParam.AsMemo for CLOB and TParam.AsBlob for
> BLOB.
> Note you cannot mix CLOB/BLOB and RAW/LONG RAW in the same query. }
> Oracle8Blobs: Boolean;

> Good luck,
> Ginger

> Jim Poe wrote:

> > I'm using Delphi 4.2, BDE 5.1, and Oracle 8.0.5 on NT.

> > I'm getting an 'ORA-01461: can bind a LONG value only for insert into a
> > LONG column' error when I try to update a CLOB field. I'm using the
> > following syntax:

> > with qryStandardUpdate do begin
> > {UPDATE FIT_DBA.STANDARD
> > SET TYPE_CD=:TYPE_CD,
> > PARENT_ID=:PARENT_ID,
> > DSCR=:DSCR,
> > DOC=:DOC
> > WHERE KEY_ID=:KEY_ID }

> > Params[0].Value := DeltaDS.FieldByName( 'TYPE_CD' ).Value;
> > Params[1].Value := DeltaDS.FieldByName( 'PARENT_ID'
> > ).Value;
> > Params[2].Value := DeltaDS.FieldByName( 'DSCR' ).Value;}
> > Params[0].Value := DeltaDS.FieldByName( 'DOC' ).Value;
> > Params[1].Value := DeltaDS.FieldByName( 'KEY_ID' ).Value;
> > try
> > ExecSQL;
> > except
> > end;
> > end;

> > Does anyone have any advice on how to get this update to work?

> > --
> > Jim Poe
> > <j...@fulcrumit.com>
> > Fulcrum InteTech, Inc.

--
Jim Poe
<j...@fulcrumit.com>
Fulcrum InteTech, Inc.

Other Threads

1. Problem update oracle CLOB in cached updates

2. Oracle CLOB or BLOB UPDATE in cached Update mode

3. cannot update CLOB field under Oracle 8.x

4. BDE retrieving extra 1 byte in Oracle CLOB operation and oracle trigger problems

5. Oracle CLOB ADO problem

6. CLOB Oracle fetch problem

7. ADO, Oracle 8i and CLOB

8. PROBLEM WITH ORACLE CLOB FIELDS

9. Using BLOB and CLOB fields with OLE DB ORACLE

10. CLOB data type whit OLE DB Oracle connection