Link to home
Start Free TrialLog in
Avatar of riccardo catelli
riccardo catelli

asked on

returned (.... , .....) why no data?

CREATE OR REPLACE FUNCTION pck_gestione_tracce_sistema.p_get_rotta_traccia_sistema6(
    IN in_id_traccia_sistema numeric,
    IN in_id_versione numeric,
    IN in_limite_inferiore numeric,
    IN in_limite_superiore numeric,
    IN id_limite_inferiore_data date,
    IN id_limite_superiore_data date,
    IN in_id_viaggio numeric,
    OUT on_out_total_count numeric,
    OUT oc_out_recordset character varying)
  RETURNS record AS
$BODY$
DECLARE
   LD_LIMITE_INFERIORE_DATA Date ;
   LD_LIMITE_SUPERIORE_DATA Date ;
   LK_PACKAGE_NAME varchar(1000);
   LK_FUNCTION_NAME varchar(1000);
   LN_DEBUG_INFO Integer;
 
   ref refcursor;
   ref1 refcursor;

Begin  

   
   LN_DEBUG_INFO := 1;
   LK_PACKAGE_NAME := 'PCK_GESTIONE_DATI';
   LK_FUNCTION_NAME := 'P_GET_ROTTA_TRACCIA_SISTEMA';
   LD_LIMITE_INFERIORE_DATA  := TO_DATE(TO_CHAR(ID_LIMITE_INFERIORE_DATA,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS');
   LD_LIMITE_SUPERIORE_DATA  := TO_DATE(TO_CHAR(ID_LIMITE_SUPERIORE_DATA,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS');

    If (LN_DEBUG_INFO = 1) Then
      PERFORM nereus.p_logging(IN_TIPO        => 'I'::character varying,
                IN_CODICE      => 'INFO'::character varying,
                IN_DESCRIZIONE => 'Input call'::character varying,
                IN_MODULO      => LK_PACKAGE_NAME::character varying, --LK_PACKAGE_NAME,
                IN_FUNZIONE    => LK_FUNCTION_NAME::character varying, --LK_FUNCTION_NAME,
                IN_NOTE        => 'ID_TRACK: ' || IN_ID_TRACCIA_SISTEMA || ' VERS: ' || IN_ID_VERSIONE || ' LIM_INF: ' || IN_LIMITE_INFERIORE || ' LIM_SUP: ' || IN_LIMITE_SUPERIORE || ' DATA_INF:' ||
                                          TO_CHAR(ID_LIMITE_INFERIORE_DATA,'DD/MM/YYYY HH24:MI:SS') || ' DATA_SUP: ' || TO_CHAR(ID_LIMITE_SUPERIORE_DATA, 'DD/MM/YYYY HH24:MI:SS') || ' ID_VIAGGIO: ' || IN_ID_VIAGGIO,
                        IN_USER_ID     => '0');
    End If;
      
     
     IF (IN_ID_VIAGGIO Is Not Null) Then
      Open ref FOR Select Count(*)
                        into on_out_total_count
                        From (Select T.*,
                              LAG(T.LATITUDINE) OVER(Order By T.VERSIONE Asc) LAT_PREV,
                              LAG(T.LONGITUDINE) OVER(Order By T.VERSIONE Asc) LON_PREV
                              From nereus.STORICO_TRACCE_SISTEMA T
                              Where T.ID_TRACCIA_SISTEMA = IN_ID_TRACCIA_SISTEMA
                              And T.VERSIONE < IN_ID_VERSIONE
                              And T.LATITUDINE Is Not Null
                                And T.LONGITUDINE Is Not Null
                              And T.ID_VIAGGIO_IN_CORSO = IN_ID_VIAGGIO
                              And T.DATA_AGGIORNAMENTO Between LD_LIMITE_INFERIORE_DATA And LD_LIMITE_SUPERIORE_DATA) Z
                          Where ((LAT_PREV Is Not Null And Z.LATITUDINE <> LAT_PREV) Or (LON_PREV Is Not Null And Z.LONGITUDINE <> LON_PREV))
                          Or (LAT_PREV Is Null And LON_PREV Is Null);
         --on_out_total_count:=ref::numeric;
         RETURN;--on_out_total_count;

         Open ref1 FOR SELECT *
                       into oc_out_recordset
                       FROM
                      (Select *
                       From (Select Q.*, row_number() OVER (ORDER BY Q.VERSIONE) AS RNUM
                           From (Select Z.*
                               From (      Select T.*,
                                          LAG(T.LATITUDINE) OVER(Order By T.VERSIONE Asc) LAT_PREV,
                                          LAG(T.LONGITUDINE) OVER(Order By T.VERSIONE Asc) LON_PREV
                                    From nereus.STORICO_TRACCE_SISTEMA T
                                    Where T.ID_TRACCIA_SISTEMA = IN_ID_TRACCIA_SISTEMA --30001--
                                    And T.VERSIONE < IN_ID_VERSIONE --3--3--4--5 --
                                    And T.LATITUDINE Is Not Null
                                    And T.LONGITUDINE Is Not Null
                                    And T.ID_VIAGGIO_IN_CORSO = IN_ID_VIAGGIO --2 --
                                    And T.DATA_AGGIORNAMENTO Between LD_LIMITE_INFERIORE_DATA  And LD_LIMITE_SUPERIORE_DATA
                                                Order By T.VERSIONE Desc) Z
                                         Where ((LAT_PREV Is Not Null And Z.LATITUDINE <> LAT_PREV) Or (LON_PREV Is Not Null And Z.LONGITUDINE <> LON_PREV))
                                        Or (LAT_PREV Is Null And LON_PREV Is Null)) Q
                                  ) X
                             Where RNUM <= IN_LIMITE_SUPERIORE + ID_LIMITE_SUPERIORE_DATA ) Y          
                        Where RNUM > IN_LIMITE_SUPERIORE;
           
           RETURN ;--1; --oc_out_recordset ;
     End If;
     
     
Exception
    When Others Then
      PERFORM nereus.P_LOGGING(IN_TIPO        => 'E'::character varying,
                IN_CODICE      => Sqlstate::character varying,
                IN_DESCRIZIONE => Sqlerrm::character varying,
                IN_MODULO      => LK_PACKAGE_NAME::character varying,--LK_PACKAGE_NAME,
                IN_FUNZIONE    => LK_FUNCTION_NAME::character varying,--LK_FUNCTION_NAME,
                IN_NOTE        => 'ID_TRACK: ' || IN_ID_TRACCIA_SISTEMA || ' VERS: ' || IN_ID_VERSIONE || ' LIM_INF: ' || IN_LIMITE_INFERIORE || ' LIM_SUP: ' || IN_LIMITE_SUPERIORE || ' DATA_INF:' ||
                          TO_CHAR(ID_LIMITE_INFERIORE_DATA, 'DD/MM/YYYY HH24:MI:SS') || ' DATA_SUP: ' || TO_CHAR(ID_LIMITE_SUPERIORE_DATA,'DD/MM/YYYY HH24:MI:SS') || ' ID_VIAGGIO: ' || IN_ID_VIAGGIO::character varying,
            IN_USER_ID     => '0'::character varying);
      --ON_OUT_TOTAL_COUNT := -1;
     
End
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pck_gestione_tracce_sistema.p_get_rotta_traccia_sistema6(numeric, numeric, numeric, numeric, date, date, numeric)
  OWNER TO postgres;
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image

Can you clarify the problem? What is the underlying data ? ie data tables and example test data that replicates the problem.  What arguments have you provided to the function that illustrate the problem.
Try using TIMESTAMP instead of DATE type in PostgreSQL PostgreSQL DATE type does not have a time part whereas Oracle does. eg.
postgres=# create table xxx( t date );
CREATE TABLE
postgres=# insert into xxx values( now() );
INSERT 0 1
postgres=# select to_char(t,'DD/MM/YYYY HH24:MI:SS') from xxx;
       to_char
---------------------
 08/06/2017 00:00:00
Avatar of riccardo catelli
riccardo catelli

ASKER

Hello Heart Man 2 and thank you for the advice.
Unfortunately, it does not solve my problem.
I compiled commenting on the line of code between date1 and date2 but the result remains the same ... ie nothing (,). I have today sent the same problem with 3 different solutions. Only using the TABLE return data type I got the lines of the result sets but this forces me to have to modify the Oracle side the same function because it is obliged to use the same call both on the DB ORacle side and on the DB Postgres side. Other ideas?
Thank you

https://www.experts-exchange.com/questions/29028047/Code-equivalent-Type-Cursor-Oracle.html?notificationFollowed=190489983
Cursors to work correctly in PostgreSQL have to be tied to a transaction.  So you must open a transaction explicitly on the database connection before you open the cursor.  The cursor internally is just a string.  I'm not sure if you can get Oracle and PostgreSQL client code to be exactly the same if you insist on using cursor variables.

One possibility is to use table returning functions instead.  It depends if you need the cursor rescroll functionality.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.