1197846 - Error: 'Database Connector Error: 'HY000:[Oracle][ODBC][Ora]ORA-01456: May not perform insert/delete/update operation inside a READ ONLY transaction' in Crystal Reports

SAP Knowledge Base Article - Public

1197846 - Error: 'Database Connector Error: 'HY000:[Oracle][ODBC][Ora]ORA-01456: May not perform insert/delete/update operation inside a READ ONLY transaction' in Crystal Reports

Symptom

  • When refreshing a report, it generates an error.
  • In Crystal Reports, when attempting to refresh a report based off an Oracle Stored Procedure that performs either an Insert or Delete  while using an ODBC connection, it displays the error:

    "Database Connector Error: 'HY000:[Oracle][ODBC][Ora]ORA-01456: May not perform insert/delete/update operation inside a READ ONLY transaction"

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Reproducing the Issue

  1. In Crystal Reports, create a report off an Oracle Stored Procedure that perform Insert or Delete action on the database using an ODBC connection.
        
  2. When refreshing the report, it generates the error:

    "Database Connector Error: 'HY000:[Oracle][ODBC][Ora]ORA-01456: May not perform insert/delete/update operation inside a READ ONLY transaction"

Cause

  • Crystal Reports is a reporting tool and only needs to read data from the data source, therefore when connecting to a data source using an ODBC connection, it opens a Read Only connection, and an error or warning will occurs if the stored procedure used to report from perform an action against the database like: Insert, Update or Delete.

Resolution

  • It is possible to open a read/write connection when using an ODBC connection in Crystal Reports, by setting the registry key:  AccessMode to the value: 1, but note that it is not supported, nor is it recommended because Crystal Reports is a reporting tool, and not a database management utility.
     
    WARNING  The following involves editing the registry. Using the Registry Editor incorrectly can cause serious problems that may require you to reinstall the Microsoft Windows operating system. Use the Registry Editor at your own risk. It is strongly recommended that you make a backup copy of the registry files before you edit the registry. For information on how to edit the registry key, view the 'Changing Keys And Values' online Help topic in the Registry Editor (Regedit.exe). 
       
  • To open a read/write ODBC connection in Crystal Reports:
       
    1. Close Crystal Reports.
        
    2. Open the Microsoft Registry Editor (Regedit.exe)
        
    3. Navigate to the path corresponding to the version of Crystal Reports used:
           
      •  For Crystal Reports 2008 :  
         
        • HKEY_LOCAL_MACHINE\Software\Business Objects\Suite 12.0\Crystal Reports\Database\ODBC
            
      • For Crystal Reports 2011, 2013, 2016:  
         
        • HKEY_LOCAL_MACHINE\Software\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\ODBC
                 
            
      • Notes:

        For 64bit version of Micosoft Windows, the beginning of the path will be slighlty different.
        For example, for Crystal Reports 2011, the path will be:   
         
        HKEY_LOCAL_MACHINE\Software\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\ODBC 
          
        Also note that the last few keys may not exist on your system. If it is the case, create the keys manually.

    4. In the ODBC folder, create a DWORD entry, and called it: AccessMode
       
    5. Set AccessMode to the value: 0
        
      Possible values:
        
      0 : Open a Read/Write connection.
        1 : Open a Read connection only.

      Once the above is completed, Crystal Reports will now open a Read/Write ODBC connection.

Keywords

CR, READ ONLY WRITE ODBC CRYSTAL REPORTS INSERT DELETE UPDATE TRANSACTION ACCESSMODE , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016