Pages: 1

#1 2013-06-28 09:24:12

peak.tibor
Member
Registered: 2013-02-22
Posts: 19

Bug in Oracle interface? Please confirm or confute

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;

Offline

#2 2013-06-28 13:01:39

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: Bug in Oracle interface? Please confirm or confute

What is the executed SQL statement sent at OCI level in such case?

One one the generated variable names sounds invalid.

Offline

#3 2013-06-28 13:47:12

peak.tibor
Member
Registered: 2013-02-22
Posts: 19

Re: Bug in Oracle interface? Please confirm or confute

When num_of_fields = 4 the SQL statements that gets executed:

- drop table insdemo
- create table insdemo (a001 varchar2(100),a002 varchar2(100),a003 varchar2(100),a004 varchar2(100))
- insert into insdemo values(?,?,?,?)

Before executing the last one, string constants are bound to the params with the Statement.BindTexts function.

The resulting table is like this:

Cols:  A001  A002  A003  A004
Rec1: b001  b002  b003  b004

Which generated variable names sounds invalid?

Tibor

Offline

#4 2013-06-28 14:03:20

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: Bug in Oracle interface? Please confirm or confute

The issue is for the SELECT or INSERT statement, not at table creation.

Offline

#5 2013-06-28 14:33:21

peak.tibor
Member
Registered: 2013-02-22
Posts: 19

Re: Bug in Oracle interface? Please confirm or confute

Yes, the issue is when the params are used/bound.

Offline

#6 2013-06-28 18:09:34

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: Bug in Oracle interface? Please confirm or confute

So, what is the INSERT statement?

One of the generated parameter name (e.g. AA AB AC) is invalid.
We need to identify which one is rejected by Oracle.

Offline

#7 2013-06-28 21:00:03

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,571
Website

Re: Bug in Oracle interface? Please confirm or confute

2peak.tibor try to generate insert statement without params and run it via sqlplus\toad
Something like.
insert into insdemo values(`b001`,`b002`,..........);

Is it work for 95 column table?

Offline

#8 2013-06-29 19:07:16

peak.tibor
Member
Registered: 2013-02-22
Posts: 19

Re: Bug in Oracle interface? Please confirm or confute

I made two kind of tests:

1. Using PL/SQL Developer a I made the same SQL operations. Created the table, created procedure to insert the values using params and it can handle more than a hundred params without any problem.

2. With the Mormot framework I inserted the values as constants, not using params. It is working.

Here is the code:

  num_of_fields := 120;

  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 + '''b' + FormatFloat('000',i)+ ''')' else SQL := SQL + '''b' + FormatFloat('000',i)+ ''',';

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

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

  Statement.ExecutePrepared;

  j := Statement.UpdateCount;
  Statement.Free;

  Memo1.Lines.Add('j: ' + IntToStr(j));

Offline

#9 2013-06-29 19:23:36

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: Bug in Oracle interface? Please confirm or confute

Yes, this is as expected.

Our question is still what is the exact SQL statement generated and executed by the OCI when ? parameters are converted to :AA :AB :AC .... parameters.
This is where the problem is.

Offline

#10 2013-06-29 20:27:05

peak.tibor
Member
Registered: 2013-02-22
Posts: 19

Re: Bug in Oracle interface? Please confirm or confute

How can I help to answer this question?

Offline

#11 2013-06-30 06:38:50

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: Bug in Oracle interface? Please confirm or confute

Using the IDE debugger and step into the SynDBOracle unit source when preparing the INSERT statement and see how ? parameters are converted to named OCI parameters.

Offline

#12 2013-06-30 14:59:38

peak.tibor
Member
Registered: 2013-02-22
Posts: 19

Re: Bug in Oracle interface? Please confirm or confute

When 94 params (no error):
insert into insdemo values(:AA,:BA,:CA,:DA,:EA,:FA,:GA,:HA,:IA,:JA,:KA,:LA,:MA,:NA,:OA,:PA,:AB,:BB,:CB,:DB,:EB,:FB,:GB,:HB,:IB,:JB,:KB,:LB,:MB,:NB,:OB,:PB,:AC,:BC,:CC,:DC,:EC,:FC,:GC,:HC,:IC,:JC,:KC,:LC,:MC,:NC,:OC,:PC,:AD,:BD,:CD,:DD,:ED,:FD,:GD,:HD,:ID,:JD,:KD,:LD,:MD,:ND,:OD,:PD,:AE,:BE,:CE,:DE,:EE,:FE,:GE,:HE,:IE,:JE,:KE,:LE,:ME,:NE,:OE,:PE,:AF,:BF,:CF,:DF,:EF,:FF,:GF,:HF,:IF,:JF,:KF,:LF,:MF,:NF)

