IDENTITY in Oracle is pretty straight forward:
e.g:
CREATE TABLE MyTable(
ID NUMBER(10) NOT NULL,
Description VARCHAR2(128)
)
/
CREATE SEQUENCE SeqMyTable
/
CREATE OR REPLACE TRIGGER TiMyTable BEFORE INSERT ON MyTable
FOR EACH ROW
BEGIN
SELECT NVL(:new.ID, SeqMyTable.NEXTVAL) INTO :new.ID FROM DUAL;
END;
/
So far so good.
However, in my case I am dealing with a large application which is
running 'mixed', eg, originally designed for MS SQL Sever, and now
being adapted to run under Oracle as well. As the @.@.Identity is wildly
used, I thought of an alternative for returning the last identiry
value. As the queries themselves are already parsed, replacing all
occurrences of @.@.Identity with something else is a breeze.
So, my idea is to implement a global identity value with a package.
Can anybody tell me if the following is a good or a bad plan, and if
there are any caveats?
Thanks
Code:
CREATE OR REPLACE PACKAGE SomePackageName
IS
FUNCTION GetIdentity RETURN NUMBER;
PROCEDURE SetIdentity(Identity IN NUMBER);
END SomePackageName;
/
CREATE OR REPLACE PACKAGE BODY SomePackageName
IS
Identity NUMBER(10) := NULL;
FUNCTION GetIdentity
RETURN NUMBER
IS
BEGIN
RETURN Identity;
END;
PROCEDURE SetIdentity(Identity IN NUMBER)
IS
BEGIN
SomePackageName.Identity := Identity;
END;
END SomePackageName;
/
CREATE TABLE MyTable(
ID NUMBER(10) NOT NULL,
Description VARCHAR2(128)
)
/
CREATE SEQUENCE SeqMyTable
/
CREATE OR REPLACE TRIGGER TiMyTable BEFORE INSERT ON MyTable
FOR EACH ROW
BEGIN
SELECT NVL(:new.ID, SeqMyTable.NEXTVAL) INTO :new.ID FROM DUAL;
SomePackageName.SetIdentity(:new.ID);
END;
/
Application example:
SET SERVEROUTPUT ON;
DECLARE
NewID NUMBER(10);
BEGIN
INSERT INTO MyTable(Description) VALUES('Hello world');
NewID := SomePackageName.GetIdentity;
dbms_output.put_line('New ID = ' || TO_CHAR(NewID));
END;
/
Hmmm, actually, I'd better post this in the Oracle groups. Sorry for
spamming Oracle stuff here.
