Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Oracle "ORA-00972: identifier is too long" on drillthrough

  1. #1
    Join Date
    Feb 2007
    Posts
    2

    Lightbulb Oracle "ORA-00972: identifier is too long" on drillthrough

    We are using Oracle 9i with Mondrian 2.4.2.9831 (released Aug. 31, 2007), and we encountered the ORA-00972 error when attempting to drillthrough from a cube cell to the underlying detail rows.

    Setting mondrian.trace.level to a value greater than 0 proved useless, because according to other threads, the SQL statement is generated by Jpivot, not Mondrian. Since the error occurs when the SQL statement is being parsed by Oracle, the standard Oracle trace facilities appeared to be useless, because they all seem to come into play AFTER the statement is parsed.

    What to do, what to do ...

    One of our developers got the brilliant idea of compiling our own debug version of the jpivot.jar file. After generating a drillthrough SQL statement, and prior to executing it, our debug version sends the SQL statement to the application server's log file (Tomcat in this case, so catalina.out). We then copied the statement to a SQL query tool.

    As it turns out, Jpivot uses the hierarchy level names to generate column aliases for use as output column headings in the drillthrough window. For the key columns, it suffixes the name with " (key)", an additional 5 characters. One of the resultant column headings was 32 characters long, and Oracle was limiting column aliases to 30 characters.

    Something like :

    <Level name="Extra Long Hierarchy Level Name" column="KEY_COLUMN_NAME" ... >

    was translated to:

    SELECT ...
    "HIERARCHY_TABLE_NAME"."KEY_COLUMN_NAME" AS "Extra Long Hierarchy Level Name (key)"
    ...


    A quick shortening of the level name took care of the problem.

    New rule of thumb: when dealing with Oracle, make hierarchy level names 25 characters or less.

    Hope the length of the post isn't too bad. I get frustrated when I have a problem and people post things like "we found a solution" and then fail to post the details of the solution.

  2. #2
    Join Date
    Nov 1999
    Posts
    1,618

    Default

    Mondrian should automatically truncate generated column names that are too long for the DBMS. I have logged a bug 1893959, "Generated drill-through columns too long for DBMS", http://sourceforge.net/tracker/index...02&atid=414613

    > Hope the length of the post isn't too bad. I get frustrated when I have a problem and people
    > post things like "we found a solution" and then fail to post the details of the solution.

    An excellent post! Because your article includes the error message and a description of the underlying cause and solution, this will make it easy for people who have the same problem to find this post using google.

  3. #3
    Join Date
    Nov 1999
    Posts
    1,618

    Default

    Bug 1893959 is fixed in change 10642, will be in mondrian-3.0.1.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.