ERROR 22-7: Invalid option name AUTOCOMMIT. How to turn off autocommit?

[b1958]

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

[Kurt_Bremser]

Could it be that you are looking for the dbcommit= option?

[b1958]

Don't think so, Kurt. I want full Control of the transactions With "begin transaction" and "commit" or "rollback"

[Kurt_Bremser]

Then you should not use a libname, but pass-through SQL.

[b1958]

Yes maybe, but proc fedsql uses implicit passthrough if I'm not wrong.

[SuryaKiran]

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

[b1958]

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.

[b1958]

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;

[SASKiwi]

Have you tried the DBCONINIT LIBNAME option to set AUTOCOMMIT with an Oracle statement:

http://documentation.sas.com/?docsetId=acreldb&docsetTarget=p1agt4p0hj4fyun103no9xpgskdv.htm&docsetV...

[b1958]

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=

[LinusH]

C'mon, how can we respond if you don't show us exactly what you tried and the log?

[b1958]

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

[SASKiwi]

SET AUTOCOMMIT OFF is not a valid PL/SQL statement, it will only work in the SQL*PLUS tool. Try AUTOCOMMIT OFF.

[b1958]

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.

[SASKiwi]

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.