[DBAL-596] OCI8 - ORA-06502 executing a procedure with out parameters (potentially very large out parameters) Created: 05/Sep/13  Updated: 21/Nov/13  Resolved: 21/Nov/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.3.4
Fix Version/s: 2.4
Security Level: All

Type: Improvement Priority: Major
Reporter: Francois G. Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: OCI8, ORA-06502
Environment:

Symfony 2.3.4, PHP 5.4.6-1ubuntu1.2, Apache, Linux



 Description   

Currently converting a business application from a custom-built framework to Symfony, with Doctrine and OCI8 driver.

I noticed the following error when executing a procedure with out parameters.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

We are returning potentially very large values.

This can be fixed very easily, by doing a small change on bindParam() :
File: doctrine/dbal/lib/Doctrine/DBAL/Driver/OCI8/OCI8Statement.php

Initial function
    public function bindParam($column, &$variable, $type = null,$length = null)
    {
        $column = isset($this->_paramMap[$column]) ? $this->_paramMap[$column] : $column;

        if ($type == \PDO::PARAM_LOB) {
            $lob = oci_new_descriptor($this->_dbh, OCI_D_LOB);
            $lob->writeTemporary($variable, OCI_TEMP_BLOB);
            
            return oci_bind_by_name($this->_sth, $column, $lob, -1, OCI_B_BLOB);
        } else {
            return oci_bind_by_name($this->_sth, $column, $variable);               
        }
    }
Modified function
    public function bindParam($column, &$variable, $type = null,$length = null)
    {
        $column = isset($this->_paramMap[$column]) ? $this->_paramMap[$column] : $column;

        if ($type == \PDO::PARAM_LOB) {
            $lob = oci_new_descriptor($this->_dbh, OCI_D_LOB);
            $lob->writeTemporary($variable, OCI_TEMP_BLOB);
            
            return oci_bind_by_name($this->_sth, $column, $lob, -1, OCI_B_BLOB);
        } else {
            if ($length != null) {
               return oci_bind_by_name($this->_sth, $column, $variable, $length);
            } else {
               return oci_bind_by_name($this->_sth, $column, $variable);               
            }
        }
    }

This modification requires very small changes, there is already a $length = null parameter, it's just a matter of using it.

Note that I'm not returning a LOB, so the first part of the function does not apply to my situation.

Thanks



 Comments   
Comment by Steve Müller [ 21/Nov/13 ]

Fixed in commit:
https://github.com/doctrine/dbal/commit/8d7e9c9951fec89677b426f7935214a806dc9839





[DBAL-563] Oracle "IDENTITY" last inserted ID is returning 0 instead of the real ID Created: 19/Jul/13  Updated: 31/Dec/13  Resolved: 31/Dec/13

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Mohammad A. ZeinEddin Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: cascade, lastInsertedId, oci8, oracle
Environment:

Oracle, OCI8


Attachments: File LastInsertId.php     File OCI8Connection.php     File OCI8Statement.php    

 Description   

I am using doctrine 2 with oracle, the tables in the database has some triggers that generate the IDs, and I am trying to us Doctrine 2 cascade persist when mapping on one-to-many, and I use "IDENTITY" in the mapping, but there is a problem which is the one-side of the relation is returning 0 as last inserted ID, which is wrong, my ID mapping of my tables is like the following:

/**

  • @orm\Id
  • @orm\Column(type="integer");
  • @orm\GeneratedValue(strategy="IDENTITY")
    */
    protected $id;

and my entities looks like the following:

/**

  • @ORM\Entity
  • @ORM\Table(name="clients")
    **/
    class Client {
    /**
  • @ORM\Id
  • @ORM\GeneratedValue(strategy="IDENTITY")
  • @ORM\Column(type="integer")
    */
    protected $id;

/** @ORM\Column(name="name",type="string",length=255,unique=true) */
protected $name;

/**

  • @ORM\OneToMany(targetEntity="ContactInformation", mappedBy="client", cascade= {"persist"}

    )
    **/
    protected $contactInformations;

public function __construct()

{ $this->contactInformations = new ArrayCollection(); }

public function getId()

{ return $this->id; }

public function getName() { return $this->name; }

public function setName($name) { $this->name = $name; return $this; }

public function getContactInformations() { return $this->contactInformations; }

public function addContactInformations(Collection $contactInformations)
{
foreach ($contactInformations as $contactInformation) { $contactInformation->setClient($this); $this->contactInformations->add($contactInformation); }
}

/**
* @param Collection $tags
*/
public function removeContactInformations(Collection $contactInformations)
{
foreach ($contactInformations as $contactInformation) { $contactInformation->setClient(null); $this->contactInformations->removeElement($contactInformation); }
}

public function setContactInformations($contactInformations) { $this->contactInformations = $contactInformations; return $this; }
}

