Hi
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.
Thanks
Could it be that you are looking for the dbcommit= option?
Don't think so, Kurt. I want full Control of the transactions With "begin transaction" and "commit" or "rollback"
Then you should not use a libname, but pass-through SQL.
Yes maybe, but proc fedsql uses implicit passthrough if I'm not wrong.
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
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.
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;
Have you tried the DBCONINIT LIBNAME option to set AUTOCOMMIT with an Oracle statement:
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=
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
SET AUTOCOMMIT OFF is not a valid PL/SQL statement, it will only work in the SQL*PLUS tool. Try AUTOCOMMIT OFF.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.