Hitachi Vantara Pentaho Community Forums
Results 1 to 12 of 12

Thread: ORA-00972: identifier is too long

  1. #1

    Default ORA-00972: identifier is too long

    When attempting to perform a database lookup, I get the following error:

    "ORA-00972: identifier is too long"
    (For any who don't know, Oracle limits identifiers to a maximum of 30 characters, and this error occurs if you use more than 30.)

    The column I am referencing has an identifier of 30 characters, so I am wondering what overhead is involved with pentaho performing a lookup, is it adding a prefix or suffix?

    A lot of applications run off this database, so it would be unlikely for me to be able to make the column name shorter, so a pentaho solution would be preferable.

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    psychic mode on ... you have "strange" characters in your lookup key, e.g. diacritics or so? ... anything not in ASCII range 0-127 ... psychic mode off

    Regards,
    Sven

  3. #3

    Default

    The key is:

    "ESOLVE_PRESENTATION_PRESNTR_ID"

    which is exactly 30 characters (As you can see, the english was dialed-down a bit to make it 30 characters when the database was created.)

    I saw a similar problem elsewhere on the forums, relating to Mondrian:
    http://forums.pentaho.org/showthread.php?t=59786

    and though there might be an issue with pentaho altering the column identifier when executing the SQL.


    As this should work, and I saw a similar problem related to mondrian

    EDIT: The problem has been fixed by using a different view, but this is a bandaid solution, and is far from ideal.

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    And do you know why your different view fixes things?

    Regards,
    Sven

  5. #5

    Default

    Sorry, should have been clearer on this matter.

    The reason the new view works as a "Bandaid" fix is because the view changes the column name to be far shorter, well under the 30 character limit imposed by Oracle. This adds to my belief that Pentaho is adding characters to the column identifier (similar to the link in my post below).

    What I'm really looking for is a way to remove any extra characters, so the SQL executes with the column identifier exactly the way it is in Oracle (That is, exactly 30 characters long).

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    PDI is not adding characters to your names.

    Regards,
    Sven

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Oracle identifiers have a 30 bytes limit, not 30 characters. If you set the default codepage for the database to something multi-byte, the limit is probably between 15 and 30 somewhere. The same troublesome behavior goes for varchar2 data types etc.

    Joshua, it took me 5 seconds to look this up on Google. I guess it was *still* easier to assume that Kettle was doing something wrong he :-)


    Matt

  8. #8

    Default

    Matt,
    Couple of reasons I thought Pentaho was adding characters, firstly it was added as a possible explanation when I discovered the thread: http://forums.pentaho.org/showthread.php?t=59786
    Secondly, identifying the column works perfectly (as far as I'm aware) through SQL (SQL currently performs the same task I'm using Pentaho to replace.)

    If it was the byte limit, as you say, wouldn't the SQL cause the same error, rather than working normally?

    I did check things like this with my DBA before I posted on here, but I will spend some time with him (and a Pentaho consultant - brought in for an unrelated matter) tomorrow and see if we can fix it.

    Thanks for the help, I will add to this thread if I cannot solve the problem tomorrow.

  9. #9

    Default

    I don't know much about oracle, but can you not log the queries it executes, then you could see what kettle was sending to it rather that kettles prepared statement.
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  10. #10
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Joshua, I was only kidding because let's be clear about this, the real WTF is not you nor your DBA. It's not Kettle either. The real WTF is an RDBMS that has such ridiculous limits :-)

  11. #11

    Default

    Bugg,
    Yes, I certainly can. Although I'm not the DBA, I'm sure he'll be happy to do this. Thanks for the suggestion.

    Matt,
    Truer words have never been spoken.

    Now that I know Pentaho doesn't add characters to a lookup key, I will try and nab the Oracle DBA guy for some time and see what's screwing up on our end.

    Between him, the Pentaho guy and myself, we should be able to fix this, I will post the solution here tomorrow if I find one, incase others have this problem.

  12. #12

    Default

    Well, I've fixed the error.

    I changed the table to point at a view (with a shorter name, as a bandaid fix) and when I changed it back, it worked. I am unable to reproduce the error, so I am assuming it is gone.

    What worries me is that I double and tripple checked all of the names I was using in the lookup, and I am sure they were correct.

    Thanks for all the help, although it's disappointing to have the error disappear when I don't have a clue what caused it.

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.