4 Replies Latest reply: Oct 2, 2012 9:12 PM by cj RSS

    How to store the value a oracle sequence?

    952056
      Regards;

      I have a Table with a ID column, this column is the Primary Key. I have a sequence for store the value of ID. The code what I use is the next:
      (Other form send the value of fields)

      $conn = oci_connect("USER","PASSWORD","SID");

      for($i=1;$i<=$_POST["hdnLine"];$i++)


      $strSQL = "INSERT INTO CAT_HOST_IP (ID_CAT_HOST_IP, HOST, IP, AUTORIZACION, COMENTARIOS)";
      $strSQL .="VALUES ";
      $strSQL .="(".S_CAT_HOST_IP.NEXTVAL.",'".$_POST["txtHost$i"]."', ";
      $strSQL .="'".$_POST["txtIP$i"]."','".$_POST["txtAutorizacion$i"]."', ";
      $strSQL .="'".$_POST["txtComentarios$i"]."') ";

      echo $strSQL. "<br>\n";

      $objParse = oci_parse($conn, $strSQL);
      $objExecute = oci_execute($objParse, OCI_DEFAULT);
      }
      oci_close($conn);

      When display $strSQL is this the result:

      INSERT INTO CAT_HOST_IP (ID_CAT_HOST_IP, HOST, IP, AUTORIZACION, COMENTARIOS)VALUES (S_CAT_HOST_IPNEXTVAL,'HOST', 'IP','SI', 'Comentario')

      I can't obtain the value of S_CAT_HOST_IPNEXTVAL. obviously the next message is displayed
      Warning: oci_execute() [function.oci-execute]: ORA-00984: column not allowed here in C:\AppServ\www\Multiple_Insert_CatHost02.php on line 55

      I hope can Help me. Thanks!
        • 1. Re: How to store the value a oracle sequence?
          cj
          I think you want something like:
          <?php
          
          /*
            drop table mytab;
            drop sequence mytab_seq;
            create table mytab (id number, data varchar2(20));
            create sequence mytab_seq;
          */
          
          $c = oci_connect("hr", "welcome", "localhost/XE");
          $s = oci_parse($c, "insert into mytab (id, data) values (mytab_seq.nextval, 'abc') returning id into :id");
          oci_bind_by_name($s, ":id", $id, 20, SQLT_CHR);
          oci_execute($s);
          echo "id is $id\n";
          
          ?>
          There are two other serious problems with your code:
          1. You need to filter the input values and not trust data passed in. Use your own filtering functions in combination with http://php.net/manual/en/book.filter.php
          2. You need to use bind variables for performance and security. Do not concatenate post data into a SQL statement.
          • 2. Re: How to store the value a oracle sequence?
            952056
            Thanks!

            I found this solution,

            <?
            $conn = oci_connect("monitordb","MONITORDB","ORCL");

            $sql = "SELECT S_CAT_HOST_IP.NEXTVAL FROM DUAL";
            $stmt = oci_parse($conn,$sql );
            oci_execute($stmt);
            oci_fetch($stmt);
            $val = oci_result($stmt, 'NEXTVAL');

            oci_free_statement($stmt);
            for($i=1;$i<=$_POST["hdnLine"];$i++)

            if($_POST["txtHost$i"] != "")

            $conn = oci_connect("USER","PASSWORD","SID");
            $strSQL = "INSERT INTO CAT_HOST_IP (ID_CAT_HOST_IP, HOST, IP, AUTORIZACION, COMENTARIOS)";
            $strSQL .="VALUES ";
            $strSQL .="(".$val.",'".$_POST["txtHost$i"]."', ";
            $strSQL .="'".$_POST["txtIP$i"]."','".$_POST["txtAutorizacion$i"]."', ";
            $strSQL .="'".$_POST["txtComentarios$i"]."') ";

            echo $strSQL. "<br>\n";
            $objParse = oci_parse($conn, $strSQL);
            $objExecute = oci_execute($objParse, OCI_DEFAULT);
            }
            }
            ?>
            • 3. Re: How to store the value a oracle sequence?
              952056
              Thanks, I appreciate your help. I try use your code, but I can't obtain the value of sequence, If I use the next line:

              $s = oci_parse($c, "INSERT INTO CAT_HOST_IP (ID_CAT_HOST_IP, HOST, IP, AUTORIZACION, COMENTARIOS) VALUES (S_CAT_HOST_IP.NEXTVAL, $_POST["txtHost$i"], $_POST["txtIP$i"], $_POST["txtAutorizacion$i"],$_POST["txtComentarios$i"]) ");

              The result is:

              INSERT INTO CAT_HOST_IP (ID_CAT_HOST_IP, HOST, IP, AUTORIZACION, COMENTARIOS) VALUES (S_CAT_HOST_IP.NEXTVAL, 'A', 'A', 'A', 'A')

              The sentence not display the value of sequence. The sentence S_CAT_HOST_IP.NEXTVAL It should display the value of the sequence. I will follow your suggest for better code.

              Thank you!
              • 4. Re: How to store the value a oracle sequence?
                cj
                What errors are you seeing?

                Do you want each row to use a new sequence value? Your example that fetches the sequence first would reuse the value. What behavior do you want.