ORA-01790 on mass insert or update with version 6.0.x

[basjansen126]

The problem

Whenever a mass insert is called (through a model or the query builder) oracle throws the following error:

ORA-01790: expression must have same datatype as corresponding expression

The problem here is that I am trying to insert data into a table with a column that accepts either an integer or null.

For example: I have a table with people. The job_id attribute can be null or an integer. The job_id attribute on my Person in the first case is null. The job_id attribute on my Person in the second case is some id. Due to not being the same data type the ORA-01790 exception is thrown, even if my job_id column can be either null or an integer.

Example of code which results in an error:

$newPeople = [
    [
         'name' => 'Foo'
         'job_id' => null,
    ],
    [
         'name' => 'Bar',
         'job_id' => 1,
    ],
    [
         'name' => 'Test',
         'job_id' => 2,
    ]
];

Then:

DB::table('people')->insert($newPeople);

OR

$person = new Person();
$person->insert($newPeople);

This has been occurring since updating to 6.0.x. Version 5.8.x does not have this problem.

System details

• Ubuntu 18.04.1
• PHP 7.2.15
• Laravel 6.0
• Laravel-OCI8 6.0.4

[yajra]

Thanks for reporting, I was able to replicate the issue now. If you can, please do not hesitate to submit a PR for a Fix.

[yajra]

This has been occurring since updating to 6.0.x. Version 5.8.x does not have this problem.

Are you sure the issue only exists on 6.x? I think the problem is with the current implementation where batch insert uses UNION sql which I think was implemented since 5.x. Anyways, will check again further when I got the chance.

-- Edit -- Found in commit history that I made some modifications when binding the values and that causes this issue.

[basjansen126]

Thanks for finding the issue! Hopefully it will be easy to resolve.

For now I have added a workaround in my own code so the error will not get triggered. This is okay for short term, but in the long run I would like to be able to use insert() again.

[yajra]

Fixed on v8.2.1 🚀