Error inserting BLOB into Oracle

Question

I am using this wrapper as the Oracle PDO in PHP is experimental: https://github.com/yajra/pdo-via-oci8

Here is the PHP code to insert a BLOB that works in MySQL but not yet in Oracle:

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();
}

In oracle I get this exception: 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)

Any ideas what I am doing wrong?

sstan · Accepted Answer · 2015-06-26 02:01:30Z

1

When inserting a new row in Oracle with a BLOB column, you need to initialize the BLOB column to an empty BLOB before being able to set the bytes.

I am not familiar with the PHP side of things, but basically, your insert statement will have to become something like this:

INSERT INTO packets (packet)
VALUES (empty_blob())
RETURNING packet INTO :packet

answered Jun 26, 2015 at 2:01

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rukiman Over a year ago

I tried that but still cannot get the blob in there. It is zero bytes.

sstan Over a year ago

Sorry, but the next step involves more in-depth knowledge of PHP and the specific library you are using, which I don't have. I only know how to address your specific error from your question (invalid LOB locator), which is that you need to create an empty blob first, so that you can then bind to it. And then, as a last step, you can push your bytes in. Sorry that I can't help with that last part. But hopefully you are now closer to a resolution.

Andreas Macher · Accepted Answer · 2015-11-04 12:26:34Z

0

It helps to allocate the BLOB with the DBMS_LOB_CREATETEMPORARY Procedure.

DECLARE
  v_blob BLOB;
BEGIN
  DBMS_LOB_CREATETEMPORARY(v_blob, TRUE);
  v_raw_var := 'abcde';
  DBMS_LOB.APPEND(v_blob, v_raw_var);

  INSERT INTO myTab (Key, MyBlobField)
  VALUES (1, v_blob);

  COMMIT;
END;

answered Nov 4, 2015 at 12:26

Andreas Macher

1

Comments

celsowm · Accepted Answer · 2019-06-30 17:46:04Z

0

<?php

   include '../conexao_oracle.php';

   $db->beginTransaction(); // VERY IMPORTANT !
   $stmt = $db->prepare(
       "INSERT INTO packets (packet) ".
       "VALUES (EMPTY_BLOB()) ".
       "RETURNING packet INTO :packet");
   $stmt->bindParam(':packet', $blob, PDO::PARAM_LOB);
   $blob = fopen($nist->getActualFile(), 'rb');
   $stmt->execute();
   $db->commit();

answered Jun 30, 2019 at 17:46

celsowm

47410 gold badges43 silver badges64 bronze badges

Collectives™ on Stack Overflow

Error inserting BLOB into Oracle

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related