Does DMBS_SQL (and EXECUTE IMMEDIATE) not work with Sequences?

[CRoberts]

I was writting an anonymous PLSQL block to re-synchronize Sequences, sometimes Sequences get out of wack with their target tables. Fine, so I want to simply increment the sequence. But, neither Native Dynamic Sql nor DBMS_SQL approaches is working for me. Are Sequences excluded from being manipulated these two ways? No hard coding, I want generic code that works in any schema.

Here is my example of using DBMS_SQL, the loop is working for SELECT ....NEXTVAL FROM DUAL, but no incrementing of the Sequence ever happens. What am I doing wrong?

--
-- Found and defined elsewhere in the file are these definitions. Just a array of Sequences with
-- the table name and the max PK value generated from the Sequences. Then a
-- record type that matches the array.
--
TYPE gTabRec_Typ IS RECORD
(
Sequence_Name User_Sequences.Sequence_Name%TYPE,
Last_Number User_Sequences.Last_Number%TYPE,
Table_Name User_Tables.Table_Name%TYPE,
MaxPk INTEGER
);

TYPE gLuSequencesType_Nt IS TABLE OF gTabRec_Typ INDEX BY BINARY_INTEGER;

gSeqRec gTabRec_Typ;
gLuSequences gLuSequencesType_Nt;

. . .
-- Here starts an ebedded Procedure that completely ignores my incrementing efforts for the called Sequences.
-- And yes, I have checked to see that the Sequences are being properly called up
-- and values in the array are indeed correct.
-- Loops are working as they should be.

PROCEDURE FixSequences_Prc IS
vCursorNr INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
vSqlStmt VARCHAR2(200);
vIndx INTEGER := gLuSequences.FIRST;
vCnt1 INTEGER := 0;
vCnt2 INTEGER := 0;
vProsRows INTEGER := 0;
BEGIN
LOOP
EXIT WHEN vIndx IS NULL;

gSeqRec := gLuSequences(vIndx);

IF gSeqRec.Last_Number <= gSeqRec.MaxPk THEN

vCnt1 := gSeqRec.Last_Number;
vCnt2 := gSeqRec.MaxPk + 1;
vSqlStmt := 'SELECT '||gSeqRec.Sequence_Name||'.NEXTVAL FROM DUAL';
DBMS_SQL.Parse (vCursorNr, vSqlStmt, DBMS_SQL.NATIVE);

WHILE vCnt1 <= vCnt2 LOOP
vProsRows := DBMS_SQL.EXECUTE (vCursorNr);
vCnt1 := vCnt1 + 1;
END LOOP;

DBMS_SQL.CLOSE_CURSOR (vCursorNr);

END IF;

vIndx := gLuSequences.NEXT (vIndx);
END LOOP;

END FixSequences_Prc;

. . .

 

[NuWood]

Hi

Have you taken the cache size into account which controls the allocation of sequence numbers into the cache for faster processing.

If you have a cache size of 20 then the last_number will be 21 as the cache takes the first 20 numbers you use a <= comparing a number with the last_number of the sequence. This might cause your issue. Turn off caching for the sequence.


regards

 

[sem]

Did you analyze vProsRows value? It's 0 that means that NO ROWS were FETCHED. So, in fact you did not select from dual, just opened cursors on it. Try to use EXECUTE_AND_FETCH.
Why don't you try to recreate those sequences, it may be much faster? Regards, Dima