On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,605 Users
  • 2,269,766 Discussions
  • 7,916,793 Comments

Discussions

How to store the value a oracle sequence?

952056
952056 Member Posts: 16
edited Oct 2, 2012 10:12PM in PHP
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!

Answers

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,657 Employee
    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.
  • 952056
    952056 Member Posts: 16
    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);
    }
    }
    ?>
  • 952056
    952056 Member Posts: 16
    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!
  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,657 Employee
    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.
This discussion has been closed.