Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Question Unanswered: 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

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Have you tried wrapping the procedure calls in a begin end block?

    Code:
    begin
       drop_table('a');
       drop_table('b');
    end;
    Incidentally - the purpose of ADO is to (attempt) to offer a single unified means of access to any backend. As you've just found, this is a bit more complex than supporting a few sql queries.

    If you have the option/choice/time, consider using something less generic, IMHO dbexpress is much quicker that ADO, and stuff like NCOCI or DOA (specific to certain development languages) absolutely fly in comparison.

    Hth
    Bill
    Last edited by billm; 11-29-04 at 00:14. Reason: dyslexic keyboard
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Nov 2004
    Posts
    2
    Thanks for your feedback Bill.

    I actually came to the same "Anonymous Block" conclusion. And yes it works !

    Unfortunately we are tied into using ADO as standard, and a change away from this would mean a major rewrite of our data access layer. But thanks for the heads-up anyway. Our managenment of SQL \ PL/SQL \ T-SQL is flexible enough to allow for all the differences in these different flavours of SQL.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •