Re: Dynamic tables in PL/SQL proc

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 06 Mar 1999 02:50:58 GMT
Message-ID: <36e396d5.2419469@192.86.155.100>

A copy of this was sent to "Arjan van Bentem" <avbentem_at_DONT-YOU-DAREdds.nl> (if that email address didn't require changing) On Fri, 5 Mar 1999 22:42:05 +0100, you wrote:

>Thomas Kyte wrote
>>if any of the delete or inserts fail -- the original data will be there. the
>>procedure will run as a single statement, either they ALL work or none do.
>
>Really? The procedure will run as a single statement?
>

yup..

>If so, then I never realized that! I have to check at the office on Monday...
>I always figgered that if, for example, the second INSERT fails, the DELETE
>and the first INSERT still need to be rollbacked manually, like below (uhhh, I
>do not recall the exact syntax of setting the start of a transaction):
>
> begin
> set transaction my_start;
> delete from t;
> insert into t select * from t1;
> insert into t select * from t2;
> insert into t select * from t3;
> insert into t select * from t4;
> insert into t select * from t5;
> commit;
> exception
> when others then
> rollback to my_start;
> end;
>

no, you don't need the exception handlers. a procedure is treated as an atomic statement -- it either SUCCEEDS and all statements succeed or it fails (and all statements fail).

btw: its "savepoint my_start", not set transaction. In Oracle, there are implicit savepoints around each statement and each procedure to make each one atomic (so if an insert fails -- the triggers on the table being inserted into roll back as well for example)

Here is an example:

SQL> create table emp as select * from scott.emp; Table created.

SQL> alter table emp add constraint emp_pk primary key(empno); Table altered.

SQL> create or replace procedure REFRESH_EMP   2 as
  3 begin

  4          delete from emp;
  5          insert into emp select * from scott.emp where rownum = 1;
  6          -- the next insert will FAIL
  7          insert into emp select * from scott.emp where rownum = 1;

  8 end;
  9 /
Procedure created.

SQL> REM see how many rows in to begin with SQL> select count(*) from emp;

  COUNT(*)

---

        15

SQL>
SQL> exec refresh_emp
begin refresh_emp; end;

*
ERROR at line 1:

ORA-00001: unique constraint (TKYTE.EMP_PK) violated
ORA-06512: at "TKYTE.REFRESH_EMP", line 7
ORA-06512: at line 1

SQL> REM see how many after...
SQL> select count(*) from emp;

  COUNT(*)

---

        15

see, the emp table was preserved after the delete and one insert. the second insert failed and rolled back itself, the sucessful insert and the delete.

>Arjan.
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Mar 05 1999 - 20:50:58 CST