topic Re: SAS Connect to Oracle, is there an implied commit? in SAS Programming

SAS Connect to Oracle, is there an implied commit?

fdsaaaa — Mon, 27 Jan 2020 04:46:27 GMT

I have some code that is working , but I wonder why I don't need an explicit commit statement (which would be required in Oracle)

e.g

proc sql;
connect to oracle (server=myserver user=myuserid pw=mypass );
  execute(
  insert into myuserid.mytable 
  (mycol)
  select '1' as mycol from dual
  )
  ) by oracle;

quit;

This code inserts the data into the correct table without an explicit commit .

Why do I not need a commit? is this controlled by an option somewhere ?

thanks

Re: SAS Connect to Oracle, is there an implied commit?

unison — Mon, 27 Jan 2020 04:50:15 GMT

Not 100% familiar, but @SASKiwi's response here: https://communities.sas.com/t5/SAS-Procedures/Oracle-commit-for-two-or-more-sql-statements/td-p/452773

might help.

Re: SAS Connect to Oracle, is there an implied commit?

r_behata — Mon, 27 Jan 2020 04:57:49 GMT

Try :

proc sql;
connect to oracle (server=myserver user=myuserid pw=mypass );
  execute(  insert into myuserid.mytable   (mycol)  select '1' as mycol from dual  )  ) by oracle;

  execute ( COMMIT) by oracle;

  DISCONNECT from Oracle;
quit;

Re: SAS Connect to Oracle, is there an implied commit?

fdsaaaa — Mon, 27 Jan 2020 18:53:35 GMT

thanks, yes I know this works when you explicitly add a commit statement .

My question is why do you get the same result even if you do not have a commit statement?

in Oracle with an insert statement you need have a commit statement afterwards for the changes to be visible to other users .

As I am passing native oracle SQL in the execute statement, I would have expected that I needed to pass an explicit commit statement - but I do not .

I would like to know if this is always the case, and I never need to pass a commit statement, or whether there is some option or similar somewhere that controls this behavior.

thanks

Re: SAS Connect to Oracle, is there an implied commit?

Tom — Mon, 27 Jan 2020 19:10:25 GMT

What have you set for the AUTOCOMMIT and DBCOMMIT options?

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=p00lgy3xwh61b8n16kffwq3veagu.htm&docsetVersion=9.4&locale=en

Re: SAS Connect to Oracle, is there an implied commit?

fdsaaaa — Mon, 27 Jan 2020 19:37:09 GMT

Thanks, These are libname options, so they are not set as I am not using a libname .

I have searched for similar in the system options but have not been able to find anything.