We help IT Professionals succeed at work.
Get Started

Urgent 500pts - Oracle 9i returning id after insert using 'returning' with a select stmt

nbkbar7 asked
Last Modified: 2008-01-09
Oracle is not my area of expertise.

Here's what I want to accomplish:

I want to return the sequence number from the row I just inserted (sounds simple enough)

Here's what I have:

Trigger (works)

Create Or Replace Trigger "IMO_INCIDENT_TBL_TRIGGER"
Before Insert on IMO_INCIDENT
For Each Row
    IF :new.Incident_SeqNo is NULL Then
      Select IMO_INCIDENT_SEQ01.nextval Into :new.Incident_SeqNo from Dual;
    End IF;

Sequencer (works)

create sequence IMO_INCIDENT_SEQ01
minvalue 1
maxvalue 999999999999999999999999999
start with 31
increment by 1


This is the SQL I can't get working for me...

insert into imo_incident (incident_number)
      values      ('proc')
      returning incident_seqno into id;

- How do I define this var (id) to receive the value of incident_seqno?
- How can I get the value returned as a scalar or single column value?
- can I select that value using a query?
select ( the insert stmt that returns incident_seqno ) from dual or something??

- Should I instead create a stored procedure? e.g.

create or replace procedure incinsert(id out number) is
  insert into imo_incident (incident_number)
      values      ('proc')
      returning incident_seqno into id;
end incinsert;

- If so how do I call the sp and obtain the result?
- Again can I do this with a select stmt?
- Do I create the sp, use it and then drop it? Or leave it there? (multi-user environment ...)

Ultimately I need to get this value back into some .NET code (which is closer to my area of expertise), I'd like to have a single select query to do the insert and return the result.

Need it as soon as is reasonably possible...

thx in advance!!

Watch Question
Data Architect
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE