Laravel OCI8

https://github.com/yajra/laravel-oci8
Admin
Laravel OCI8 is an Oracle Database Driver package for Laravel that extends Illuminate/Database using...
Tokens:6,381
Snippets:91
Trust Score:9.7
License:MIT
Update:2 months ago
Show doc for...
Tokens:
Raw
### Environment Variable Configuration

Source: https://github.com/yajra/laravel-oci8/blob/master/README.md

Examples of setting Oracle connection parameters in the .env file.

```ini
DB_CONNECTION=oracle
DB_HOST=oracle.host
DB_PORT=1521
DB_SERVICE_NAME=orcl
DB_DATABASE=xe
DB_USERNAME=hr
DB_PASSWORD=hr
DB_CONNECT_TIMEOUT=10
DB_RETRY_COUNT=3
DB_RETRY_DELAY=1
DB_TRANSPORT_CONNECT_TIMEOUT=60
DB_EXPIRE_TIME=0
```

```ini
DB_CONNECTION=oracle
DB_HOST=oracle1.host, oracle2.host
DB_PORT=1521
DB_SERVICE_NAME=orcl
DB_LOAD_BALANCE=no
DB_DATABASE=xe
DB_USERNAME=hr
DB_PASSWORD=hr
```

--------------------------------

### Configure Auto-Increment Start and Cache

Source: https://github.com/yajra/laravel-oci8/wiki/Auto-Increment-Support

Sets a custom starting value and disables caching for the auto-increment sequence.

```php
Schema::create('posts', function($table)
{
    $table->increments('id')->start(10000)->nocache();
    $table->string('title');
}
```

--------------------------------

### Install Laravel OCI8 via Composer

Source: https://github.com/yajra/laravel-oci8/wiki/Installation

Execute this command in your terminal to add the package to your Laravel project.

```bash
composer require yajra/laravel-oci8:"5.2.*"
```

--------------------------------

### Publish Configuration File

Source: https://github.com/yajra/laravel-oci8/wiki/Installation

Run this Artisan command to publish the package configuration file.

```bash
$ php artisan vendor:publish --tag=oracle
```

--------------------------------

### Publish Configuration

Source: https://github.com/yajra/laravel-oci8/blob/master/README.md

Run this Artisan command to copy the default configuration file to config/oracle.php.

```bash
php artisan vendor:publish --tag=oracle
```

--------------------------------

### Implement Dynamic Configuration Callback

Source: https://github.com/yajra/laravel-oci8/blob/master/README.md

Static method to modify connection parameters dynamically at runtime.

```php
namespace App\Models\Oracle;

class Config {

    public static function dynamicConfig(&$config) {

        if (Illuminate\Support\Facades\Auth::check()) {
            $config['username'] = App\Oracle\Config::getOraUser();
            $config['password'] = App\Oracle\Config::getOraPass();
        }

    }
}
```

--------------------------------

### Optional Capsule Configuration

Source: https://github.com/yajra/laravel-oci8/wiki/Stand-alone-Usage

Sets up the event dispatcher and global static access for the Capsule instance.

```php
// Set the event dispatcher used by Eloquent models... (optional)
use Illuminate\Events\Dispatcher;
use Illuminate\Container\Container;
$capsule->setEventDispatcher(new Dispatcher(new Container));

// Make this Capsule instance available globally via static methods... (optional)
$capsule->setAsGlobal();
```

--------------------------------

### Execute Oracle Procedure with Shortcut Method

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Procedure

Utilizes the package's helper method to execute a procedure with an array of bindings.

```php
$procedureName = 'youpackagename.yourprocedurename';

$bindings = [
    'user_id'  => $id,
];

$result = DB::executeProcedure($procedureName, $bindings);

dd($result);
```

--------------------------------

### Configure Oracle Connection with Capsule

Source: https://github.com/yajra/laravel-oci8/wiki/Stand-alone-Usage

Initializes the database manager and extends it with an Oracle driver, including session variable configuration.

