mORMot Open Source

peak.tibor

Member

Registered: 2013-02-22

Posts: 19

ReUse of SQLDBOracleStatement fails: ORA-24333 zero iteration count

in SynDbOracle it is stated that you could reuse a Statement "with Prepare once followed by several  ExecutePrepared methods" :

  TSQLDBOracleStatement = class(TSQLDBStatementWithParamsAndColumns)
  ............
  public
    {{ create an OCI statement instance, from an existing OCI connection
     - the Execute method can be called once per TSQLDBOracleStatement instance,
       but you can use the Prepare once followed by several  ExecutePrepared methods
     - if the supplied connection is not of TOleDBConnection type, will raise
       an exception }
    constructor Create(aConnection: TSQLDBConnection); override;

It really works when it is a simple "select * count .." statement. But ...

I created a stored procedure with params that inserts data values into a table as a new record (insert into ...). I would like to use one statement (that calls this stored procedure) to insert several records.
And I got this Oracle error:  ORA-24333 zero iteration count

Do I misunderstand something?

Here is my code:

  SQL := 'call ins_inv_data_1(?,?,?,?,?) ';
  Statement := props.NewThreadSafeStatement;

  Statement.Prepare(SQL,False);

  Statement.Bind(1,inv_id,paramIn);
  Statement.Bind(2,cust_id,paramIn);
  Statement.BindTextS(3,order_dt);
  Statement.BindTextS(4,ship_dt);
  Statement.Bind(5,cicus,paramOut);
  Statement.ExecutePrepared;                      // this works OK
  j := Statement.UpdateCount;
  Statement.ParamToVariant(5,out1,true);

  Statement.Reset;
  Statement.Bind(1,inv_id,paramIn);
  Statement.Bind(2,cust_id,paramIn);
  Statement.BindTextS(3,order_dt);
  Statement.BindTextS(4,ship_dt);
  Statement.Bind(5,cicus,paramOut);

  Statement.ExecutePrepared;

Here I receive the above error! Can any one help?

Thanks

Tibor

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: ReUse of SQLDBOracleStatement fails: ORA-24333 zero iteration count

It does also work with multiple INSERTs.

I suspect the problem comes from the fact that it is a problem with the stored procedure.

I've tried to force the iter parameter of OCIStmtExecute() to be always 1 in such case, as expected by http://docs.oracle.com/cd/B19306_01/app … tm#i574897
See http://synopse.info/fossil/info/172a2aa9c9

Hope it fixes your problem.

peak.tibor

Member

Registered: 2013-02-22

Posts: 19

Re: ReUse of SQLDBOracleStatement fails: ORA-24333 zero iteration count

My stored procedure is quite simple:

create or replace procedure ins_inv_data_1
  (inv_id in number
  ,cust_id in number
  ,order_dt in varchar2
  ,ship_dt in varchar2
  ,cicus out number
  )
as
Begin
insert into invoice values(
inv_id,cust_id, to_date(order_dt,'yyyy.mm.dd'),to_date(ship_dt,'yyyy.mm.dd')
);
cicus := 2;
end;

I do not know whether I should make any modification on it regarding this error message.
But I applied your modification in SynDBOracle.pas and the error disappeared!

So thanks a lot, this helped.
If you think I should also make some modifications in my stored procedure, I'm ready to do it.

Regards,

Tibor

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: ReUse of SQLDBOracleStatement fails: ORA-24333 zero iteration count

No, I do not think your stored procedure is incorrect.
It just sounds fine.

Why do you use stored procs instead of plain SQL?

Error was in SynDBOracle, since here our regression tests do not use stored procedures, but basic CRUD operations.

Thanks for the feedback!