Oracle 12.2.0.1 keeps throwing ORA-01406

[EgonHugeist]

I did not know about this but this makes sense. What is really strange in this case is that I receive the exact same message when the ClientCodePage is set to AL32UTF8 (AL32UTF16 crashes with TNS protocol error, AL32UTF32 )

characterset 'UTF16' of oracle should be supported on 7.3 inbetween, have fun

[aehimself]

characterset 'UTF16' of oracle should be supported on 7.3 inbetween, have fun

Good work, as ever :) Great news, thanks!

[EgonHugeist]

Thanks,

we are doing some performance measurement with external frameworks like mORMot. And using "UTF16" instead of "AL32UTF8" pimps the whole Oracle performance by 10% in avg for all character columns.

So knowing you database and setting the correct ClientCharset before connecting is recommented.
In case of Oracle if no charset is given we connect with the defaults(charset zero, ncharset zero), determining the default charset, closing the connection (bad OCI doesn't allow a call of OCIEnvNlsCreate https://docs.oracle.com/cd/B10501_01/ap ... i15re6.htm with a zeroed Charset and a non zerored NCHARSET -> wheired exceptions will follow) and reconnect. JFYI

[aehimself]

characterset 'UTF16' of oracle should be supported on 7.3 inbetween, have fun

Good work, as ever :) Great news, thanks!

I was too quick, unfortunately. I attempted to change the ClientCodePage to UTF16, and the same error occurred:

An error happened at 0x0000000000FB221A while opening dataset:

EZSQLException was raised with the message ORA-01406: a lehívott oszlop értéke csonkolva lett
SQL: FETCH ROW

Connection information: Oracle 12.1.0, client version: 19.6.0, database access component version: 7.3.0-33cc5d39
Connected to database ****, schema **** at host **** as user ****
Loaded library: C:\WINDOWS\oci.dll
Transaction isolation level: None
AutoEncodeStrings: True
controls_cp: CP_UTF16
codepage: UTF16

Loaded modules:
[...]

I'll try to find the field which is causing the havoc and send you the binary data.

[aehimself]

So the funny thing is that there are no special characters in the resultset, and when the error starts popping up I'm adding a field which has no value.
I suppose we are just overflowing the allocated buffer, regardless of contents...? Maybe during metadata collection...?

Prepare Statement 11 : SELECT * FROM T_MYTABLE WHERE ID = 1
Execeute prepared Statement 11
Prepare Statement 12 : SELECT ALL_TAB_COLUMNS.OWNER, ALL_TAB_COLUMNS.TABLE_NAME, ALL_TAB_COLUMNS.COLUMN_NAME, ALL_TAB_COLUMNS.DATA_TYPE, ALL_TAB_COLUMNS.DATA_LENGTH, ALL_TAB_COLUMNS.DATA_PRECISION, ALL_TAB_COLUMNS.DATA_SCALE, ALL_TAB_COLUMNS.NULLABLE, ALL_TAB_COLUMNS.DATA_DEFAULT, ALL_TAB_COLUMNS.COLUMN_ID, ALL_COL_COMMENTS.COMMENTS FROM ALL_TAB_COLUMNS JOIN ALL_COL_COMMENTS ON ALL_COL_COMMENTS.TABLE_NAME = ALL_TAB_COLUMNS.TABLE_NAME AND ALL_COL_COMMENTS.COLUMN_NAME = ALL_TAB_COLUMNS.COLUMN_NAME AND ALL_COL_COMMENTS.OWNER = ALL_TAB_COLUMNS.OWNER WHERE ALL_TAB_COLUMNS.TABLE_NAME like 'T_MYTABLE' ORDER BY ALL_TAB_COLUMNS.COLUMN_ID
Execeute prepared Statement 12
Execeute prepared FETCH ROW
EZSQLException was raised while opening dataset with the message: ORA-01406: fetched column value was truncated
SQL: FETCH ROW

When I manually execute the metadata command it executes normally, though.

[EgonHugeist]

Hi, what ever i'm doing i can't reproduce the truncation error.

Could you please make a minimal demo-app to reproduce it?

[aehimself]

Could you please make a minimal demo-app to reproduce it?

Hello,
I made a table extract with a binary representation of the fields. However it's a test database I'd still be more calm if the dump would not be public.
I can send you a link where you can download the files and test.
Would this method work?

[EgonHugeist]

If there is no other way around.. yes. But write a guide how i can restore the dump. I never did that with Oracle..
The more i need to know what exacly i've todo to reproduce it.

Oth: plz consider the "zeosbugtemplate" app of AVZ in your ..\zeosbugtemplate\ folder. Add a small create script, some data and the bug is resolved in some minutes, usually.

[EgonHugeist]

Ok, it took me an half hour to port your example.
Sadly there is no regression to see. As you wrote there is just plain ANSI used. So let's find diffs:

My oracle client version exacly matches the host version. Yours:

Server is Oracle 11.2.0 and client library is 12.2.0.

does not match.

While trying to port your example i found some diffs according the given field values which are different: BCD/FMTBCD.
That's the code to get your example working:

Code: Select all

