phporacle-databaseoracle-call-interface

OCI_INVALID_HANDLE always returned for OCI-Lob::save()


I'm getting the following warning from my php: "OCI-Lob::save(): OCI_INVALID_HANDLE" for lines 38, 39

<?php
  $lob_e = oci_new_descriptor($connection, OCI_D_LOB);
  $lob_w = oci_new_descriptor($connection, OCI_D_LOB);


  $stid = oci_parse($connection, "
            UPDATE RES_LICENCE_TEXT T
          SET
            LICENCE_TEXT_EN =   EMPTY_CLOB(),
            LICENCE_TEXT_CY =   EMPTY_CLOB(),
            updated_by = :my_updated_by,
            updated_date = SYSDATE

        WHERE T.hall_Seq = :my_hall_seq
        and :my_academic_year = academic_year
        and :my_app_period = app_period
        and step_name = 'assoc_instr'
        and :offer_id = offer_id

      RETURNING LICENCE_TEXT_EN,LICENCE_TEXT_CY INTO :CLOBDATA_E, :CLOBDATA_W");

    oci_bind_by_name($stid, ':MY_ACADEMIC_YEAR', $_SESSION['chosen_year']);
    oci_bind_by_name($stid, ':MY_APP_PERIOD',  $_SESSION['chosen_app_period']);
  oci_bind_by_name($stid, ':offer_id',      $offer_id);
    oci_bind_by_name($stid, ':MY_HALL_SEQ',      $_SESSION['hall_seq']);

    oci_bind_by_name($stid, ':CLOBDATA_E',  $lob_e, -1, OCI_B_CLOB);
    oci_bind_by_name($stid, ':CLOBDATA_W',  $lob_w, -1, OCI_B_CLOB);
    oci_bind_by_name($stid, ':my_updated_by',  $_SESSION['loguser']);


    $success = oci_execute($stid, OCI_DEFAULT);

    if (!$success) {
        oci_rollback($connection);
        $text_insertion_error = 0;
    } else {
        $lob_e->save($html_e_string);
        $lob_w->save($html_w_string);
        oci_commit($connection);
        $text_insertion_error = 1;
    }

    oci_free_descriptor($lob_e);
    oci_free_descriptor($lob_w);
    oci_free_statement($stid);


?>

The lines in question are: $lob_e->save($html_e_string); and $lob_w->save($html_w_string); Can I get some help with this please? Cheers


Solution

  • Your WHERE clause is not matching any rows. The statement successfully matches 0 rows so the else branch is followed, but the LOB locators aren't associated with a row and are unusable.

    Try changing the logic to:

    if (!$success) {
        oci_rollback($connection);
        $text_insertion_error = 0;
    } else if (oci_num_rows($stid) === 1) {
        $lob_e->save($html_e_string);
        $lob_w->save($html_w_string);
        oci_commit($connection);
        $text_insertion_error = 1;
    }
    

    You almost certainly want to add an extra else branch (ie. when oci_num_rows($stid) > 1) to throw an error.