```php
require 'vendor/autoload.php';

use Illuminate\Database\Capsule\Manager as Capsule;
use Yajra\Oci8\Connectors\OracleConnector;
use Yajra\Oci8\Oci8Connection;

$capsule = new Capsule;

$manager = $capsule->getDatabaseManager();
$manager->extend('oracle', function($config)
{
    $connector = new OracleConnector();
    $connection = $connector->connect($config);
    $db = new Oci8Connection($connection, $config["database"], $config["prefix"]);

    // set oracle session variables
    $sessionVars = [
        'NLS_TIME_FORMAT'         => 'HH24:MI:SS',
        'NLS_DATE_FORMAT'         => 'YYYY-MM-DD HH24:MI:SS',
        'NLS_TIMESTAMP_FORMAT'    => 'YYYY-MM-DD HH24:MI:SS',
        'NLS_TIMESTAMP_TZ_FORMAT' => 'YYYY-MM-DD HH24:MI:SS TZH:TZM',
        'NLS_NUMERIC_CHARACTERS'  => '.,',
    ];

    // Like Postgres, Oracle allows the concept of "schema"
    if (isset($config['schema']))
    {
        $sessionVars['CURRENT_SCHEMA'] = $config['schema'];
    }

    $db->setSessionVars($sessionVars);

    return $db;
});

$capsule->addConnection(array(
    'driver'   => 'oracle',
    'host'     => 'oracle.host',
    'database' => 'xe',
    'username' => 'user',
    'password' => 'password',
    'prefix'   => '',
    'port'  => 1521
));

$capsule->bootEloquent();
```

--------------------------------

### Execute Oracle Function via Shortcut Method

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Function

Use the executeFunction helper method to call Oracle functions with parameter bindings.

```php
$result = DB::executeFunction('function_name(:binding_1,:binding_n)', [':binding_1' => 'hi', ':binding_n' => 'bye'], PDO::PARAM_LOB)
```

--------------------------------

### Manage Oracle Sequences with DB::getSequence

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Sequence

Provides methods to create, drop, and retrieve values from Oracle sequences.

```php
$sequence = DB::getSequence();
// create a sequence
$sequence->create('seq_name');
// drop a sequence
$sequence->drop('seq_name');
// next value
$sequence->nextValue('seq_name');
// current value
$sequence->currentValue('seq_name');
$sequence->lastInsertId('seq_name');
// check if exists
$sequence->exists('seq_name');
```

--------------------------------

### Create Oracle Procedure with SQL

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Procedure

Defines a basic stored procedure using standard SQL syntax.

```sql
CREATE OR REPLACE PROCEDURE myproc(p1 IN NUMBER, p2 OUT NUMBER) AS
BEGIN
    p2 := p1 * 2;
END;
```

--------------------------------

### Perform Database Operations with Eloquent

Source: https://github.com/yajra/laravel-oci8/wiki/Stand-alone-Usage

Demonstrates using the configured database connection to interact with Eloquent models.

```php
include 'database.php';

// Create the User model
class User extends Illuminate\Database\Eloquent\Model {
    public $timestamps = false;
}

// Grab a user with an id of 1
$user = User::find(1);

echo $user->toJson(); die();
```

--------------------------------

### Configure Oracle Database Connection

Source: https://github.com/yajra/laravel-oci8/wiki/Installation

Define the oracle connection settings in your database configuration file.

```php
    'oracle' => [
        'driver'        => 'oracle',
        'tns'           => env('DB_TNS', ''),
        'host'          => env('DB_HOST', ''),
        'port'          => env('DB_PORT', '1521'),
        'database'      => env('DB_DATABASE', ''),
        'username'      => env('DB_USERNAME', ''),
        'password'      => env('DB_PASSWORD', ''),
        'charset'       => env('DB_CHARSET', 'AL32UTF8'),
        'prefix'        => env('DB_PREFIX', ''),
        'prefix_schema' => env('DB_SCHEMA_PREFIX', ''),
    ],
```

