Solved

Oracle Equivalent of Scope Identity in MS SQL

Posted on 2009-07-15
4
803 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 500 total points
Comment Utility
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
Comment Utility
select last_insert_id()
select @err
0
 

Author Closing Comment

by:Glen_D
Comment Utility
yes....thx;  created so many Oracle triggers, should gave caught that...thx
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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.
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now