Translation SQL Script for Oracle

Question

I have the script below in SQL Server, but I need to mount it in Oracle. I don’t know anything about Oracle and I couldn’t make it work. Could you help me? Oracle: 11.2.0.4.0

SQL script:

DECLARE @DataExec datetime
DECLARE @DataRef datetime

SET @DataExec = '20161008'
set @DataRef = '20101205'

Select datediff(dd, @DataExec, Max(DatBase)) as DiasDiferenca,  
    max(DatBase) as DataLimite,
    count(1) as Qtde_Registros
from Tabela
where datbase <=@DataRef

Try Oracle Script:

VAR DataExec date
VAR DataRef date

Exec :DataExec := to_date('20161008','YYYYMMDD')
Exec :DataRef := to_date('20101205','YYYYMMDD')

Select (:DataExec - max(DATBASE)) as DiasDiferenca,  
    max(DATBASE) as DataLimite,
    count(1) as Qtde_Registros
from DELQMST 
where DATBASE <= :DataRef;

Error:

ORA-00932: inconsistent datatypes: expected CHAR got DATE

Answer 1

Try:

v_DataExec timestamp(3) v_DataRef timestamp(3)

v_DataExec := '20161008' v_DataRef := '20101205'

Select Max - v_DataExec as DiasDiferenca,
max(DatBase) as DataLimite, count(1) as Qtde_Registros from Tabela where datbase <=v_DataRef

Answer 2

Try it that way:

SELECT (TO_DATE('20161008', 'YYYYMMDD') - MAX(DatBase)) AS diasdiferenca
      ,MAX(DatBase) AS datalimite
      ,COUNT(1) AS qtde_registros
  FROM DELQMST
 WHERE datbase <= TO_DATE('20101205', 'YYYYMMDD')