sqloracletriggers

Oracle filtering wrong data in trigger


In a trigger, I have these SQL lines:

select p.place 
into place
from places p
where p.prime_commodity = p_Comm;

for x in (select * from some_table s where s.place = place) 

This returns every row in some_table, not filtering anything by "place", and misses some rows that should be there.

This works fine though:

for x in (select * from some_table s where s.place = 'ABC') 

Example: some_table:

Place InsPlace InsState
* * A
* ABC A
ABC * D
ABC ABC A

Code:

select '*' 
into place
from places p
where p.prime_commodity = p_Comm   -- doesn't matter, select '*' into place will select only '*', 

select listagg(s.place || ' ' || s.insplace || ' ' || s.insstate, chr(10)) within group (order by txt) 
into dummy from some_table s
where s.place = place; -- <-- doesn't work

raise_application_error(-20001,place || chr(10) || dummy); -- only for debugging in trigger

Expected:

*
* * A
* ABC A

Actual result:

*
* * A
* ABC A
ABC * D
ABC ABC A

(all rows anyway, "where s.place = place" can be commented and result not even gonna change)

Can it be fixed? What am I missing? Can it be that place is '*' symbol? Could it be that oracle uses it as "anything" symbol inside triggers for some reason?

In normal SQL window, any of it works like it should, only in trigger it does that shenanigan.

Before I used:

CONTINUE when x.place <> place;

at the beginning of for loop, but now it doesn't return some rows that it should, and results in something like

*
* * A    <- wheres "* ABC A" row?
ABC * D
ABC ABC A

I also tried:

select * 
from some_table s 
where s.place = place 

in the trigger

Expected: rows with s.place = place

Result: all rows


Solution

  • This is wrong:

    select * from some_table s where s.place = place) 
                                     ---------------
                                       this
    

    Never name variables the same as column names; it is the same as if you used where 1 = 1 (i.e. no filtering at all).

    Rename variable to e.g. v_place, then

    select p.place 
    into v_place              --> this
    from places p
    where p.prime_commodity = p_Comm;
    

    and - finally:

    ... where s.place = v_place)