[Resolved] getting the a sequence number

[amrigo]

I need to get the generated sequence number from a sequence
after an insert into a table is done. (using php/oracle)

To associate this sequence number to a session variable, so i can pass this value, between others scripts that will insert in others tables related to the first table.

I am not sure i have another unique id in the table to make a reference

The best situation would be to get the sequence at the moment it is generated by the insert, but how can it be done?

Thank´s in advance

[lazzerous]

I'm guessing sequence is a field in your table...

is it incrementing with each insert, or non-sequential?

do you have an id field?

id int(11) unique atuo_increment

if you do, or have any field that increments with each new row, you could run this query after your insert:

select max(id) from table;

this will grab the most recent entry.

-=Lazz=-

[amrigo]

it is an incremental field unique for each record, but a lot of people could be using the system at the same time, i am aware that using the max function will mix the sequences that these users are creating! or max will always get the exact value for the sequence at the exact moment that the insert is comitted

I would like something like:
$sql "INSERT INTO TABLE (SEQ, DATA) VALUES (SEQ.nextval, '$data')";
$_SESSION['record_sequence'] = SEQ.nextval ;😕

[Sxooter]

Is there a SEQUENCE.currval?

[amrigo]

i am really newbye with oracle i dont know what is SEQUENCE.currval

But a sequence number is gebnerated by the insert ...

can i get this value with ocidefinebyname or ociresult?

[amrigo]

ok reading otn i saw currval is the current value , i need to make a new select below the insert or can i assign sequence.currval
inside the insert to any php variable?

[Sxooter]

I'm actually a postgresql user. I'll translate from Postgresql to Oracle here, see if it works...

The way a sequence works, is that inside the current session, SEQ.currval has no value until SEQ.nextval or SEQ.setval has been called.

SEQ.setval is NOT transactionally safe, and should only be accessed when creating the initial schema / data set load. once up and running it shouldn't be messed with.

So, you can do it a couple of ways:

1: first select the SEQ.nextval yourself, then build that number into your inserts.
2: Run the first insert, then select SEQ.currval and use that in subsequent inserts / updates.

Both are transactionally safe methods, it's kinda up to you which you think is a better fit for your style / the database your working on.

Basically run a query like this, after the insert:

select SEQ.currval as id

then use the ora_ functions in php (get_value??? I'm not familiar with the Oracle syntax, just guessing.) to grab the id and use it in all your subsequent inserts.

[amrigo]

I plan to do it the second way, oracle an postgresql are similar in many things we do use both here, thank´s for mention the existence of a currval value wich i didn´t knew before ....

Cheers

[Sxooter]

The currval thing was just a total wild guess, since you had nextval, and reminded so much of postgresql, I just figured...

Glad you got it working.

🙂

[amrigo]

In Oracle there is this function OCIBindByName, so i need define the numbers of digits that will return to this

function like below:

define('NUMDIGITS', 9);

//this comes inside an sql clausule
returning sequencia into :seq_atual

$seq_atual = OCIBindByName($sql, ':SEQ_ATUAL', $acdt_sequencia, NUMDIGITS);

and work fine to solve my problem

We do use both dbs here would be fine to discover the same way to do it in Postgresql

Cheers

[Sxooter]

I'll post a new topic here in database for ya on it...

[amrigo]

Great!

Cheers

[Sxooter]

here