Fetch Returns only 1 Row - Interactive Grid - Oracle Apex

Question

I have an interactive grid which has a dynamic action to fetch returns from another table

begin

for c in (select 
REW_SIZE,
IN_STOCK,
DESCRIPTION,
FINANCIAL_YEAR_ID
into 
:REW_SIZE,
:IN_STOCK,
:DESCRIPTION,
:FINANCIAL_YEAR_ID
from 
T_SORDER_PROFOMA_REWINDING
where so_id = :so_id)

loop

:REW_SIZE := c.REW_SIZE;
:IN_STOCK :=c.IN_STOCK;
:DESCRIPTION:=c.DESCRIPTION;
:FINANCIAL_YEAR_ID:=c.FINANCIAL_YEAR_ID;

end loop;
end;

When I had simple select into query, it gave "exact fetch returns more than requested number of rows" thn I applied the above code but it returns only 2nd row. I have 2 rows in the table for this ID.

Your loop could get 2, 3 or 1000s of records but it's still just setting and overwriting the same output variables every time. Also, it doesn't make much sense to use an "INTO" clause in a FOR loop query. — Jeffrey Kemp
Ok I'll remove the into clause. How can I stop it from overwriting the same row? — sbl
The dynamic action is running some PL/SQL but it only has access to the currently selected row in the grid; you can't "move" to another record or create a new record this way. This looks like an X-Y problem; if you take a step back and explain what you are actually trying to achieve and why, a better solution may be able to be proposed. — Jeffrey Kemp
I need to takes rows from one table to show and enter into another table. — sbl
you'll have to do a bit more work. Start here: mywiki.wooledge.org/XyProblem then explain what exactly you're trying to achieve. — Jeffrey Kemp

Giliam Giliam · Accepted Answer · 2020-06-20T06:52:55

For what I understand, you want to get some extra data columns. Your best shot is to create a view to join these table and deliver the columns you need. If you need editing, you will have to create an instead of trigger on the view as well, to do a correct dml on one or both tables.

create view which joins both tables
create instead of trigger (if you need editing)
query of the Interactive Grid should be on the view

This solution adds the extra data columns to the source of the IG, which is better and easier in my opinion.

Fetch Returns only 1 Row - Interactive Grid - Oracle Apex

1 Answers