SQL Script Translation 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. can 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

Tentativa Script Oracle:

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;

Erro:

ORA-00932: inconsistent datatypes: expected CHAR got DATE

Answer:

Tente:

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
Scroll to Top