Returning the ID of a Newly Created Row in Oracle’s PL-SQL

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.

CREATE OR REPLACE PROCEDURE SP_CUSTOMERS
(
	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.

CREATE OR REPLACE PROCEDURE SP_CUSTOMERS
(
	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.