| Incorrect select from a clustered table (probably OCI10 problem) [message #172858] |
Thu, 18 May 2006 06:57  |
rmachalek
Messages: 3
Registered: May 2006
|
Junior Member |
|
|
I've encountered a problem when selecting from a clustered table. The data seems to be corrupted when using some select conditions (depending on the access to the table). When there is no condition, the select returns obviously wrong data (longer than the maximum length, characters in number column, etc.). When a different access is chosen, i.e. access by index, the data is ok.
This results in unusable export dump file in this case as there are some attempts to insert char(3) values into char(1) columns during import, because the export table was based on the bad select with no condition. The import hangs on this. We have made an attempt to run the same select using OCI9 and the result is correct.
So I suppose this is a OCI10 problem.
Is there a patch or some workaround for this?
Thanks
Richard Machalek
|
|
|
|
|
|
| Re: Incorrect select from a clustered table (probably OCI10 problem) [message #172897 is a reply to message #172860] |
Thu, 18 May 2006 09:39  |
rmachalek
Messages: 3
Registered: May 2006
|
Junior Member |
|
|
The Oracle version is 10.1.0.4.0.
The select itself doesn't return any error as OCI obviously doesn't check the data against the data type and data length of the corresponding column.
The import either hangs (usually the first pass) or raises
ORA-01401: inserted value too large for column
(usually the second pass).
I've further studied the case and found this: the problem is probably in the cluster key, whose character length times number of occurences cannot exceed some internal limit.
Try the following scenario, count the number of rows that are good (note that there is only one value inserted to the table)and then change the l_column_length variable. If you increase the length of the cluster key, the number of good rows will decrease.
drop table testcluster_tb;
drop cluster testcluster_cl;
rem create cluster
create cluster testcluster_cl
(clustered_column varchar2(100)
)
hashkeys 100
/
rem create table
create table testcluster_tb
(
clustered_column varchar2(100)
)
cluster testcluster_cl (clustered_column);
rem filling random data
declare
l_random varchar2(2000);
l_column_length integer := 10;
begin
l_random:=dbms_random.string('a',l_column_length);
delete from testcluster_tb; commit;
for i in 1..10 loop
for j in 1..200 loop
insert into testcluster_tb (clustered_column) values (l_random);
end loop;
end loop;
commit;
end;
/
rem wrong select
SELECT * FROM testcluster_tb;
rem good select
SELECT * FROM testcluster_tb ORDER BY clustered_column;
|
|
|
|
|
|
| Re: Incorrect select from a clustered table (probably OCI10 problem) [message #173044 is a reply to message #172991] |
Fri, 19 May 2006 02:40 |
rmachalek
Messages: 3
Registered: May 2006
|
Junior Member |
|
|
The DIRECT=Y option didn't help, the table doesn't contain any LOB.
But I think the export problem is secondary, because it is based on the select problem. The export is just unable to cope with the wrong select as you can see in the example supplied (all rows should return the same value but only the first few does).
This is obviously a bug and I suppose it will have to be patched as we can't come up with a way to workaround it.
Richard
|
|
|
|
| Re: Incorrect select from a clustered table (probably OCI10 problem) [message #173060 is a reply to message #172858] |
Fri, 19 May 2006 04:11 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
| You reported |
In case the "Report message" option serves as a moderator notifier I would like to ask you for some technical advice. Regards Richard
|
No, it is not:
| From the sticky |
If you feel that a message is not admissible for some reason (offending language/opinions - personal attacks - ...), you can report it to the moderator staff. But if you want extra attention for your topic, reporting will act contra productive.
|
More tips and tricks
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
