Help using Base SAS procedures

Oracle commit for two or more sql statements

Reply
Occasional Contributor
Posts: 6

Oracle commit for two or more sql statements

[ Edited ]

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,

...

Super User
Posts: 5,876

Re: Oracle commit for two or more sql statements

Posted in reply to aclaraujo
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?
Data never sleeps
Occasional Contributor
Posts: 6

Re: Oracle commit for two or more sql statements

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.

Super User
Posts: 3,918

Re: Oracle commit for two or more sql statements

Posted in reply to aclaraujo

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;
Occasional Contributor
Posts: 6

Re: Oracle commit for two or more sql statements

[ Edited ]

Hi SASKiwi, 

Thanks for response!

 

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

Super User
Posts: 5,876

Re: Oracle commit for two or more sql statements

Posted in reply to aclaraujo
Then you (just) need to upload those tables first.
Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 154 views
  • 0 likes
  • 3 in conversation