BookmarkSubscribeRSS Feed
b1958
Fluorite | Level 6

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

16 REPLIES 16
b1958
Fluorite | Level 6

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

b1958
Fluorite | Level 6

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

SuryaKiran
Meteorite | Level 14

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
b1958
Fluorite | Level 6

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
Fluorite | Level 6

 

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;

 

b1958
Fluorite | Level 6

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
Tourmaline | Level 20
C'mon, how can we respond if you don't show us exactly what you tried and the log?
Data never sleeps
b1958
Fluorite | Level 6
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
PROC Star

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

b1958
Fluorite | Level 6

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
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 2435 views
  • 2 likes
  • 5 in conversation