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
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