Solved

Getting newly inserted AutoId+SELECT @@Identity

Posted on 2006-07-20
18
1,258 Views
Last Modified: 2008-01-16
Hi  Experts

I am inserting into a table using autoid(by creating a sequence and trigger)
i need to get newly inserted id.
In SQL Server i have used SELECT @@Identity.
is there any replacement for the same in oracle 8i

Thank you
Shiju S N
0
Comment
Question by:Shiju Sasidharan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
  • +1
18 Comments
 
LVL 14

Author Comment

by:Shiju Sasidharan
ID: 17149253
i am using VB6 and Oracle
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17149352
Hi,

what about :
insert into table (col2, col3, col4) values( 'val2', 'val3', val4' ) returning col1 into val1

not sure about how you would tie all this together in vb, you would have to have an out parameter added to teh statement i would assume.

good luck.

:)
0
 
LVL 25

Expert Comment

by:jrb1
ID: 17149481
I don't know of a returning parm on an insert.  Also, when you say you have an autoid, what do you mean?  Do you have an Oracle trigger to set the value?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 18

Expert Comment

by:rbrooker
ID: 17149557
Hi,

Oracle insert statement syntax: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_913a.htm
details how a returning clause works on an insert statement.

:)
0
 
LVL 25

Expert Comment

by:jrb1
ID: 17149759
That's the main reason I'm here...I learn new stuff all the time.  Thanks!  (for what it's worth...I concur with rbooker's solution)
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17150695
After your insert, runn this

select <yoursequence>.currval from dual;
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17150782
Sathy:

Hi,

this will work, but what about this : if it is a web app that is inserting into the table, there may be a point when more than one insert happens at the exact same time.  in this scenario, the time taken between the insert and the reading of the sequence current value ( definately milliseconds as program control would have to go back to the web server, a new db request built and then executed ), there may have been more inserts.  you would end up reading an incorrect value from the sequence.

with an insert into returning value, you know the value returned is going to be the correct value associated with the insert, and not a value that is 3 inserts down the track.

:)
0
 
LVL 14

Author Comment

by:Shiju Sasidharan
ID: 17159658

Thank u all for the valuable comments

Could u please provide with a sample example

suppose my table is Table1 with fields F1,F2,F3 and F4 where F1 is autogenerated
my query is

INSERT INTO TABLE1 ( F2, F3,F4 )  VALUES ( V2, V3, V4 )

i just need to get newly inserted F1 field value.

0
 
LVL 14

Author Comment

by:Shiju Sasidharan
ID: 17159662
i am not using stored procedures ,instead directly executing insert query from my application
0
 
LVL 14

Author Comment

by:Shiju Sasidharan
ID: 17161099
now what i do is
after this insert
INSERT INTO TABLE1 ( F2, F3,F4 )  VALUES ( V2, V3, V4 )

i am using

SELECT MAX(F1)  FROM  TABLE1

before committing transaction
i dont know whether this will lead to the scenario rbrooker was discussing
0
 
LVL 25

Expert Comment

by:jrb1
ID: 17162037
What kind of program are you using?  Like rbooker said, you do it all in one statement:

declare
   v_f1 table1.f1%type;
begin
   INSERT INTO TABLE1 ( F2, F3,F4 )  VALUES ( V2, V3, V4 )  returning F1 into v_f1;
end;
/

0
 
LVL 25

Expert Comment

by:jrb1
ID: 17162046
oh yeah, vb6.  run the sql with the returning statement.  send another variable to the SQL call, and it will be set to the value.
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17162083
also, when using select max( uncommitted value ), this does not guarentee correct-ness ( is that a word?? )
imagine a slow and fast connection.

slow - inserts a record, does not commit
fast - inserts a record, does not commit
fast - reads max value - commits
slow - reads max value ( from fast connection, not correct slow value ) - commits

slow connection has read the incorrect value.

"insert into returning" completes both operations in one single database call. you will never get the wrong value.

:)
0
 
LVL 14

Author Comment

by:Shiju Sasidharan
ID: 17163673
how do we implement the same in vb ?
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17164457
Hi,

had a quick google, and this is the only thing i can find...

http://www.orafaq.com/forum/t/10872/0/

talks about using a bind variable in the into clause.
i would then assume that you would been to set the command up with an output parameter and have the bind variable automagically populate the output parameter.

good luck.

:)
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17212130
Hi,

how are you going on this?  got a solution?

:)
0
 
LVL 14

Author Comment

by:Shiju Sasidharan
ID: 17215255
>>talks about using a bind variable in the into clause.
how can we get the value from bind variable using vb ?
0
 
LVL 18

Accepted Solution

by:
rbrooker earned 500 total points
ID: 17218536
Hi,

Hi,

try this :

   dim v_output ...
   dim cmdSP ...

   Set cmdSP = New ADODB.Command
   With cmdSP
        ' setup command object
        .ActiveConnection = <connection>
        .CommandText = "insert into <blah>( col2, col3, col4 ) values( val2, val3, val4 ) returning col1 into val1"

        ' Appending parameters
        Set parOutput = .CreateParameter("val1", adChar, adParamOutput, 50)
        Call .Parameters.Append(parOutput)

        ' calling my sp
        Call .Execute

        ' getting the result from output parameter.
        v_output = SetStringVal(parOutput.Value)

    End With


i dont know if it will work, but it might give you something to start from.

heres a couple of links that might also shed some light:
http://www.vbcity.com/forums/topic.asp?tid=24523
http://support.microsoft.com/kb/308051/

good luck man.

:)
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
error in my cursor 5 65
Toad 12.10 Enterprise visual interface 4 47
scheduler notification 9 80
construct a query sql 11 43
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question