Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: insert record and return ID field

    I have a form that users fill out and then is submitted to my oracle database. A trigger is setup to assign an ID # each time data from the form is submitted. I then want to return to the user the ID # for their form so they can check the status later. My problem is that I am discovering that the most recent insert is not always the last value. For example if I insert five forms and then display all the ID #'s they might look something like this, with 5 being the last ID # inserted.

    ID #
    -------
    3
    4
    5
    1
    2

    Is this normal? To display the case number to the user I was just creating a record set of the ID's and displaying the last value, but after testing it, that is not going to work. Any suggestions on how I can display the ID # back to the user? (Maybe I should be asking this in the ASP area?) This is my first time using oracle so I am not sure how everything works quite yet. Thanks for the help

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: insert record and return ID field

    The Oracle INSERT statement has a returning clause:

    insert into emp (name) values (:name)
    returning id into :id;

    If you can't utilize the returning clause via ASP (I don't know whether you can or not) then you could encapsulate the insert in a stored procedure with an OUT parameter.

  3. #3
    Join Date
    Mar 2004
    Posts
    6
    Thanks for the suggestions. What about the insert though, values are not inserted or stored in any order in the database?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Correct. You cannot rely on physical order of records in the database. For example, if there is space near the "start" of the table (e.g. after deletion of some records) then a new insert may be placed there rather than at the end.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •