CODEWITHSUNDEEP
.netoracle-databaseplsqlmygeneration
Michael Rosario
Michael Rosario

Reputation: 848

Using MyGeneration, doodads, and Oracle XE, is it possible to implement an "auto number primary key" scheme?

Using MyGeneration, doodads, and Oracle XE, is it possible to implement an "auto number primary key" scheme?

Problem facts: I am using Oracle XE. I have implemented the following table and trigger:

CREATE TABLE  "USERS" 
(   
    "ID" NUMBER(38,0), 
    "USER_NAME" VARCHAR2(50), 
    "PASSWORD" VARCHAR2(50), 
    "EMAIL" VARCHAR2(100), 
     CONSTRAINT "USERS_PK" PRIMARY KEY ("ID") ENABLE
)
/

CREATE OR REPLACE TRIGGER  "BI_USERS" 
  before insert on "USERS"               
  for each row  
begin   
    select "USERS_SEQ".nextval into :NEW.ID from dual; 
end; 

/
ALTER TRIGGER  "BI_USERS" ENABLE
/

MyGeneration / Doodads created the following stored proc...

CREATE OR REPLACE PROCEDURE "XXX"."PI_USERS"
(
    p_ID IN USERS.ID%type,
    p_USER_NAME IN USERS.USER_NAME%type,
    p_PASSWORD IN USERS.PASSWORD%type,
    p_EMAIL IN USERS.EMAIL%type
)
IS
BEGIN


    INSERT
    INTO USERS
    (
        ID,
        USER_NAME,
        PASSWORD,
        EMAIL
    )
    VALUES
    (
        p_ID,
        p_USER_NAME,
        p_PASSWORD,
        p_EMAIL
    );
END PI_USERS;

The sequence and trigger combination is working fine. The BusinessEntity class in C# does not receive the new ID.

Any recommended ways to allow the calling code receive the new record ID?

Upvotes: 1

Views: 624

Answers (2)

Sam
Sam

Reputation: 11

Why not use a sequence? If your preference is to use TAPI I still think the ID and WHO columns are best served in a trigger on a sequence. Is the issue the need for a pure, dense sequence where there are no skipped numbers?

Upvotes: 1

Doug Porter
Doug Porter

Reputation: 7897

I haven't used Doodads, so not sure if this is what it is expecting, but if you change the procedure like below using the returning clause and make the p_ID parameter in out the p_ID parameter should hold the newly added ID after it is executed.

CREATE OR REPLACE PROCEDURE "XXX"."PI_USERS"
(
    p_ID IN OUT USERS.ID%type,
    p_USER_NAME IN USERS.USER_NAME%type,
    p_PASSWORD IN USERS.PASSWORD%type,
    p_EMAIL IN USERS.EMAIL%type
)
IS
BEGIN


    INSERT
    INTO USERS
    (
        ID,
        USER_NAME,
        PASSWORD,
        EMAIL
    )
    VALUES
    (
        p_ID,
        p_USER_NAME,
        p_PASSWORD,
        p_EMAIL
    )
    RETURNING ID INTO p_ID;

END PI_USERS;

Upvotes: 2

Related Questions