This discussion is archived
3 Replies Latest reply: Apr 14, 2011 12:31 AM by 633274 RSS

need to perfom commit 2 times on CLOB column

Keng Heng. Chan Newbie
Currently Being Moderated
Hi All, I've to perform 2 times of commit for CLOB columns for a update transaction. Anybody got any issues on this topic ?

My svr spec:
DB Std 11.2.0.1.0
PHP 5.3.6
Linux 64 bits.

Updated on 13-April-2011
I've tested a few round, and it is strange, those varcharr2 columns, can be updated at first commit, three other clob columns only updated after second time commit from the php form.

Thought oracle should apply all update to the columns once received the commit signal.

Edited by: sqltech on Apr 13, 2011 2:59 PM
  • 1. Re: need to perfom commit 2 times on CLOB column
    633274 Newbie
    Currently Being Moderated
    It may be because English is not your mother tongue, or perhaps it's because it is not my mother tongue, but I can't understand your question at all...

    Do you want to commit twice (?) or do you want to nest transactions? Do you already have some code or tables?
  • 2. Re: need to perfom commit 2 times on CLOB column
    Keng Heng. Chan Newbie
    Currently Being Moderated
    Hi, I will describe more here. I've created 2 php page, first page is Form Entry by user, when user click submit, it will call second php script to perform update to oracle table.
    In the first Form, I've two fields, one will update to a varchar2 column, second field will update a clob column.

    When I click submit at first time, I check the oracle db, it will update the varchar2 column, NOT clob column, and if I click submit at second time on the same page, I check the db again, the clob column is updated.

    below are my second php page code:
    ======end=========
    <?php
    header("Content-Type: text/html; charset=UTF-8");
    extract($_POST);
    $con = oci_connect('usera', 'paswd', 'orcl', 'AL32UTF8');
    $lob1 = oci_new_descriptor($con, OCI_D_LOB);
    $SQL = "
    update table1
    set col_varchar2 = :col_varchar2,
         col_clob = EMPTY_CLOB()
    where code = :code
    RETURNING col_clob INTO :col_clob
    ";
    $statement = oci_parse($con, $SQL);
    oci_bind_by_name($statement, ':code', $code);
    oci_bind_by_name($statement, ':col_varchar2', $col_varchar2);
    oci_bind_by_name($statement, ':col_clob', $lob1, -1, OCI_B_CLOB);
    oci_execute($statement, OCI_DEFAULT);
    if($lob1->save($col_clob)){
         oci_commit($con);
         echo "Update Success Content\n";
    }else{
         oci_rollback($con);
         echo "Couldn't upload Content\n";
    }
    $lob1->free();
    oci_free_statement($statement);
    oci_close($con);
    ?>
    ======end=========
  • 3. Re: need to perfom commit 2 times on CLOB column
    633274 Newbie
    Currently Being Moderated
    First of all, see the warning at the extract() manual page <http://es2.php.net/extract>:
    Do not use extract() on untrusted data, like user input (i.e. $_GET, $_FILES, etc.).
    From your description, I'd dare say that the form is not always sending the data you think it sends. Your code does not perform any data validation so such case will go unnoticed. Some random ideas you can try:

    - Check whether your calls to oci_* functions are successful or not.
    - Use var_dump() to inspect your variables prior to insertion.
    - Build a test page with data hard-coded into variables.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points