and the other entity:

/**
* @ORM\Entity
* @ORM\Table(name="contact_informations")
**/
class ContactInformation {
/**
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
* @ORM\Column(type="integer")
*/
protected $id;

/**
* @ORM\OneToOne(targetEntity="ContactInformationType")
* @ORM\JoinColumn(name="type_id", referencedColumnName="id")
**/
protected $type;

/** @ORM\Column(type="text") */
protected $value;

/**
* @ORM\ManyToOne(targetEntity="Client", inversedBy="contact_informations")
* @ORM\JoinColumn(name="client_id", referencedColumnName="id")
**/
private $client;

public function getId() { return $this->id; }

public function getType()

{ return $this->type; }

public function setType($type)

{ $this->type = $type; return $this; }

public function getValue()

{ return $this->value; }

public function setValue($value)

{ $this->value = $value; return $this; }

public function getClient()

{ return $this->client; }

public function setClient($client = null)

{ $this->client = $client; return $this; }

}

I also want to add: why don't Doctrine 2 just use the oracle "returning id into" statement, in this case regardless the identity mapping this will always return the inserted ID, and it will work with "AUTO", "SEQUENCE", "IDENTITY" and I think any other mapping word used!

I did try to trace where the problem come from, and it seems that when using OCI8 oracle driver that the invoked method is
Doctrine\ORM\Id\IdentityGenerator::generate
and it invokes
Doctrine\DBAL\Connection::lastInsertId
and is returning 0, I don't know why it is being invoked since the sequenceName is null (there is no sequence in the definition!)

Maybe a good solution is to check if the $statement is an 'INSERT INTO ' sql statement, then we bind an output variable to the statement which will hold the "returning ID into :output_variable" value... what do you think?



 Comments   
Comment by Mohammad A. ZeinEddin [ 20/Jul/13 ]

I am not professional in Doctrine, but I want to suggest something to get the last inserted id for Oracle... I think this is even better than using the sequence name to get it... and it works for all types of ID generation methods...!

I think a good solution will be to do something like this in the "Doctrine\DBAL\Driver\OCI8\OCI8Statement::__construct" (may be there is a better place or way, this is just a suggestion), and make it like the following:
1- first we check if the statement is an insert statement then we add a ' returning id into :lastInsertId' sql, here we need somehow to get the primary key column name, data type length (max_length) and make it dynamic, 'id' is just the PK in my case...
2- we bind the ':lastInsertId' parameter so that we can get it as output parameter.

here is a sample code, maybe it needs a lot of enhancement

public function __construct($dbh, $statement, OCI8Connection $conn)
{
list($statement, $paramMap) = self::convertPositionalToNamedPlaceholders($statement);
if (stripos($statement, 'INSERT INTO ') === 0) {
$statement = $statement . ' returning id into :lastInsertId';
}
$this->_sth = oci_parse($dbh, $statement);
$this->_dbh = $dbh;
$this->_paramMap = $paramMap;
$this->_conn = $conn;
if (stripos($statement, 'INSERT INTO ') === 0) {
oci_bind_by_name($this->_sth, ':lastInsertId', $this->lastInsertId, OCI_B_INT);
}
}

and then when executing (execute method) we will have $this->lastInsertId set for us and containing the last inserted id even if it is from a sequence... can you implement such thing? and by this the "http://docs.doctrine-project.org/en/latest/reference/basic-mapping.html#identifier-generation-strategies" Identifier Generation Strategies "IDENTITY" will work for oracle and will be full portable

Comment by Mohammad A. ZeinEddin [ 20/Jul/13 ]

I just attached the suggested changes in OCI8 diver files, I just need help with 2 TODO issues, and I think then all will be fine

Comment by Stanislav Ivanov [ 01/Oct/13 ]

I'm suggesting this is a bug and not an improvement as it leads to different ORM behavior when using different database drivers.

