topic Oracle commit for two or more sql statements in SAS Procedures

Oracle commit for two or more sql statements

aclaraujo — Tue, 10 Apr 2018 12:04:29 GMT

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,

...

Re: Oracle commit for two or more sql statements

LinusH — Tue, 10 Apr 2018 22:15:58 GMT

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?

Re: Oracle commit for two or more sql statements

SASKiwi — Wed, 11 Apr 2018 00:20:39 GMT

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;

Re: Oracle commit for two or more sql statements

aclaraujo — Wed, 11 Apr 2018 12:42:59 GMT

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.

Re: Oracle commit for two or more sql statements

aclaraujo — Wed, 11 Apr 2018 13:49:41 GMT

Hi SASKiwi,

Thanks for response!

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

Re: Oracle commit for two or more sql statements

LinusH — Thu, 12 Apr 2018 02:56:55 GMT

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