Hello all,
I know this might be a strange design but this is what we are facing. We have a select statement that joins over 9 tables, each table has 905 columns, and the select statement select all columns in each table. This query results in error ORA-00913.
We believe this might be a limitation in Oracle or a bug. We can fix this by retrieving the data into multiple patches, like joining 5 tables at a time.
The question since we are doing this in ProC*, is there away to get the data by tweaking the query instead of getting the data in multiple steps?
Here is the query that causes the above error, as you see, I am using an alias to the same table in order to replicate the problem using sqlplus:
select L0.*, L1.*, L2.*, L3.*, L4.*, L5.*, L6.*, L7.*, L8.*, L9.*
FROM tabXYZ L0, tabXYZ_1 L1, tabXYZ_2 L2, tabXYZ_3 L3, tabXYZ_4 L4, tabXYZ_4 L5, tabXYZ_4 L6, tabXYZ_4 L7, tabXYZ_4 L8, tabXYZ_4 L9
where L0.id_key = (select id_key from tabXYZ_log where log_key = 12 and flag = 'A')
and L0.name = L1.name and L0.num = L1.num and L0.id_key = L1.id_key
and L0.name = L2.name and L0.num = L2.num and L0.id_key = L2.id_key
and L0.name = L3.name and L0.num = L3.num and L0.id_key = L3.id_key
and L0.name = L4.name and L0.num = L4.num and L0.id_key = L4.id_key
and L0.name = L5.name and L0.num = L5.num and L0.id_key = L5.id_key
and L0.name = L6.name and L0.num = L6.num and L0.id_key = L6.id_key
and L0.name = L7.name and L0.num = L7.num and L0.id_key = L7.id_key
and L0.name = L8.name and L0.num = L8.num and L0.id_key = L8.id_key
and L0.name = L9.name and L0.num = L9.num and L0.id_key = L9.id_key
order by L0.name;