Hello,
we have violations in the below example for the mentioned quality rule.
Engineering Dashboard - Source code display
procedure prOpenByKeys( pKeys in CBPKG##GLOBALTYPES.KeyListType
,pRefCursor out rctGUARANTEESETLINKS )
Is
Begin
-- Argument Assertion
-- cbpkg##assert.AllNotNull(pCurrentValueRawList => pKeys);
open pRefCursor for
select rawtohex(t.UIDGUARANTEESETID) As UIDGUARANTEESETID, rawtohex(t.UIDGUARANTEEID) As UIDGUARANTEEID, nvl(t.ora_rowscn, -99) As "concurrencyToken"
from CBHGUARANTEESETLINKS t
where t.UIDGUARANTEESETID in (select /*+ cardinality ( kk 10)*/ distinct hextoraw(column_value) key from table(cast(pKeys as CBTYPEGUIDARRAY)) kk);
End prOpenByKeys;
-- -----------------------------------------------------------------
Can you please tell us how the problem can be fixed and if it is really a violation or if it is a false positive?
Thanks
Elisa
Hello,
This is a true violation, we can that the SQL query:
from CBHGUARANTEESETLINKS t where t.UIDGUARANTEESETID in (select /*+ cardinality ( kk 10)*/ distinct hextoraw(column_value) key from table(cast(pKeys as CBTYPEGUIDARRAY)) kk);
is called inside the loop:
open pRefCursor for
...
...
End prOpenByKeys;
Regards,
Please sign in to leave a comment.
Hello!
Thanks for the answer. How this violation can be avoided in a stored procedure? How the code can be written in order to avoid the violation in your opinion?
Thanks for the collaboration
Elisa
Hello,
Can you provide us with the source code of the procedure "prOpenByKeys" this will help us to understand ?
Regards,
Hello,
the code is the following:
procedure prOpenByKeys( pKeys in CBPKG##GLOBALTYPES.KeyListType
,pRefCursor out rctGUARANTEESETLINKS )
Is
Begin
-- Argument Assertion
-- cbpkg##assert.AllNotNull(pCurrentValueRawList => pKeys);
open pRefCursor for
select rawtohex(t.UIDGUARANTEESETID) As UIDGUARANTEESETID, rawtohex(t.UIDGUARANTEEID) As UIDGUARANTEEID, nvl(t.ora_rowscn, -99) As "concurrencyToken"
from CBHGUARANTEESETLINKS t
where t.UIDGUARANTEESETID in (select /*+ cardinality ( kk 10)*/ distinct hextoraw(column_value) key from table(cast(pKeys as CBTYPEGUIDARRAY)) kk);
End prOpenByKeys;
Thank you! For this example this is a false violation, that should be investigated!
I invite you to open a customer ticket for the support to investigate the issue.