Oracle commit for two or more sql statements

[aclaraujo]

Hi everyone,

How to performer a begin/commit/rollback operation for two or more steps in proc sql?

For example:

proc sql;


connect to oracle as ora(...);

**begin transaction

insert into ora.a;

insert into ora.b;

delete from ora.c;

**commit transaction for success

or

**rollback transaction for fail

quit;

Thanks,

...

[LinusH]

Don't think that's possible, at least when using libname access. Commit is sent at least after each statement.
Rollback is managed by the COMMIT and ERRLIMIT options.
What is your use case?

[aclaraujo]

Hi, LinusH

Thanks for your attention!

I have a linked_people (id_people1, id_people2) table and a history_people (id_people, operation_date) table. Everytime when insert a row in people_linked table, have be inserted two rows in history_people table.
This operation need be atomic.

[SASKiwi]

Just wrap the code you would use in Oracle PL-SQL, including COMMIT and ROLLBACK commands, in an EXECUTE statement:

proc sql;connect to oracle as ora(...);

execute(

**begin transaction

insert into ora.a;

insert into ora.b;

delete from ora.c;

**commit transaction for success

or

**rollback transaction for fail
) by ora;

quit;

[aclaraujo]

Hi SASKiwi, 

Thanks for response!

But, the problem is source table is a sas dataset. 

[LinusH]

Then you (just) need to upload those tables first.