Re: Oracle oledb bug?

On 21 Feb 2006 11:19:20 -0800, sdbillsfan_at_gmail.com wrote:

>All,
> I am encountering an issue with gathering stats from an OLEDB
>connection when the stats to be gathered include a composite index. I
>receive the ambiguous " OraOLEDB: ORA-03113: end-of-file on
>communication channel" from the calling program and even if my
>anonymous block has an exception handler it is never fired. Below is a
>simple vbs script to replicate the problem. I am running Oracle9i
>Enterprise Edition Release 9.2.0.6.0 on Win2k with ADO v2.8 but have
>replicated this using several other configurations/platforms.
>
>Set db = CreateObject("ADODB.Connection")
>db.open "Provider=OraOLEDB.Oracle;User Id=system;Password=manager;Data
>Source="
>
>db.execute "begin execute immediate 'create table teststats(a NUMBER, b
>NUMBER)'; end;"
>
>db.execute "begin for x in 1..4000 loop insert into teststats
>values(x,x); end loop; end;"
>
>'works fine with the following single column index
>'db2.execute "begin execute immediate 'create index teststats_idx on
>teststats(a)'; end ;"
>
>'breaks with this composite index
>db.execute "begin execute immediate 'create index teststats_idx on
>teststats(a,b)'; end ;"
>
>db.execute "begin DBMS_STATS.GATHER_TABLE_STATS( OWNNAME=>null,
>TABNAME=>'teststats', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
>
>CASCADE=>true); end;"
>
>db.execute "begin execute immediate 'drop table teststats'; end;"
>db.close

First of all just the idea you want to do this in a front end tool is of course horrible. DDL is a server task, shouldn't be done in a front end, and this code has nothing to do with proper administration. It is just a plain utter nightmare, especially as you also expose the system password which is still set to the default everyone knows (except for the people who never read installation instructions: they ask here).

That said, you should know, on your way to hell, that there are at least a 1000 different causes for ora-3113, and those causes can only be diagnosed by means of searching on Metalink, and/or submitting a service request on this site.

--
Sybrand Bakker, Senior Oracle DBA