ORA-16000: database open for read-only access

[ettienne]

I need to sync data from a legacy ERP database to an Oracle database.
I'm running a test VBScript with the following:

Set oCn = CreateObject("ADODB.Connection")
oCn.Open "Data Source=strdb; UID=user; PWD=password; DBA=W;"
sSQL = "update CUSTFILES set CMEMO='ABC' where CLIC='107647'"
oCn.Execute sSQL

The error returned is:
Microsoft OLE DB Provider for ODBC Drivers [Oracle][ODBC][Ora]ORA-16000: database open for read-only access

The DBA says the user ID has read/write access to the Oracle database.
I can run the same query successfully in Oracle SQL Developer using the same credentials.

Any pointers on why this connection is read only?

Sage 300 Certified Consultant

 

[fredericofonseca]

hum. looks like your ODBC datasource is configured to be readonly

would be better to change to Oracle provider if you can.
and have a look at

http://www.connectionstrings.com/oracle/

for connection strings you can use.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ettienne]

Um, how can you tell the ODBC datasource is read only?
I've looked at connection strings, that's where I got what I'm using. I can query the data and get recordsets, just not update them.

Sage 300 Certified Consultant

 

[ettienne]

Sage 300 Certified Consultant

 

[ettienne]

I tried alternatives with the same results:
oCn.Open "Driver={Oracle in OraClient11g_home1};Dbq=strdb;Uid=user;Pwd=password;"

oCn.Open "Driver={Oracle in OraClient11g_home1};Server=strdb;Uid=user;Pwd=password;"

Sage 300 Certified Consultant

 

[fredericofonseca]

the oracle database you are connecting to was started up as readonly and that is why you get that issue then.

note that if the final destination table is on a remote server and CUSTFILES is a synonym to that other db then if either of the databases is readonly you will get that issue.

if the database is a "standby" database you will get the same result.


In any case and just to clarify it, run the update statement through sqlplus as that will bypass any other possible issues.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ettienne]

The database in question is a live (not standby) database in production, it is not in read only mode.
We can write changes to the db from applications and from Oracle SQL Developer.
Strange...

Sage 300 Certified Consultant

 

[ettienne]

The problem was the environment the script was running under: for some reason this returned read only access.
Running the same script from a command line solved the problem.

Sage 300 Certified Consultant