Re: Simple PL/SQL question about sequences

Jim Mc Ree wrote:
>
> I want to implement a PL/SQL function where I pass in the name of a
> sequence and get back the next value in the sequence. I am brand-new to
> Oracle and PL/SQL and haven't found a way to do this (that compiles) on
> my own, so I thought the net would have the answer. It seems like such
> a simple problem. Here's what I currently have:
>
> ---------------------------
> CREATE OR REPLACE FUNCTION read_counter (counter_name in VARCHAR2)
> RETURN NUMBER IS
> counter NUMBER;
>
> BEGIN
>
> SELECT :counter_name.nextval INTO counter FROM dual;
> RETURN (counter);
>
> END;
> /

  counter_name.nextval is undefined in your program and schema. So, it will encounter a compilation error. Anyway, you can implement your function by using dynamic SQL. Please refer to DBMS_SQL package for detail.

  Related Topics: Export and Import, Sequence cache, SQL parsing

---
Name   : Lun Wing San
Title  : Oracle Application Developer of Hong Kong Productivity Council
         Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841