Hi

Is there a maximum number of fields for Oracle9?

I have a large query returning 196 fields from a complex query using several union all into a TOraclewwDataSet.
Everything runs fine.
But when I add 5 extra (string and date) fields the query returns an ORA-03113 End of Communication Channel-error on Oracle9, and the connection to the database is lost.
It works fine on Oracle10 and Oracle11.
If the query is executed in PL/SQLDeveloper on the Oracle9-database, it works fine.
We use Delphi XE2 and DOA ??

So: Is there some sort of maximum number of fields for Oracle9?

ORA-03113 usually means that the Oracle Server process for the current session has crashed. Can you check if a user trace dump file exist on the database server that corresponds to this ORA-03113 error? Usually you will see an ORA-00600 error here, with some error codes that may help us find the cause of the problem. If it does, can you send it to me?

Hi Marco,

we made a trace file. In the trace file we see the sql and the beginning of the parsing of the fields. After 14 fields the trace files stops. There is no error in the trace. No changes were made in the first 18 fields.

The strange things is that the application crashed with a ora error but its not in the trace file.

kind regards

Ruud

Hi Marco,

we kind off found a workaround. The faulting query schematic was:

SELECT lin.*
,tot.*
,someextrafields
,:variablename extrastringfield
FROM (SELECT fields
FROM TABLES
UNION ALL
SELECT fields
FROM TABLES) lin
,(SELECT * from TABLE WHERE ses=1) tot
WHERE lin.trn=tot.trn
ORDER BY ORDERFIELDS;

if we move the variable to the Union parts it works:

SELECT lin.*
,tot.*
,someextrafields
FROM (SELECT fields
,:variablename extrastringfield
FROM TABLES
UNION ALL
SELECT fields
,:variablename extrastringfield
FROM TABLES) lin
,(SELECT * from TABLE WHERE ses=1) tot
WHERE lin.trn=tot.trn
ORDER BY ORDERFIELDS;


It works but we don't understand why it works.
Do you? Its a normal string variable that gets its value in both situations.

kind regards

Ruud

Unfortunately there is usually not much logic to ORA-03113 errors.