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

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

b1958 — Wed, 11 Apr 2018 13:23:23 GMT

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

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

Kurt_Bremser — Wed, 11 Apr 2018 13:28:00 GMT

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

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

SuryaKiran — Wed, 11 Apr 2018 13:30:07 GMT

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

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

b1958 — Wed, 11 Apr 2018 13:32:59 GMT

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

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

Kurt_Bremser — Wed, 11 Apr 2018 13:37:36 GMT

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

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

b1958 — Wed, 11 Apr 2018 13:48:14 GMT

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.

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

b1958 — Wed, 11 Apr 2018 13:47:21 GMT

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

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

b1958 — Thu, 12 Apr 2018 08:42:48 GMT

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;

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

SASKiwi — Sat, 14 Apr 2018 02:03:11 GMT

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

http://documentation.sas.com/?docsetId=acreldb&docsetTarget=p1agt4p0hj4fyun103no9xpgskdv.htm&docsetVersion=9.4&locale=en

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

b1958 — Mon, 16 Apr 2018 10:32:09 GMT

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=

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

LinusH — Mon, 16 Apr 2018 15:52:54 GMT

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

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

b1958 — Tue, 17 Apr 2018 06:30:56 GMT

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

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

SASKiwi — Wed, 18 Apr 2018 20:59:38 GMT

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

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

b1958 — Thu, 19 Apr 2018 13:00:51 GMT

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.

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

LinusH — Thu, 19 Apr 2018 20:50:01 GMT

Now is the time to talk to your Oracle DBA. This is not SAS domains.

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

SASKiwi — Sat, 21 Apr 2018 00:05:02 GMT

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.

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

b1958 — Sat, 21 Apr 2018 05:31:26 GMT

Sure I tried Google and as I think I mentioned above I tried several variations of the autocommit off. But thanks.