- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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/4527...
might help.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
What have you set for the AUTOCOMMIT and DBCOMMIT options?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.