I coded a function to select some flux in a queue and lock them with an updated flag. I made it with a cursor and it worked great. But i need to get the ID of the flux i locked to process them in my application.
So i start to code a function:
CREATE OR REPLACE Function getIDArray
RETURN VARCHAR2 is
arr varchar2(1000);
CURSOR flux_to_process
IS
SELECT FLUX_ID, LOCKED_FLAG
FROM (
SELECT FLUX_ID, FLUX, GROUP_STORE_ID, STORE_ID, REFID, FLUX_TYPE, LOCKED_FLAG
FROM DEV_ISB_TRANSACTIONS.BUFFER_FLUX
WHERE status = 0
AND LOCKED_FLAG = 0
ORDER BY DATE_CREATION ASC)
WHERE ROWNUM <= 8;
BEGIN
FOR flux_rec IN flux_to_process
LOOP
IF flux_rec.LOCKED_FLAG = 0
THEN
UPDATE DEV_ISB_TRANSACTIONS.BUFFER_FLUX
SET LOCKED_FLAG = 1
WHERE FLUX_ID = flux_rec.FLUX_ID;
arr := flux_rec.FLUX_ID;
else exit;
COMMIT;
END IF;
END LOOP;
RETURN arr;
END;
The function compilation return an OK but i got no return of my values.
Do you guys have any clue to how to do this ?
WHERE ROWNUM <= 1)UPDATE ... WHERE ... IN (SELECT ...) RETURNING ... BLUK COLLECT INTO ...(docs.oracle.com/cd/B19306_01/appdev.102/b14261/…)exitkeyword, i suggest you remove the else part, because if in your firstflux_rectheflux_rec.LOCKED_FLAG <> 0then the program will exit without results.