Oracle uses Sequences for auto-incrementing, however, unlike SQL Server you cannot assign them as a column property or a default value. Therefore the MyGeneration code generator has no idea that you want to use an Oracle Sequence as an identity column value. Take a look at the "SeqTest" table below, the ID column is defined as the primary key. We want to use the ID column as an Identity or AutoIncrement column and we also want to use our timestamp column for concurrency checking. Can EntitySpaces handle this? You bet.
Take a look at our "SeqTest" Oracle table below.
CREATE TABLE "TEST"."SeqTest"
(
"ID" NUMBER,
"TimeStamp" NUMBER,
"Data" VARCHAR2(100),
CONSTRAINT "PK" PRIMARY KEY ("ID") VALIDATE ,
CHECK ("ID" IS NOT NULL) VALIDATE ,
CHECK ("TimeStamp" IS NOT NULL) VALIDATE ,
CHECK ("Data" IS NOT NULL) VALIDATE
)
Our Oracle Sequence.
CREATE SEQUENCE "TEST"."SEQ_ID" NOCYCLE NOORDER CACHE 20 NOMAXVALUE MINVALUE 1 INCREMENT BY 1 START WITH 1
Below is an image of the MyMeta Browser open in MyGeneration with our SeqTest Selected.
Because Oracle has no real metadata itself to indicate the AutoIncrement column or Concurrency column information we need to store some user metadata in MyGeneration so that the EntitySpaces templates will know what to do. To do this we put a few name/value pairs in the MyGeneration User Metadata indicating that we want to use the "SEQ_ID" Oracle Sequence as our Identity or AutoIncrement Column value and to indicate that we want to use the TimeStamp column as our concurrency column. These entries are placed at the table level, in the user metadata simply by clicking on the table with the User Meta Data window and entering values similiar to those shown below.
Let's examine these two name/value pairs:
AUTOKEY:ID / SEQ_ID = Treat the ID column as an AutoIncrement Column and use the SEQ_ID sequence to get the value.
CONCURR:TimeStamp / 1 = Treat the TimeStamp column as a concurrency column and bump it's value by 1 upon each update.
Now let's take a look at what effect these two name/value pairs have on our insert and update statements.
The INSERT statement.
Notice that the SEQ_ID sequence is used to populate the pID parameter's value and that the pID parameter is also declared as an OUT parameter so that it can be brought back after the insert statement completes. The pTimeStamp parameter is also assigned to 1 and declared as an OUT parameter for the same reason.
CREATE PROCEDURE "TEST"."proc_SeqTestInsert"
(
pID OUT "SeqTest"."ID"%type,
pTimeStamp OUT "SeqTest"."TimeStamp"%type,
pData IN "SeqTest"."Data"%type
)
IS
BEGIN
SELECT SEQ_ID.NextVal INTO pID FROM DUAL;
pTimeStamp := 1;
INSERT INTO "SeqTest"
(
"ID",
"TimeStamp",
"Data"
)
VALUES
(
pID,
pTimeStamp,
pData
);
END;
The UPDATE statement.
This bumps the TimeStamp column by 1 but not before it compares the current TimeStamp value to make sure it hasn't changed since we last read the data (in the Where statement). The pTimeStamp parameter is also declared as an OUT parameter to ensure that the new value is brought back after the update is complete.
CREATE PROCEDURE "TEST"."proc_SeqTestUpdate"
(
pID IN "SeqTest"."ID"%type,
pTimeStamp IN OUT "SeqTest"."TimeStamp"%type,
pData IN "SeqTest"."Data"%type
)
IS
pConncurrency "SeqTest"."TimeStamp"%type := pTimeStamp;
BEGIN
UPDATE "SeqTest"
SET
"TimeStamp" = "TimeStamp" + 1,
"Data" = pData
WHERE "ID" = pID
AND "TimeStamp" = pConncurrency;
IF SQL%ROWCOUNT = 1 THEN
pTimeStamp := (pConncurrency + 1);
ELSE
Raise_application_error(-20101, 'NO RECORDS WERE UPDATED');
END IF;
END;
These are the stored procedures generated by the EntitySpaces Oracle stored procedure template, however the same holds true for the EntitySpaces dynamic sql generation, it works the same way.