Home » SQL & PL/SQL » SQL & PL/SQL » How to get the new ID after insert a record?
How to get the new ID after insert a record? [message #17026] Tue, 21 December 2004 23:41 Go to next message
rushing
Messages: 135
Registered: January 2004
Location: Beijing,China
Senior Member
Using a sequence and trigger to create records' ID automatually, how to get the ID after insert a record?

For example,
create table tb1 ID int,name varchar(20);
(ID is created by trigger)

insert into tb1 (name) values ("a");
How to get the ID of this new record? We have to consider that there are many users on several computers to insert at the same time.
Re: How to get the new ID after insert a record? [message #17028 is a reply to message #17026] Wed, 22 December 2004 00:49 Go to previous messageGo to next message
Frank Naude
Messages: 4596
Registered: April 1998
Senior Member
You can use the RETURNING clause. Example:

INSERT INTO tb1 (name) VALUES ("a") 
   RETURNING id INTO :bind_var1;


Best regards.

Frank
Re: How to get the new ID after insert a record? [message #17047 is a reply to message #17028] Wed, 22 December 2004 15:56 Go to previous messageGo to next message
rushing
Messages: 135
Registered: January 2004
Location: Beijing,China
Senior Member
Run and error occured.
What is bind_var1?
How to define it?
Re: How to get the new ID after insert a record? [message #17049 is a reply to message #17047] Wed, 22 December 2004 16:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
scott@ORA92> -- create table:
scott@ORA92> create table tb1
  2    (ID int,
  3  	name varchar(20))
  4  /

Table created.


scott@ORA92> -- create sequence:
scott@ORA92> create sequence tb1_id_seq
  2  /

Sequence created.


scott@ORA92> -- create trigger:
scott@ORA92> create or replace trigger tb1_bir
  2    before insert on tb1
  3    for each row
  4  begin
  5    select tb1_id_seq.nextval into :new.id from dual;
  6  end tb1_bir;
  7  /

Trigger created.

scott@ORA92> show errors
No errors.


scott@ORA92> -- declare bind variable:
scott@ORA92> VARIABLE bind_var1 NUMBER


scott@ORA92> -- insert:
scott@ORA92> INSERT INTO tb1 (name) VALUES ('a') RETURNING id INTO :bind_var1
  2  /

1 row created.


scott@ORA92> -- view value of bind_var1 that contains value of tb1.id:
scott@ORA92> PRINT bind_var1

 BIND_VAR1
----------
         1


scott@ORA92> -- results of insert:
scott@ORA92> select * from tb1
  2  /

        ID NAME
---------- --------------------
         1 a

scott@ORA92> 
Re: How to get the new ID after insert a record? [message #17050 is a reply to message #17049] Wed, 22 December 2004 17:45 Go to previous messageGo to next message
rushing
Messages: 135
Registered: January 2004
Location: Beijing,China
Senior Member
this statement:
VARIABLE bind_var1 NUMBER

Should I use it before each insert or define onle once?
How can I get the bind_var1 from program, such as VB?
Thank you! Barbara.
Re: How to get the new ID after insert a record? [message #17058 is a reply to message #17050] Wed, 22 December 2004 20:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
If executing from SQL*Plus, you would only declare the bind_var1 once. Each time you insert, it would be over-written with the new value. I don't have VB, but I believe you would declare your variable in Visual Basic and pass it to Oracle. I think VB uses ? for bind variables like Oracle uses : for them, but I am not sure. Hopefully someone else who is familiar with VB will respond.
Re: How to get the new ID after insert a record? [message #17063 is a reply to message #17058] Wed, 22 December 2004 23:09 Go to previous messageGo to next message
rushing
Messages: 135
Registered: January 2004
Location: Beijing,China
Senior Member
Hi Barbara,
Can I get bind_var1 by select statement?
Maybe use parameter?
Re: How to get the new ID after insert a record? [message #17089 is a reply to message #17063] Thu, 23 December 2004 13:14 Go to previous message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
Your question is unclear. bind_var1 is just a variable name. You could name it anything. The select statement using the reuturning clause returns the tb1.id generated by the sequence into whatever variable you include in the returning clause. That variable can be a local variable in a procedure or function or an anonymous pl/sql block or an ouput parameter of a procedure or a host variable or whatever you pass to it from Visual Basic. Here is an example of a procedure that would return the tb1.id into an output parameter of a procedure:

CREATE OR REPLACE PROCEDURE insert_tb1
  (p_name IN  tb1.name%TYPE,
   p_id   OUT tb1.id%TYPE)
AS
BEGIN
  INSERT INTO tb1 (name) VALUES (p_name) RETURNING id INTO p_id;
END insert_tb1;
/


Perhaps it would help if you said what you want to do with the value of tb1.id once you get it.

If you were to execute the procedure above, you would still need to have some variable to receive the value of the output parameter. For example, in SQL*Plus:

VARIABLE some_variable NUMBER
EXECUTE insert_tbl ('a', :some_variable)
PRINT some_variable


You need to figure out what the corresponding things are in Visual Basic.
Previous Topic: sql query needed.
Next Topic: Pivoting data
Goto Forum:
  


Current Time: Thu Jun 18 04:34:24 CDT 2026