oracle SELECT ... FOR UPDATE with PHP

Question

I have the second consultation:

SELECT proxNum FROM orcamento FOR UPDATE;

UPDATE budget SET proxnum = proxnum + 1;

It serves so that I can capture the next available budget number and already do an update by adding 1 unit to that number. This should happen before the application when used by another user captures the same number.

The query works perfectly, the database used is oracle 11g.

However, I cannot apply this in php, as 2 queries are not accepted at once. I have tried using cursors using oci_new_cursor, but without success. Some code snippets I tried to adapt from examples from this site PHP oci_new_cursor Examples If anyone can help me please. I don’t know what to try anymore.

If I try to run the code, the following errors are displayed:

• Warning: oci_execute(): ORA-00911: invalid Character. Fatal error:
• Could not execute statement.

---

<?php
include "config.php"; //arquivo de configuração
//------------------------------------------------------------------------------------------------------//

$query2 = "
SELECT proxnum FROM orcamento FOR UPDATE of proxnum;
UPDATE orcamento SET proxnum = proxnum + 1; ";

$s2 = oci_parse($c, $query2);
if (!$s2) {
    $m2 = oci_error($c);
    trigger_error('Could not parse statement: ' . $m['message'], E_USER_ERROR);
} //prepara para a execução


$r2 = oci_execute($s2);
if (!$r2){
    $m2 = oci_error($s2);
    trigger_error('Could not execute statement: ' . $m['message'], E_USER_ERROR);
}// executa a consulta

?>

Answer 1

Have you considered using an Oracle Sequence?

You can create a Quence and then call her nextval, it auto-increments by itself:

Ex: Create a seat in the bank (documentation)

CREATE SEQUENCE seq_orcamento
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

Then you call her nextval command on your internet:

INSERT INTO orcamento (nr_orcamento, ...) values (seq_orcamento.nextval, ...)

This way you don’t have to worry about doing the increment and have the guarantee to always have a valid Quence, because it increases itself even without committing.