Comment by Steve Müller [ 24/Nov/13 ]

Unfortunately the approach you suggested won't work as we are not able to identify the PK to return from the insert statement on the fly.

Comment by Mohammad A. ZeinEddin [ 24/Nov/13 ]

can't we get the column/s name/s from the mapping in the OCI8Statement.php file? isn't there anyway to do that? can you suggest an approad to do that? because the retuning id into :var is the right way to do that in oracle for all types of mapping!

Comment by Steve Müller [ 24/Nov/13 ]

Sure basically your approach is the way to go but I don't see a way how to determine the column name to return the last insert id for. The driver does not know what the identity column for a particular insert statement is.

Comment by Stanislav Ivanov [ 25/Nov/13 ]

Steve, I disagree. Oracle last insert id should not rely on identity column, instead, it should rely on current sequence value. And this is properly implemented in OCI8Connection (https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Driver/OCI8/OCI8Connection.php, lastInsertId method).

Besides, the proper triggers are implemented in OraclePlatform (https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/OraclePlatform.php).

As I see in OraclePlatform::getCreateAutoIncrementSql() method, the sequence name is like following:

$table . '_SEQ'

So, I think that the problem is that EntityManager (or some lower level) does not provide the proper sequence name to OCI8Connection::lastInsertId() method causing it to trigger this code:

if ($name === null) {
    return false;
}

Please, check if it helps. As for now Doctrine ORM is literally unusable with Oracle.

Comment by Stanislav Ivanov [ 25/Nov/13 ]

Okay, seems, I've found the way it is correctly done (I had predefined sequence name, don't now if it working for entity-based generated schema):

    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     * @ORM\SequenceGenerator(sequenceName="seq_mytable", initialValue=75)
     */
    private $id;

So, we're using SEQUENCE generated value strategy for Oracle automatically and we fall back to manually defined sequence generator.

Just run persist() and flush() and got correctly set newly generated id. So cool.

Comment by Mohammad A. ZeinEddin [ 25/Nov/13 ]

This does not work for us, we are generating IDs automatically based on some triggers, so sequenceName does not work in our case... the only thing that I found working is by the modifications suggested up in the files...

Comment by Stanislav Ivanov [ 25/Nov/13 ]

It surely has nothing to do with Oracle driver as custom id field can be generated using triggers on every database.

So, you need to implement your brand new generated value strategy as it does not comply with IDENTITY and SEQUENCE documentation. It would be a nice extension.

Maybe this could help: http://ranskills.wordpress.com/2011/05/26/how-to-add-a-custom-id-generation-strategy-to-doctrine-2-1

Comment by Mohammad A. ZeinEddin [ 25/Nov/13 ]

As you see here:
http://docs.doctrine-project.org/en/2.0.x/reference/basic-mapping.html#identifier-generation-strategies
the IDENTITY generation strategy is not implemented in Oracle... and I use it since the ID is generated bu the DB... so I think that it is better to change to the oracle way to get the last inserted ID when using this strategy...

Comment by Steve Müller [ 25/Nov/13 ]

IDENTITY generation strategy is SOMEHOW implemented in Oracle with the workaround of creating a (before insert) trigger on the specific table that uses a sequence to emulate an autoincrementation. I guess this is just a compatibility approach for IDENTITY strategy on a best effort basis and should not be relied on. This is also the reason why it is stated in the documentation as not fully portable. The issue discussed here is also not an issue of Doctrine ORM IMO as it is not responsible for evaluating if an IDENTITY strategy needs a sequence for the underlying driver to obtain the last insert ID. However there already seems to be hack for exactly the same case in PostgreSQL. See:

https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Mapping/ClassMetadataFactory.php#L453

What we probaby COULD do is add another check in the ClassMetadataFactory for the Oracle platform to tell it to use a sequence for IDENTITY strategy. But that still is rather hackish to be honest...

Comment by Benjamin Eberlei [ 13/Dec/13 ]

Steve Müller The real issue is indeed, that IDENTITY is not really supported for Oracle. We would need to find a way to support it generically or throw an exception in the ORM if Oracle is used with IDENTITY.

Comment by Steve Müller [ 29/Dec/13 ]

Step one in fixing this issue has been applied in PR https://github.com/doctrine/dbal/pull/428 and fixed in commit https://github.com/doctrine/dbal/commit/d2845256d22a0ea2c5e5392aa67f4b95f252d5c4.
Step two has been supplied in ORM in PR https://github.com/doctrine/doctrine2/pull/890.

