Re: VB6->CR7->crash Oracle...

Howard Lee Harkness wrote in message <372ce31d.31301618_at_gnews.august.net>...
>The symptoms are that after I run a CR7 report (using VB6 to call the
>OCX), our Oracle database quits working, reporting that several of the
>tables that I have been using are corrupt. Oracle quits working
>completely, and nobody else can even log in to the database. The
>problem is complicated by the fact that I am also calling a number of
>stored procedures from VB, one of which creates a new view. I also
>typically have an open SQL*plus session.

If Oracle is running on a server and the VB6/CR7 app on a client machine, then I strongly doubt that that VB6/CR7 can cause the corruption of the database. Assuming that the SQL (or stored procedures) they use are not "hostile" in any manner. But then this problem is not related to the actual product (e.g. VB6 or CR7), but to the actual SQL statements/stored procedures used. The same calls from another application (Delphi, SQL*Plus etc) will cause the same problems.

The second issue is that of connectivity. You mention that no-one can connect either. That may mean that the listener crashed for some reason - maybe some kind of exception occured that it can not handle. Or maybe there's a bug in the protocol stack on the server (not unusual either as I have run into this a few times on UNIX).

Remember that the client application "talks" via SQL*Net to the Oracle server. It is extremely unlikey that this can cause corruption or crashes in the database (assuming that no "hostile" SQL is used). IOW, the communication between the client and server is handled by standard software. The only times I've seen when SQL*Net gets it knickers in a knot is when you are using different versions on the client than on the server. But even that should not cause the database to go belly up. The client, listener or shadow process may hang.

Oracle is an extremely robust piece of software - the only time I ever ran into corruption problems were due to a stuff up by the people who upgraded the operating system and "accidently" screwed up the raw devices.

Thus, if Oracle is crashing and corrupting the database, I would look for the cause of the problem on the server. Check the operating system, the Oracle installation, the hardware and so on. I would think that in your case the VB6/CR7 simply "triggers" the fault on the server - they do not seem to me to be the actual cause of the problem.

>The only suggestion that I have heard so far is that the MS ODBC
>driver should be replaced with one from Oracle.

That simply addresses the interface between the application and SQL*Net on the -client- machine. OK, assuming that you are running VB6/CR7, ODBC and Oracle all together on Windows-NT, then yes. VB6/CR7 and even ODBC can royally screw up Oracle - they share the same operating system on the same machine.

If Oracle bombs, then the 1st place to look is in the Alert Log. Usually a Trace File is also dumped. They should point you towards the problem - or at least describe the symptoms of the crash in great detail.

You also may want to look at the V$SESSION, V$SESSION_EVENT and V$SESSION_WAIT system tables - pick up the rows for the VB6/CR7 process (using the session id or SID) and see what is happening while the report runs.

You can also activate ODBC Tracing on the client. This will produce a (large) ODBC log file with every single ODBC call traced - including the SQL statements that CR7 generates for the reports. Take a look at these and make sure they make sense. Or cut and paste them into an editor, tidy them up and run them via SQL*Plus.

Hope you get the problem solved soon.

regards,
Billy Received on Mon May 03 1999 - 08:52:38 CDT