5 Replies Latest reply on Aug 30, 2005 1:26 PM by Hemant Kamat

    ORA-00972: identifier too long with CMR relationship

    Casey Helbling Newbie

      I have this cmr setup between a company and a division(which is another company).

      /**
      * @ejb:interface-method
      *
      * @ejb.relation name = "CompanyDivisions" role-name="company-has-divisions"
      * target-ejb="Company" target-role-name="divisions-of-the-company" type="java.util.Set"
      *
      * @jboss.target-relation fk-column="PARENT_COMPANY" related-pk-field="id"
      */
      public abstract Set getCompanyDivisions();

      when the remove on company is called it pukes with an ORA-00972: identifier too long exception. the generated sql alias for the relationship is too long.

      generated sql ---
      -----
      SELECT
      COMPANY.PARENT_COMPANY,
      COMPANY_Company_companyDivisions.COMPANY_NAME

      FROM COMPANY COMPANY, COMPANY COMPANY_Company_companyDivisions
      WHERE ........ blah blah...
      -----

      As you can see the second alias is over the oracle 30 some char limit. Is there a way around this or something that I am missing?

      Thanks.
      Casey

        • 1. Re: ORA-00972: identifier too long with CMR relationship
          Casey Helbling Newbie

          Also this wasnt happening in 3.2.3 but started happening when I upgraded to 3.2.5.

          • 2. Re: ORA-00972: identifier too long with CMR relationship
            Casey Helbling Newbie

            Also, we are using oracle 10g. Is there supposed to be a type mapping for this in standardjbosscmp-jdbc.xml?

            • 3. Re: ORA-00972: identifier too long with CMR relationship
              Alexey Loubyansky Master

              Can you post the stacktrace for the query?
              We don't have default mapping for 10g yet. You are welcome to contribute. Thanks.

              • 4. Re: ORA-00972: identifier too long with CMR relationship
                Hemant Kamat Newbie

                Hi,
                I seem to be having similar issues on JBoss 3.2.4 and Oracle 9i. Try as I could, I couldn't figure out a way to restric the CMR alias names. Here;s the query being generated

                SQL: SELECT ORGANIZATIONAL_UNIT.ORG_UNT_ID, ORGANIZATIONAL_UNIT.ORG_UNT_PAR_ID, ORGANIZATIONAL_UNIT_organizationalUnitParent.ORG_UNT_NM, ORGANIZATIONAL_UNIT_organizationalUnitParent.ORG_UNT_VOID_IND, ORGANIZATIONAL_UNIT_organizationalUnitParent.ORG_UNT_EFF_DT, ORGANIZATIONAL_UNIT_organizationalUnitParent.ORG_UNT_END_DT, ORGANIZATIONAL_UNIT ORGANIZATIONAL_UNIT, ORGANIZATIONAL_UNIT ORGANIZATIONAL_UNIT_organizationalUnitParent WHERE
                .....

                I get a ORA-00972: identifier too long exception. How is the ORGANIZATIONAL_UNIT_organizationalUnitParent alias generated in JBoss ? How can I restrict it to 30 characters for Oracle ?

                Thanks,
                Hemant.

                • 5. Re: ORA-00972: identifier too long with CMR relationship
                  Hemant Kamat Newbie

                  Hi,
                  I found the source of the problem. Looks like this may be a bug in the JBoss Code itself..
                  Environment: JBoss 3.2.4, Oracle 9i
                  Scenario : Bean called OrganizationalUnitBean (table : ORGANIZATIONAL_UNIT) . This had two CMR's to itself - parent and child relationships e.g getOrganizationalUnitParent() and getOrganizationalUnitChild().
                  The generated alias name for the CMR's was something like ORGANIZATONAL_UNIT_organziationalUnitParent and this was more than the customary 30 character limitation.
                  The source of the problem seems to be :-
                  org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand
                  if(join)
                  {
                  relationTableAlias = relationTable;
                  relatedTableAlias = relatedTable.equals(relationTable) ?
                  relationTable + '_' + cmrField.getFieldName() : relatedTable;
                  }
                  There is no truncating of the alias name here.. and that's causing the problems. Not sure if this needs to be rectified or maybe the design of the bean itself. For the time being, I have changed the method names to be getParent() for e.g..