0

I'm writing a php code to upload a csv file into an Oracle DB by mean of php oci. I want an atomic insertion of all the rows of the file i.e if one row has missing/wrong field then whole csv file must not be inserted in DB. I know I can achieve this using INSERT ALL statement, but I can't make it work with parameter binding. E.g. oci_bind_by_name only works with INSERT of a single line, it doesn't work for me with INSERT ALL or INSERT of multiple rows.

Do you have an alternative solution or a working code snippet?

Many many thanks

APC
  • 144,005
  • 19
  • 170
  • 281
mp94
  • 47
  • 6

2 Answers2

1

For performance, consider using oci_bind_array_by_name() and inserting columns individually. This may not suit all data. There is an example in "Array Binding and PL/SQL Bulk Processing" in Oracle's PHP OCI8 book http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html

$a = array('abc', 'def', 'ghi', 'jkl');
$s = oci_parse($c, "begin mypkg.myinsproc(:a); end;"); 
oci_bind_array_by_name($s, ":a", $a, count($a), -1, SQLT_CHR); 
oci_execute($s);

Other languages (e.g. Python) have an "executemany()" capability, but that is not available in PHP OCI8.

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
  • 1
    So essentially you would write an PL/SQL procedure to do the job for you, instead of handlig the inserts in php..right? – mp94 Jun 06 '19 at 06:50
0

In general, it's good practice to verify that the data is correct before trying to insert it into your DB, i.e. verify that there are no missing rows/incorrect fields before sending the data over the wire. This will help you reduce egress and potential high cost of significant failure rates.

If that's not a viable option, you could use a combination of the OCI_NO_AUTO_COMMIT flag in the call to oci_execute() and then check the return value. If oci_execute() fails, then you can call oci_rollback(). A code example is provided here: https://www.php.net/manual/en/function.oci-rollback.php#refsect1-function.oci-rollback-examples

Ling Toh
  • 2,404
  • 1
  • 16
  • 24
  • 1
    It's true that it's good to validate data before inserting, but some (non PHP !) tools can help recover from this, and save the cost of you doing it yourself, for example see batcherrors in https://blogs.oracle.com/opal/efficient-and-scalable-batch-statement-execution-in-python-cx_oracle – Christopher Jones Jun 04 '19 at 06:32