Executing batches of SQL statements in Oracle via MDAC ODBC driver/ADO .Execute method? problems!

From: Alastair Cameron <alastair.cameron_at_blueyonder.co.uk>
Date: 19 Sep 2001 02:32:18 -0700
Message-ID: <b824c39d.0109190132.6a669e99_at_posting.google.com>


I have a VB6 application which reads blocks of SQL statements from a text file, and executes them via the ADO .Execute method. The command that is being sent through to the .Execute method will contain multiple SQL statements.

This works perfectly for SQL Server. The application has now being modified to work also on Oracle (the SQL is very similar).

A typical batch of statements is shown below, and typically consists of CREATE/SELECT/UPDATE/INSERT INTO statements. Nothing fancy. Each statement may span multiple lines; in addition the statements are obviously separated by CR/LF and the TAB character appears in places.

I've had problems with the MS Oracle ODBC driver returning invalid character after the ADO .Execute method has been submitted.

Do I need to include the ORACLE statement terminator (;) after every statement in the batch? are -- comments acceptable?

Do I need to put all statements in a batch inside a BEGIN / END;

Do I need a COMMIT; after a CREATE TABLE and before any other commands (such as SELECT) which use it in the same batch?

Anything else I need to be aware of when submitting batches of statements?

I've only ever executed single statements at a time, but the format of the file is such that I don't want to parse it statement by statement, and besides both Oracle and SQL Server supports execution of batches of SQL statements, and the SQL server version of the App is working perfecrtly.

Many thanks.

Alastair

------------------------------------->

  • This is a comment
  • This is another comment

CREATE TABLE grde_cbe (

 cbe       VARCHAR2(10),
 supertype     NUMBER(1,0),

 default_nam_scm VARCHAR2(10),
 default_nam_scm_name VARCHAR2(255),
 cbe_name      VARCHAR2(255),
 cbe_sys_lbl     VARCHAR2(255),
 acc       VARCHAR2(10))

  • This is a third comment

INSERT INTO
 grde_cbe
SELECT
 t.object_id,
 1,
 rns.thing3,
 nstx.text_value,
 tx.text_value,
 lbl.value,
 NULL
FROM
 thingt t,
 thingt rns,
 textt tx,
 thingt ns,
 textt nstx,
 identifier_details lbl Received on Wed Sep 19 2001 - 11:32:18 CEST

Original text of this message