OCI-21560 error with Delphi 10.1 x64 app

dexter · 2017-12-16 21:54:58

Hi,

when I tried to convert my app from x32 to x64 platform I faced the following issue:
during my first select from Oracle I'm getting the error: {"ESQLDBOracle(0B293D90)":{"Message":"TSQLDBOracleLib error: OCI-21560: argument 10 is null, invalid, or out of range"}}
stack trace API 014279D9 SynDBOracle.TSQLDBOracleStatement.Prepare (3395) 0141F2C3 SynDBOracle.TSQLDBOracleConnection.NewStatementPrepared (2094)
0136B0C0 SynDB.TSQLDBConnectionProperties.NewThreadSafeStatementPrepared (4850) 0136A56A SynDB.TSQLDBConnectionProperties.Execute (4719)
01428CAD uOracleProvider.TOracleProvider.CheckIfConfigTableExists (103) 01428E45 uOracleProvider.TOracleProvider.InternalConnect (111)
010BCC3B uAbstractProvider.TAbstractProvider.Connect (110) 010C0F44 uAbstractProvider.TProviderList.Get (443)

I have tracked down to the line which fails: SynDBOracle.pas.TSQLDBOracleConnection.Connect method (1976):

    Check(self,nil,TypeByName(fEnv,fError,fContext,Pointer(type_owner_name),length(type_owner_name),
      Pointer(type_NymberListName),length(type_NymberListName),nil,0,OCI_DURATION_SESSION,OCI_TYPEGET_HEADER,
      fType_numList),fError);

The select looks like this:

  if not FProp.Execute('SELECT table_name FROM dba_tables where owner = ? and table_name = ?',  [UpperCase(GetSchema()), GetConfigTableName()]).Step then
...

In x32 bit mode everyghing is fine.
OCI is version 12.n, connecting to Oracle 10

Hope it helps.

Regards.

ab · 2017-12-17 08:36:16

Are you 100% this is in the TypeByName() OCI call?

It is weird, because it sounds like if the OCI_DURATION_SESSION = OCI_DURATION_BEGIN_ = 10 is what is expected.
See https://docs.oracle.com/cd/A91202_01/90 … i17n35.htm

dexter · 2017-12-17 08:54:42

Yes, I'm pretty sure, I debugged until this line, tried to step over it and got exception.
Maybe Delphi is not very accurate when debugging 64bit app, but certainly the error is in Connect method, because it fails during connection.

Did you manage to replicate the issue?

Last edited by dexter (2017-12-17 08:55:36)

dexter · 2018-01-03 19:16:37

Hi ab,

I have updated the mORMot from latest master and I'm getting now access violation:
20180103 19120122 EXC EAccessViolation ("Access violation at address 000007FEC1D85CC4 in module 'OraOCIICUS12.dll'. Wr
ite of address 0000000000000018") at 000007FEC1D85CC4 stack trace API 004164B8 System.@RaiseAgain (21565) 0195CCDC SynD
BOracle.TSQLDBOracleStatement.Prepare (3396) 019545B3 SynDBOracle.TSQLDBOracleConnection.NewStatementPrepared (2094) 007
16F90 SynDB.TSQLDBConnectionProperties.NewThreadSafeStatementPrepared (4850) 0071643A SynDB.TSQLDBConnectionProperties.E
xecute (4719) 0195E72D uOracleProvider.TOracleProvider.CheckIfConfigTableExists (144) 0195E8C5 uOracleProvider.TOraclePr...

It happens on line 1948 of SynDBOracle, EnvNlsCreate call:

    if fEnv=nil then
      // will use UTF-8 encoding by default, in a multi-threaded context
      // OCI_EVENTS is needed to support Oracle RAC Connection Load Balancing
      EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode,
        nil,nil,nil,nil,0,nil,OCI_UTF8,OCI_UTF8);

Hope this helps.
In x32 platform is fine.

Last edited by dexter (2018-01-03 19:18:10)

dexter · 2018-01-04 07:47:02

Looks like it's project specific issue.
I have just created an empty project which does a simple select in x64 mode and it works fine.
Perhaps some project compiler settings are creating the original issue.

dexter · 2018-01-04 12:05:55

Hi ab,

I managed to reproduce the problem.
See below the demo project with inline comments:

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SynDB, SynDBOracle,
  System.SysUtils;