```php
'oracle' => [
    'driver' => 'oracle',
    'host' => 'oracle.host',
    'port' => '1521',
    'database' => 'xe',
    'service_name' => 'sid_alias',
    'username' => 'hr',
    'password' => 'hr',
    'charset' => '',
    'prefix' => '',
]
```

--------------------------------

### Configure Oracle User Provider

Source: https://github.com/yajra/laravel-oci8/blob/master/README.md

Update the auth.php configuration file to use the oracle driver for user authentication.

```php
'providers' => [
    'users' => [
        'driver' => 'oracle',
        'model' => App\User::class,
    ],
]
```

--------------------------------

### Consume Oracle Cursor via Query Builder and PDO

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Cursor

Demonstrates basic retrieval of a cursor result set using Laravel's Query Builder and standard PDO.

```php
// via Query Builder
return DB::select("SELECT mycursor(5) AS mfrc FROM dual");

// via PDO
$pdo = DB::getPdo();
$stmt = $pdo->prepare("SELECT mycursor(5) AS mfrc FROM dual");
$result = $stmt->execute();
return $stmt->fetchAll(PDO::FETCH_OBJ);
```

--------------------------------

### Configure PHPStan Extension

Source: https://github.com/yajra/laravel-oci8/blob/master/README.md

Register the OCI8 extension in your phpstan.neon file to enable static analysis for DB methods.

```neon
includes:
    - vendor/yajra/laravel-oci8/extension.neon
```

--------------------------------

### Register OCI8 Service Provider

Source: https://github.com/yajra/laravel-oci8/wiki/Installation

Add this class to the providers array in your config/app.php file.

```php
Yajra\Oci8\Oci8ServiceProvider::class
```

--------------------------------

### Create Oracle Procedure via Laravel Migrations

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Procedure

Executes raw SQL commands within Laravel migration up and down methods to manage stored procedures.

```php
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $command = "
            CREATE OR REPLACE PROCEDURE myproc(p1 IN NUMBER, p2 OUT NUMBER) AS
            BEGIN
                p2 := p1 * 2;
            END;
        ";

        DB::connection()->getPdo()->exec($command);
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        $command = "DROP PROCEDURE myproc";
        DB::connection()->getPdo()->exec($command);
    }
```

--------------------------------

### Querying BLOB fields

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Blob

Retrieving BLOB data directly as a value instead of an OCI-Lob object.

```php
$data = DB::table('mylobs')->get();
foreach ($data as $row) {
    echo $row->blobdata . '<br>';
}
```

--------------------------------

### Set Oracle Session Date Format

Source: https://github.com/yajra/laravel-oci8/wiki/Date-Formatting

Configures the session-level date format for Oracle database connections.

```php
// set oracle session date and timestamp format
DB::setDateFormat('MM/DD/YYYY');
```

--------------------------------

### Saving BLOB via Eloquent model

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Blob

Assigning binary data directly to model attributes and saving the record.

```php
Route::post('save-post', function()
{
    $post = new Post;
    $post->title            = Input::get('title');
    $post->company_id       = Auth::user()->company->id;
    $post->slug             = Str::slug(Input::get('title'));
    // set binary field (content) value directly using model attribute
    $post->content          = Input::get('content');
    $post->save();
});
```

--------------------------------

### Manage Oracle Triggers with DB::getTrigger

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Trigger

Use the trigger instance to automate sequence-based auto-incrementing or to remove triggers from the database.

```php
$trigger = DB::getTrigger();
// create an auto-increment trigger
$trigger->autoIncrement($table, $column, $triggerName, $sequenceName);
// drop a trigger
$trigger->drop($triggerName);
```

--------------------------------

### Register Service Provider

Source: https://github.com/yajra/laravel-oci8/blob/master/README.md

Add the service provider to the providers array in config/app.php for Laravel versions below 5.5.

```php
Yajra\Oci8\Oci8ServiceProvider::class,
```

--------------------------------

### Configure Eloquent Model for Custom Sequence

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Sequence

Enables automatic primary key incrementing by defining a public $sequence property on the Eloquent model.

