Out of topic, Oracle SEQUENCE

[Daniel Chacón Sánchez]

Hi I know this the struts mail list, but I have a question and I hope that
someone know the answer

I'm using an oracle database, I have a sequence to obtain the nextval that
will be the key of the row that I will insert, after the insert I need to
know which is that value so I can tell the user with which value the row was
inserted. Is there a sure fire way to do this, or am I stuck with select
max(id) from table and hope nothing else has been inserted in that few
milliseconds it takes to go from my insert statement to my select statement?

Some code, for better explanation:


CREATE SEQUENCE user_seq INCREMENT 1 MINVALUE 0 NOMAXVALUE  START WITH
0  NOCACHE
NOCYCLE



insert into users values (user_seq .nextval, 'userName');



Then I need to know the value with which the row was inserted, how can I do
that


Thanks! Sorry fot the out of topic question

Re: Out of topic, Oracle SEQUENCE

[Adam J Samere]

Use curval to obtain the last selected value for the current oracle 
session. Note that you cannot access curval until nextval has been used 
at least once in the current oracle session.


select user_seq.curval
from dual

Daniel Chacón Sánchez wrote:
Hi I know this the struts mail list, but I have a question and I hope 
that

someone know the answer

I'm using an oracle database, I have a sequence to obtain the nextval 
that

will be the key of the row that I will insert, after the insert I need to
know which is that value so I can tell the user with which value the 
row was

inserted. Is there a sure fire way to do this, or am I stuck with select
max(id) from table and hope nothing else has been inserted in that few
milliseconds it takes to go from my insert statement to my select 
statement?


Some code, for better explanation:


CREATE SEQUENCE user_seq INCREMENT 1 MINVALUE 0 NOMAXVALUE  START WITH
0  NOCACHE
NOCYCLE



insert into users values (user_seq .nextval, 'userName');



Then I need to know the value with which the row was inserted, how can 
I do

that


Thanks! Sorry fot the out of topic question





-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: Out of topic, Oracle SEQUENCE

[Daniel Chacón Sánchez]

Thanks

so, first:

insert into users_values (user_seq .nextval, 'userName');

and then:

select user_seq.curval from dual

There is not a way to make only one sentence to do that? Like in SQL 2000:



SET NOCOUNT ON INSERT INTO

   USER_VALUES

   (USER_NAME)



VALUES ('DANIEL')

*SELECT @@IDENTITY* as userCode SET NOCOUNT OFF

Re: Out of topic, Oracle SEQUENCE

[Brett Connor]

