Help using Base SAS procedures

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

Reply
Occasional Contributor
Posts: 17

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

[ Edited ]

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

Super User
Posts: 10,217

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 17

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

Posted in reply to KurtBremser

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

Super User
Posts: 10,217

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 17

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

[ Edited ]
Posted in reply to KurtBremser

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

Valued Guide
Posts: 590

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

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

Thanks,
Suryakiran
Occasional Contributor
Posts: 17

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

[ Edited ]
Posted in reply to SuryaKiran

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.

Occasional Contributor
Posts: 17

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

[ Edited ]

 

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;

 

Super User
Posts: 3,914

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

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

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

 

Occasional Contributor
Posts: 17

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

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=

Super User
Posts: 5,876

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

C'mon, how can we respond if you don't show us exactly what you tried and the log?
Data never sleeps
Occasional Contributor
Posts: 17

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

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

Super User
Posts: 3,914

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

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

Occasional Contributor
Posts: 17

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

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.
Super User
Posts: 3,914

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

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.

Ask a Question
Discussion stats
  • 16 replies
  • 371 views
  • 2 likes
  • 5 in conversation