How can I convert a small query from SQL Server to Oracle, I don't know how to apply their equi-CodePudding

I have always worked with SQL Server and now I want to learn Oracle, how can I convert this little query from SQL Server to Oracle?

I have errors in this query:

with cte as, OFFSET @start ROWS FETCH ,
and exists(select 1 from cte where cte.id_city = h.id_city),
(dateadd(second, h.days, h.datehome) >= @from and dateadd(second, h.days, h.datehome) <= @to)

I don't know what the Oracle equivalent will be.

This is my full query

CREATE OR REPLACE 
PACKAGE PKG_HOME AS 

CREATE PROCEDURE SP_HOME_DATA (
from            in timestamp,
to              in timestamp,
start           number,                
page            number    
)                          


AS
BEGIN   
 
    with cte as (
            select   isnull(h.id_city,'') as id_city
                     from main_bdd.home h  
                     left join second_bdd.owner.clients cl on cl.idHome = h.idHome                  
                     where ((dateadd(second, h.days, h.datehome) >= @from and dateadd(second, h.days, h.datehome) <= @to))
                     order by h.datehome asc                 
                     OFFSET @start ROWS FETCH NEXT @page ROWS ONLY
        )

        select 
         
                     isnull(h.id_city,'') as id_city
                     isnull(h.last_ubication,'') as last_ubication            
                     from main_bdd.home h  
                     left join second_bdd.owner.clients cl on cl.idHome = h.idHome 
                     where ((dateadd(second, h.days, h.datehome) >= @from and dateadd(second, h.days, h.datehome) <= @to))
                     AND exists(select 1 from cte where cte.id_city = h.id_city)
                     order by h.datehome asc 
END; 
END PKG_HOME;

CodePudding user response：

You want something like (although second_bdd.owner.clients looks wrong as it should be schema_name.table_name and not schema_name.table_name.something_else):

CREATE PACKAGE PKG_HOME AS
PROCEDURE SP_HOME_DATA (
  i_from   IN  timestamp,
  i_to     IN  timestamp,
  i_start  IN  number,                
  i_page   IN  number,
  o_cursor OUT SYS_REFCURSOR
);
END PKG_HOME;
/

CREATE PACKAGE BODY PKG_HOME AS
PROCEDURE SP_HOME_DATA (
  i_from   IN  timestamp,
  i_to     IN  timestamp,
  i_start  IN  number,                
  i_page   IN  number,
  o_cursor OUT SYS_REFCURSOR
)                          
AS
BEGIN
  OPEN o_cursor FOR
  with cte as (
    SELECT h.id_city
    FROM   main_bdd.home h  
           left join second_bdd.owner.clients cl
           on cl.idHome = h.idHome                  
    WHERE  h.day   INTERVAL '1' SECOND * h.datehome
             BETWEEN i_from AND i_to
    ORDER BY h.datehome asc                 
    OFFSET i_start ROWS FETCH NEXT i_page ROWS ONLY
  )
  SELECT h.id_city,
         h.last_ubication
  FROM   main_bdd.home h  
         LEFT JOIN second_bdd.owner.clients cl
         on cl.idHome = h.idHome 
  WHERE  h.day   INTERVAL '1' SECOND * h.datehome
             BETWEEN i_from AND i_to
  AND    EXISTS(
           select 1 from cte where cte.id_city = h.id_city
         )
  order by h.datehome asc;
END; 
END PKG_HOME;
/

Note: In Oracle '' and NULL are identical so using COALESCE(value, '') (the Oracle equivalent of the ISNULL function) is identical to value without any wrapped function.

db<>fiddle here