yajra / pdo-via-oci8

PHP PDO_OCI functions via OCI8 extension

Oracle PDO Userspace Driver for OCI8

PDO via Oci8

The yajra/pdo-via-oci8 package is a simple userspace driver for PDO that uses the tried and tested OCI8 functions instead of using the still experimental and not all that functional. PDO_OCI library.

Please report any bugs you may find.

• Installation
• Credits

Installation

Add yajra/laravel-pdo-via-oci8 as a requirement to composer.json:

{
    "require": {
        "yajra/laravel-pdo-via-oci8": "2.*"
    }
}

And then run composer update

PHP 8 Support

When using PHP 8, please use version 3: "yajra/laravel-pdo-via-oci8": "3.*".

Testing

There is a test suite (using PHPUnit with a version bigger than 6.x) on the test directory. If you want to test (you must test your code!), create a table called people with two columns:

1. name as varchar2(50)
2. email as varchar2(30)

And some environment variables:

1. OCI_USER with the database user name
2. OCI_PWD with the database password
3. OCI_STR with the database connection string

And then go to the test dir and run PHPUnit like:

phpunit --colors .

Example to get it up and running on docker DB container-registry.oracle.com/database/enterprise:12.2.0.1

create pluggable database testpdb admin user oracle identified by system file_name_convert = ('/pdbseed/', '/testpdb01/');
alter pluggable database testpdb open;

ALTER SESSION SET CONTAINER=testpdb;

CREATE TABLE person (name NVARCHAR2(50), email NVARCHAR2(30));

License

The MIT License (MIT). Please see License File for more information.

Credits

• crazycodr/pdo-via-oci8
• ramsey/pdo_oci8
• To all contributors of this project

pdo-via-oci8's Issues

Configuration

I have to use pdo-via-oci with laraval/Asgard CMS application. I was wondering how to configure the pdo-via-oci8 driver. What are the steps to be followed after installing it via composer? Where do I specify the Oracle credentials? How does the PDO driver get access to these credentials?

Question mark in string literal is replaced with pseudo named parameter

The way this library currently uses preg_replace on the statement, there is no way to use a question mark within a string literal in a query due to it being replaced by a named parameter in the prepare function.

Given a query like select 'http://www.test.com/?id=' || usr.id url from users usr the question mark would be replaced with a named parameter like ":p0".

Would it not be best to only match on question marks that are not contained within single quotes?

Laravel push queue fail

I used the laravel function to push a database queue and got some error below.
Error Code : 1438
Error Message : ORA-01438: value larger than specified precision allowed for this column
Position : 109
Statement : insert into jobs ("queue", attempts, reserved, reserved_at, available_at, created_at, payload) values (:p0, :p1, :p2, :p3, :p4, :p5, :p6) returning id into :p7
Bindings : [default,0,0,,1470367614,1470367614,{"job":"Illuminate\Queue\CallQueuedHandler@call","data":{"commandName":"LendingFront\Jobs\SendSmsJob","command":"O:28:"LendingFront\Jobs\SendSmsJob":4:{s:10:"connection";N;s:5:"queue";N;s:5:"delay";N;s:6:"\u0000*\u0000job";N;}"}},0]

I find the error is occur in "Yajra\Pdo\Oci8\Statement" "bindParam" function,
when case PDO::PARAM_INT the bind value is not the current value.

The second record is the after bind value and the first record is the correct value.

I change the $ociType To SQLT_CHR then every thing is correct.
case PDO::PARAM_INT:
$ociType = SQLT_CHR;
break;
I don't know this is the bug or some thing else. Could you help me ?

Call to a member function save() on null

Hi! I'm just getting following error after executing a store procedure... Call to a member function save() on null on line \vendor\yajra\laravel-pdo-via-oci8\src\Pdo\Oci8\Statement.php:735

// Save blob objects if set.
if ($result && count($this->blobObjects) > 0) {
    foreach ($this->blobObjects as $param => $blob) {
        /* @var OCILob $blob */
        $blob->save($this->blobBindings[$param]);
    }
}

It's happening when the param is set but the output retrieved is empty... It looks like is missing a isset before save...
I reported in case someone else has the same issue...

// Save blob objects if set.
if ($result && count($this->blobObjects) > 0) {
    foreach ($this->blobObjects as $param => $blob) {
        /* @var OCILob $blob */
        if(isset($this->blobBindings[$param])){
            $blob->save($this->blobBindings[$param]);
        }
    }
}

• Operating System
• PHP Version 8.0.8
• PDO-VIA-OCI8 Version "v9.0.0"

Declaration of Yajra\Pdo\Oci8::query($statement, $fetchMode = null, $modeArg = null, $ctorArgs = null) must be compatible with PDO::query(string $query, ?int $fetchMode = null, mixed ...$fetchModeArgs)

Summary of problem or feature request

Declaration of Yajra\Pdo\Oci8::query($statement, $fetchMode = null, $modeArg = null, $ctorArgs = null) must be compatible with PDO::query(string $query, ?int $fetchMode = null, mixed ...$fetchModeArgs)

• macOS Catalina
• PHP 8
• PDO-VIA-OCI8 Version: 1.x

oci_connect(): OCI_SUCCESS_WITH_INFO: ORA-28002: the password will expire within 97 days

Summary of problem or feature request

