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?

Improve company productivity with a Business Account.Sign Up

x
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.