In trigger I have that lines:
<code>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)
</code>
<code>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)
</code>
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)
that gives me every row in “some_table”, not filtering anything by “place”
and misses some rows that should be there
(
<code>for x in (select * from some_table s where s.place = 'ABC')
</code>
<code>for x in (select * from some_table s where s.place = 'ABC')
</code>
for x in (select * from some_table s where s.place = 'ABC')
works fine tho)
example
some_table:
<code>| Place | InsPlace | InsState
| -------- | -------- |
| * | * | A
| * | ABC | A
| ABC | * | D
| ABC | ABC | A
</code>
<code>| Place | InsPlace | InsState
| -------- | -------- |
| * | * | A
| * | ABC | A
| ABC | * | D
| ABC | ABC | A
</code>
| Place | InsPlace | InsState
| -------- | -------- |
| * | * | A
| * | ABC | A
| ABC | * | D
| ABC | ABC | A
code:
<code>select '*' into place
from places p
where p.prime_commodity = p_Comm -- doesnt 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; --<- doesnt work
raise_application_error(-20001,place || chr(10) || dummy); -- only for debugging in trigger
</code>
<code>select '*' into place
from places p
where p.prime_commodity = p_Comm -- doesnt 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; --<- doesnt work
raise_application_error(-20001,place || chr(10) || dummy); -- only for debugging in trigger
</code>
select '*' into place
from places p
where p.prime_commodity = p_Comm -- doesnt 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; --<- doesnt work
raise_application_error(-20001,place || chr(10) || dummy); -- only for debugging in trigger
expected
<code>*
* * A
* ABC A
</code>
<code>*
* * A
* ABC A
</code>
*
* * A
* ABC A
result
<code>*
* * A
* ABC A
ABC * D
ABC ABC A
</code>
<code>*
* * A
* ABC A
ABC * D
ABC ABC A
</code>
*
* * 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
<code>CONTINUE when x.place <> place;
</code>
<code>CONTINUE when x.place <> place;
</code>
CONTINUE when x.place <> place;
at the begining of for loop
but now it doesnt give some rows that it should
and results in something like
<code>*
* * A <- wheres "* ABC A" row?
ABC * D
ABC ABC A
</code>
<code>*
* * A <- wheres "* ABC A" row?
ABC * D
ABC ABC A
</code>
*
* * A <- wheres "* ABC A" row?
ABC * D
ABC ABC A
tried: select * from some_table s where s.place = place in trigger
expected: rows with s.place = place
resulted: all rows
New contributor
user26814368 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
This is wrong:
<code>select * from some_table s where s.place = place)
---------------
this
</code>
<code>select * from some_table s where s.place = place)
---------------
this
</code>
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
<code>select p.place
into v_place --> this
from places p
where p.prime_commodity = p_Comm;
</code>
<code>select p.place
into v_place --> this
from places p
where p.prime_commodity = p_Comm;
</code>
select p.place
into v_place --> this
from places p
where p.prime_commodity = p_Comm;
and – finally:
<code>... where s.place = v_place)
</code>
<code>... where s.place = v_place)
</code>
... where s.place = v_place)