Stored Procedure with concurrent SELECT / UPDATE

From: Mau C <nobody_at_hotmail.com>
Date: Mon, 07 Feb 2011 09:57:31 +0100
Message-ID: <iioc5r$v98$1_at_nnrp-beta.newsland.it>



Hi all,
here are the requirements that I'm messing with:
- Oracle 10g
  • Table like this CREATE TABLE TMP_TABLE ( PKID INTEGER NOT NULL ENABLE, -- Sequence FIELD1 VARCHAR2(45) NOT NULL ENABLE, FIELD1 VARCHAR2(45) NOT NULL ENABLE, STATUS CHAR(1) DEFAULT '0' NOT NULL ENABLE, -- 0 free (unset) , 1 reserved (set) CONSTRAINT TMP_TABLE_PK PRIMARY KEY (PKID) USING INDEX ENABLE ) ; with about hundreds of thousands records.
  • Stored procedure should be Select the first available 10 records with status=0 (no sorting required), update status=1 and returns the selected rows into a ref cursor (Java Oracle.CURSOR) with the TMP_TABLE%ROWTYPE.
  • Stored procedure should be called by concurrent processes: no more one process must select the same 10 records (PKIDs). I'm starting with

create or replace
PACKAGE PKGTEST IS
  TYPE vCursor IS REF CURSOR RETURN TMP_TABLE%ROWTYPE; END PKGTEST;
/

CREATE OR REPLACE
  PROCEDURE pSelectTMP(
    O OUT PKGTEST.VCURSOR
    )
  AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

-- 
-- Something I don't know to do...
--
COMMIT;
END pSelectTMP;
/

If REF CURSOR can't be used... I could change also the Java-side client
to manage anything else (VARRAY?).

I'v tried with the UPDATE ... RETURNING clause without success...
Thanks in advance
Best regards,
M.
Received on Mon Feb 07 2011 - 02:57:31 CST

Original text of this message