When 95 params (fails):

insert into insdemo values(:AA,:BA,:CA,:DA,:EA,:FA,:GA,:HA,:IA,:JA,:KA,:LA,:MA,:NA,:OA,:PA,:AB,:BB,:CB,:DB,:EB,:FB,:GB,:HB,:IB,:JB,:KB,:LB,:MB,:NB,:OB,:PB,:AC,:BC,:CC,:DC,:EC,:FC,:GC,:HC,:IC,:JC,:KC,:LC,:MC,:NC,:OC,:PC,:AD,:BD,:CD,:DD,:ED,:FD,:GD,:HD,:ID,:JD,:KD,:LD,:MD,:ND,:OD,:PD,:AE,:BE,:CE,:DE,:EE,:FE,:GE,:HE,:IE,:JE,:KE,:LE,:ME,:NE,:OE,:PE,:AF,:BF,:CF,:DF,:EF,:FF,:GF,:HF,:IF,:JF,:KF,:LF,:MF,:NF,:OF)


So the issue is with the 'OF' named parameter.
I modified the value with the debugger to have 'PF' instead of 'OF' and it was executed without any problem.

Offline

#13 2013-06-30 17:59:43

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: Bug in Oracle interface? Please confirm or confute

So :OF is not good as parameter name.
OF is a keyword, but the : before it mark it as parameter...
No reason, but Oracle sounds very sensitive here.

Just try the latest commit:
http://synopse.info/fossil/info/54739fa01c

Offline

#14 2013-07-01 05:09:35

peak.tibor
Member
Registered: 2013-02-22
Posts: 19

Re: Bug in Oracle interface? Please confirm or confute

With 95 parameters the named params seems (and fails execution):

'insert into insdemo values(:AA,:BA,:CA,:DA,:EA,:FA,:GA,:HA,:IA,:JA,:KA,:LA,:MA,:NA,:OA,:PA,:QA,:RA,:SA,:TA,:UA,:VA,:WA,:XA,:YA,:ZA,:ZB,:ZC,:ZD,:ZE,:ZF,:ZG,:ZH,:ZI,:ZJ,:ZK,:ZL,:ZM,:ZN,:ZO,:ZP,:ZQ,:ZR,:ZS,:ZT,:ZU,:ZV,:ZW,:ZX,:ZY,:ZZ,:Z[,:Z\,:Z],:Z^,:Z_,:Z`,:Za,:Zb,:Zc,:Zd,:Ze,:Zf,:Zg,:Zh,:Zi,:Zj,:Zk,:Zl,:Zm,:Zn,:Zo,:Zp,:Zq,:Zr,:Zs,:Zt,:Zu,:Zv,:Zw,:Zx,:Zy,:Zz,:Z{,:Z|,:Z},:Z~,:Z'#$7F',:Z'#$80',:Z'#$81',:Z'#$82',:Z'#$83',:Z'#$84',:Z'#$85',:Z'#$86')'


From today I will be on holiday, so probably will not be able to make further tests.

Tibor

Offline

#15 2013-07-01 07:00:14

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: Bug in Oracle interface? Please confirm or confute

Yes, previous commit was not finished.

Should be fixed by http://synopse.info/fossil/info/acad52fac1

Thanks for the report.

Offline

#16 2013-07-11 06:01:46

peak.tibor
Member
Registered: 2013-02-22
Posts: 19

Re: Bug in Oracle interface? Please confirm or confute

I could test the fix, it works now. The max. number of parameters is 666! Thanks a lot!

I have one question regarding this ReplaceParamsByNames function:

You remove the last ';', saying it can couse problem.
But when I create a stored procedure the sytax requires a ';' at the end of the "create procedure ..." command.
So I always have to make manual changes to this ReplaceParamsByNames function when a version is introduced.

Could you make some workaround to keep this last ";" for Oracle?

Thanks,

Tibor

Offline

#17 2013-07-11 12:10:29

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: Bug in Oracle interface? Please confirm or confute

peak.tibor wrote:

Could you make some workaround to keep this last ";" for Oracle?

This was ticket http://synopse.info/fossil/tktview?name=4a7da3c6a1

It has been fixed: now any statement ending with END; will still have its last ; character, as expected.

Offline

#18 2013-07-12 06:07:15

peak.tibor
Member
Registered: 2013-02-22
Posts: 19

Re: Bug in Oracle interface? Please confirm or confute

Thanks a lot, it works now.

Offline

Pages: 1

Board footer

Powered by FluxBB