Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle Equivalent of Scope Identity in MS SQL

Posted on 2009-07-15
4
Medium Priority
?
822 Views
Last Modified: 2012-05-07
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;
0
Comment
Question by:Glen_D
4 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 24859695
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
 
LVL 14

Expert Comment

by:shru_0409
ID: 24859760
select last_insert_id()
select @err
0
 

Author Closing Comment

by:Glen_D
ID: 31603735
yes....thx;  created so many Oracle triggers, should gave caught that...thx
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24859826
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question