I'm using Oracle Report Builder 11.1.2.2.0. I have few queries defined in my report and I want to execute some pl/sql code when there are no rows returned from one of my queries.
So for example:
if (query returned no rows) then
do_something();
end if;
How can I check it?
You can try to convert your query to a function with exception handling such as
create of replace function get_color( i_color_id color_palette.id%type )
return color_palette.fg_color%type is
o_color color_palette.fg_color%type;
begin
select fg_color
into o_color
from color_palette
where id = i_color_id;
return o_color;
exception when no_data_found then return null;
end;
and execute the code below
if ( get_color(:id) is null ) then
paint_it_to_black();
end if;
As far as I can tell, there's no way to do that - not in a simple manner, that is. You'll have to run the same query twice: once to display the result (if any), and another time to check whether that query returned something or not.
It means that it'll be much slower, of course (performing the same query twice).
A workaround might be preparing data into a separate table (see if you could use a global temporary table) only once, and then
select * from that_table (without any conditions, as you've already done that when you inserted data into it)Or, if a query you're interested in is simple & fast, just use it in your PL/SQL procedure. You'll have to maintain the same code on multiple places. See if you can create a function that returns a table - that would simplify things (sort of).