Var
  SR: TSearchRec;
  fs: TFileStream;
  bts: TBytes;
  Path, fn, S: String;
  P: Pointer;
  I64: Int64;
  Cur: Currency;
  DT: TDateTime;
  FT: TFieldType;
  BCD: TBCD;
begin
  PageControl2.ActivePageIndex := 2;
  {ZQuery3.SQL.LoadFromFile('D:\Developer\zeos_oracle_truncate\_table.sql');
  try
    ZQuery3.ExecSQL;
  except end;}
  Path := 'D:\Developer\zeos_oracle_truncate\';
  ZQuery3.SQL.Text := 'select * from T_TEST';
  ZQuery3.Open;
  if ZQuery3.Eof then begin
    if FindFirst(Path+'*.bin', faAnyFile , SR) = 0 then begin
      ZQuery3.Insert;
      repeat
        fs := TFileStream.Create(Path+SR.Name, fmOpenRead);
        fn := ChangeFileExt(SR.Name, '');
        FT := ZQuery3.FieldByName(fn).DataType;
        if FT in [ftWideString, ftWideMemo] then begin
          SetLength(s, fs.Size shr 1);
          fs.Read(Pointer(s)^, fs.Size);
          ZQuery3.FieldByName(fn).AsString := S;
        end else if (fs.Size = 8) and (FT in [ftFmtBcd, ftLargeInt]) then begin
          fs.Read(I64, fs.Size);
          ZQuery3.FieldByName(fn).AsLargeInt := I64;
        end else if (fs.Size = 8) and (FT in [ftBcd]) then begin
          fs.Read(Cur, fs.Size);
          ZQuery3.FieldByName(fn).AsCurrency := Cur;
        end else if (fs.Size = SizeOf(TBCD)) and (FT in [ftBcd,ftFmtBcd]) then begin
          fs.Read((@BCD.Precision)^, fs.Size);
          if BCD.Precision < 19 <----------------------------------- I got weird values here: Precision 129,scale2 that won't work!
          then ZQuery3.FieldByName(fn).AsBCD := BCD
          else ZQuery3.FieldByName(fn).AsCurrency := 0;
        end else if (fs.Size = 8) and (FT in [ftTime, ftDate, ftDateTime]) then begin
          fs.Read(DT, fs.Size);
          ZQuery3.FieldByName(fn).AsDateTime := DT;
        end else begin
          SetLength(bts, fs.Size);
          fs.Read(Pointer(bts)^, fs.Size);
          ZQuery3.FieldByName(fn).AsBytes := bts;
        end;
        fs.Free;
      until FindNext(sr) <> 0;
      FindClose(SR);
      ZQuery3.Post;
    end;
    ZQuery3.Close;
  end;

Could you change the client version to the exact serverversion first?

[aehimself]

The initial error was on a different server. This report is only for ClientCodePage 'UTF16', and the test case I sent is on a different machine. Complete log from my app is the following:

Code: Select all

An error happened at 0x0000000000FB855A while opening dataset:

EZSQLException was raised with the message ORA-01406: fetched column value was truncated
 SQL: FETCH ROW

Connection information: Oracle 12.1.0, client version: 19.6.0, database access component version: 7.3.0-a3b9e21e
Connected to database database, schema schema at host testoracle.local as user user
Loaded library: C:\WINDOWS\oci.dll
Transaction isolation level: None
AutoEncodeStrings: True
controls_cp: CP_UTF16
codepage: UTF16

AE MultiSQL version 1.1.0.114

