Hey Thomas,
it works great. Thanks. BTW, dint mean to hide my version number. Its 8.1.6.
-sravan
Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9qo0kt0sd9_at_drn.newsguy.com>...
> In article <e2a16bc8.0110181633.3e5eb57a_at_posting.google.com>,
> beingmeus_at_yahoo.com says...
> >
> >Hello,
> >I was wondering if I can use sequence name as a variable in a
> >function. I want to pass the seq. name as a input parameter to a
> >function. Lets say :
> >
> >CREATE FUNCTION test_func
> > (seq_name varchar2)
> > return number;
> >IS
> > temp_value number;
> >BEGIN
> > select seq_name.nextval into temp_value from dual;
> > return temp_value;
> >END;
> >/
> >
> >When I execute this function, am getting an error saying
> >"Invalid reference to variable 'SEQ_NAME' "
> >
> >So, my Question is how do I pass the name of seq. as parameter and get
> >the next value of the sequence ?
> >
> >Thanks
> >-Sravan
>
>
> Well, you can do this -- the best way will depend on your version which you have
> chosen to not share with us.
>
> The most SCALABLE, efficient way is to just code:
>
> select seqname.next into into Your_Var from dual;
>
> instead of
>
> your_var := function( 'seqname' );
>
> it is not that many more characters and will perform the best. If that is not
> good enough, you can (in 8i and up)
>
> CREATE FUNCTION test_func(seq_name varchar2) return number
> IS
> temp_value number;
> BEGIN
> execute immediate 'select ' || seq_name || '.nextval from dual'
> INTO temp_value;
>
> return temp_value;
> END;
> /
>
>
> in 8.0 and before, look up dbms_sql to do dynamic sql.
Received on Fri Oct 19 2001 - 20:33:35 CDT
