Re: Transaction Handling

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 01 Feb 1999 23:36:30 GMT
Message-ID: <36bc386d.113614939@inet16.us.oracle.com>

On Mon, 01 Feb 1999 22:54:08 GMT, avilner_at_usa.net wrote:

>Dear Oraclers:
>
>I have, what seems to be an easy-to-solve issue with transaction handling, but
>I am not sure how to do it with Oracle (Sybase and others hanle this quite
>well).
>
>A process is running on the user workstation (Windows 95/98), connected to
>the Oracle 7.3 database. This process has a bunch of DML statements. In the
>middle of this process, a stored procedure is called that inserts/deletes
>records from a table. I want the procedure to commit the changes to the table
>it made, without committing changes the rest of the process is in the process
>of making.
>
>In essence, it should be something like:
>BEGIN TRANSACTION <outside process>
> INSERT INTO X;
> DELETE FROM Y;
> UPDATE Z;
>
> CALL SP:
> BEGIN TRANSACTION SP;
> INSERT INTO A;
> DELETE FROM A;
> COMMIT SP; --- And nothing else
>
> .. More DMLs for the outside process...
>COMMIT/ROLLABACK <outside process>

Oracle8i handles this quite well.

eg.

SQL> create table a (
  2 n number );
Table created.

SQL> create table b (
  2 n number );
Table created.

SQL> create or replace
  2 procedure populate_b is
  3 pragma autonomous_transaction;
  4 begin
  5 for i in 1 .. 10 loop
  6 insert into b values ( i );
  7 end loop;
  8 commit;
  9 end;
 10 /
Procedure created.

SQL> begin

  2    insert into a values ( 1 );
  3    insert into a values ( 2 );
  4    insert into a values ( 3 );

  5
  6 populate_b;
  7
  8 insert into a values ( 4 );
  9 insert into a values ( 5 );
 10
 11 rollback;
 12 end;
 13 /
PL/SQL procedure successfully completed.

SQL> select * from a;
no rows selected

SQL> select * from b;

         N

---

         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

chris.

>
>How can something like this be implemented with Oracle?
>Thank you in advance!
>
>Alex Vilner
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Feb 01 1999 - 17:36:30 CST