Set data source transaction access mode in Framework Manager issue

[Indlovu_emhlophe]

Hi,

I have a report for which there is a requirement to update a row on a table when a specific parameter has a value.

To do the work on the database I created a stored procedure. The stored procedure was imported into Framework Manager from the Oracle database using the 'Metadata wizard'. On the 'Definition' page of the query, the type was selected as 'Data Query'.

The stored procedure does return data as well.

During validation in FM, the query fails with 'ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction'.

An internet search for the error lead me to the following IBM support page:
https://www.ibm.com/support/pages/running-report-stored-procedure-results-uda-sql-0107-error

The page explains that the properties of the query 'Transaction Access Mode' and 'Transaction Statement Mode' must be modified.

The problem is that there are no such properties for that query. I also checked other queries based on stored procedures and the same applies.

Please assist.

[MFGF]

Hi,

I have a report for which there is a requirement to update a row on a table when a specific parameter has a value.

To do the work on the database I created a stored procedure. The stored procedure was imported into Framework Manager from the Oracle database using the 'Metadata wizard'. On the 'Definition' page of the query, the type was selected as 'Data Query'.

The stored procedure does return data as well.

During validation in FM, the query fails with 'ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction'.

An internet search for the error lead me to the following IBM support page:
https://www.ibm.com/support/pages/running-report-stored-procedure-results-uda-sql-0107-error

The page explains that the properties of the query 'Transaction Access Mode' and 'Transaction Statement Mode' must be modified.

The problem is that there are no such properties for that query. I also checked other queries based on stored procedures and the same applies.

Please assist.

Hi,

How is your Oracle data source connection set up in CA? Is it defined as a Data Server (which means a JDBC-only connection) or is it defined as a data source using the legacy Admin Console? If the latter, is the data source connection set up to use the Oracle Native Client to connect, or JDBC? What I'm trying to get to is whether you are using Compatible Query or Dynamic Query in your model (and package). The link you posted mentions UDA errors also, which would be Compatible Query, but you didn't mention in your post if you are getting these?

If you're using Compatible Query (and therefore the Oracle Native Client), have you checked the Transaction Isolation Level in your data source properties (in CA, not in FM)? My guess is it's currently set to "Read Committed" and needs to be set higher. The docs for this are here:

https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ug_cra.doc/c_isolationlevels.html

Cheers!

MF.