Re: PL/SQL Catch*22?

From: Authorised User <bp_at_bp.com>
Date: 1997/11/07
Message-ID: <01bceb62$09a8a1a0$b90b66a1@eusunw2415.rec.bp.com>#1/1

TRy this, if it works...

CREATE OR REPLACE PROCEDURE create_Table (

         create_table_in IN VARCHAR2 )
IS

        error_exp               EXCEPTION;
        cid                     NUMBER;
        -- the rows returned from the dbms_sql.execute function
        rec                     NUMBER; 
BEGIN
        cid := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(cid, create_table_in ,dbms_sql.v7);
        rec:= dbms_sql.execute(cid);
        IF rec <= 0 THEN -- we did no successfully excute the statment
                raise error_exp;
        END IF;
        COMMIT;
        DBMS_SQL.CLOSE_CURSOR(cid);
        EXCEPTION
                WHEN error_exp THEN
                        DBMS_SQL.CLOSE_CURSOR(cid);
                        RAISE;
                WHEN OTHERS THEN
                        DBMS_SQL.CLOSE_CURSOR(cid);
                        RAISE;

END Create_Table;

cheers Received on Fri Nov 07 1997 - 00:00:00 CST