Multiple procedure calls/DDL commands in single ADO execute

Hi there Oracle Gurus,

I am porting our data access layer from MSSQL to Oracle (9i), and am having some fundamental issues with issuing multiple procedure calls and DDL script in a single EXECUTE. For example, I have a database creation routine which:
1. drops all tables (if they exist)
2. creates tables / indexes / relationships
3. insters pre-defined data

To achieve stage 1, I have a stored procedure that drops a table if it exists:

CREATE OR REPLACE  PROCEDURE "AUSER"."DROP_TABLE"  
    (table_name IN VARCHAR2) AS
BEGIN
      IF TABLE_EXISTS(table_name)
      THEN
            EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
            DBMS_OUTPUT.PUT_LINE('Table ' || table_name || ' has been dropped');
      ELSE
            DBMS_OUTPUT.PUT_LINE('Table ' || table_name || ' cannot dropped because it doesnt exist');
      END IF;

END;


Tradidtionally I would have a single script that calls this procedure for each table:
CALL DROP_TABLE('Table1')
CALL DROP_TABLE('Table2')
CALL DROP_TABLE('Table3')

This will not work (with or without a ; delimiter).  And I really dont want to go down the route of issuing a single command at a time, as stage 3 involves somewhere in the region of 700 INSERTS.

I guess the pure Oracle solution to this is to compile all commands into a stored procedure, and call this procedure once.  But I dont really want to expose our code in the backend.

Any ideas ?

Andy
Start Free Trial