Loaded modules:
0000000000400000 - C:\LocalWork\AEMultiSQL\Win64\Release\AEMultiSQL.exe
0000000180000000 - C:\Program Files\Dell\QuickSet\dadkeyb.dll
00007FFF47A50000 - C:\WINDOWS\OraOCIEI19.dll
00007FFF98080000 - C:\WINDOWS\oci.dll
00007FFF9A260000 - C:\WINDOWS\SYSTEM32\CRYPTUI.dll
00007FFFA06F0000 - C:\WINDOWS\oraons.dll
00007FFFB9A00000 - C:\WINDOWS\SYSTEM32\TextShaping.dll
00007FFFBB9F0000 - C:\WINDOWS\SYSTEM32\textinputframework.dll
00007FFFC4490000 - C:\WINDOWS\SYSTEM32\cscapi.dll
00007FFFC7790000 - C:\WINDOWS\SYSTEM32\Secur32.dll
00007FFFC7EE0000 - C:\WINDOWS\SYSTEM32\SRVCLI.DLL
00007FFFC92F0000 - C:\WINDOWS\SYSTEM32\netapi32.dll
00007FFFC9660000 - C:\WINDOWS\SYSTEM32\winspool.drv
00007FFFC9810000 - C:\WINDOWS\WinSxS\amd64_microsoft.windows.common-controls_6595b64144ccf1df_6.0.19041.1_none_b555e41d4684ddec\comctl32.dll
00007FFFC9D20000 - C:\WINDOWS\SYSTEM32\msimg32.dll
00007FFFCDE30000 - C:\WINDOWS\SYSTEM32\winhttp.dll
00007FFFD2EE0000 - C:\WINDOWS\SYSTEM32\version.dll
00007FFFD5840000 - C:\WINDOWS\system32\windowscodecs.dll
00007FFFD6360000 - C:\WINDOWS\SYSTEM32\SAMCLI.DLL
00007FFFD8D50000 - C:\WINDOWS\SYSTEM32\wintypes.dll
00007FFFD9550000 - C:\WINDOWS\SYSTEM32\CoreUIComponents.dll
00007FFFD99B0000 - C:\WINDOWS\SYSTEM32\CoreMessaging.dll
00007FFFD9C50000 - C:\WINDOWS\system32\uxtheme.dll
00007FFFDA090000 - C:\WINDOWS\SYSTEM32\VCRUNTIME140.dll
00007FFFDA5F0000 - C:\WINDOWS\SYSTEM32\DWMAPI.DLL
00007FFFDAAC0000 - C:\WINDOWS\SYSTEM32\windows.storage.dll
00007FFFDB510000 - C:\WINDOWS\SYSTEM32\wtsapi32.dll
00007FFFDB660000 - C:\WINDOWS\SYSTEM32\kernel.appcore.dll
00007FFFDBA70000 - C:\WINDOWS\SYSTEM32\ntmarta.dll
00007FFFDBAE0000 - C:\WINDOWS\SYSTEM32\WINSTA.dll
00007FFFDBEC0000 - C:\WINDOWS\SYSTEM32\wkscli.dll
00007FFFDC1A0000 - C:\WINDOWS\SYSTEM32\IPHLPAPI.DLL
00007FFFDC1E0000 - C:\WINDOWS\SYSTEM32\NETUTILS.DLL
00007FFFDC1F0000 - C:\WINDOWS\SYSTEM32\DNSAPI.dll
00007FFFDC500000 - C:\WINDOWS\system32\mswsock.dll
00007FFFDC780000 - C:\WINDOWS\SYSTEM32\Wldp.dll
00007FFFDCBC0000 - C:\WINDOWS\SYSTEM32\SSPICLI.DLL
00007FFFDCC40000 - C:\WINDOWS\SYSTEM32\profapi.dll
00007FFFDCD50000 - C:\WINDOWS\System32\KERNELBASE.dll
00007FFFDD020000 - C:\WINDOWS\System32\msvcp_win.dll
00007FFFDD0C0000 - C:\WINDOWS\System32\ucrtbase.dll
00007FFFDD1C0000 - C:\WINDOWS\System32\win32u.dll
00007FFFDD1F0000 - C:\WINDOWS\System32\CRYPT32.dll
00007FFFDD3B0000 - C:\WINDOWS\System32\bcryptPrimitives.dll
00007FFFDD4E0000 - C:\WINDOWS\System32\gdi32full.dll
00007FFFDD5F0000 - C:\WINDOWS\System32\bcrypt.dll
00007FFFDD620000 - C:\WINDOWS\System32\SHELL32.dll
00007FFFDDD60000 - C:\WINDOWS\System32\sechost.dll
00007FFFDE360000 - C:\WINDOWS\System32\SHLWAPI.dll
00007FFFDE3C0000 - C:\WINDOWS\System32\USER32.dll
00007FFFDE560000 - C:\WINDOWS\System32\Msctf.dll
00007FFFDE680000 - C:\WINDOWS\System32\ole32.dll
00007FFFDE7B0000 - C:\WINDOWS\System32\WS2_32.dll
00007FFFDE9D0000 - C:\WINDOWS\System32\RPCRT4.dll
00007FFFDEB00000 - C:\WINDOWS\System32\comdlg32.dll
00007FFFDEBF0000 - C:\WINDOWS\System32\oleaut32.dll
00007FFFDECC0000 - C:\WINDOWS\System32\shcore.dll
00007FFFDED70000 - C:\WINDOWS\System32\clbcatq.dll
00007FFFDEE20000 - C:\WINDOWS\System32\msvcrt.dll
00007FFFDEEC0000 - C:\WINDOWS\System32\GDI32.dll
00007FFFDEF10000 - C:\WINDOWS\System32\NSI.dll
00007FFFDEF20000 - C:\WINDOWS\System32\KERNEL32.DLL
00007FFFDEFF0000 - C:\WINDOWS\System32\advapi32.dll
00007FFFDF0A0000 - C:\WINDOWS\System32\combase.dll
00007FFFDF540000 - C:\WINDOWS\System32\IMM32.DLL
00007FFFDF5B0000 - C:\WINDOWS\SYSTEM32\ntdll.dll

Why not to load fields with .AsBytes? They do not support it...?

P.s.: would you please remove / mask that SELECT query in your post?

[EgonHugeist]

P.s.: would you please remove / mask that SELECT query in your post?

Which one? Sry

Why not to load fields with .AsBytes? They do not support it...?

Nope, i don't know why, did you ever test it? Look to my code, there are conversions included.
However it would be nice to find the reason..

Note i'me using the 11.2 developer edition. If i've time i'll try to update(add a never version if possible) my server and repeat the test.