SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
fdsaaaa
Obsidian | Level 7

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

5 REPLIES 5
r_behata
Barite | Level 11

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;
fdsaaaa
Obsidian | Level 7

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

 

 

fdsaaaa
Obsidian | Level 7

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.  

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2512 views
  • 1 like
  • 4 in conversation