Re: DDL Parsing Code for VB?

Steve S wrote:

> I am using 8.1.7.0.0 Standard Edition, ODBC Driver 8.1.77.0
>
> I guess the underlying question is;
> Is there a way to run a multi-statement DDL script using pass-through
> queries from Access?
>
> Every method I try errors out if there is more than one statement
> ending with a ';' or a '/'.

Using those terminators are SQL*Plus syntax - i.e. is used by SQL*Plus to determine the start and end of SQL statements.

These separators are illegal in a SQL statement - when passed to Oracle, you will get a [ORA-00911: invalid character].

Oracle does allow multiple statements in a single "transmission". It's called an anonymous PL/SQL block. Something like this:

mySQLstatement = 'BEGIN' +
                 '   EXECUTE IMMEDIATE blah blah ;' +
                 '   EXECUTE IMMEDIATE blah blah ;' +
                 'END;'

Then you pass that string to your SQL passthru function to Oracle.

However.. it makes more sense to rather implement this a regular PL/SQL stored proc. Limit the SQL bandwidth generated by the client. Limit the amount of database administration stuff in the client and put that where it should be - in the database.

--
Billy