How to retrieve oracle nextval into variable right after it is assigned to some row in database

Question

0.00/5 (No votes)

See more:

Hi Everyone,

What I am trying to do is to generate unique ids for queries created by users.

I am using oracle sequence to assign the query id, which is primary key in the table.

After creating the query, I want to display the query id created, which is basically the sequence no.

Thank you..!!

What I have tried:

Firstly, displaying the sequence.currval will not guarantee right outpput because within that time other user can insert query into database.

Secondly, if I select the id that is created by x user exactly at y time, that might work. But that requires another query to be fired.

Is there any alternative way?

Posted 1-Nov-17 19:38pm

planetz

Updated 1-Nov-17 22:37pm

Add a Solution

Comments

GKP1992 2-Nov-17 2:31am

As far as I understand, you insert the query into a table with query id as the primary key. Is that correct? Also, which version of Oracle are you using?

planetz 2-Nov-17 3:04am

Yes GKP1992, that is correct. I am using oracle 10g.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

GKP1992 · Answer 1 · 2017-11-01T22:37:00

Solution 1

Hi,

You can use the returning clause to do that.

Example:

SQL

INSERT INTO {query_table} (query_id, query)
   VALUES (sequence.nextval, query_From_User)
RETURNING query_id into {a_defined_variable}

This will give you the id of the inserted record.

Posted 1-Nov-17 22:37pm

GKP1992

Comments

planetz 3-Nov-17 5:42am

this query has to be in a procedure. right? else where can I declare 'a_defined_variable'?

GKP1992 3-Nov-17 7:19am

I think it is called a block in Oracle. Goes like this

declare {var_name} {vartype} := {value};
begin
{write query that uses var here.}
end

How to retrieve oracle nextval into variable right after it is assigned to some row in database

1 solution

Solution 1

Add your solution here

Preview 0

How to retrieve oracle nextval into variable right after it is assigned to some row in database

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us