Oracle Equivalent of Scope Identity in MS SQL

I have a long sp that calls for inserting new facilities.  The tbl_Facility conatins a MainID that is generated each time a new faciltiy is entered.  Other production tables are populated afterwards to include the new mainid; in MS SQL we use the Scope Identity function to insert this new MainID (same ID in the tbl_Facility) to all other tables...what is the equivalent code in Oracle?


CREATE OR REPLACE PROCEDURE usp_InsertFacility
(
vUserID IN NUMBER,
vProgramID  IN NUMBER,
vProjectID IN NUMBER,
vCenterID  IN NUMBER,
vFacility_Name IN CLOB,
vFacilityDescription IN CLOB,
vBldGrpID IN NUMBER,
vBldID IN NUMBER,
vRoomID IN NUMBER,
vRequirement IN CLOB,
vElementID IN NUMBER,
vShuttle_Legacy IN VARCHAR2,
vCapID IN NUMBER,
vStartDate IN VARCHAR2,
vEndDate IN VARCHAR2,
vUsageID  IN NUMBER,
vReadinessID  IN NUMBER,
vConfidenceID  IN NUMBER,
vLEOTransSysDev  IN NUMBER,
vLEOTransOps IN VARCHAR2,
vYearLEOTransOpsBegan IN VARCHAR2,
vLunarTransSysDev IN VARCHAR2,
vLunarTransOps IN VARCHAR2,
vLunarSurfaceSysDev_Ops IN VARCHAR2)



AS
MainID NUMBER;

UserID NUMBER;
ProgramID NUMBER;
ProjectID NUMBER;
CenterID NUMBER;
Facility_Name CLOB;
FacilityDescription CLOB;
BldGrpID NUMBER;
BldID NUMBER;
RoomID NUMBER;
Requirement CLOB;
ElementID  NUMBER;
Shuttle_Legacy varchar2(5);
CapID NUMBER;
StartDate varchar2(10);
EndDate  varchar2(10);
UsageID  NUMBER;
ReadinessID  NUMBER;
ConfidenceID   NUMBER;
LEOTransSysDev varchar2(50);
LEOTransOps varchar2(50);
YearLEOTransOpsBegan varchar2(50);
LunarTransSysDev varchar2(50);
LunarTransOps varchar2(50);
LunarSurfaceSysDev_Ops varchar2(50);
err NUMBER;

BEGIN

INSERT INTO tbl_Facility (Facility_Name, Facility_Description, BldGrpID, BldID, RoomID, CenterID, Shuttle_Legacy, CapID, UserID)
VALUES(vFacility_Name, vFacilityDescription, vBldGrpID, vBldID, vRoomID, vCenterID, vShuttle_Legacy, vCapID, vUserID);



select @err = @@ERROR , @MainID = SCOPE_IDENTITY()



if @err = 0

begin

  insert into LU_Facility (Facility_Name)
  (select distinct InitCap(ltrim(rtrim(Facility_Name))) from tbl_Facility where Facility_Name not in (select facility_name from LU_Facility));

  err := 0;

end;
Glen_DAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Hope you will be having a Sequence for your Primary key column.

In that case sequencename.next_val will give you the next value for that sequence
sequencename.currval will return the current value for that sequence.

Hope this helps
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shru_0409Commented:
select last_insert_id()
select @err
0
Glen_DAuthor Commented:
yes....thx;  created so many Oracle triggers, should gave caught that...thx
0
schwertnerCommented:
First you have to define the sequence:

CREATE SEQUENCE dept
INCREMENT BY 1
START WITH 1
MAXVALUE 9999
NOCACHE
NOCYCLE;

The first solution is to use BEFORE INSERT TRIGGER
In the the trigger you should put
select dept.nextval into :new.column_name:

The second solution (most simple) is to put in the INSERT statement the put in the VALUE clause of INSERT the value  dept.nextval

YINSERT INTO tbl_Facility (MainID, Facility_Name, Facility_Description, BldGrpID, BldID, RoomID, CenterID, Shuttle_Legacy, CapID, UserID)
VALUES(dept.nextval ,vFacility_Name, vFacilityDescription, vBldGrpID, vBldID, vRoomID, vCenterID, vShuttle_Legacy, vCapID, vUserID);



if MainID is the Primary key column. If not - change the name.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.