Re: sequence name as a variable

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.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp