SEO

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

On 21 Jul 2004 13:36:58 -0700, jared@hwai.com (Jared) wrote:

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

You have a variable sql statement. You are using static sql.
Consequently this is not going to work and you need dbms_sql or better
execute immediate (8i and higher)

However, you'll now will incur a hard or soft parse for every
invocation of the function. You are trying to save a few keystrokes by
introducing a sledgehammer in your application.


--
Sybrand Bakker, Senior Oracle DBA

Yes, I got it working last night using DBMS_SQL and SQL trace was
pretty clear on my choice of elegance over efficiency. Back to the
efficient, labor-intensive approach for me.

Thanks for responding!

Kind regards,
jh



Sybrand Bakker <sybrandb@hccnet.nl> wrote in message news:<g6ntf0l4rlo9f98d5fml8vvmmbtsc9djm7@4ax.com>. ..
> On 21 Jul 2004 13:36:58 -0700, jared@hwai.com (Jared) wrote:
>
> >Am I going to have to construct a string using DBMS_SQL? I was hoping
> >there was an easier way.
>
> You have a variable sql statement. You are using static sql.
> Consequently this is not going to work and you need dbms_sql or better
> execute immediate (8i and higher)
>
> However, you'll now will incur a hard or soft parse for every
> invocation of the function. You are trying to save a few keystrokes by
> introducing a sledgehammer in your application.