Page 1 of 1

ORA-01406: fetched column value was truncated

Posted: 25.03.2021, 18:03
by aehimself
Error seems to be unrelated to anything, consistently appears on Oracle 12.2, 19.0; changing ClientCodePage does not seem to affect the outcome.
Just execute a query:

Code: Select all

select TABLE.*,rowid from TABLE
The error message pops up:
EZSQLException was raised with the message SQL Error: ORA-01406: fetched column value was truncated

Code: 1406 Message: FETCH ROW
If you leave out ,rowid from the query all works fine.

I doubt but it would be awesome if this would have a connection with the same error I get when codepage is set to UTF-16...

Re: ORA-01406: fetched column value was truncated

Posted: 30.03.2021, 09:46
by aehimself
Michael,

I saw you committed a test in the suite but was unable to reproduce the issue. When I was investigating the same error with the UTF-16 encoding I was trying to find the place where the actual buffer gets allocated but did not succeed. If you can point me to where this happens I can try to check what is going south.

Re: ORA-01406: fetched column value was truncated

Posted: 01.04.2021, 05:40
by EgonHugeist
Hi,

the buffer/rowsize calculation happens in ZDbcOracleResultSet.pas method: procedure TZOracleResultSet_A.Open.

It would be nice if you could resolve that. And if you could resolve the UTF16 missue i would be happy too. Looking forward :bash: i can't reproduce both of them. :nurse:

Re: ORA-01406: fetched column value was truncated

Posted: 25.08.2021, 21:04
by aehimself
Necroing a thread as I had time to look into my abandoned forum posts.

My guess is that the issue is going to be with the WideMemo (RowID) field. Using the query

SELECT LARGEINTEGERFIELD, RowID FROM MYTABLE WHERE ROWNUM < 72000

raises the error. Below the 72000 records issue doesn't seem to appear. Also, leaving out the extra field (LARGEINTEGERFIELD or whatever, type doesn't seem to have an effect).

I found AllocateOracleSQLVars in ZDbcOracleUtils but allocating a ridiculously large memory has no effect. I also set internal_buffer_size and row_prefetch_size to one million (statement creation uses these to allocate it's memory) but the error remains.

Code: Select all

Oracle 19.0.0, client version: 19.8.0, database access component version: 8.0.0-b4f1a652c
RawStringEncoding=DB_CP, codepage=AL32UTF8, internal_buffer_size=1000000, row_prefetch_size=1000000, OCIMultiThreaded=True, LobCacheMode=OnLoad

An error happened while Opening dataset:

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

Stack trace:
00c0e491 MyApp.exe ZDbcOracle      1387 TZOracleConnection.HandleErrorOrWarning
00bdcc78 MyApp.exe ZDbcOracleResultSet 2709 TZOracleResultSet.Next
008c366d MyApp.exe ZDbcCachedResultSet 2589 TZCachedResultSet.Fetch
008c41f7 MyApp.exe ZDbcCachedResultSet 2866 TZCachedResultSet.MoveAbsolute
00898681 MyApp.exe ZDbcResultSet    2636 TZAbstractResultSet.Next
00cd5248 MyApp.exe ZAbstractRODataset  2056 TZAbstractRODataset.FetchOneRow
00cd5125 MyApp.exe ZAbstractRODataset  2032 TZAbstractRODataset.FetchRows
00cd6645 MyApp.exe ZAbstractRODataset  2460 TZAbstractRODataset.GetRecord
0086040e MyApp.exe Data.DB       13589 TDataSet.GetRecord
008612c4 MyApp.exe Data.DB       13999 TDataSet.GetNextRecord
00861884 MyApp.exe Data.DB       14143 TDataSet.GetNextRecords
00860fe9 MyApp.exe Data.DB       13929 TDataSet.SetBufferCount
00861108 MyApp.exe Data.DB       13953 TDataSet.UpdateBufferCount
0085d736 MyApp.exe Data.DB       12681 TDataSet.DoInternalOpen
0085d853 MyApp.exe Data.DB       12708 TDataSet.OpenCursor
0085d60f MyApp.exe Data.DB       12660 TDataSet.SetActive
0085d3b0 MyApp.exe Data.DB       12616 TDataSet.Open
00f3474a MyApp.exe uSQLActionThread   700 TOpenThread.InternalExecute
00e1d4bf MyApp.exe uWorkerThread     100 TWorkerThread.Execute
004f1714 MyApp.exe madExcept         HookedTThreadExecute
00551620 MyApp.exe System.Classes   15573 ThreadProc
00410fda MyApp.exe System       25380 ThreadWrapper
004f15e9 MyApp.exe madExcept         ThreadExceptFrame
7ff8f60c KERNEL32.DLL               BaseThreadInitThunk
7ff8f68a ntdll.dll                RtlUserThreadStart
Exception is raised because ZDbcOracleResultSet.pas : 2672 (TZOracleResultSet.Next)

Code: Select all

    Status := FPlainDriver.OCIStmtExecute(FOCISvcCtx, FStmtHandle,
      FOCIError, FIteration, 0, nil, nil, OCI_DEFAULT);
returns the Status -1 (OCI_ERROR) and : 2709 calls HandleErrorOrWarning which throws the exception.

It's getting late and I simply don't know where else I can immaturely raise allocation sizes to test. I'll probably return to this issue tomorrow but any help is greatly appreciated :)