CODEWITHSUNDEEP
sqloracle-databaseplsqlreportoraclereports
westman379
westman379

Reputation: 713

Oracle Reports Builder - checking if query is empty

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?

Upvotes: 0

Views: 964

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

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;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142743

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

  • check whether it contains any rows or not
  • simply 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).

Upvotes: 1

Related Questions