Re: q: sync pl/sql and dynamic ddl statement ?

"Ted Chyn" <ericis6_at_home.com> wrote in message news:<DphY6.331625$oc7.33826949_at_news2.rdc2.tx.home.com>...
> sir: in pl/sql block contain execute immediate ddl statement such as
> 'create table test_1';
> when block is executing dynamic ddl sql statement is commited whether the
> whole pl/sql
> is sucessful or not. Is there a way to make dynamic sql in sync with pl/sql
> block code -i.e. if pl/sql fail no table will be created.
>
> thnx ted chyn

Since all DDL involves an implicit commit before and after DDL statement execution you probably need to resort to error trapping and issuing a drop table command at the procedure end on any error.

You did not give any details of what you are trying to do, but since you do not want the table to exist on error would a temporary table, create temporary table, work instead of a normal table? A temporary table would only be a solution as long as no other session needed to see the data, and the data does not need to be retained after session termination.

• Mark D Powell --