Tenho algumas consultas SQL brutas para o SQL Server que usam SCOPE_IDENTITY para recuperar a ID gerada para um INSERT específico imediatamente após esse INSERT ocorrer tudo em uma execução…

INSERT into Batch(
BatchName,
BatchType,
Source,
Area
) Values (
@strBatchName,
@strType,
@strSource,
@intArea
);

SELECT SCOPE_IDENTITY() BatchID;

A questão é

Qual é a melhor maneira de fazer isso para um banco de dados Oracle?

Pode ser feito no Oracle através do SQL padrão ou preciso mudar isso para usar um procedimento armazenado e colocar algo semelhante no corpo do processo armazenad

Se for um processo armazenado, qual é a maneira padrão de fato para recuperar o último número de sequência gerado, tendo o cuidado de considerar que provavelmente haverá execuções sobrepostas em vários encadeamentos, para que esse mecanismo precise recuperar o ID gerado corretamente e não necessariamente o último ID absoluto gerado.

Se dois forem executados simultaneamente, cada um deve retornar o ID gerado correto de cada chamada respectiva. Observe que não estou usando o "@@ IDENTITY" do SQL Server devido à natureza multithread das chamadas.

Prefiro mantê-lo como SQL bruto, se possível, pois é muito mais fácil para mim gerenciar em várias plataformas (arquivo único contendo o bloco SQL de cada plataforma separado por tags de identificação do DBMS). Os procs armazenados são um pouco mais trabalhosos para eu gerenciar, mas posso seguir esse caminho se for o único caminho possíve