I sat down to write what I thought was a simple function today, but I
am having a problem with it. I am hoping someone with a clear eye can
point out what I missed.

The idea is to pass a sequence name to a function and get
sequence.nextval returned. What I wrote was:

create or replace function get_seq_number2 (sequence_name in char)
return number
as
next_seq number;
seq_name varchar2(40);
begin
seq_name := sequence_name;
select seq_name.nextval into next_seq from dual;
return next_seq;
end get_seq_number2;
/

What I get on compiling is

Errors for FUNCTION GET_SEQ_NUMBER2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/2 PL/SQL: SQL Statement ignored
7/9 PLS-00487: Invalid reference to variable 'SEQ_NAME'
7/9 PL/SQL: ORA-02289: sequence does not exist
SQL>

OK. so I decided perhaps seq_name should be a bind variable, but that
produced

Errors for FUNCTION GET_SEQ_NUMBER2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/9 PLS-00049: bad bind variable 'SEQ_NAME.NEXTVAL'

And there it stands.


It is simple to do this with a static value for the sequence_number,
btw:

create or replace function get_display_name_seq_number return number
as
next_seq number;
begin
select display_name_seq.nextval into next_seq from dual;
return next_seq;
end;
/

Am I going to have to construct a string using DBMS_SQL? I was hoping
there was an easier way.

TIA -

Kind regards,
jh