I tried to get data from query. But this warning causes the application to crash. But this works fine in pdo-via-oci8 v2.0. The error is giving the version from pdo-via-oci8 v3.0.

Code snippet of problem

try {
    $pdo = DB::connection('core')->getPdo();
} catch (\Throwable $e) {
    dd($e);
}

System details

• Ubuntu 22.04
• PHP 8.1
• PDO-VIA-OCI8 3.0

ORA-22275: invalid LOB locator specified while inserting binary data

Here is my PHP code:
public function insertPacket($nist) {
$blob = fopen($nist->getActualFile(), 'rb');
$sql = "INSERT INTO packets(packet) VALUES(:packet)";
$query = $this->link->prepare($sql);
$query->bindParam(':packet', $blob, PDO::PARAM_LOB);
if(!$query->execute()) {
trigger_error(print_r($query->errorInfo(), true), E_USER_ERROR);
}
return $this->link->lastInsertId();
}

This works in MySQL. However using the pdo-via-oci8 I get this error:
Fatal error: Uncaught exception 'Oci8Exception' with message ' in C:\wamp\www\project\includes\PdoViaOci8\Statement.php on line 156
Oci8Exception: Error Code : 22275
Error Message : ORA-22275: invalid LOB locator specified
Position : 12
Statement : INSERT INTO packets(packet) VALUES(:packet)
Bindings : []
Stack Trace : Array

Is inserting BLOB supported?

setFetchMode(PDO::FETCH_CLASS, 'Classtype') and fetchAll() returns array<stdClass>

Summary

When using setFetchMode(\PDO::FETCH_CLASS, 'Classtype') and fetchAll(), the returned type is array<\stdClass> because fetchAll() will ignore the previously set fetchClassName.

The expected behaviour is that fetchAll() returns array<Classtype> as set previously with setFetchMode().

Test case

class Classtype
{
    public int $id;
}

$sql = "SELECT 1 as id FROM dual";

/*
Working test, $result is array<Classtype>
*/
$stmt = $pdo->query($sql);
$result = $stmt->fetchAll(\PDO::FETCH_CLASS, 'Classtype');
var_dump($result);
/*
array(1) {
    [0]=>
    object(Classtype)#5 (1) {
        ["id"]=>
        int(1)
    }
}
*/

/*
Failing test, $result is array<stdClass>
*/
$stmt = $pdo->query($sql);
$stmt->setFetchMode(\PDO::FETCH_CLASS, 'Classtype');
$result = $stmt->fetchAll();
var_dump($result);
/*
array(1) {
    [0]=>
    object(stdClass)#6 (1) {
        ["id"]=>
        string(1) "1"
    }
}
*/

System details

• Windows Server
• PHP 7.4.2
• PDO-VIA-OCI8 2.4

Get the resource of oci8 connection

Summary of problem or feature request

I want to use ref cursor as an output. pdo does not supports ref cursors, so i need to bind it by oci_bind_by_name.
For that I need private resource property of Oci8 class called dbh. Maybe add getter for it like GetResource() ?

Code snippet of problem

$pdo = DB::getPdo();
$resource = $pdo->getResource();

System details

• Operating System Win10
• PHP Version 7.4.25
• Laravel Version 8.65
• Laravel-OCI8 Version 8

set oracle connection identifier

Summary of problem or feature request

regarding yajra/laravel-oci8#453

Add feature to set the connection identifier as an option by calling oci_set_client_identifier()
https://www.php.net/manual/de/function.oci-set-client-identifier.php

Code snippet of problem

$user = getenv('OCI_USER') ?: self::DEFAULT_USER;
$pwd = getenv('OCI_PWD') ?: self::DEFAULT_PWD;
$dsn = getenv('OCI_DSN') ?: self::DEFAULT_DSN;
$con = new Oci8($dsn, $user, $pwd, [PDO::OCI_ATTR_CLIENT_IDENTIFIER => $expectedIdentifier]);
$this->assertNotNull($con);

$stmt = $con->query("SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') as IDENTIFIER FROM DUAL");
$foundClientIdentifier = $stmt->fetchColumn(0);

System details

• Operating System macOS 12.1
• PHP Version 8.0.8
• PDO-VIA-OCI8 Version 2.2.0, origin/master HEAD

Problem with bind param oci collection

Hello man, thanks to you for your work,

Problem and code

I have this procedure and type in my oracle DB, the idea is use a collection like parameter, this is an example

CREATE OR REPLACE TYPE ARRAY_DATOS2 IS VARRAY(100) OF varchar2(500);

CREATE OR REPLACE PROCEDURE PRUEBA_VARRAY2(P_TIP IN VARCHAR2,P_CUR IN OUT ARRAY_DATOS2) IS
v_num number;
BEGIN
-- this line insert size of collection in table ARAUCANO
  v_num:=P_CUR.count;
  INSERT INTO ARAUCANO(CONTENIDO) VALUES (TO_CHAR(v_num));
-- this lines insert the content of each record in table ARAUCANO
  for i in 1..P_CUR.count loop
    INSERT INTO ARAUCANO(CONTENIDO) VALUES (P_CUR(i));
  end loop;
  COMMIT;
END;

in my php i have this:

