'INSERT RETURNING INTO' can clean up old coding methods

Just remembered in Oracle 11g there are two lots of  functionality that can help with existing code like this


SELECT ex_sequence_number.NEXTVAL
INTO v_ex_sequence_number
FROM dual;
p_batch_number := v_ex_sequence_number; -- out parameter


INSERT INTO upload_validated (
sequence_number,
widget_cd,
comments)
VALUES (
v_ex_sequence_number,
p_widget_cd,
'This is the old way to do it');


First we don't need to do the select any more as we can just say


INSERT INTO upload_validated (
sequence_number,
widget_cd,
comments)
VALUES (
ex_sequence_number.NEXTVAL,-- no need to do the old way
p_widget_cd,
'This is the old way to do it');

But because we also need to set the out parameter (p_batch_number using the same sequence value) we also need to use the returning into clause for the insert.

INSERT INTO upload_validated (

sequence_number,

widget_cd,

comments)

VALUES (

ex_sequence_number.NEXTVAL,-- no need to do the old way

p_widget_cd,

'This is the old way to do it')

RETURNING sequence_number

INTO p_batch_number

Which is a much nicer and concise way to do it.