riccardo catelli
asked on
returned (.... , .....) why no data?
CREATE OR REPLACE FUNCTION pck_gestione_tracce_sistem a.p_get_ro tta_tracci a_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_SISTE MA';
LD_LIMITE_INFERIORE_DATA := TO_DATE(TO_CHAR(ID_LIMITE_ INFERIORE_ DATA,'DD/M M/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS');
LD_LIMITE_SUPERIORE_DATA := TO_DATE(TO_CHAR(ID_LIMITE_ SUPERIORE_ DATA,'DD/M M/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::characte r 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_INFERIOR E_DATA,'DD /MM/YYYY HH24:MI:SS') || ' DATA_SUP: ' || TO_CHAR(ID_LIMITE_SUPERIOR E_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_SIST EMA 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_coun t;
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_SIST EMA 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::characte r 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_INFERIOR E_DATA, 'DD/MM/YYYY HH24:MI:SS') || ' DATA_SUP: ' || TO_CHAR(ID_LIMITE_SUPERIOR E_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_sistem a.p_get_ro tta_tracci a_sistema6 (numeric, numeric, numeric, numeric, date, date, numeric)
OWNER TO postgres;
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_SISTE
LD_LIMITE_INFERIORE_DATA := TO_DATE(TO_CHAR(ID_LIMITE_
LD_LIMITE_SUPERIORE_DATA := TO_DATE(TO_CHAR(ID_LIMITE_
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
IN_FUNZIONE => LK_FUNCTION_NAME::characte
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_INFERIOR
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_SIST
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:
RETURN;--on_out_total_coun
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_SIST
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
IN_FUNZIONE => LK_FUNCTION_NAME::characte
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_INFERIOR
IN_USER_ID => '0'::character varying);
--ON_OUT_TOTAL_COUNT := -1;
End
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION pck_gestione_tracce_sistem
OWNER TO postgres;
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
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
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
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.
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 TRIALMembers 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.