Link to home
Create AccountLog in
Avatar of Robert Cearley
Robert CearleyFlag for United States of America

asked on

Need an Example of an Oracle Stored Procedure Pulls the first value from a Queue Table, after flagging that record as "in process".

I have the following Oracle Stored Procedure.  


1. I'm needing a way to get the first record from a queue table and then flag that same record in the queue table as being retrieved so that other calls to that same procedure do not return that same record.  


2. I'm wanting to return the ID of the updated record


CREATE OR REPLACE EDITIONABLE PROCEDURE QUEUE_GET_NEXT 

(MyId OUT Number) 


AS


BEGIN


    update QUEUE

    set status=1  -- set the status to "in process", so that we know this record is currently processing

    where status is null             

    and rownum = 1  -- pick up the first record

    returning id into :MyId;   --return the ID of the record that was updated 

      

end;            


ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account