var
  Prop: TSQLDBConnectionPropertiesThreadSafe;

procedure RunSelect();
begin
// this one fails (when runs second time) in Win64 because of parameters, in Win32 is OK
  Prop.Execute('SELECT table_name FROM dba_tables where owner = ? and table_name = ?', ['1', '2']);

// this one is OK in both Win32 and Win64
//  Prop.Execute('SELECT table_name FROM dba_tables where owner = ''1'' and table_name = ''2''', []);
end;

procedure TestOCI();
begin
  Prop := TSQLDBOracleConnectionProperties.Create('OraServer/OraDB', '', 'user', 'password');
  try
    RunSelect();
    writeln('OK');
  finally
// if remove Prop.Free - it works OK in all cases (with or without select parameters)
    Prop.Free;
  end;
end;

begin
{$IFDEF Win64}
  SynDBOracleOCIpath := '.\Oracle12_x64';
{$ELSE}
  SynDBOracleOCIpath := '.\Oracle12_x32';
{$ENDIF}
  try
    TestOCI;
    TestOCI;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  readln;
end.

The output is:

OK
ESQLDBOracle: TSQLDBOracleLib error: OCI-21560: argument 10 is null, invalid, or out of range

Last edited by dexter (2018-01-04 14:05:29)

ab · 2018-01-05 08:46:44

If you use a regular table (not dba_tables), does it work?

dexter · 2018-01-05 09:10:44

No, it does not.

I have noticed another thing: with one parameter everything is fine, with 2 it's not.

// this one fails in Win64 on second call
  Prop.Execute('SELECT * FROM fbnk_account t where t.recid = ? or t.recid = ?', ['1', '2']);

// this one is OK in Win64
Prop.Execute('SELECT * FROM fbnk_account t where t.recid = ?', ['1']);

Sargon · 2018-02-12 08:49:22

dexter wrote:

No, it does not.
I have noticed another thing: with one parameter everything is fine, with 2 it's not.
// this one fails in Win64 on second call
  Prop.Execute('SELECT * FROM fbnk_account t where t.recid = ? or t.recid = ?', ['1', '2']);

// this one is OK in Win64
Prop.Execute('SELECT * FROM fbnk_account t where t.recid = ?', ['1']);

I have the same problem. OCI is version 12.n, connecting to Oracle 11, berlin 10.1 x64

Last edited by Sargon (2018-02-12 08:49:42)

mpv · 2018-02-12 09:57:53

@Sargon - can you try to replace

OCITypeGetOpt = (OCI_TYPEGET_HEADER, OCI_TYPEGET_ALL);

to

{$MINENUMSIZE 4}
OCITypeGetOpt = (OCI_TYPEGET_HEADER, OCI_TYPEGET_ALL);
{$MINENUMSIZE 1}

and test your case? I don't have a x64 OCI right now to test.

Sargon · 2018-02-12 11:04:26

mpv wrote:

@Sargon - can you try to replace
OCITypeGetOpt = (OCI_TYPEGET_HEADER, OCI_TYPEGET_ALL);
to
{$MINENUMSIZE 4}
OCITypeGetOpt = (OCI_TYPEGET_HEADER, OCI_TYPEGET_ALL);
{$MINENUMSIZE 1}
and test your case? I don't have a x64 OCI right now to test.

GREAT!!! many many thanks!!! It's work. You are right!

mpv · 2018-02-12 11:11:35

Applied to trunk see commit
Thanks for testing and sorry for issue

ab · 2018-02-12 11:18:27

Nice catch!

Perhaps the cleanest way to fix it is to use plain constant, and a ub4 pointer.
It will be closest to the way SynDBOracle access the low-level OCI API.
See https://synopse.info/fossil/info/e55e3e8635

Sargon · 2018-02-12 11:32:10

mpv wrote:

Applied to trunk see commit
Thanks for testing and sorry for issue

not at all!

ab wrote:

Nice catch!
Perhaps the cleanest way to fix it is to use plain constant, and a ub4 pointer.
It will be closest to the way SynDBOracle access the low-level OCI API.
See https://synopse.info/fossil/info/e55e3e8635

Thanks!

Last edited by Sargon (2018-02-12 11:33:02)

mpv · 2019-10-30 18:56:10

I made a VERY DIRTY hack in SynDBOracle to fix binding of BLOB parameters longer when 2000 bytes - see #250

