ORM OCI8 - ORA-00923: FROM keyword not found where expected Position

[Sudheert7]

Summary of problem

Error Code : 923 Error Message : ORA-00923: FROM keyword not found where expected Position : 17 Statement : 
SELECT 1.NEXTVAL as "id" FROM DUAL Bindings : []

System details Operating System: Ubuntu PHP Version: 8.3 Laravel Version: 10.0 PHP OCI 8-3.4.0 Laravel-OCI8 Version: 10.6 Oracle DB Version : 19

Example:

ProductModel product_id product_code product_name product_detail

Creating of new product is working, saving in oracle DB

OderModel product_id order_code

Select drop down to select prod ID Belongs to relation with Form1 belongs to Product::class, prod_id

When creating order , exception from eloquent.

Error Code : 923 Error Message : ORA-00923: FROM keyword not found where expected Position : 17 Statement : SELECT 1.NEXTVAL as "id" FROM DUAL Bindings : []

Code snippet of problem

public function up()
    {
        Schema::create('orders', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('order_code')->unique();
            $table->unsignedBigInteger('product_id')->nullable();
            $table->foreign('product_id', 'product_fk_9953139')->references('id')->on('products');
            $table->timestamps();
            $table->softDeletes();
        });
    }

 public function order()
    {
        return $this->belongsTo(Product::class, 'product_id');
    }

    public function store(StoreOrderRequest $request)
    {
        $order = Order::create($request->all());

        return redirect()->route('admin.order.index');
    }

laravel-debugbar:

Error Code : 923 Error Message : ORA-00923: FROM keyword not found where expected Position : 17 Statement : SELECT 1.NEXTVAL as "id" FROM DUAL Bindings : []
vendor/yajra/laravel-pdo-via-oci8/src/Pdo/Oci8/Statement.php#797

[04:23:51] LOG.error: Error Code    : 923
Error Message : ORA-00923: FROM keyword not found where expected
Position      : 17
Statement     : SELECT 1.NEXTVAL as "id" FROM DUAL
Bindings      : []
 (Connection: oracle, SQL: SELECT 1.NEXTVAL as "id" FROM DUAL) {
    "userId": 1,
    "exception": {
        "errorInfo": null,
        "connectionName": "oracle"
    }
}

Tried to Debug - Run query in SQL Developer(IDE)

Actual Query as in Application

SELECT 1.NEXTVAL as "id" FROM DUAL

Error: ORA-00923: FROM keyword not found where expected

923. 00000 - "FROM keyword not found where expected"

Working Query

SELECT '1.NEXTVAL' as "id" FROM DUAL

Result: 1.NEXTVAL

[Sudheert7]

Fixed by editing Line 125 in file laravel-oci8/src/Oci8/Schema/Sequence.php

Fix:

public function nextValue($name)
    {
        if (! $name) {
            return 0;
        }

        $name = $this->wrap($name);

        return $this->connection->selectOne("SELECT '$name.NEXTVAL' as \"id\" FROM DUAL")->id;
    }

and edited line 370 in file laravel-pdo-via-oci8/src/Pdo/Oci8.php

public function lastInsertId(string $name = null): false|string
    {
        if (! isset($this->table)) {
            return 0;
        }

        if (is_null($name)) {
            $name = $this->table.'_id_seq';
        }

        if (! $this->checkSequence($name)) {
            return 0;
        }

        $stmt = $this->query("SELECT '$name.CURRVAL' FROM DUAL", PDO::FETCH_COLUMN);

        return $stmt->fetch();
    }

[yajra]

Thanks for reporting, please feel free to submit a PR to fix it.

[yajra]

Can you provide the model which has 1 as the sequence name? Did you override the $sequence property by any chance?