3 Replies Latest reply: Apr 14, 2011 2:31 AM by 633274 RSS

    need to perfom commit 2 times on CLOB column

    Keng Heng. Chan
      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
      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
          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
            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:
            header("Content-Type: text/html; charset=UTF-8");
            $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);
                 echo "Update Success Content\n";
                 echo "Couldn't upload Content\n";
            • 3. Re: need to perfom commit 2 times on CLOB column
              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.