Oracle Error When Using Database Link in Query ORA-16000

[pmason44]

This is my first post to this forum so please ignore that I'm a complete novice lol.

When attempting to use a database link in a query inside Report Studio, i receive the following error message:

RQP-DEF-D177
An error occured while performing operation 'sqlPrepareWithOptions' status='-9'.

Details:
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
ORA-16000: database or pluggable database open for read-only access

RSV-SRV-0042 Trace back:
RSReportService.cpp(766): UDAException: CCL_CAUGHT: RSReportService::processImpl()
RSReportServiceMethod.cpp(253): UDAException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_Request
RSASyncExecutionThread.cpp(873): UDAException: RSASyncExecutionThread::checkException
RSASyncExecutionThread.cpp(317): UDAException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_Request
RSASyncExecutionThread.cpp(918): UDAException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_Request
RSQueryMgrBasic.cpp(296): UDAException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommand
QFSSession.cpp(1190): UDAException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSSession.cpp(1188): UDAException: CCL_CAUGHT: QFSSession::ProcessDoRequest()
QFSSession.cpp(1145): UDAException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSConnection.cpp(2087): UDAException: CCL_RETHROW: QFSConnection::DescribeDataSourceQuery
QFSQuery.cpp(667): UDAException: CCL_RETHROW: QFSQuery::DescribeDataSourceQuery
QFSQuery.cpp(667): UDAException: CCL_RETHROW: QFSQuery::DescribeDataSourceQuery
Source/AQE_UDAFacade.cpp(1860): UDAException: CCL_RETHROW:
apidyn.cpp(250): UDAException: CCL_RETHROW: sqlPrepareWithOptions UDAException


This happens with any database query with a database link in it. Both my host database and the one I'm connecting to are Oracle (11g i think but 12c if newer). I know these queries and links work as I can execute the same query in SQL Developer and SQL plus.

Example query:

select case_nbr, created_dttm
from owner.case_hdr@database_link_1;

FYI, when I click on the question mark icon in the top right corner, it says we are running IBM Cognos Analytics 11.0.12. Please let me know if any other information is needed. Thanks.

[MFGF]

This is my first post to this forum so please ignore that I'm a complete novice lol.

When attempting to use a database link in a query inside Report Studio, i receive the following error message:

RQP-DEF-D177
An error occured while performing operation 'sqlPrepareWithOptions' status='-9'.

Details:
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
ORA-16000: database or pluggable database open for read-only access

RSV-SRV-0042 Trace back:
RSReportService.cpp(766): UDAException: CCL_CAUGHT: RSReportService::processImpl()
RSReportServiceMethod.cpp(253): UDAException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_Request
RSASyncExecutionThread.cpp(873): UDAException: RSASyncExecutionThread::checkException
RSASyncExecutionThread.cpp(317): UDAException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_Request
RSASyncExecutionThread.cpp(918): UDAException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_Request
RSQueryMgrBasic.cpp(296): UDAException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommand
QFSSession.cpp(1190): UDAException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSSession.cpp(1188): UDAException: CCL_CAUGHT: QFSSession::ProcessDoRequest()
QFSSession.cpp(1145): UDAException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSConnection.cpp(2087): UDAException: CCL_RETHROW: QFSConnection::DescribeDataSourceQuery
QFSQuery.cpp(667): UDAException: CCL_RETHROW: QFSQuery::DescribeDataSourceQuery
QFSQuery.cpp(667): UDAException: CCL_RETHROW: QFSQuery::DescribeDataSourceQuery
Source/AQE_UDAFacade.cpp(1860): UDAException: CCL_RETHROW:
apidyn.cpp(250): UDAException: CCL_RETHROW: sqlPrepareWithOptions UDAException


This happens with any database query with a database link in it. Both my host database and the one I'm connecting to are Oracle (11g i think but 12c if newer). I know these queries and links work as I can execute the same query in SQL Developer and SQL plus.

Example query:

select case_nbr, created_dttm
from owner.case_hdr@database_link_1;

FYI, when I click on the question mark icon in the top right corner, it says we are running IBM Cognos Analytics 11.0.12. Please let me know if any other information is needed. Thanks.

Hi,

The important piece in the error looks to be the message coming back from Oracle:

ORA-16000: database or pluggable database open for read-only access

From googling that error, it looks like your database has been started in read-only mode, and the way you are trying to access it in Cognos might be causing the issue - for example, the transaction isolation level in the connection might be trying to lock records to preserve a stable view.

It's worth asking your database people if the database can be restarted not in read-only mode, and/or what transaction isolation level the connection should be using - if this needs changing, you'll need to ask your Cognos admin to alter it in the data source connection properties.

Cheers!

MF.

[pmason44]

Ok thanks for the quick response. I spoke with our database administrators and they basically told me this is the only way we can create this connection because of our security. So they will set me up with a user account to connect directly instead of using a database link. Thanks.