yajra / laravel-sql-loader
Oracle SQL Loader for Laravel
Fund package maintenance!
yajra
Patreon
www.paypal.me/yajra
Installs: 1 044
Dependents: 0
Suggesters: 0
Security: 0
Stars: 9
Watchers: 1
Forks: 3
Open Issues: 0
Requires
- php: ^8.2
- ext-oci8: >=3.0.1
- ext-pdo: *
- illuminate/database: ^10.0|^11.0
- illuminate/filesystem: ^10.0|^11.0
- illuminate/support: ^10.0|^11.0
- yajra/laravel-oci8: ^10.6|^11.3
Requires (Dev)
- larastan/larastan: ^2.9.7
- laravel/pint: ^1.16
- mockery/mockery: ^1.6.12
- orchestra/testbench: ^9.1.1
- pestphp/pest: ^2.34.7
- pestphp/pest-plugin-laravel: *
- rector/rector: ^1.1
README
A Laravel package that allows you to easily load data into Oracle database using sqlldr
.
Requirements
- Oracle Instant Client with Tools Package
- Laravel 10.x or higher
- Laravel OCI8 10.x or higher
Prerequisites
- Before you can use this package, you need to install the Oracle Instant Client with Tools Package. You can download the package from the Oracle website.
- You should also take note of the path where the
sqlldr
executable is located.- For example, if you installed the Oracle Instant Client with Tools Package in
/usr/local/oracle/instantclient_19_6
, thesqlldr
executable will be located in/usr/local/oracle/instantclient_19_6/sqlldr
. - You can also add the path to the
sqlldr
executable to your system's PATH environment variable. - You can also set the path to the
sqlldr
executable in the.env
file using theSQL_LOADER_PATH
key. - You can also set the path to the
sqlldr
executable in theconfig/sql-loader.php
file using thesqlldr
key. - You can symlink the
sqlldr
executable to/usr/local/bin
using the following command:sudo ln -nfs /usr/local/oracle/instantclient_19_6/sqlldr /usr/local/bin/sqlldr
- For example, if you installed the Oracle Instant Client with Tools Package in
- Knowledge of how to use
sqlldr
is also required. You can read the documentation here.
Installation
You can install the package via composer:
composer require yajra/laravel-sql-loader:^1.0
Quick Start
Below is a quick example of how to use the package:
Route::get('sql-loader', function () { Schema::dropIfExists('employees'); Schema::create('employees', function ($table) { $table->id(); $table->string('name'); $table->integer('dept_id'); $table->timestamps(); }); Yajra\SQLLoader\CsvFile::make(database_path('files/employees.csv'), 'w') ->headers(['name', 'dept_id', 'created_at', 'updated_at']) ->insert([ ['John Doe', 1, now(), now()], ['Jane Doe', 2, now(), now()], ['John Doe', 1, now(), now()], ['Jane Doe', 2, now(), now()], ]) ->close(); $loader = Yajra\SQLLoader\SQLLoader::make(); $loader->inFile(database_path('files/employees.csv')) ->dateFormat('YYYY-MM-DD HH24:MI:SS') ->withHeaders() ->into('employees') ->execute(); return DB::table('employees')->get(); });
Execution Mode
The default execution mode is Mode::APPEND
. The package supports the following execution mode:
Yajra\SQLLoader\Mode::INSERT
- Insert data into table.Yajra\SQLLoader\Mode::APPEND
- Append data to table.Yajra\SQLLoader\Mode::REPLACE
- Replace data in table.Yajra\SQLLoader\Mode::TRUNCATE
- Truncate table then insert data.
Date Formats
The SQL*Loader default date format is YYYY-MM-DD"T"HH24:MI:SS."000000Z"
to match Laravel's model date serialization.
You can change the date format using the dateFormat
method.
$loader->dateFormat('YYYY-MM-DD HH24:MI:SS');
Available Methods
Options
You can pass additional options to the sqlldr
command using the options
method.
$loader->options(['skip=1', 'load=1000']);
Input File(/s)
You can set the input file to use for the SQL*Loader command using the inFile
method.
$loader->inFile(database_path('files/employees.csv'));
You can also set multiple input files.
$loader->inFile(database_path('files/employees.csv')) ->inFile(database_path('files/departments.csv')),
Mode
You can set the execution mode using the mode
method.
$loader->mode(Yajra\SQLLoader\Mode::TRUNCATE);
Into Table
You can set the table to load the data into using the into
method. This method accepts the following parameters:
table
- Specifies the table into which you load data.columns
- The field-list portion of a SQL*Loader control file provides information about fields being loaded.terminatedBy
- The terminated by character.enclosedBy
- The enclosed by character.trailing
- set totrue
to configure SQL*Loader to treat missing columns as null columns.formatOptions
- Specifying Datetime Formats At the Table Level.when
- Specifies a WHEN clause that is applied to all data records read from the data file.
$loader->into('employees', ['name', 'dept_id']);
With Headers
Using withHeaders
will skip the first row of the CSV file.
Important
withHeaders
must be called before theinto
method.- This method assumes that the headers are the same as the table columns.
- Non-existent columns will be flagged as
FILLER
. - Date headers will be automatically detected and data type is appended in the control file.
- Date values must follow the default date format. If not, use the
dateFormat
method. - If the headers are different from the table columns, you should define the
columns
in theinto
method.
Building a CSV File from Eloquent Collection
$users = User::all(); Yajra\SQLLoader\CsvFile::make(database_path('files/users.csv'), 'w') ->headers(array_keys($users->first()->toArray())) ->insert($users->toArray()) ->close();
Loading CSV File with Headers
Load users from oracle
to backup
database connection.
$loader->inFile(database_path('files/users.csv')) ->withHeaders() ->mode(Yajra\SQLLoader\Mode::TRUNCATE) ->connection('backup') ->into('users') ->execute();
Wildcard Path with Headers
When using a wildcard path, the first file is assumed to contain the headers. The succeeding files should not have headers or it will be reported as a bad record.
$loader->inFile(database_path('files/*.csv')) ->withHeaders() ->mode(Yajra\SQLLoader\Mode::TRUNCATE) ->into('employees') ->execute();
- employees-1.csv
name,dept_id
John Doe,1
Jane Doe,2
- employees-2.csv
John Doe,1
Jane Doe,2
Constants
In some cases, we need to insert constant values to the table. You can use the constants
method to set the constant value.
Important
constants
must be called before the into
method.
$loader->withHeaders() ->constants([ 'file_id CONSTANT 1', 'created_at EXPRESSION "current_timestamp(3)"', 'updated_at EXPRESSION "current_timestamp(3)"', ]) ->into('users');
Connection
You can set the connection name to use for the SQL*Loader command using the connection
method.
$loader->connection('oracle');
Disk
You can set the disk to use for the control file using the disk
method.
$loader->disk('local');
Logging
You can get the logs of the execution using the logs
method.
return nl2br($loader->logs());
Custom Control File
You can use a custom control file by passing the control file name to the as
method.
$loader->as('employees.ctl');
Execute
You can execute the SQL*Loader command using the execute
method.
$loader->execute();
You can also set the execution timeout in seconds. Default is 3600 seconds / 1 hr.
$loader->execute(60);
Execution Result
You can check if the execution was successful using the successfull
method.
if ($loader->successfull()) { return 'Data loaded successfully!'; }
Process Result
You can get the process result using the result
method.
$result = $loader->result();
Using array as data source
You can use an array as a data source by using begindData
method.
$loader = Yajra\SQLLoader\SQLLoader::make(); $loader->beginData([ ['John', 1], ['Jane', 1], ['Jim, K', 2], ['Joe', 2], ]) ->mode(Yajra\SQLLoader\Mode::TRUNCATE) ->into('employees', [ 'name', 'dept_id', ]) ->execute();
Available Configuration
You can publish the configuration file using the following command:
php artisan vendor:publish --provider="Yajra\SQLLoader\SQLLoaderServiceProvider" --tag="config"
Connection Config
You can set the connection name to use for the SQL*Loader command.
'connection' => env('SQL_LOADER_CONNECTION', 'oracle'),
SQL*Loader Path Config
You can set the path to the SQL*Loader executable.
'sqlldr' => env('SQL_LOADER_PATH', '/usr/local/bin/sqlldr'),
Disk Config
You can set the disk to use for the control file.
'disk' => env('SQL_LOADER_DISK', 'local'),
Credits
License
The MIT License (MIT). Please see License File for more information.