Executing batches of SQL statements in Oracle via MDAC ODBC driver/ADO .Execute method? problems!
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