ORA-00972: identifier too long with CMR relationship

[caseyhelbling]

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

[caseyhelbling]

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

[caseyhelbling]

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

[aloubyansky]

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

[akasmat]

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.

[akasmat]

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..