May be better solution exist, or somebody who knows pascal internals can tell me do I need to cleanup a hacked string length (see pull request for details)?

Any help is welcome!

mpv · 2019-10-31 11:38:03

To be sure monkey patching of string length do not broke a pascal strings they length are now restored for Oracle BLOB bindings. We put a patch #250 to our test environment to verify everything is OK

ab · 2019-11-01 07:11:47

I will look further to the patch.
My first idea is that we have to call UniqueString() before patching the string header, otherwise weird things may happen on multi-thread process if the string has a RefCount>1.

ab · 2019-11-01 14:31:52

Please check https://github.com/synopse/mORMot/commit/fb9e42da9f4

mpv · 2019-11-01 15:52:53

Thanks! Looks better when original patch. I'll check after 12 November - do not have access to Oracle because of vacation. For a while original patch works on my productions

ab · 2019-11-01 17:37:30

There is was no potential issue with your patch - unless the string is used in another thread... but which should not be the case in most cases.
Calling UniqueString() won't create any copy, unless RefCount>1, just to be sure/safe.

Enjoy your vacations!

Cahaya · 2023-02-04 14:09:41

Similar error with oracle xe 21c, delphi 7, win64.

vProps.Execute('select item_no from items where item_no = ?',['GA-002']);

But ok, with syndb.tquery params and newthreadsafestatementprepared.

vRow:=vProps.NewThreadSafeStatementPrepared('select item_no from items where item_no=%',['''GA-002'''],true);

Last edited by Cahaya (2023-02-04 14:40:42)

ab · 2023-02-05 07:55:37

Your problem is that you can't bind 'GA-002' into item_no ? parameter (your first Execute command).
If you don't bind the parameter, but put as text, it works (your 2nd command).

So I guess it is not a Execute/NewThreadSafeStatementPrepared issue, but a binding issue.

vProps.Execute('select item_no from items where item_no = ''GA-002''');

is likely to work.

What is the exact error?
What is the item_no column type in the DB?
Which exact version of mORMOt do you use?

mORMot Open Source

#1 2017-12-16 21:54:58

OCI-21560 error with Delphi 10.1 x64 app

#2 2017-12-17 08:36:16

Re: OCI-21560 error with Delphi 10.1 x64 app

#3 2017-12-17 08:54:42

Re: OCI-21560 error with Delphi 10.1 x64 app

#4 2018-01-03 19:16:37

Re: OCI-21560 error with Delphi 10.1 x64 app

#5 2018-01-04 07:47:02

Re: OCI-21560 error with Delphi 10.1 x64 app

#6 2018-01-04 12:05:55

Re: OCI-21560 error with Delphi 10.1 x64 app

#7 2018-01-05 08:46:44

Re: OCI-21560 error with Delphi 10.1 x64 app

#8 2018-01-05 09:10:44

Re: OCI-21560 error with Delphi 10.1 x64 app

#9 2018-02-12 08:49:22

Re: OCI-21560 error with Delphi 10.1 x64 app

#10 2018-02-12 09:57:53

Re: OCI-21560 error with Delphi 10.1 x64 app

#11 2018-02-12 11:04:26

Re: OCI-21560 error with Delphi 10.1 x64 app

#12 2018-02-12 11:11:35

Re: OCI-21560 error with Delphi 10.1 x64 app

#13 2018-02-12 11:18:27

Re: OCI-21560 error with Delphi 10.1 x64 app

#14 2018-02-12 11:32:10

Re: OCI-21560 error with Delphi 10.1 x64 app

#15 2019-10-30 18:56:10

Re: OCI-21560 error with Delphi 10.1 x64 app

#16 2019-10-31 11:38:03

Re: OCI-21560 error with Delphi 10.1 x64 app

#17 2019-11-01 07:11:47

Re: OCI-21560 error with Delphi 10.1 x64 app

#18 2019-11-01 14:31:52

Re: OCI-21560 error with Delphi 10.1 x64 app

#19 2019-11-01 15:52:53

Re: OCI-21560 error with Delphi 10.1 x64 app

#20 2019-11-01 17:37:30

Re: OCI-21560 error with Delphi 10.1 x64 app

#21 2023-02-04 14:09:41

Re: OCI-21560 error with Delphi 10.1 x64 app

#22 2023-02-05 07:55:37

Re: OCI-21560 error with Delphi 10.1 x64 app

Board footer