This is my original script create from SQL Server:
CREATE PROCEDURE [dbo].[SP_ObterDadosApontamentosOperacional]
@dataInicio as dateTime = null,
@dataFim as datetime = null,
@embarcacaoId as varchar(50) = null
AS
BEGIN
if(@dataInicio is null)
begin
set @dataInicio = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
end
if(@dataFim is null)
begin
set @dataFim = (DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0)) +1)
end
select
codigoOperacao.Id as 'Id',
codigoOperacao.Descricao as 'Descricao',
codigoOperacao.Codigo as 'CodigoOperacao',
count(codigoOperacao.Codigo) as 'TotalUnitario'
into #parcial
from INFO_APONTAMENTO as apontamento
inner join CODIGOS_OPERACAO as codigoOperacao on codigoOperacao.Id = apontamento.CodigosDeOperacao_Id
where apontamento.DataCadastro between @dataInicio and @dataFim
and ((apontamento.Barco_Id = @embarcacaoId) or @embarcacaoId is null)
group by
codigoOperacao.Codigo,
codigoOperacao.Descricao,
codigoOperacao.Id
declare @somaTotal as int = (select sum(totalUnitario) from #parcial)
select *, @somaTotal as TotalAgregado from #parcial
end
And This is the script generated by the oracle sql developer migration tool.
CREATE GLOBAL TEMPORARY TABLE tt_parcial
AS (
SELECT codigoOperacao.Id Id ,
codigoOperacao.Descricao Descricao ,
codigoOperacao.Codigo CodigoOperacao ,
0 TotalUnitario
FROM INFO_APONTAMENTO apontamento
JOIN CODIGOS_OPERACAO codigoOperacao ON codigoOperacao.Id = apontamento.CodigosDeOperacao_Id
WHERE 1=2
);
/
CREATE OR REPLACE PROCEDURE SP_ObterDadosApontamentosOperacional
(
iv_dataInicio IN DATE DEFAULT NULL ,
iv_dataFim IN DATE DEFAULT NULL ,
v_embarcacaoId IN VARCHAR2 DEFAULT NULL
)
AS
v_dataInicio DATE := iv_dataInicio;
v_dataFim DATE := iv_dataFim;
v_somaTotal NUMBER(10,0) := ( SELECT SUM(totalUnitario)
FROM tt_parcial );
v_cursor SYS_REFCURSOR;
BEGIN
IF ( v_dataInicio IS NULL ) THEN
BEGIN
v_dataInicio := utils.dateadd('DAY', utils.datediff('DAY', 0, SYSDATE), 0) ;
END;
END IF;
IF ( v_dataFim IS NULL ) THEN
BEGIN
v_dataFim := (utils.dateadd('MS', -3, utils.dateadd('DD', utils.datediff('DD', 0, SYSDATE), 0)) + 1) ;
END;
END IF;
DELETE FROM tt_parcial;
UTILS.IDENTITY_RESET('tt_parcial');
INSERT INTO tt_parcial (
SELECT codigoOperacao.Id Id ,
codigoOperacao.Descricao Descricao ,
codigoOperacao.Codigo CodigoOperacao ,
COUNT(codigoOperacao.Codigo) TotalUnitario
FROM INFO_APONTAMENTO apontamento
JOIN CODIGOS_OPERACAO codigoOperacao ON codigoOperacao.Id = apontamento.CodigosDeOperacao_Id
WHERE apontamento.DataCadastro BETWEEN v_dataInicio AND v_dataFim
AND ( ( apontamento.Barco_Id = v_embarcacaoId )
OR v_embarcacaoId IS NULL )
GROUP BY codigoOperacao.Codigo,codigoOperacao.Descricao,codigoOperacao.Id );
OPEN v_cursor FOR
SELECT * ,
v_somaTotal TotalAgregado
FROM tt_parcial ;
DBMS_SQL.RETURN_RESULT(v_cursor);
EXCEPTION WHEN OTHERS THEN utils.handleerror(SQLCODE,SQLERRM);
END;
And thats the output erros:
- Error(29,34): PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set specification>
I noticed that there are errors in line 21 select, in line 50 join
I have never worked with oracle, but due to changes I will have to do this conversion and I have some difficulties