Hello,
I am trying to implement a SQL profile for a sentence, which I think is not shared by the database, I am seeing a SQL sentence different only by one condition which is (file_id). It is displayed inside of the trace file as a different value for every execution.
The code of the package that is executing this sentence is as follow:
L_get_cost_change_rec := 'SELECT DISTINCT dt.item,
dt.supplier\_site as supplier,
il.loc,
il.loc\_type,
ROUND((1 - NVL( dt.' ||L\_discount\_1|| ' , 0) / 100) \*
(1 - NVL( dt.' || L\_discount\_2 || ', 0) / 100) \*
(1 - NVL( dt.' || L\_discount\_3 || ', 0) / 100) \*
(1 - NVL( dt.' || L\_discount\_4 || ', 0) / 100) \*
(1 - NVL( dt.' || L\_discount\_5 || ', 0) / 100) \* dt.unit\_cost,2) unit\_cost
FROM fah\_supp\_publish\_upld\_head sh,
fah\_supp\_publish\_upld\_detail dt,
item\_loc il,
store s,
fah\_v\_store\_supp\_key\_region sskr
WHERE sh.file\_id = '|| I\_file\_id || '
AND sh.file\_id = dt.file\_id
AND dt.status = ''N'' -- valid records only
AND dt.unit\_cost > 0 -- only valid costs
AND dt.item = il.item
AND il.loc = s.store
AND s.store\_close\_date IS NULL
AND EXISTS (SELECT 1
FROM fah\_rollout\_system\_matrix rol
WHERE rol.loc\_type = ''S''
AND rol.system = ''ORACLE''
AND rol.loc = s.store)
--
AND EXISTS (SELECT 1
FROM item\_supp\_country isc
WHERE isc.item = dt.item
AND isc.supplier = dt.supplier\_site
AND isc.origin\_country\_id = ''MX'')
--
AND EXISTS (SELECT 1
FROM item\_supp\_country\_loc iscl
WHERE iscl.item = dt.item
AND iscl.loc = il.loc
AND iscl.supplier = dt.supplier\_site
AND iscl.origin\_country\_id = ''MX''
AND iscl.unit\_cost != ROUND((1 - NVL( dt.' ||L\_discount\_1|| ' , 0) / 100)
*
(1 - NVL( dt.' || L\_discount\_2 || ', 0) / 100) \*
(1 - NVL( dt.' || L\_discount\_3 || ', 0) / 100) \*
(1 - NVL( dt.' || L\_discount\_4 || ', 0) / 100) \*
(1 - NVL( dt.' || L\_discount\_5 || ', 0) / 100) \* dt.unit\_cost,2))
AND sskr.region = sh.region
AND sskr.supplier\_key = sh.supplier\_key
AND sskr.store = s.store
' ;
This value is appearing with different value inside of the trace file:
WHERE sh.file_id = '|| I_file_id || '
Is there any way to use this value as bind variable inside of the package?
Thanks in advance.
Kind regards,
Francisco Mtz.
Well, its easy to use a bind variable, but the calling application needs to know this and supply it.
ie, the package would need to change the SQL to be a bind and then pass in the value during execution, ie
L_get_cost_change_rec := 'select ..... file_id = :file_id ... ';
open my_ref_cur for L_get_cost_change_rec using l_file_id
Alternatively you could have the package flip to cursor sharing before and after the call, ie
execute immediate 'alter session set cursor_sharing = force';
open my_ref_cur for L_get_cost_change_rec ;
execute immediate 'alter session set cursor_sharing = exact';
I prefer the former because it will guard you from SQL injection