$conn = oci_connect('ALUM_INT', 'ALUM_INT', 'DB10');
$v_tipo = 'I';
$designaciones = ['1|0317031','2|0480458','3|1925866'];
$categories = oci_new_collection($conn,'ARRAY_DATOS2','SIUC');
foreach ($designaciones as $value) {
    $categories->append($value);
}
$options = array("type_name" => "ARRAY_DATOS2","schema" => "SIUC");
$pdo = DB::getPdo();
    $stmt_rec = $pdo->prepare("begin SIUC.PRUEBA_VARRAY2(:p1,:p2); end;");
    $stmt_rec->bindParam(':p1',  $v_tipo, PDO::PARAM_STR);
    $stmt_rec->bindParam(':p2',  $categories, SQLT_NTY, -1, $options);
    $result = $stmt_rec->execute();
dd($result);

the $result is "true" but in table ARAUCANO is 0, the count of the collection, is 0

select * from araucano;
1 0

System details

ORACLE 12 DB
PHP 5.5.1

ok, now check the function in your code

public function bindParam($parameter, &$variable, $dataType = PDO::PARAM_STR, $maxLength = -1, $options = null)

I found this in the type SQLT_NTY

case SQLT_NTY:
                $ociType = SQLT_NTY;
                $schema    = isset($options['schema']) ? $options['schema'] : '';
                $type_name = isset($options['type_name']) ? $options['type_name'] : '';
                // set params required to use custom type.
this line 467 -->         $variable = $this->connection->getNewCollection($type_name, $schema);
                break;

in line 467, overwrite the value of the IN param with a new collection, I test that put this line before and after

echo('<br>->TAMA 1');   
var_dump($variable->size());
  $variable = $this->connection->getNewCollection($type_name, $schema);
echo('<br>->TAMA 2');   
var_dump($variable->size());

the size is 3 and then 0

I replace the code with this

$variable2 = $this->connection->getNewCollection($type_name, $schema);
$variable2->assign($variable);

$variable = $this->connection->getNewCollection($type_name, $schema);

$variable->assign($variable2);

and solve the problem, result

 select * from araucano;
1	3	
2	1|0317031	
3	2|0480458	
4	3|1925866	

this is a patch only for collections, I wait to you for a true solution

sorry by my english

thanks in advance

ORA-12533: TNS:illegal ADDRESS parameters

Summary of problem or feature request

I am running Laravel 6
I'm getting the error below:

ORA-12533: TNS:illegal ADDRESS parameters

My .env
DB_CONNECTION=oracle
DB_HOST=127.0.0.1
DB_POST=1521
DB_DATABASE=XE
DB_USERNAME=HR
DB_PASSWORD=hshahashs

My controller:

namespace App\Http\Controllers;

use App\Models\Employee;

class HomeController extends Controller
{
    public function index(){

        $employees = Employee::all();
        dd($employees);
    }
}

Models

<?php
namespace App\Models;

use Yajra\Oci8\Eloquent\OracleEloquent as Eloquent;

class Employee extends Eloquent {

    protected $connection = 'oracle';

    protected $table = 'employees';

    // define binary/blob fields
//    protected $binaries = ['content'];

    // define the sequence name used for incrementing
    // default value would be {table}_{primaryKey}_seq if not set
//    protected $sequence = null;

}

I tried this directly and it works without the package

       $conn = oci_connect("HR", "hshahashs", "localhost/XE");
        $stdi = oci_parse($conn, "SELECT * FROM employees");

        if(oci_execute($stdi)){
            dd(oci_fetch_assoc($stdi));
        }

Please assist

• Operating System - windows (Wamp)
• PHP Version -PHP 7.3
• PDO-VIA-OCI8 Version

Calling reset() on an object is deprecated in (Statement.php on line 690) PHP 8.1.4

Summary of problem or feature request

I'm using Laravel OCI8 v9 which I have many deprecation logs for Calling reset() function for PHP 8.1.4:

local.WARNING: reset(): Calling reset() on an object is deprecated in *\vendor\yajra\laravel-pdo-via-oci8\src\Pdo\Oci8\Statement.php on line 690

I already post comment in Laravel OCI8 here

Code snippet of problem

The following code fixed my issue hope do the same for other, line 690, 691of yajra\laravel-pdo-via-oci8\src\Pdo\Oci8\Statement.php need to be update from this:

 if ((is_array($row) || is_object($row)) && is_resource( reset($row) )) {
                $stmt = new self(reset($row)), $this->connection, $this->options);

