ORA-24337 statement handle not prepared

[Reijo Pitkanen]

D7, DBexpress, Oracle9i (9.2).
Trying to insert data into an Oracle table with SqlConnection.ExcuteDirect
method, something like this:

procedure TForm1.OstonTalletus;
var
Teksti:String;
TD: TTransactionDesc;
begin
if not DM1.SQLConnection1.InTransaction then
begin
TD.TransactionID := 1;
TD.GlobalID:=0; // Session based local transaction
TD.IsolationLevel := xilREADCOMMITTED;
DM1.SQLConnection1.StartTransaction(TD);
try
Teksti:='INSERT INTO KARREK.SIEMEN
(SIEM_NAV,TUNNUS,SIEM_PVM,SIEM_KERTA,SIEM_LAJI,SON_KKNRO,'+
'AL_MUUEMA,AL_EMAMAA,AL_EMASYNTU,AL_EMAEUTAR,AL_EMAKKNRO,AL_EMANIMI,AL_TYPE,
REKTAL_PVM,'+
'TULOS3,ODOTET_POI,KASITTELIJA,MUUTOSPV,SIEMEN_NO)'+
'
VALUES(7,''041111'',TO_DATE(''01/11/2004'',''MM/DD/YYYY''),2,''A'',41034,323
232356,''FI'',221111453,4,66667,''LUOVI'','''','+

'TO_DATE(''02/22/2004'',''MM/DD/YYYY''),''+'',TO_DATE(''10/10/2004'',''MM/DD
/YYYY''),''Reijo Pitkänen'',SYSDATE,57) ';
DM1.SQLConnection1.ExecuteDirect(Teksti);
DM1.SQLConnection1.Commit(TD); {on success, commit the changes};
except
DM1.SQLConnection1.Rollback(TD); {on failure, undo the changes};
raise;
end;
end // DM1.SQLConnection1 in transaction
else
Raise EDataBaseError.Create('Tietokanta on lukittu, yritä uudelleen!');
end;

Well this works fine, but when I increase the amount of fields in the Sql
statement to about 50 fields I get error message:
"ORA-24337 statement handle not prepared"

Oracle Metalink says something like this about ORA-24337:
"Workaround:
Reallocate statement handle, if OCIStmtPrepare() fails.
In Pro precompile with RELEASE_CURSOR=YES."

Does anyone know, how this can be done with DBExpress?

-Reijo Pitkanen

[Dmitry Arefiev [gs-soft.ru]]

Hello Reijo !

You code should work with Oracle dbExpress driver.
Can you have wrong syntax in "50 fields" version of your SQL ?
At least, post it to group.

> Teksti:='INSERT INTO KARREK.SIEMEN

Regards,
Dmitry

--
Dmitry Arefiev, gs-soft Company, community.gs-soft.com
Personal - www.da-soft.com

SAPx - Delphi to SAP R/3 direct access
Saphir - SAP R/3 metadata on your fingertips
MetaBase - ERWIN model in Delphi applications
NCOCI8 - Freeware Delphi to ORACLE direct access

Reijo Pitkanen wrote:
> D7, DBexpress, Oracle9i (9.2).
> Trying to insert data into an Oracle table with SqlConnection.ExcuteDirect
> method, something like this:
>
> procedure TForm1.OstonTalletus;
> var
> Teksti:String;
> TD: TTransactionDesc;
> begin
> if not DM1.SQLConnection1.InTransaction then
> begin
> TD.TransactionID := 1;
> TD.GlobalID:=0; // Session based local transaction
> TD.IsolationLevel := xilREADCOMMITTED;
> DM1.SQLConnection1.StartTransaction(TD);
> try
> Teksti:='INSERT INTO KARREK.SIEMEN
> (SIEM_NAV,TUNNUS,SIEM_PVM,SIEM_KERTA,SIEM_LAJI,SON_KKNRO,'+
> 'AL_MUUEMA,AL_EMAMAA,AL_EMASYNTU,AL_EMAEUTAR,AL_EMAKKNRO,AL_EMANIMI,AL_TYPE,
> REKTAL_PVM,'+
> 'TULOS3,ODOTET_POI,KASITTELIJA,MUUTOSPV,SIEMEN_NO)'+
> '
> VALUES(7,''041111'',TO_DATE(''01/11/2004'',''MM/DD/YYYY''),2,''A'',41034,323
> 232356,''FI'',221111453,4,66667,''LUOVI'','''','+
>
> 'TO_DATE(''02/22/2004'',''MM/DD/YYYY''),''+'',TO_DATE(''10/10/2004'',''MM/DD

> /YYYY''),''Reijo PitkДnen'',SYSDATE,57) ';

