Re: Retrieving the key of a newly inserted record

Claus Scherschel wrote:

> Hi everyone,
>
> I'm using an after insert trigger/sequence combination to create an
> incremental number for the table's primary key.
>
> I would like to write a stored procedure that executes an insert statement,
> writing the procedure's parameters into my table. That's not big deal. But
> I haven't a clue how to retrieve the primary key value of the new record.
> There's no other unique index which would help. Retrieving the row id would
> be sufficient, I think.
>
> Database is Oracle 8.1.7.
>
> Thanks in advance,
>
> Claus.

Galen's advice may well be the best for what you are doing. But everyone should be aware of the RETURNING clause of a SQL statement such as demonstrated below:

• :bnd1 is a bind variable INSERT INTO emp VALUES (empseq.nextval, 'LEWIS', 'CLARK', 7902, SYSDATE, 1200, NULL, 20) RETURNING sal*12, job INTO :bnd1, :bnd2;
• :1 as a bind array INSERT INTO employee VALUES ('Kitty Mine', 'Peaches Fuzz', 'Meena Katz') RETURNING REF(employee) INTO :1;

UPDATE emp
SET job ='MANAGER', sal = sal + 1000, deptno = 20 WHERE ename = 'JONES'
RETURNING sal*0.25, ename, deptno INTO bnd1, bnd2, bnd3;

DELETE FROM emp
WHERE job = 'SALESMAN' AND COMM < 100
RETURNING sal INTO :1;

Daniel Morgan Received on Fri Dec 20 2002 - 17:35:31 CST