Home » Developer & Programmer » JDeveloper, Java & XML » id (oracle 10g)
id [message #480256] Fri, 22 October 2010 02:40 Go to next message
luker
Messages: 8
Registered: October 2010
Junior Member
Hi all, i have a question. I have inserted a record in a table that has ID as the primary key that auto-increments. After that insert i want to get the value of the ID fileld of that record.
How can i do with a sql query?


Thank you all.


Re: id [message #480264 is a reply to message #480256] Fri, 22 October 2010 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
as the primary key that auto-increments

This does not exist in Oracle. Wink

insert into ... values (...) returning id into :myvariable;

Regards
Michel
Re: id [message #480268 is a reply to message #480264] Fri, 22 October 2010 02:57 Go to previous messageGo to next message
luker
Messages: 8
Registered: October 2010
Junior Member
i am not using pl/sql, i have to use sql, the field id is auto incremented by a sequence.
Re: id [message #480269 is a reply to message #480268] Fri, 22 October 2010 03:00 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
then in which stuff will you store returned ID?
How do you want to use that ID?

regards,
Delna
Re: id [message #480270 is a reply to message #480268] Fri, 22 October 2010 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i am not using pl/sql

What I mentioned is generic, :myvariable can be a bind variable of ANY language or tool that accepts to pass a variable to a SQL statement.
Here's an example with SQL*Plus (and don't tell me your field is filled by a trigger and mine is not, there is no difference):
SQL> var myvar number
SQL> insert into t values(10) returning col into :myvar;

1 row created.

SQL> print myvar
     MYVAR
----------
        10

Regards
Michel

[Updated on: Fri, 22 October 2010 03:04]

Report message to a moderator

Re: id [message #480271 is a reply to message #480269] Fri, 22 October 2010 03:04 Go to previous messageGo to next message
luker
Messages: 8
Registered: October 2010
Junior Member
i have a java application that inserts a record in that table, so after that insert statement i want to get the value of the ID field of the last inserted record, for other stuff in the application.

[Updated on: Fri, 22 October 2010 03:05]

Report message to a moderator

Re: id [message #480272 is a reply to message #480271] Fri, 22 October 2010 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In case of Java, replace ":myvar" by "?".

Regards
Michel
Re: id [message #480273 is a reply to message #480272] Fri, 22 October 2010 03:11 Go to previous messageGo to next message
luker
Messages: 8
Registered: October 2010
Junior Member
this is my code:
PreparedStatement ps = null;
		ResultSet rs = null;
		ps = conn.prepareStatement("insert into t values(10) returning col into ? ");
                ps.execute();


but how do i get the value of the ID?
Re: id [message #480275 is a reply to message #480273] Fri, 22 October 2010 03:14 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
Bind a variable to the statement.
You should (most probably) also use a bind for the value you are inserting (unless your app should only ever insert a single value)
Re: id [message #480276 is a reply to message #480275] Fri, 22 October 2010 03:17 Go to previous messageGo to next message
luker
Messages: 8
Registered: October 2010
Junior Member
Frank wrote on Fri, 22 October 2010 03:14
Bind a variable to the statement.
You should (most probably) also use a bind for the value you are inserting (unless your app should only ever insert a single value)



ok, but how?
Re: id [message #480277 is a reply to message #480276] Fri, 22 October 2010 03:20 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
That is part of Java and we are not java developers.

regards,
Delna
Re: id [message #480278 is a reply to message #480277] Fri, 22 October 2010 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ok, but how?


First hit when I google for "jdbc insert returning":

http://forums.oracle.com/forums/thread.jspa?messageID=1702717

Regards
Michel

[Updated on: Fri, 22 October 2010 03:27]

Report message to a moderator

Re: id [message #480301 is a reply to message #480276] Fri, 22 October 2010 06:18 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
luker wrote on Fri, 22 October 2010 10:17
Frank wrote on Fri, 22 October 2010 03:14
Bind a variable to the statement.
You should (most probably) also use a bind for the value you are inserting (unless your app should only ever insert a single value)



ok, but how?

I expected this question, so I googled for the following terms: java bind variables
A list of very useful links was returned for those VERY obvious searchterms!
Re: id [message #480302 is a reply to message #480277] Fri, 22 October 2010 06:19 Go to previous message
Frank
Messages: 7877
Registered: March 2000
Senior Member
delna.sexy wrote on Fri, 22 October 2010 10:20
That is part of Java and we are not java developers.

regards,
Delna

Yes we are Razz
Previous Topic: Problem with XMLTABLE with parameters
Next Topic: How can we put attributes in aliases inside XMLForest
Goto Forum:
  


Current Time: Sun Apr 20 00:20:31 CDT 2014

Total time taken to generate the page: 0.08927 seconds