Create Sequence/Trigger in Oracle

Use the following script

DROP SEQUENCE SEQNAME;

CREATE SEQUENCE SEQNAME
START WITH 1
MAXVALUE 9999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE INSERT
ON TABLENAME
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
SELECT SEQNAME.NEXTVAL
INTO :NEW.PRIMARYKEYNAME
FROM DUAL;

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR('-20000','TRIGGERNAME');
END;
/

Greetz,

G

Iterate over Cursor in Oracle

To create a procedure to iterate over the records in a cursor, use the following script

CREATE OR REPLACE PROCEDURE CursorProc IS

CURSOR c_Cursor IS
SELECT col1 FROM Table;

col1 Table.col1%TYPE;

BEGIN

OPEN c_Cursor;
FETCH c_Cursor INTO col1;

WHILE c_Cursor%found loop
dbms_output.put_line('col1 : ' col1);

FETCH c_Cursor INTO col1;
END LOOP;

CLOSE c_Cursor;

END CursorProc;

Greetz,
G