CODEWITHSUNDEEP
oracle-databasesymfonydoctrine-orm
Christian Reyes Argotty
Christian Reyes Argotty

Reputation: 28

SQL error when trying to import Oracle metadata into Symfony2 using Doctrine

I'm trying to connect Oracle with Symfony 2.8, but when I run

php app/console doctrine:mapping:convert xml ./src/Occidente/RspBundle/Resources/config/doctrine/metadata/orm --from-database --force

to generate metadata to create entities from my existing database, I get the following error message:

Warning: oci_fetch_all(): ORA-01427: single-row subquery returns more than one row

Upvotes: 1

Views: 316

Answers (1)

Francois
Francois

Reputation: 120

I was also having this issue with an import from an Oracle database. I debugged Doctrine/DBAL and found that when it's importing metadata for the tables, it looks for table comments in the all_col_comments Oracle metadata table which will produce multiple lines for a table if you have 2 tables in 2 different schemas (or 2 tables belonging to 2 different owners) with exactly the same table name and column names.

Error message with debug enabled

I've submitted a pull request on the Doctrine DBAL repository on GitHub to correct this (probably rare) issue. As a workaround, you can delete the table that's causing the conflict from one of the schemas OR edit the OraclePlatform.php file in the /lib/Doctrine/DBAL/Platforms/ directory under your project's vendor directory and change the getListTableColumnsSQL function as follows:

/**
 * {@inheritDoc}
 */
public function getListTableColumnsSQL($table, $database = null)
{
    $table = $this->normalizeIdentifier($table);
    $table = $this->quoteStringLiteral($table->getName());

    $tabColumnsTableName = "user_tab_columns";
    $colCommentsTableName = "user_col_comments";
    $ownerCondition = '';
    $innerOwnerCondition = '';

    if (null !== $database && '/' !== $database) {
        $database = $this->normalizeIdentifier($database);
        $database = $this->quoteStringLiteral($database->getName());
        $tabColumnsTableName = "all_tab_columns";
        $colCommentsTableName = "all_col_comments";
        $ownerCondition = "AND c.owner = " . $database;
        $innerOwnerCondition = "AND d.OWNER = c.OWNER";
    }

    return "SELECT   c.*,
                     (
                         SELECT d.comments
                         FROM   $colCommentsTableName d
                         WHERE  d.TABLE_NAME = c.TABLE_NAME
                         AND    d.COLUMN_NAME = c.COLUMN_NAME " . $innerOwnerCondition . "
                     ) AS comments
            FROM     $tabColumnsTableName c
            WHERE    c.table_name = " . $table . " $ownerCondition
            ORDER BY c.column_name";
}

Upvotes: 1

Related Questions