```php
class User extends Model {
    public $sequence = 'user_id_seq';
}

$producer = new User();
$producer->name = 'Doe, John M.';
$producer->save();

dump($producer->id); // should be 1 if this is the first entry.
```

--------------------------------

### Create Table with Auto-Increment

Source: https://github.com/yajra/laravel-oci8/wiki/Auto-Increment-Support

Defines a table with an auto-incrementing primary key using Schema Builder.

```php
Schema::create('posts', function($table)
{
    $table->increments('id');
    $table->string('title');
    $table->string('slug');
    $table->text('content');
    $table->timestamps();
});
```

--------------------------------

### Define OracleEloquent Model with BLOB fields

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Eloquent

Extend the OracleEloquent class and specify BLOB fields in the $binaries array.

```php
use Yajra\Oci8\Eloquent\OracleEloquent as Eloquent;

class Post extends Eloquent {

    // 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;

}
```

--------------------------------

### Execute Stored Procedure with Cursor Binding

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Cursor

Uses a transaction to execute a stored procedure, binding a cursor parameter and fetching results using native OCI8 functions.

```php
$sql = "begin sgc.pintegracaomodoffline.ListaCidade(:pTabResultado, :pCodRetorno, :pMsgRetorno); end;"

return DB::transaction(function($conn) use ($sql){
    $pdo = $conn->getPdo();
    $stmt = $pdo->prepare($sql);

    $stmt->bindParam(':pTabResultado', $lista, PDO::PARAM_STMT);
    $stmt->bindParam(':pCodRetorno', $cod, PDO::PARAM_INT);

    $stmt->bindParam(':pMsgRetorno', $text, PDO::PARAM_STR, 100);
    $stmt->execute();

    oci_execute($lista, OCI_DEFAULT);
    oci_fetch_all($lista, $array, 0, -1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC );
    oci_free_cursor($lista);

    return $array;
});
```

--------------------------------

### Save BLOB data via Model

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Eloquent

Assign binary data directly to the model attribute before calling save().

```php
Route::post('save-post', function()
{
    $post = new Post;`
    $post->title            = Input::get('title');
    $post->company_id       = Auth::user()->company->id;
    $post->slug             = Str::slug(Input::get('title'));
    // set binary field (content) value directly using model attribute
    $post->content          = Input::get('content');
    $post->save();
});
```

--------------------------------

### Execute Oracle Procedure Manually

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Procedure

Uses PDO to prepare and execute a stored procedure call with bound parameters.

```php
$pdo = DB::getPdo();
$p1 = 8;

$stmt = $pdo->prepare("begin myproc(:p1, :p2); end;");
$stmt->bindParam(':p1', $p1, PDO::PARAM_INT);
$stmt->bindParam(':p2', $p2, PDO::PARAM_INT);
$stmt->execute();

return $p2; // prints 16
```

--------------------------------

### DB::executeFunction

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Function

Executes an Oracle stored function using the provided binding parameters and optional PDO parameter types.

```APIDOC
## DB::executeFunction

### Description
Executes a stored Oracle function with support for parameter binding.

### Signature
`DB::executeFunction(string $function, array $bindings, int $type = PDO::PARAM_STR)`

### Parameters
- **function** (string) - Required - The function name and binding placeholders (e.g., 'myfunc(:x)').
- **bindings** (array) - Required - An associative array of binding keys and values.
- **type** (int) - Optional - The PDO parameter type (defaults to PDO::PARAM_STR).

### Example
```php
$result = DB::executeFunction('myfunc(:x)', [':x' => 2]);
```
```

--------------------------------

### Execute Oracle Function via Query Builder and PDO

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Function

Invoke stored functions using standard Laravel Query Builder or raw PDO statements.

```php
// via Query Builder
$result = DB::selectOne("select myfunc(2) as value from dual");
return $result->value; // prints 6

// via PDO
$pdo = DB::getPdo();
$x = 2;

$stmt = $pdo->prepare("begin :y := myfunc(:x); end;");
$stmt->bindParam(':y', $y);
$stmt->bindParam(':x', $x);
$stmt->execute();