To this:

 if ((is_array($row) || is_object($row)) && is_resource( current( ( (array) $row) ) )) {
                $stmt = new self(current(((array) $row)), $this->connection, $this->options);

The above code fixed my deprecation logs. this is my Log::alert(reset($row) . ' ' . current(((array) $row)) );
[2022-06-11 09:12:09] local.ALERT: BACKUP BACKUP

System details

• Operating System: Windows 10 pro
• PHP Version: 8.1.4
• PDO-VIA-OCI8 Version: 3.2.2
• Laravel version: 9.17

Convert Date object automatically to oracle's date string.

It would be nice that the oci8 driver would automatically convert a date object to an oracle's date string

I was trying to insert like this

$pac = new Pac();
$pac->some_date = new DateTime();
$pac->save();

And I was receiving this error

oci_bind_by_name(): Invalid variable used for bind

After some digging, I switched the second line into this and it worked ok.

$pac->some_date = date('dd-M-Y');

It would be nice if the data type is date, to convert it to string in oci_bind_by_name.

PHP 8.1 Support

When trying to use the lib in php 8.1 I'm getting the following error:

Trace:
#0 .../vendor/yajra/laravel-pdo-via-oci8/src/Pdo/Oci8/Statement.php(690): get_mangled_object_vars(Array)

...

Exception: get_mangled_object_vars(): Argument #1 ($object) must be of type object, array given

Summary of problem or feature request

Argument must be object type in get_mangled_object_vars.

Possible fix is cast to object, like so:

 $mangledObj = get_mangled_object_vars((object)$row);

System details

• Operating System GNU/Linux
• PHP Version 8.1
• PDO-VIA-OCI8 3.2.3

fetchAll error while fetch return not array

function fetchAll() if $this->fetch() return not array (as example - FETCH_COLUMN)

    $this->_results = array();
    while ($row = $this->fetch())
    {
        if (is_resource(reset($row))) {

then line with reset($row) throw Database Exception – yii\db\Exception
reset() expects parameter 1 to be array, string given

Can't use standard DSN to connect

I tried using the PDO-via-OCI8 driver using my standard DSN but it didn't work. I then noticed the DSN was immediately passed to the OCI8 driver without modification. Looking at the commits, I noticed the DSN parser was removed back in February. I'm just curious, why was it removed? I'm thinking of restoring this functionality in my fork, but wanted to know more about its removal before proceeding.

Thanks.

Undefined constant "Yajra\Pdo\Oci8\SQLT_BOL"

Summary of problem or feature request

Encountering error Undefined constant "Yajra\Pdo\Oci8\SQLT_BOL" when executing stored procedure.
To be more specific, when binding OUT parameter.

This works on my local machine, but not on the the another machine.
The only difference I noticed between both machine are the OS version and php-oci8.
Can't confirm yet.

Recompiled oci8 on the other machine using instantclient 12.2 and it works fine now.
So maybe this require oci8 with at least instantclient 12.2?

Code snippet of problem

...
$stmt = DB::getPdo()->prepare($sqlQuery);
...
$stmt->bindParam('kd_detil', $kd_detil, 20, SQLT_INT);
// ^ this trigger the error
...

  Undefined constant "Yajra\Pdo\Oci8\SQLT_BOL"

  at vendor/yajra/laravel-pdo-via-oci8/src/Pdo/Oci8/Statement.php:351
    347▕ 
    348▕                 $this->blobObjects[$parameter] = &$variable;
    349▕                 break;
    350▕ 
  ➜ 351▕             case SQLT_BOL:
    352▕                 $ociType = SQLT_BOL;
    353▕                 break;
    354▕ 
    355▕             default:

  1   app/Models/DetailKontrak.php:259
      Yajra\Pdo\Oci8\Statement::bindParam()

      +1 vendor frames 
  3   app/Console/Commands/SyncBkdCommand.php:227
      Illuminate\Database\Eloquent\Model::save()

System details (My PC, works)

• Operating System Ubuntu 22.04.2 LTS (WSL)
• PHP Version 8.0.29
• PDO-VIA-OCI8 Version v3.4.0
• php-oci8 3.0.1 instantclient 19.16.0.0.0

System details (Other Machine, doesnt work)

• Operating System Ubuntu Ubuntu 20.04.6 LTS
• PHP Version 8.0.29
• PDO-VIA-OCI8 Version v3.4.0
• php-oci8 2.0.12 instantclient 11.2.0.4.0

System details (Other Machine, updated, works)

• Operating System Ubuntu Ubuntu 20.04.6 LTS
• PHP Version 8.0.29
• PDO-VIA-OCI8 Version v3.4.0
• php-oci8 3.0.1 instantclient 12.2.0.1.0

After updating from 3.2.0 to 3.2.1 I get this error: oci8 statement resource #1058,

I am calling select from my connection with a parameter:

DB::connection('oracle')->SELECT("SELECT MY_WO.LAST_TEN( ? ) FROM DUAL",[ $user_ID]);

[
{#4176
+"my_wo.last_ten( ? )": oci8 statement resource #1058,
},
]

If I update composer and set it to 3.2.0 everything works. Not sure what the change is or how to fix it.

• Ubuntu 20.04
• PHP 8.0.18
• PDO-VIA-OCI8 3.2.1

Thank you for any ideas

Table naming case sensitivity issue

Summary of problem or feature request

I have issue with table naming case sensitivity. As far as I can debug this probably isn't the place for this issue, but any help will be higlhy appreciated.

In development environment, everything (same php, same oracle db, but not iis) under docker everything works, DB created via Laravel's migrations. TEST/PROD DB are created long long time ago.

Additional hint: setting PDO::ATTR_CASE in yajra/laravel-oci8 doesn't work, somehow is totally ignored.

Code snippet of problem

Non working:

In tinker execute any of following code:

• TableName::all()
• DB::connection('oracle')->table('table_name')->select('*')->first();
  The repsonse is following error:

Illuminate\Database\QueryException with message 'Error Code    : 942
Error Message : ORA-00942: table or view does not exist
Position      : 29
Statement     : select * from (select * from "TABLE_NAME") where rownum = 1
Bindings      : []
 (SQL: select * from (select * from "TABLE_NAME") where rownum = 1)'

Working (narrowing the problem)

Create some random api endpoint with following content:

\DB::enableQueryLog();
    $query = <<<END
SELECT * FROM "table_name" WHERE ROWNUM = 1
END;

    $tables = \DB::connection('oracle')->select($query);

    dd($tables); // ok

If I manually update following file vendor\yajra\laravel-oci8\src\Oci8\Query\Grammars\OracleGrammar.php by commenting out 275th ($value = Str::upper($value);) line everything works. I know that this isn't the way, but I needed somehow to narrow search.

System details

Server:

• Windows Server 2016 Standard
• IIS version 10

Database:

• Oracle 11g
• Oracle instant client 11.2

Tech stack:

• Laravel version 8
• PHP 8.0.29( NTS Visual C++ 2019 x86 )
• ORM: yajra/laravel-oci8 version 8.6.2

getAttribute returning empty array instead of null in PHP v8.1.16

pdo-via-oci8/src/Pdo/Oci8.php

Line 492 in 2666f4c

return [];

When PDO attribute is not set the method getAttribute() returns empty array [] while it should return null.

This will be an issue downstream when methods will expect a string or null resulting in ERROR: Array to string conversion.

Example of usage for this attribute: Laravel Queue https://github.com/laravel/framework/blob/38f022d01fd85caf88c11cec52dade7ccccccfdf/src/Illuminate/Queue/DatabaseQueue.php#L259:L261

Thank you in advance for looking into this!

Binding variable with ociType SQLT_NTY gets cleared

Summary of problem or feature request

I found issues #73 and #74 when attempting to bind an array to pass to a stored procedure. I followed the example in #74 and found that I was having the same issue where my variable that was being bound was being cleared out.

After looking through some of the PHP documentation and this package, I found the potential issue.

It looks like the fix implemented in #74 is doing a check if the variable is equal to 'OCI-COLLECTION'; however, as of php 8, the class name has been changed to: 'OCICollection'.

PHP OCI Collection

Code snippet of problem

Existing code in Statement.php

case SQLT_NTY:
                $ociType = SQLT_NTY;

                $schema = $options['schema'] ?? '';
                $type_name = $options['type_name'] ?? '';

                if (strtoupper(get_class($variable)) == 'OCI-COLLECTION') {
                    $collection_temp = $this->connection->getNewCollection($type_name, $schema);
                    $collection_temp->assign($variable);

                    $variable = $this->connection->getNewCollection($type_name, $schema);
                    $variable->assign($collection_temp);

                    $collection_temp->free();
                } else {
                    // set params required to use custom type.
                    $variable = $this->connection->getNewCollection($type_name, $schema);
                }
                break;

Changed code in Statement.php on line 326

    if (strtoupper(get_class($variable)) == 'OCICOLLECTION') {

After I removed the hyphen from 'OCICOLLECTION', the bind was no longer reset and I was able to successfully call my stored procedure.

System details

• Operating System: Ubuntu 22.04
• PHP Version v8.1.13
• PDO-VIA-OCI8 v3.4

Request: improved support for error handling

Hi. I know this isn't an "issue" as such, but I was wondering if you'd be able to update the exception-handler class with a few helper-functions that make it easier to fetch and manage the error, making it easier to debug the faulty SQL (or PL/SQL) code that threw the exception, and also make it easier to feed back the correct information to the user.

I realise that I can extend the exception myself, but just thought it would be easier to share with others this way.

class Oci8Exception extends PDOException
{
    public function getHtmlMessage() {
        $msg = $this->getMessage();
        $msg = str_replace (chr(13).chr(10), '</p><p>', $msg);
        $msg = str_replace (chr(10), '<br />', $msg);
        return '<p>' . $msg . '</p>';
    }

    public function getOciErrorStack() {
        $msg = $this->getMessage();
        preg_match ('/(Error Message\s*:[^\r]*)\r\n/s', $msg, $out);
        return $out[1];
    }
    public function getOciErrorMsg() {
        $msg = $this->getOciErrorStack();
        preg_match ('/Error Message\s*:\s*ORA-[0-9]{5}:\s*(.*)/', $msg, $out);
        return $out[1];
    }

    public function getOriginalStatement ($rtnstr =2) {
        $msg = $this->getMessage();
        preg_match ('/(Statement\s*:)\s*([^\r]*)/', $msg, $out);
        return $out[$rtnstr];
    }

    public function getBindings ($rtnstr =2) {
        $msg = $this->getMessage();
        preg_match ('/(Bindings\s*:)\s*([^\r]*)/', $msg, $out);
        return $out[$rtnstr];
    }
}

Use oci_new_connect instead oci_connect

Hi everyone!

I have the following Code but it doesn't work.

DB::connection("oracle")->beginTransaction();
DB::connection("oracle_log")->beginTransaction();
dump(ModelExample::on("oracle")->count()); \\ 10 records
dump(ModelExample::on("oracle_log")->count()); \\ 10 records
ModelExample::on("oracle_log")->insert([
    "column" => "test"
]);
dump(ModelExample::on("oracle_log")->count()); \\ 11 records
dump(ModelExample::on("oracle")->count()); \\ I expect 10 records but i have 11 records

The oracle connection and oracle_log connection have the same configs.

Using the oci_connect function, as stated in the PHP documentation, oci_connect() with the same parameters will return the connection handle returned from the first call.
Using Laravel singletons for connections, there should be no problems with an increase in sessions.

My proposal would be to use the oci_new_connect function instead of oci_connect or use a parameter within the config on which function to use.

CLOBS that have more than 32.767 characters

It seems that the driver does not support CLOBS that have more than 32.767 characters. As far as I can see the problem is within src/yajra/Pdo/Oci8/Statement.php. The case of CLOBS is not covered so a possible solution is:

case SQLT_CLOB:
                $oci_type = OCI_B_CLOB;
                $this->_returnLobs = true;
                // create a new descriptor for lob-type
                $variable = $this->_pdoOci8->getNewDescriptor();
                break;

The solution is not tested through, and it needs more investigation.

Library should also support more standard DSNs

I'm attempting to use this library and I ran in to a snag with the DSN. The documentation doesn't mention what the DSN syntax for OCI is, so I had to guess. Looking through the code I found a sample DSN but that's not the most intuitive way to figure out the solution.

I believe the library should be updated to handle more standard DSN connection strings in addition to the OCI specific string it already understands. From the various PHP PDO documentation pages I found the following DSN strings:

mysql:host=localhost;port=3307;dbname=testdb
mssql:host=localhost;dbname=testdb
sybase:host=localhost;dbname=testdb
dblib:host=localhost;dbname=testdb
pgsql:host=localhost;port=5432;dbname=testdb

This format could be easily parsed out, and support for both types of DSNs could be obtained.

Alternately, modifying the README to include a sample DSN and connect string would be very helpful.

Support Request for SQLT_BOL or OCI_B_BOL in oci_bind_by_name

Summary of problem or feature request

I'd like to bring to your attention an issue regarding the conversion of PDO::PARAM_BOOLEAN to SQLT_INT in the bindParam function within Statement.php. This conversion is causing type mismatch errors.

I kindly request that you consider either converting PDO::PARAM_BOOLEAN to SQLT_BOL or OCI_B_BOL, or adding a case statement to handle SQLT_BOL or OCI_BO_BOL specifically.

By implementing this change, it would ensure proper type compatibility and prevent errors when working with Boolean values.

Code snippet of problem

PL/SQL

CREATE OR REPLACE FUNCTION FUNC_BOOL 
(
  PARAM1 IN BOOLEAN 
, PARAM2 OUT BOOLEAN 
) RETURN BOOLEAN AS 
BEGIN
  PARAM2 := PARAM1;
  RETURN PARAM1;
END FUNC_BOOL;

for Laravel

$outparam = null;
$bindings = [
    'p1' => [
        'value' => true,
        'type' => PDO::PARAM_BOOL,
    ],
    'p2' => [
        'value' => &$outparam,
        'type' => PDO::PARAM_BOOL,
    ],
];

$result = DB::connection()->executeFunction('FUNC_BOOL', $bindings, PDO::PARAM_BOOL);

output error

   Yajra\Pdo\Oci8\Exceptions\Oci8Exception 

  Error Code    : 6550
Error Message : ORA-06550: line 1, column 18:
PLS-00306: wrong number or types of arguments in call to 'FUNC_BOOL'
ORA-06550: line 1, column 18:
PLS-00306: wrong number or types of arguments in call to 'FUNC_BOOL'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Position      : 17
Statement     : begin :result := FUNC_BOOL(:p1, :p2); end;
Bindings      : [1,0,0]

Modification for Statement.php

1. Convert PDO::PARAM_BOOLEAN to SQLT_BOL or OCI_B_BOL instead of SQLT_INT.

   public function bindParam(..) 
   {
       ...
       switch ($dataType) {
           case PDO::PARAM_BOOL:
                   $ociType = SQLT_BOL;   // change SQLT_INT to  SQLT_BOL or OCI_B_BOL
                   break;
           ...
   }
   

2. Add a case statement specifically for SQLT_BOL or OCI_B_BOL

   public function bindParam(..) 
   {
       ...
       switch ($dataType) {
           ...
           // add 
           case SQLT_BOL :
               $ociType = SQLT_BOL;
               break;
           ...
   }
   

System details

• Windows11 (docker container)
• PHP 8.2
• PDO-VIA-OCI8 Version: 3.3.1

Integration Test using Docker

Implement integration test using docker like the implementation done on https://github.com/yajra/laravel-oci8.

missing implementation of listTableColumns fail on using laravel-ide-helper

Summary of problem or feature request

I try to use laravel-ide-helper but when i try to create the model ide-helpers I get and error
that error seem related to a query that is used to extract columns notes from db tables.

If I execute the query "manually" from Oracle SQLDeveloper, the query is perfectly valid but don't return any result because my columns don't have any comments.

Below the error message, where DEV_USERS is the real table name and REDACTED_ORACLE_SCHEMA_NAME is the oracle schema where the table is located.

Exception: An exception occurred while executing
'SELECT c.*,
(
SELECT d.comments
FROM all_col_comments d
WHERE d.TABLE_NAME = c.TABLE_NAME AND d.OWNER = c.OWNER
AND d.COLUMN_NAME = c.COLUMN_NAME
) AS comments
FROM all_tab_columns c
WHERE c.table_name = 'DEV_USERS' AND c.owner = 'REDACTED_ORACLE_SCHEMA_NAME'
ORDER BY c.column_id':

Argument 1 passed to Doctrine\DBAL\Connection::ensureForwardCompatibilityStatement() must be an instance of Doctrine\DBAL\Driver\ResultStatement, instance of Yajra\Pdo\Oci8\Statement given, called in /mnt/develop/www/acs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php on line 1314
Could not analyze class App\Models\Article.

Trace:
#0 /mnt/develop/www/acs/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(159): Doctrine\DBAL\DBALException::wrapException()
#1 /mnt/develop/www/acs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(2214): Doctrine\DBAL\DBALException::driverExceptionDuringQuery()
#2 /mnt/develop/www/acs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1035): Doctrine\DBAL\Connection->handleExceptionDuringQuery()
#3 /mnt/develop/www/acs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php(168): Doctrine\DBAL\Connection->fetchAllAssociative()
#4 /mnt/develop/www/acs/vendor/barryvdh/laravel-ide-helper/src/Console/ModelsCommand.php(432): Doctrine\DBAL\Schema\AbstractSchemaManager->listTableColumns()
#5 /mnt/develop/www/acs/vendor/barryvdh/laravel-ide-helper/src/Console/ModelsCommand.php(270): Barryvdh\LaravelIdeHelper\Console\ModelsCommand->getPropertiesFromTable()
#6 /mnt/develop/www/acs/vendor/barryvdh/laravel-ide-helper/src/Console/ModelsCommand.php(159): Barryvdh\LaravelIdeHelper\Console\ModelsCommand->generateDocs()
#7 /mnt/develop/www/acs/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): Barryvdh\LaravelIdeHelper\Console\ModelsCommand->handle()
#8 /mnt/develop/www/acs/vendor/laravel/framework/src/Illuminate/Container/Util.php(37): Illuminate\Container\BoundMethod::Illuminate\Container{closure}()
#9 /mnt/develop/www/acs/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(93): Illuminate\Container\Util::unwrapIfClosure()
#10 /mnt/develop/www/acs/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(37): Illuminate\Container\BoundMethod::callBoundMethod()
#11 /mnt/develop/www/acs/vendor/laravel/framework/src/Illuminate/Container/Container.php(596): Illuminate\Container\BoundMethod::call()
#12 /mnt/develop/www/acs/vendor/laravel/framework/src/Illuminate/Console/Command.php(134): Illuminate\Container\Container->call()
#13 /mnt/develop/www/acs/vendor/symfony/console/Command/Command.php(256): Illuminate\Console\Command->execute()
#14 /mnt/develop/www/acs/vendor/laravel/framework/src/Illuminate/Console/Command.php(121): Symfony\Component\Console\Command\Command->run()
#15 /mnt/develop/www/acs/vendor/symfony/console/Application.php(971): Illuminate\Console\Command->run()
#16 /mnt/develop/www/acs/vendor/symfony/console/Application.php(290): Symfony\Component\Console\Application->doRunCommand()
#17 /mnt/develop/www/acs/vendor/symfony/console/Application.php(166): Symfony\Component\Console\Application->doRun()
#18 /mnt/develop/www/acs/vendor/laravel/framework/src/Illuminate/Console/Application.php(93): Symfony\Component\Console\Application->run()
#19 /mnt/develop/www/acs/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(129): Illuminate\Console\Application->run()
#20 /mnt/develop/www/acs/artisan(35): Illuminate\Foundation\Console\Kernel->handle()
#21 {main}

At first sight I think that is a problem of laravel-ide-helper and I open this ticket to they github, but seem that the problem is related to a missing listTableColumns Here:

pdo-via-oci8/src/Pdo/Oci8/Statement.php

Line 16 in 9361084

class Statement extends PDOStatement

This is the ticket and the reply with some hints to check the problem:
barryvdh/laravel-ide-helper#1227

There is a way to solve this problem? I really don't know how to handle it by myself.

System details

Versions:
Laravel Version: 7.30.4
PHP Version: 7.4.20
Oracle Laravel - yajra/laravel-oci8 - Version 7.1.1

query 4th attribute can also be null

Summary of problem or feature request

pdo->query has multiple implementations. But the 4th parameter can be null.
See https://www.php.net/manual/en/pdo.query.php

With the current solution I get this error when I passing in null:

TypeError: Argument 4 passed to Yajra\Pdo\Oci8::query() must be of the type array, null given,

Code snippet of problem

$con->query('SELECT table_name FROM user_tables', null, null, null);

Solution

Match the docblock that tells it can be array|null
Also see setFetchMode.

    /**
     * Executes an SQL statement, returning the results as a
     * Yajra\Pdo\Oci8\Statement object.
     *
     * @param string $statement The SQL statement to prepare and execute.
     * @param int|null $fetchMode The fetch mode must be one of the
     *   PDO::FETCH_* constants.
     * @param mixed|null $modeArg Column number, class name or object.
     * @param array|null $ctorArgs Constructor arguments.
     * @return Statement
     */
    public function query($statement, $fetchMode = null, $modeArg = null, $ctorArgs = null)

Update to v1.0.0 not connect in L4.2

I updated from v0.15.0 to v1.0.0 and cann't connect to DB, change de namespace yajra to Yajra in /OCI8/Connectors/OracleConnector.php to make it run.

getColumnMeta returns uppercase name even if PDO::CASE_LOWER is set

The Statement::getColumnMeta() method does not respect the PDO::ATTR_CASE setting and always returns uppercase names for columns.

I think the field name should be converted to lower case:

if ($this->getAttribute(PDO::ATTR_CASE) == PDO::CASE_LOWER)
  $meta['name']  = strtolower($meta['name'] );

ORA-00932 with tabletype columns

got ORA-00932: inconsistent datatypes when one of table/view column defined as tabletype

I'm working with some oracle API, that i can't change. In the api's last update developers has added to several views column T_TAGS. Here is it's definition:

CREATE OR REPLACE FORCE VIEW "AIS_NET"."SI_V_SUBJECTS"
AS SELECT
...
SI_SUBJECTS_PKG_S.GET_VC_NAME(N_CREATOR_ID) VC_CREATOR_NAME,
CAST(
MULTISET(
SELECT VC_CODE
FROM SI_V_ENTITIES_TAGS
WHERE N_ENTITY_ID = S.N_SUBJECT_ID
AND N_ENTITY_TYPE_ID = SYS_CONTEXT('CONST', 'ENTITY_TYPE_Subject')
ORDER BY N_LINE_NO) AS TAGS) T_TAGS,
(SELECT LISTAGG(VC_CODE, ', ') WITHIN GROUP (ORDER BY N_LINE_NO)
FROM SI_V_ENTITIES_TAGS
...

And when i'm trying simple select * from SI_V_SUBJECTS', im getting:

oci_fetch_assoc(): ORA-00932: inconsistent datatypes: expected CHAR got TABLE (SQL: select * from (select * fr
om SI_V_SUBJECTS where vc_code = wingmanzz) where rownum = 1)

Can we fix this issue somehow, or only define selecting columns without T_TAGS?

Thanks and sorry for my bad english

Conversion of PDO INOUT param to OCI in 64-bit

When PHP is compiled and run as 64-bit, there is a binding issue that requires PDO::PARAM_INPUT_OUTPUT to be specified for INOUT parameters (See: yajra/laravel-oci8#59)

That fix causes a bug here where the PDO::PARAM_INPUT_OUTPUT isn't accounted for when converting from PDO types to OCI types, and causes the value to always bind as a string, which will causes issues on further binds.

How to call Procedures with cursor???

Summary of problem or feature request

My code:

create or replace PROCEDURE GET_USERS_LIST
(
C_USERS OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN C_USERS FOR
SELECT * FROM USERS;
END GET_USERS_LIST;

I can not call Procedure and encountered an error. I need some help, plz. :(

Code snippet of problem

System details

• Operating System
• PHP Version
• PDO-VIA-OCI8 Version

Compound Primary Key

Summary of problem or feature request

I have a table that has 2 fields which are primary keys from different tables. It's primary key is unique compound partent tables fields. PK_TABLE3 - UNIQUE - COLUMNS: TABLE1_ID, TABLE2_ID
When I do the ->save() it comes back with an error:
Error Code : 904 Error Message : ORA-00904: "ID": invalid identifier Position : 91 Statemen

Code snippet of problem

$table3 = new Table3();
$table3->table1_id = $table1->id;
$table3->table2_id = $table2->id;
$table3->save();

System details

• Ubuntu 20.04
• PHP 8.2.11
• PDO-VIA-OCI8 Version 9.5.1

Type error in Statement.php

Type error: Argument 3 passed to Yajra\Pdo\Oci8\Statement::setFetchMode() must be of the type array, null given, called in .../vendor/yajra/laravel-pdo-via-oci8/src/Pdo/Oci8/Statement.php on line 563

This error throws after laravel's 5.2 code in Illuminate\Database\Connection.php:343:
$fetchConstructorArgument = null;
and then
? $statement->fetchAll($fetchMode, $fetchArgument, $fetchConstructorArgument)

Check for argument is null needed

always empty collection by binding to statement in php 8

Summary of problem or feature request

i have php 8 and laravel 8. While binding oracle collections in Statement.php there is a check for oci-collection class. But in php 8 class is changed to 'OciCollection', and now we have empty collection anytime

Code snippet of problem

if (strtoupper(get_class($variable)) == 'OCI-COLLECTION') {

must be:
$collection_class = 'OCI-COLLECTION';
if(phpversion() >= 8)
{
$collection_class = 'OCICOLLECTION';
}
if (strtoupper(get_class($variable)) == $collection_class) {

System details

• macOS High Sierra(docker container)
• php 8.0.2
• PDO-VIA-OCI8 Version 2

Support other PDOStatement classes via PDO::ATTR_STATEMENT_CLASS

Summary of problem or feature request

If I would like to debug the Statements and I create a debug class that extends Statement I should be able to use that via PDO::ATTR_STATEMENT_CLASS
See https://www.php.net/manual/en/pdo.setattribute.php

PHP8 Compatibility

Hello,

First of all, congratulations on the development of this component.
With the new version of PHP8, which will be released this month, the class Statement is not compatible.

Will this change mean a new major version?

Thanks

bindParam using wrong number when using ? in statement

bindParam using wrong param number when using ?

When you try to bind params with ? the first one should be 1 not 0.

Code snippet of problem

$stmt = $this->con->prepare('INSERT INTO person (name) VALUES (?)');
$var = 'Joop';
$stmt->bindParam(1, $var, PDO::PARAM_STR);