'Gap-free object sequence as a parameter using Oracle PL/SQL function

Good day, everyone!

I've got a bunch of sequences. For example:

CREATE SEQUENCE seq_id
    INCREMENT BY 1
    START WITH 1
    MINVALUE 1
    MAXVALUE 20000
    NOCYCLE
    CACHE 1999;

But since I'm constantly using Merge, I've had the issue of gaps in the sequence that I needed to solve. This function works great for me:

CREATE OR REPLACE FUNCTION fseq_id RETURN INTEGER IS
BEGIN
  RETURN seq_id.NEXTVAL;
END fseq_id;

So my question is if I could use a parameter or a variable to use the same function for multiple sequences. Maybe with dynamic sql? It's further complicated by having to use these sequences over different schemas (as the tables for merge are located in different schemas), so I've got to give them grants and public synonyms.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source