[DDC-1384] ORA-00972: identifier is too long Created: 19/Sep/11  Updated: 30/Oct/11  Resolved: 30/Oct/11

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.0.3
Fix Version/s: 2.2
Security Level: All

Type: Bug Priority: Major
Reporter: Matheus Luis Ramos de Souza Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None

Sub-Tasks:
Key
Summary
Type
Status
Assignee
DDC-1427 Fix for columns Sub-task Resolved Alexander  
DDC-1428 Fix for tables Sub-task Resolved Alexander  

 Description   

Hi,

When the query is executed the follow problem occur:
ORA-00972:identifier is too long
The problem occur because a column name has 30 characters and the
Doctrine creates an alias with <column_name>+<position_in_select>
becoming 31 characters, in Oracle the max allowed characters in the name
is 30.
Is there any configuration that disable this mechanism?
How can I solve this issue?
Thanks!
Regards,

Matheus Souza.



 Comments   
Comment by Benjamin Eberlei [ 25/Sep/11 ]

This is tricky. No way for you to reduce the column length to < 30 chars? The problem with a fix for this would be that we would need to execute a bunch of functions for every database vendor and every column alias used in every SQL statement: quite some overhead

Comment by Matheus Luis Ramos de Souza [ 26/Sep/11 ]

I get it.
I don't know the implementation, but is there a possibilty to create a property that disable the "suffix field position" in alias, staying just <column_name>?

Tks!

Comment by Benjamin Eberlei [ 15/Oct/11 ]

The following workaround will get you around this problem until i find a better solution.

1. Create a MyOraclePlatform extends \Doctrine\DBAL\Platforms\OraclePlatform
2. Override the "getSQLResultCasing($string)" to do:

$val = parent::getSQLResultCasing($string);
return substr($val, -30);

This substrings every result alias to 30 chars.

Comment by Matheus Luis Ramos de Souza [ 17/Oct/11 ]

But how does the Doctrine will know about my implementation?
I will have to change the Driver too?

Thanks!

Comment by Benjamin Eberlei [ 17/Oct/11 ]

no, you can pass the "platform" into the DriverManager::create method as "platform" parameter for the $params AFAIK, relevant code is Doctrine\DBAL\Connection::__construct if you want to check it out.

Comment by Matheus Luis Ramos de Souza [ 17/Oct/11 ]

In some cases it works, but it doesn't work when the SqlWalker is invoked, I think that the problem is in walkSelectClause($selectClause) method in the else clause for this verification:

if ($class->isInheritanceTypeSingleTable() || $class->isInheritanceTypeJoined()) {
if ($class->isInheritanceTypeSingleTable() || $class->isInheritanceTypeJoined()) {
...
} else {
                // Add foreign key columns to SQL, if necessary
                if ($addMetaColumns) {
                    $sqlTableAlias = $this->getSqlTableAlias($class->table['name'], $dqlAlias);
                    foreach ($class->associationMappings as $assoc) {
                        if ($assoc['isOwningSide'] && $assoc['type'] & ClassMetadata::TO_ONE) {
                            foreach ($assoc['targetToSourceKeyColumns'] as $srcColumn) {
                                $columnAlias = $this->getSqlColumnAlias($srcColumn);
                                $sql .= ', ' . $sqlTableAlias . '.' . $srcColumn . ' AS ' . $columnAlias;
                                $columnAlias = $this->_platform->getSQLResultCasing($columnAlias);
                                $this->_rsm->addMetaResult($dqlAlias, $this->_platform->getSQLResultCasing($columnAlias), $srcColumn);
                            }
                        }
                    }
                }
            }
        }

the problem is in the follow lines, because the getSQLResultCasing has no effect in $sql variable that is returned:

$columnAlias = $this->getSqlColumnAlias($srcColumn);
$sql .= ', ' . $sqlTableAlias . '.' . $srcColumn . ' AS ' . $columnAlias;
$columnAlias = $this->_platform->getSQLResultCasing($columnAlias);

The correct would be this way, wouldn't be?

$columnAlias = $this->getSqlColumnAlias($srcColumn);
$columnAlias = $this->_platform->getSQLResultCasing($columnAlias);
$sql .= ', ' . $sqlTableAlias . '.' . $srcColumn . ' AS ' . $columnAlias;
  • In the version of Doctrine that I'm using, there is no DriverManager::create method, I'm using
    DriverManager::getConnection(array $params,Configuration $config = null,EventManager $eventManager = null) instead.

Tks!!

Comment by Benjamin Eberlei [ 17/Oct/11 ]

Narf, sorry that this doesn't work.

This is rather unreliable if this fails here, can we trust it in other places? I think i have to work on this by hard, its just very problematic to test this, it may be easy to miss a location.

Comment by Benjamin Eberlei [ 17/Oct/11 ]

we found a simple way to fix this, expect a patch for this soon!

Comment by Alexander [ 26/Oct/11 ]

The issue should be fixed with the code over here:
https://github.com/doctrine/doctrine2/pull/167

I haven't been able to test it on a real Oracle DB yet. Maybe you guys can give it a go?

Comment by Benjamin Eberlei [ 30/Oct/11 ]

Fixed, however will not be merged into 2.1.x because the patch is really large

Generated at Sat Oct 25 17:08:25 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.