> DM1.SQLConnection1.ExecuteDirect(Teksti);
> DM1.SQLConnection1.Commit(TD); {on success, commit the changes};
> except
> DM1.SQLConnection1.Rollback(TD); {on failure, undo the changes};
> raise;
> end;
> end // DM1.SQLConnection1 in transaction
> else

> Raise EDataBaseError.Create('Tietokanta on lukittu, yritД uudelleen!');

[Reijo Pitkanen]

Hi, Dmitry,
thanks for Your interest to this issue.
It's little bit difficult to post the code to this group because the sql is
made dynamically by code, so I post here the sqlmonitor log including the
statement leading to this error ORA-24337. If there were a syntax error in
the sql, the error code returned would be different, wouldn't it?
There is only 48 fields in this sql statement, if I counted right.
Here comes the log:
------------------------
OCI 8 - OCIStmtFetch
OCI 8 - OCIAttrGet(OCI_ATTR_ROWCNT)
OCI 8 - OCIHandleAlloc
OCI 8 - OCIHandleAlloc
INSERT INTO KARJAREK.POLVET
(LAJI,TUNNUS,NIMI,MAA,SYNTYMATUN,EUTARK,KKNRO,KORVA,KOKONIMI,SUKUNIMI,SYNTPV
M,SYNNAV,AS_ET,ROTU,SEX,
SUKURYHM,VARI,PERUSARVOM,YDINKARJA,NYKYNAV,TULO_PVM,UUSISYY,SIIRTO_PVM,SIIRR
ONSYY,ISAMAA,ISASYNTUN, ISAEUTARK,ISAKKNRO,ISANIMI,ISAROTU,EMAMAA
,EMASYNTUN,EMAEUTARK,EMAKKNRO,EMANIMI,EMAMUUTUN,EMAROTU,
EMSUKU,KANTAEMA,VIIM_POIKI,POIK_KERTA,VIIM_SIEM,SIEM_KERTA,ODOTET_POI,LAHTO_
PVM,LAHTONAV,LAHDONSYY, KASITTELIJA,MUUTOSPV)
VALUES(1,'041111','VAPPU1','FI',123456789,7,33445,1111,'VAPPU
PELTOLA','PELTOLA',TO_DATE('10/12/2004','MM/DD/YYYY'),7,'ET','AYAY',1,'A',1,
1,2,12,TO_DATE('04/30/2004','MM/DD/YYYY'),'971',TO_DATE('04/30/2004','MM/DD/
YYYY'),'971','',,,40499,'BACKGARD','AYAY','FI',555444333,8,22331,'SATU','980
5675,'AYAY','D','AITEE',TO_DATE('10/11/2003','MM/DD/YYYY'),1,TO_DATE('01/11/
2004','MM/DD/YYYY'),2,TO_DATE('10/10/2004','MM/DD/YYYY'),TO_DATE('04/30/2004
','MM/DD/YYYY'),7,'971','Reijo Pitkanen',SYSDATE)
OCI 8 - OCIStmtPrepare
OCI 8 - OCIAttrGet
OCI 8 - OCIBindByPos
OCI 8 - OCIStmtExecute
OCI 8 - OCIErrorGet
OCI 8 - OCIHandleFree(OCI_HTYPE_DESC)
OCI 8 - OCIHandleFree(OCI_HTYPE_STMT)
OCI 8 - OCITransRollback
-------------------------------------

I have now made a workaround to this problem by using a
SQLDataset,SQLDatasetProvider and a Clientdataset component something like
this:
with ClientDataSetTmp do
begin
if active= false then open;
insert;
FieldByName('LAJI').AsInteger:= 1:
.....
FieldByName('ODOTET_POI').AsString:=Odotetpoiki;

FieldByName('TULO_PVM').AsDateTime:=ClientDataset1.FieldByName('OSTOPVM').As
DateTime;
FieldByName('Kasittelija').AsString:=UserNimi;
ClientDataSetTmp.FieldByName('MUUTOSPV').AsDateTime:=Now;
post;
ClientDataSetTmp.ApplyUpdates(0);
end;

This causes an insert statement where the values are given as parameters and
the sqlmonitor log looks like this:
---------------------------
OCI 8 - OCIStmtFetch
OCI 8 - OCIAttrGet(OCI_ATTR_ROWCNT)
OCI 8 - OCIHandleAlloc
OCI 8 - OCIHandleAlloc
insert into "KARJAREK"."POLVET"
("LAJI", "TUNNUS", "NIMI", "MAA", "SYNTYMATUN", "EUTARK", "KKNRO",
"KORVA", "KOKONIMI", "SUKUNIMI", "SYNTPVM", "SYNNAV", "AS_ET", "ROTU",
"SEX", "SUKURYHM", "VARI", "PERUSARVOM", "YDINKARJA", "NYKYNAV", "TULO_PVM",
"UUSISYY", "SIIRRONSYY", "ISAMAA", "ISASYNTUN", "ISAEUTARK", "ISAKKNRO",
"ISANIMI", "ISAROTU", "EMAMAA", "EMASYNTUN", "EMAEUTARK", "EMAKKNRO",
"EMANIMI", "EMAMUUTUN", "EMAROTU", "EMSUKU", "KANTAEMA", "VIIM_POIKI",
"POIK_KERTA", "VIIM_SIEM", "SIEM_KERTA", "ODOTET_POI", "KASITTELIJA",
"MUUTOSPV")
values
(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16,
:17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31,
:32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45)
OCI 8 - OCIStmtPrepare
OCI 8 - OCIAttrGet
....
And this works !

Regards Reijo

"Dmitry Arefiev [gs-soft.ru]" <dare...@gs-soft.ru> wrote in message
news:4098...@newsgroups.borland.com...

> > /YYYY''),''Reijo Pitkänen'',SYSDATE,57) ';

> > DM1.SQLConnection1.ExecuteDirect(Teksti);
> > DM1.SQLConnection1.Commit(TD); {on success, commit the changes};
> > except
> > DM1.SQLConnection1.Rollback(TD); {on failure, undo the changes};
> > raise;
> > end;
> > end // DM1.SQLConnection1 in transaction
> > else

> > Raise EDataBaseError.Create('Tietokanta on lukittu, yritä

[Dmitry Arefiev [gs-soft.ru]]

Hello Reijo !

1. SQLMonitor output is more valuable.

2. Following line has an error:

> YYYY'),'971','',,,40499,'BACKGARD','AYAY','FI',555444333,8,22331,'SATU','980

Do you see ,,, ? Probably that leads to error.

3. To replace constants with parameters is always right decision.
And very right in Oracle.

Regards,
Dmitry

--
Dmitry L. Arefiev, gs-soft Company, http://www.gs-soft.com
Personal - http://www.da-soft.com

[Reijo Pitkanen]

Hi, Dmitry!
Those 3 commas (,,,) represent 2 empty numeric values and indeed they may
cause an error, but the error returned should be:
ORA-00936: Missing Expression!

Regards Reijo

"Dmitry Arefiev [gs-soft.ru]" <dare...@da-soft.com> wrote in message
news:4099006c$1...@newsgroups.borland.com...