Having years of background developing applications using MSSQL Server as the database, I encountered a roadblock when my stored procedure is required to return the id for my newly created row in oracle.
Immediate Work-Around
Since all my tables have a timestamp field, the most obvious thing to do is to query the topmost row of my table sorted by the timestamp field. For added accuracy, I also filtered my query against the userid which created the affected row.
(
pLAST_NAME IN CUSTOMERS.LAST_NAME%TYPE
, pFIRST_NAME IN CUSTOMERS.FIRST_NAME%TYPE
, pCREATED_BY IN CUSTOMERS.CREATED_BY%TYPE
, pCREATED_DT IN CUSTOMERS.CREATED_DT%TYPE
, pCUSTOMER_CD OUT CUSTOMERS.CUSTOMER_CD%TYPE
)
IS
BEGIN
INSERT INTO CUSTOMERS
(
LAST_NAME
, FIRST_NAME
, CREATED_BY
, CREATED_DT
)
VALUES
(
pLAST_NAME
, pFIRST_NAME
, pCREATED_BY
, pCREATED_DT
);
SELECT CUSTOMER_CD
INTO pCUSTOMER_CD
FROM CUSTOMERS
WHERE created_by = pCREATED_BY
AND created_dt = pCREATED_DT;
END SP_ARTIST_ADD;
/
While the above solution works, I am convinced that there is a better and correct method of doing it. In MSSQL Server, select @@identity is sufficient for the job but how about in Oracle?
The Oracle Way
Googling on the topic, I came across an article entitled Tuning PL/SQL Performance with the RETURNING Clause which solved my problem. It turns out the PL/SQL has a returning clause which returns column values from the affected row into PL/SQL variables or host variables. According to the article, “it eliminates the need to SELECT the row after an insert or update, or before a delete. As a result, fewer network round trips, less server CPU time, fewer cursors, and less server memory are required”. Now, revising my SP with the returning clause included.
(
pLAST_NAME IN CUSTOMERS.LAST_NAME%TYPE
, pFIRST_NAME IN CUSTOMERS.FIRST_NAME%TYPE
, pCREATED_BY IN CUSTOMERS.CREATED_BY%TYPE
, pCREATED_DT IN CUSTOMERS.CREATED_DT%TYPE
, pCUSTOMER_CD OUT CUSTOMERS.CUSTOMER_CD %TYPE
)
IS
BEGIN
INSERT INTO CUSTOMERS
(
LAST_NAME
, FIRST_NAME
, CREATED_BY
, CREATED_DT
)
VALUES
(
pLAST_NAME
, pFIRST_NAME
, pCREATED_BY
, pCREATED_DT
)
RETURNING CUSTOMER_CD INTO pCUSTOMER_CD;
END SP_ARTIST_ADD;
/
Pretty neat! Unlike @@identity, returning clause can return other fields in your table aside from your primary key making it more versatile, in my opinion, over its SQL Server counterpart.
Tis why the Oracle gods created “Sequences”