mORMot Open Source

I have already posted this earlier, but no reply received.

Mormot OCI cannot handle more than 94 params!

I think it is a Mormot framework bug, as I know Oracle itself does not have this limit.
I receive the following error message when trying to use more than 94 params: ORA-01745:invalid host/bind variable name.

Here the code I use to test the situation:
It runs OK if num_of_fields=94, fails if 95 or greater.

  num_of_fields := 95;

  SQL := 'drop table insdemo ';
  Statement := props.NewThreadSafeStatement;
  Statement.Prepare(SQL,False);
  Statement.ExecutePrepared;
  Statement.Free;

  SQL := 'create table insdemo (';
  for I := 1 to num_of_fields do if i=num_of_fields then SQL := SQL + 'a' + FormatFloat('000',i) + ' varchar2(100))'
                                                    else SQL := SQL + 'a' + FormatFloat('000',i) + ' varchar2(100),';

  Statement := props.NewThreadSafeStatement;
  Statement.Prepare(SQL,False);
  Statement.ExecutePrepared;
  j := Statement.UpdateCount;
  Statement.Free;
  Memo1.Lines.Add('create j: ' + IntToStr(j));

  SQL := 'insert into insdemo values(';
  for I := 1 to num_of_fields do if i=num_of_fields then SQL := SQL + '?)' else SQL := SQL + '?,';

  Memo1.Lines.Add('Sql command to execute: ' + SQL);

  Statement := props.NewThreadSafeStatement;
  Statement.Prepare(SQL,False);

  for i := 1 To num_of_fields Do Statement.BindTexts(i,'b' + FormatFloat('000',i));

  Statement.ExecutePrepared;
  Statement.Free;

When trying to create programmatically stored procedure I execute the appropriate "CREATE OR REPLACE PROCEDURE ... " sql command.
And I have an issue with the ";" at the end of the line. I think it is needed but the interface drops it (ReplaceParamsByNames procedure in the SynDb source).

Any idea how to solve this?

Thanks in advance.

Tibor

In the TSQLDBOracleConnection.Connect procedure there is the following useful statement:

Execute('ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD-HH24:MI:SS''',false);

I suggest to also include the following one:

Execute('ALTER SESSION SET NLS_NUMERIC_CHARACTERS=''' + DecimalSeparator + ThousandSeparator + '''',false);

This would help us who use not the '.', but the ',' as a decimal separator (e.g. in Hungary, where I live).
As I experience, the Thousandseparator is not automatically used, but the DecimalSeparator YES, so I included this statement into my library.

Tibor

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

I found the following limit for number of params:

function TSQLDBStatementWithParams.CheckParam(Param: Integer;
  NewType: TSQLDBFieldType; IO: TSQLDBParamInOutType): PSQLDBParam;
begin
  if self=nil then
    raise ESQLDBException.Create('Invalid Bind*() call');
  if (Param<=0) or (Param>64) then
    raise ESQLDBException.CreateFmt('Bind*(%d) should be 1..64',[Param]);
  if Param>fParamCount then
    fParam.Count := Param; // resize fParams[] dynamic array if necessary
  result := @fParams[Param-1];
  result^.VType := NewType;
  result^.VInOut := IO;
end;

As I know ORACLE itself does not have this max 64 param count limit.

I would need more.
Can I simple change '64' in the source to a greater number?

Thanks,

Tibor