(In case my reply to completely the wrong post didn't get read!...)

selecting max... isn't a viable solution, because of other sessions as you say.
Assuming you're at least using Java, you might want to look at

java.sql.Statement.getGeneratedKeys()

Oracle database returns generated keys in the 'C' i/f, my memory's rusty for
JDBC but I know there is a way somewhere to get the key values in the same
statement, rather than have to execute another statement for 'curval'.
getGeneratedKeys() may be it.

HTH
Brett



Quoting Daniel Chacón Sánchez [EMAIL PROTECTED]:

 Thanks

 so, first:

 insert into users_values (user_seq .nextval, 'userName');

 and then:

 select user_seq.curval from dual

 There is not a way to make only one sentence to do that? Like in SQL 2000:



 SET NOCOUNT ON INSERT INTO

 USER_VALUES

 (USER_NAME)



 VALUES ('DANIEL')

 *SELECT @@IDENTITY* as userCode SET NOCOUNT OFF


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: Out of topic, Oracle SEQUENCE

[Albert L. Sapp]

Daniel,

If you are by chance using iBatis, look at their selectKey command.  It 
is the one that seems to be referred to most for this type of need on 
the iBatis list.  If not, sorry.


Al

Brett Connor wrote:

(In case my reply to completely the wrong post didn't get read!...)

selecting max... isn't a viable solution, because of other sessions as you say.
Assuming you're at least using Java, you might want to look at

java.sql.Statement.getGeneratedKeys()

Oracle database returns generated keys in the 'C' i/f, my memory's rusty for
JDBC but I know there is a way somewhere to get the key values in the same
statement, rather than have to execute another statement for 'curval'.
getGeneratedKeys() may be it.

HTH
Brett



Quoting Daniel Chacón Sánchez [EMAIL PROTECTED]:

  

Thanks

so, first:

insert into users_values (user_seq .nextval, 'userName');

and then:

select user_seq.curval from dual

There is not a way to make only one sentence to do that? Like in SQL 2000:



SET NOCOUNT ON INSERT INTO

USER_VALUES

(USER_NAME)



VALUES ('DANIEL')

*SELECT @@IDENTITY* as userCode SET NOCOUNT OFF




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


  



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: Out of topic, Oracle SEQUENCE

[Daniel Chacón Sánchez]

Not Albert, but thanks, you have give me more solutions than in the metalink
foro of oracle, thanks!! Another solution, to do only one sentence, I do´nt
want to obtain the userCode of an insert sentence that occur between the
insert and the select user_seq.curval from dual, any other solution ??

2006/9/15, Albert L. Sapp [EMAIL PROTECTED]:


Daniel,

If you are by chance using iBatis, look at their selectKey command.  It
is the one that seems to be referred to most for this type of need on
the iBatis list.  If not, sorry.

Al

Brett Connor wrote:
 (In case my reply to completely the wrong post didn't get read!...)

 selecting max... isn't a viable solution, because of other sessions as
you say.
 Assuming you're at least using Java, you might want to look at

 java.sql.Statement.getGeneratedKeys()

 Oracle database returns generated keys in the 'C' i/f, my memory's rusty
for
 JDBC but I know there is a way somewhere to get the key values in the
same
 statement, rather than have to execute another statement for 'curval'.
 getGeneratedKeys() may be it.

 HTH
 Brett



 Quoting Daniel Chacón Sánchez [EMAIL PROTECTED]:


 Thanks

 so, first:

 insert into users_values (user_seq .nextval, 'userName');

 and then:

 select user_seq.curval from dual

 There is not a way to make only one sentence to do that? Like in SQL
2000:



 SET NOCOUNT ON INSERT INTO

 USER_VALUES

 (USER_NAME)



 VALUES ('DANIEL')

 *SELECT @@IDENTITY* as userCode SET NOCOUNT OFF



 -
 To unsubscribe, e-mail: [EMAIL PROTECTED]
 For additional commands, e-mail: [EMAIL PROTECTED]





-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: Out of topic, Oracle SEQUENCE

[Scott Van Wart]

In PL/SQL:

 DECLARE
   new_user_id NUMBER;
 BEGIN
   INSERT INTO users ( user_id, user_name ) VALUES ( user_seq.NEXTVAL, 
'userName' ) RETURNING user_id INTO new_user_id;

 END;

So, similarly (though a little bit of a hack), in JDBC (since 
generatedKeys() or whatever they call it isn't supported, though been 
promised since Oracle 7-8?):


 // context: con is a java.sql.Connection
 CallableStatement stmt = con.prepareCall( {call 
   + INSERT INTO users ( user_id, user_name ) 
   + VALUES ( user_seq.NEXTVAL, ? ) 
   + RETURNING user_id INTO ?} );
 stmt.registerOutParameter( 2, java.sql.Types.NUMERIC );
 stmt.setString( 1, userName );
 stmt.executeUpdate();
 long newId = stmt.getLong( 2 );

A few things,
 - Whitespace can be detrimental to the prepareCall statement.  Don't 
put any spaces in {call.
 - Specify the column list before VALUES--Oracle won't guarantee 
column order across databases.

 - Get yourself a nice persistence layer (Hibernate?) :).

HTH,
 Scott

Daniel Chacón Sánchez wrote:
Hi I know this the struts mail list, but I have a question and I hope 
that

someone know the answer

I'm using an oracle database, I have a sequence to obtain the nextval 
that

will be the key of the row that I will insert, after the insert I need to
know which is that value so I can tell the user with which value the 
row was

inserted. Is there a sure fire way to do this, or am I stuck with select
max(id) from table and hope nothing else has been inserted in that few
milliseconds it takes to go from my insert statement to my select 
statement?


Some code, for better explanation:


CREATE SEQUENCE user_seq INCREMENT 1 MINVALUE 0 NOMAXVALUE  START WITH
0  NOCACHE
NOCYCLE



insert into users values (user_seq .nextval, 'userName');



Then I need to know the value with which the row was inserted, how can 
I do

that


Thanks! Sorry fot the out of topic question




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: Out of topic, Oracle SEQUENCE

[Daniel Chacón Sánchez]

Well you where correct, this is the answer of a metalink user:

user_seq.CURRVAL (with two R's) is the last number that was assigned in your
session. Even if records are added in other sessions, your CURRVAL remains
the same. Try it. Open two SQL*Plus windows (you can even use the same
username) and see that CURRVAL in one remains the same even while CURRVAL in
the other increases.


Thanks all, and sorry for the out the topic question