04-11-2018 09:22 AM - edited 04-11-2018 09:23 AM
I want to run proc fedsql against Oracle and use transaction control. I need to turn off the autocommit. I have tried to do that in the libname statement as shown below, but SAS does not accept "autocommit" as an option.
I will greatly appreciate hints on how to solve this.
LIBNAME PD ORACLE PATH=XXXX SCHEMA=XXX_XX AUTHDOMAIN="Oracle.XXXX.XXX_XXX" autocommit=no ;
I get this:
ERROR 22-7: Invalid option name AUTOCOMMIT.
04-11-2018 09:28 AM
04-11-2018 09:32 AM
Don't think so, Kurt. I want full Control of the transactions With "begin transaction" and "commit" or "rollback"
04-11-2018 09:37 AM
04-11-2018 09:46 AM - edited 04-11-2018 09:47 AM
Yes maybe, but proc fedsql uses implicit passthrough if I'm not wrong.
04-11-2018 09:30 AM
Looks like its not a valid option for Oracle LIBNAME. Check the document http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003113591.htm
04-11-2018 09:44 AM - edited 04-11-2018 09:48 AM
Right. It is not listed on that page.
But autocommit seems to be on as default, and I really need to turn it off.
I should think it would be possible with transaction control with proc fedsql against Oracle, somehow.
04-12-2018 04:41 AM - edited 04-12-2018 04:42 AM
Just to illustrate my problem again, this time with a proc ds2, which is what I want to use.
The rollback in this program has no effect. The insert from the ins-statement stays.
LIBNAME PD ORACLE READBUFF=100000 SQL_FUNCTIONS=ALL UPDATEBUFF=10000 INSERTBUFF=100000 OR_BINARY_DOUBLE=NO PATH=XXXX SCHEMA=XXX_XXX AUTHDOMAIN="Oracle.XXXX.XXX_XXX" ; proc ds2 ; data _null_; method init(); declare package sqlstmt beg('begin'); declare package sqlstmt ins(%tslit(insert into pd.pd_jobs values (10,'test1',0))); declare package sqlstmt rol('rollback'); beg.execute(); ins.execute(); rol.execute(); end; enddata; run;
04-13-2018 10:03 PM
Have you tried the DBCONINIT LIBNAME option to set AUTOCOMMIT with an Oracle statement:
04-16-2018 06:32 AM
Thanks, I tried it.
But I could not get it to accept "SET AUTOCOMMIT OFF", "AUTOCOMMIT OFF", or any of the other variations that I tried after the DBCONINIT=
04-17-2018 02:30 AM
LIBNAME PD ORACLE DBCONINIT="SET AUTOCOMMIT OFF" READBUFF=100000 SQL_FUNCTIONS=ALL UPDATEBUFF=10000 INSERTBUFF=100000 OR_BINARY_DOUBLE=NO PATH=XXXX SCHEMA=XXX_XXX AUTHDOMAIN="Oracle.XXXXX.XXX_XXX" ;
ERROR: The DBMS connection failed. The DBCONINIT=<command> option failed with this message: ORA-00922: manglende eller ugyldig valg. ERROR: Error in the LIBNAME statement.
manglende eller ugyldig valg = missing or invald option
04-19-2018 09:00 AM
Hi, did you mean this:
LIBNAME XXXX ORACLE DBCONINIT="AUTOCOMMIT OFF" OR_BINARY_DOUBLE=NO PATH=CCDWU SCHEMA=XXX_XXXX AUTHDOMAIN="Oracle.XXXX" ;
It says Invalid SQL statement.
ERROR: The DBMS connection failed. The DBCONINIT=<command> option failed with this message: ORA-00900: ugyldig SQL-setning.
04-20-2018 08:05 PM
Another option I found was "alter session set autocommit off". I just found this by googling Oracle forums - you should try googling yourself or as @LinusH says consult your Oracle DBA.