IBM Support

"ORA-29280: invalid directory path ... ORA-06512: at "SYS.UTL_FILE", line 41" when using Optimise2 with an Oracle database

Troubleshooting


Problem

User opens Excel spreadsheet (.XLS file) report. User logs into Controller. User clicks F9 (refresh) to run the report. User recieves error message.

Symptom

The error message varies depending on version of Controller.

Controller 10.2.1:

Error occured at 15/11/2016 13:56:14 in Microsoft Office 2010, Error No=5, Source=FrangoDirect.ExcelLinkD.FetchValuesBulk#System.Web.Services, Description=System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Exception: ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 562
ORA-06512: at line 101
at Cognos.Controller.Proxy.CCRWS.ExcelLinkB_FetchValuesBulk(String sGuid, String sKeys, String sMcurr, Boolean bLocLang, String sUser, String sRep, Boolean bIsLnk, String sSortOrd, String sParams)


Earlier versions of Controller:
Information
Standard Error
Number: 5
Source: FrangoDirect.ExcelLinkD.FetchValuesBulk#ControllerProxyClient
Description: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.IO.FileNotFoundException: File not found
at Cognos.Controller.Proxy.CCRWS.ExcelLinkB_FetchValuesBulk(String sGuid, String sKeys, String sMcurr, Boolean bLocLang, String sUser, String sRep, Boolean bIsLnk, String sSortOrd, Int32 lOptBitSet)...

Cause

The value for "Server Directory" is incorrectly configured, inside the section "Enhanced Reporting Optimization" in Controller Configuration.

More Information:
The value of 'Server Directory' must be set to the drive letter and folder of the location of the share (which also must be located on the Oracle server).

In one real-life example, this was incorrectly left blank:

Diagnosing The Problem

One thing you may find is that there is a strangely-named (for example 'ADMGXLI_CALCPER_3TEMPTAB.tbl') file located inside your Optimise2 share.

Resolving The Problem

Fix

Configure the value of 'Server Directory' to be set to the correct drive letter and folder of the location of the share, on the Oracle server.


    Steps:

    1. Logon to the Oracle server as an administrator

    2. Check the name of the folder name which you are using for your share (for example \\oraclesvr\optimise2)

    • For example, on Windows the folder name may be: C:\OPTIMISE2

    3. Check that the value of the Oracle parameter "UTL_FILE_DIR" (for the Controller database).

    • TIP: You can do this by running the following Oracle script:
      • SELECT value FROM v$parameter WHERE name = 'utl_file_dir';
    • Ensure that this value is set to the same folder name (for example C:\OPTIMISE2) that you found in step 2.

    4. Logon to the Controller application server as an administrator

    5. Launch 'Controller Configuration' from the Start Menu

    6. Open the section "Enhanced Reporting Optimization"

    7. Modify "Server Directory" to the correct value

    • for example C:\OPTIMISE2

    8. Save changes

    9. On the client device, ensure that Controller/Excel is closed (so that the changes in settings are updated in the system)

    10. Finally, re-launch Excel and logon to Controller again. Re-try.


==============================================================

Workaround:

Disable the use of ERO ("Optimise2"), by removing the value 'Optimise2' from the 'Name' of cell A1 of the spreadsheet.

  • TIP: For more information, see separate IBM Technote #1347048.


Steps:
The following instructions are based on Excel 2007.
  • The instructions may need to be modified slightly if you are using a different version of Excel.

1. Launch Microsoft Excel
2. Open the 'bad' spreadsheet
3. Click the tab 'Formulas'
4. Click 'Name Manager'
5. Highlight the entry 'Optimise2' (which refers to Sheet1, cell A1)
6. Click 'Delete':

7. Save changes and test.

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.1;10.2.0;10.1.1;10.1","Edition":"Not Applicable","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
23 June 2018

UID

swg21405638