Re: Getting primary key value after insert

From: Steve McDaniels <steve.mcdaniels_at_sierra.com>
Date: Fri, 22 Sep 2000 15:42:22 -0700
Message-ID: <8qgn75$qnv$1@spiney.sierra.com>

SQL> create sequence seq_test_id start with 1 increment by 1   2 /

Sequence created.

SQL> create table test
  2 (
  3 testid number,
  4 notes varchar2(50)
  5 )
  6 /

Table created.

SQL> create or replace function test_add (notes test.notes%type) return number is
  2 id number;
  3 begin
  4 insert into test values (seq_test_id.nextval, notes) returning testid into id;
  5 return(id);
  6 exception
  7 when others then return(0);
  8 end;
  9 /

Function created.

SQL>
SQL> set serverout on
SQL> declare n number;

  2 begin
  3 n:=test_add('sample data');
  4 dbms_output.put_line('Test_add returned: ' || to_char(n) );   5 end;
  6 /
Test_add returned: 1

PL/SQL procedure successfully completed.

Commit complete.
SQL> drop function test_add
  2 /

Function dropped.

SQL> drop table test
  2 /

Table dropped.

SQL> drop sequence seq_test_id
  2 /

Sequence dropped.

SQL> "Troy Simpson" <Troy_Simpson_at_ncsu.edu> wrote in message news:l2mnssgauntb2dli6pp3to7f12n166ke1b_at_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:42:22 CDT