return $y; // prints 6
```

--------------------------------

### Inserting records with BLOB

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Blob

Using the insertLob method to insert records containing BLOB data.

```php
$id = DB::table('mylobs')->insertLob(
    array('name' => 'Insert Binary Test'),
    array('blobfield'=>'Lorem ipsum Minim occaecat in velit.')
    );
```

--------------------------------

### Create Oracle Stored Function

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Function

Define a stored function in SQL*Plus or SQL Developer before calling it from PHP.

```sql
CREATE OR REPLACE FUNCTION myfunc(p IN NUMBER) RETURN NUMBER AS
BEGIN
    RETURN p * 3;
END;
```

--------------------------------

### Create Oracle PL/SQL Function Returning Cursor

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Cursor

Defines a stored function that returns a SYS_REFCURSOR based on a provided input parameter.

```sql
-- Create the PL/SQL stored function as:
CREATE OR REPLACE FUNCTION mycursor(p1 IN NUMBER) RETURN SYS_REFCURSOR AS
  rc SYS_REFCURSOR;
BEGIN
  OPEN rc FOR SELECT city FROM locations WHERE ROWNUM < p1;
  RETURN rc;
END;
```

--------------------------------

### Inserting BLOB via transaction

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Blob

Manually inserting a BLOB using a database transaction and PDO parameter binding.

```php
DB::transaction(function($conn){
    $pdo = $conn->getPdo();
    $sql = "INSERT INTO mylobs (id, blobdata)
        VALUES (mylobs_id_seq.nextval, EMPTY_BLOB())
        RETURNING blobdata INTO :blob";
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':blob', $lob, PDO::PARAM_LOB);
    $stmt->execute();
    $lob->save('blob content');
});
```

--------------------------------

### Updating records with BLOB

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Blob

Using the updateLob method to update existing records containing BLOB data.

```php
$id = DB::table('mylobs')->whereId(1)->updateLob(
    array('name'=>'demo update blob'),
    array('blobfield'=>'blob content here')
    );
```

--------------------------------

### autoIncrement($table, $column, $triggerName, $sequenceName)

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Trigger

Creates an auto-increment trigger for a specified table and column.

```APIDOC
## autoIncrement($table, $column, $triggerName, $sequenceName)

### Description
Creates an auto-increment trigger for the given table and column using the specified trigger and sequence names.

### Parameters
- **table** (string) - The name of the table.
- **column** (string) - The name of the column to auto-increment.
- **triggerName** (string) - The name of the trigger to create.
- **sequenceName** (string) - The name of the sequence to associate with the trigger.
```

--------------------------------

### DB::getTrigger()

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Trigger

Retrieves the trigger manager instance to perform operations on Oracle database triggers.

```APIDOC
## DB::getTrigger()

### Description
Returns an instance of the trigger manager to perform operations such as creating or dropping triggers.

### Usage
```php
$trigger = DB::getTrigger();
```
```

--------------------------------

### Insert Record and Retrieve ID

Source: https://github.com/yajra/laravel-oci8/wiki/Auto-Increment-Support

Inserts a record into an Oracle table and returns the generated ID, specifying the column name if it is not 'id'.

```php
  $id = DB::connection('oracle')->table('users')->insertGetId(
      array('email' => 'john@example.com', 'votes' => 0), 'userid'
  );
```

--------------------------------

### drop($triggerName)

Source: https://github.com/yajra/laravel-oci8/wiki/Oracle-Trigger

Drops an existing Oracle database trigger.

```APIDOC
## drop($triggerName)

### Description
Drops an existing trigger from the database.

### Parameters
- **triggerName** (string) - The name of the trigger to drop.
```

--------------------------------

### Update JSON path in Oracle

Source: https://github.com/yajra/laravel-oci8/blob/master/README.md

Updates a specific key within a JSON column using Laravel's fluent query builder syntax. Requires Oracle 19c or higher and supports only one path update per call.

```php
DB::table('users')
    ->where('id', $id)
    ->update(['options->profile->settings->theme' => 'dark']);
```
Tokens:
Raw