help on a generic get_nextval function?

From: Jared <jared_at_hwai.com>
Date: 21 Jul 2004 13:36:58 -0700
Message-ID: <480f610.0407211236.1dd93a5f@posting.google.com>

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 Received on Wed Jul 21 2004 - 15:36:58 CDT