SQL & PL/SQL

Hi, I'm a Oracle beginner (Some Experience with SQL)

I'm having some troubles trying to figure out how to create a stored procedure in "Oracle 9i" that returns an auto incrementing primary key. I've done this several times on MS SQL but cannot seem to get it to work in Oracle.

This is an example I've been working on using a test table

I created this table

CREATE TABLE Test (
ID INTEGER PRIMARY KEY,
Value VARCHAR2(64))

Then I created a sequence my tables primary key, like so;

CREATE OR REPLACE SEQUENCE Test_Seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

Then I write the following stored procedure

CREATE OR REPLACE PROCEDURE "CRDC"."INSERT_TEST" (
key OUT NUMBER,
val IN VARCHAR)
IS
BEGIN
key := Test_Seq.nextval; # ERROR HERE
INSERT INTO Test (ID, Value) VALUES (key, val);
END "INSERT_TEST";

but it won't let me assign the sequence value to my output parameter.

As well I'd rather have the key auto increment by using a trigger like so:

CREATE OR REPLACE TRIGGER Test_Trigger
BEFORE INSERT ON Test
FOR EACH ROW
BEGIN
SELECT Test_Seq.nextval INTO :new.id FROM dual;
END;

so it works with any insert statement into the table. But I'm totally baffled how to get the ID out of the procedure (I'd use a call to SCOPEIDENTITY in MS SQL, any similiar calls I can use?). I'm sure this is a simple matter but due to my lack of experience on Oracle I just can't seem to make it work.

Thanx in advance for any help

Greg Underwood
greg @ gregscrib.net