Hi all!
I need help.
I have this function that launches a Select. My Oracle Grid tells me, when I tune the Select of this function, that there is a Cartesian Product. I understand what a Cartesian Product is, but I fail to find it. Can you help me by pointing out WHERE is the misteke? Thank you!!
P.s. Oracle EE 10.2.0.4 on windows Server 2003 R2 SP2 64Bit
[code]
create or replace
FUNCTION "ANA_GETVERBALE"
(
v_VerbaleId IN NUMBER DEFAULT NULL,
v_Sezione IN VARCHAR2 DEFAULT NULL,
v_NumeroVerbale IN VARCHAR2 DEFAULT NULL,
v_DataVerbale IN DATE DEFAULT NULL,
v_TargaVeicolo IN VARCHAR2 DEFAULT NULL,
v_Serie IN VARCHAR2 DEFAULT NULL,
v_LoggedUser IN VARCHAR2 DEFAULT NULL
)
RETURN SYS_REFCURSOR
AS
cv_1 SYS_REFCURSOR;
BEGIN
OPEN cv_1 FOR
SELECT
ANA_M.VerbaleId,
ANA_M.Sezione,
ANA_M.NumeroVerbale,
ANA_M.DataVerbale,
ANA_M.TargaVeicolo,
ANA_M.Serie,
SCH_C.StatoCartellinoId,
LOV_StatoCartellino.ListOfValueName StatoCartellino,
sch_c.cartellinoid
FROM ANA_Materia_Verbale ANA_M
INNER JOIN SCH_Cartellini SCH_C ON SCH_C.SoggettoId=ana_m.verbaleid AND SCH_C.TipoSoggettoId = SIS_CONSTANTS_PKG.VB
INNER JOIN SIC_PROFILO_STATO SIC_PSC ON SIC_PSC.STATOID=SCH_C.STATOCARTELLINOID
INNER JOIN SIC_PROFILI_USERS SIC_PUC ON SIC_PUC.PROFILOID=SIC_PSC.PROFILOID AND SIC_PUC.PERSONALID=v_LoggedUser
LEFT JOIN TYP_ListOfValues LOV_StatoCartellino ON LOV_StatoCartellino.ListOfValueId = SCH_C.StatoCartellinoId
WHERE ( v_VerbaleId = SIS_CONSTANTS_PKG.AnyBigint OR ANA_M.VerbaleId = v_VerbaleId )
AND ( v_Sezione = SIS_CONSTANTS_PKG.AnyString OR REGEXP_LIKE(ANA_M.Sezione, '^' || v_Sezione || '$', 'i') )
AND ( v_serie = SIS_CONSTANTS_PKG.AnyString OR REGEXP_LIKE(ANA_M.Serie, '^' || v_Serie || '$', 'i') )
AND ( v_NumeroVerbale = SIS_CONSTANTS_PKG.AnyString OR ANA_M.NumeroVerbale = v_NumeroVerbale )
AND ( v_DataVerbale = SIS_CONSTANTS_PKG.AnyDateTime OR ANA_M.DataVerbale = v_DataVerbale )
AND ( v_TargaVeicolo = SIS_CONSTANTS_PKG.AnyString OR REGEXP_LIKE(ANA_M.TargaVeicolo,'^' || v_TargaVeicolo || '$', 'i') );
RETURN cv_1;
END;
[/code]