Hiya Adam, there are a couple of different ways to do what you want to do. Personally, when I insert rows into a table and I want to use a sequence to generate my ID's for me, I use a TRIGGER. This way nobody can insert bogus data into the table. Let's walk through a quick sample:
/******************************************/
SQL> create sequence id_seq
2 start with 1
3 increment by 1
4 /
Sequence created.
SQL> create table name_values(
2 id number,
3 name varchar2(30),
4 value varchar2(255)
5 )
6 /
Table created.
SQL> create trigger name_values_bi_trig
2 before insert on name_values
3 for each row
4 begin
5 select id_seq.nextval
6 into :new.id
7 from dual;
8 end;
9 /
Trigger created.
SQL> insert into name_values( name, value )
2 values( 'Oracle', 'Database' );
1 row created.
SQL> insert into name_values( name, value )
2 values( 'Java', 'Language' );
1 row created.
SQL> select * from name_values;
ID NAME VALUE
1 Oracle Database
2 Java Language
/******************************************/
The reason you can't assign a seq.nextval to a variable in your function is because PL/SQL won't let you do that, only SQL understands how to do that. You can use a "singleton select" in PL/SQL to do what you're trying to do:
/******************************************/
SQL> create or replace function get_next_id
2 return number
3 is
4 l_id number;
5 begin
6 select id_seq.nextval
7 into l_id
8 from dual;
9 return l_id;
10 end;
11 /
Function created.
SQL> select get_next_id from dual;
GET_NEXT_ID
3
/******************************************/
Hope that helps!
sean
)