Getting primary key value after insert

From: Troy Simpson <Troy_Simpson_at_ncsu.edu>
Date: Fri, 22 Sep 2000 18:14:41 -0400
Message-ID: <l2mnssgauntb2dli6pp3to7f12n166ke1b@4ax.com>

I'm trying to create a function in an Oracle 8.1.6 database. The following code is how I create the function:

create or replace function test_add ( InNotes IN test.notes%TYPE ) return number
as

    id test.pkid%TYPE;
  begin
    ----select test_pkid_seq.nextval into id from dual;     insert into test ( pkid, notes ) i

      values ( test_pkid_seq.nextval, InNotes ) 
      returning pkid into id;

    dbms_output.put_line( id );
    commit;
    return id;
 end;
 /

The function is created and I executed it from within PLSQL like this and get the following error messages:

select test_add( 'asdf' ) from dual;
select test_add( 'asdf' ) from dual

       *
ERROR at line 1:

ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "DESIGN.TEST_ADD", line 6
ORA-06512: at line 1

This topic was in another new group message and that describe that this was possible.
I can seem to figure out why I can't get it to work.

Thanks,
Troy_Simpson_at_ncsu.edu Received on Fri Sep 22 2000 - 17:14:41 CDT