Me.Thoughts.ToString()

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
BEGININSERT 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
BEGININSERT 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.