As soon as the PR on ORM side gets merged it is possible to use IDENTITY generator strategy with Oracle

Comment by Doctrine Bot [ 31/Dec/13 ]

A related Github Pull-Request [GH-890] was closed:
https://github.com/doctrine/doctrine2/pull/890

Comment by Steve Müller [ 31/Dec/13 ]

Fixed in commits:
https://github.com/doctrine/dbal/commit/d2845256d22a0ea2c5e5392aa67f4b95f252d5c4
https://github.com/doctrine/doctrine2/commit/a7b9140d2fddcab995b2597be4d589155ff1aa8f





[DBAL-444] OraclePlatform getSequenceNextValSQL not handling case/quoting properly on 11g Created: 10/Feb/13  Updated: 03/Jan/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.2
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Max Milaney Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: oci8, oracle, sequence
Environment:

PHP version 5.4.11
Oracle 11g Instant Client version 11.2.0.3.0
Oracle Database 11g Enterprise Edition version 11.2.0.3.0 (x64)
OCI8 DBAL driver


Attachments: File example.php    

 Description   

I have an installer script that uses ORM SchemaTool to create the entities in the DB and then populates with basic data using basic EM->persist calls via ORM.

Sequence objects are created, and when using the 10g Instant Client everything worked correctly, however, upon upgrade to latest version of the Instant Client Oracle seems to be expecting consistent case for these schema objects. It appears as if they are being created with a quoted name as they are created in lowercase. OraclePlatform::getSequenceNextValSQL, however, generates "SELECT entity_id_seq.nextval FROM DUAL" and this fails with error "General error: 2289 OCIStmtExecute: ORA-02289: sequence does not exist".

Executing "SELECT "entity_id_seq".nextval FROM DUAL" directly on the DB returns the correct value.

I believe this may also impact the code in http://www.doctrine-project.org/jira/browse/DBAL-278



 Comments   
Comment by Max Milaney [ 10/Mar/13 ]

Hi there,
Wondering if there is any update on this? I'm having to use a workaround in my applications.
Cheers,
Max

Comment by Benjamin Eberlei [ 14/Mar/13 ]

Can you maybe show an entity definition with its sequence mapping?

Comment by Max Milaney [ 17/Mar/13 ]

Here you are mate. Please see attachment.

Comment by Benjamin Eberlei [ 04/Apr/13 ]

I cant seem to find the problem, in DBAL "lib/Doctrine/DBAL/Platforms/OraclePlatform.php" on line 171, the sequence statement is created with $sequence->getQuotedName($platform), but this only works if quoting is requrested for the sequence.

How do you actually create the sequence? Your entity doesnt have @GeneratedValue.

What does the create schema command say with "--dump-sql" flag? Is the SQL quoted?

Comment by Steve Müller [ 03/Jan/14 ]

Max Milaney Can you please test if this still exists in the current master branch? If so, can you please provide the information requested by Benjamin Eberlei ? Otherwise hunting this down is rather hard... Thank you!





[DBAL-375] Warning "Udefined index dbname" while creating database with oci8 driver Created: 31/Oct/12  Updated: 20/Apr/13

Status: Open
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.2.2, 2.3.1
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: pavel patrin Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: dbname, oci8


 Description   

In config specified:

doctrine:
dbal:
driver: "oci8"
host: "localhost"
port: "1521"
dbname: "orcl50"
user: "SYSTEM"
password: "123456"
charset: UTF8

When i create database (with symfony 2, doctrine:database:create), got that error:

=====================================
Could not create database for connection named orcl50
Notice: Undefined index: dbname in /path/to/symfony/vendor/doctrine-dbal/lib/Doctrine/DBAL/Driver/OCI8/Driver.php line 67
=====================================

If i comment "unset($params['dbname'])" in CreateDatabaseDoctrineCommand.php:54 all works fine.



 Comments   
Comment by Kris Willis [ 13/Nov/12 ]

I'm experiencing the same issue and your fix appears to work for me too; thanks!

Comment by Benjamin Eberlei [ 20/Apr/13 ]

on Oracle CREATE DATABASE is actually a CREATE USER. I am not sure the command should allow to do this.





Generated at Mon Apr 21 00:40:23 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.