Solved

Oracle Equivalent of Scope Identity in MS SQL

Posted on 2009-07-15
4
810 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

617 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