Oracle SQL - UNION breaks when "table does not exist"

Question

I am new to Oracle SQL (and not generally experienced in SQL anyway). I would appreciate it if I could get some help with my problem.

I need to query several tables and views (30 or so) and do it as a UNION across all and return a single result set. However, some of the tables may or may not exist. I know the exact names of all 30 tables/views to be queried. I may do other queries prior to doing the large UNION, but eventually, I must return a single result of all rows. I cannot "define" variables in my sql script.

These are constraints placed on my and beyond my control. In order to work with the existing workflow, I have to operate within these contraints.

The problem is that if any of the tables/views do not exist, then the entire UNION fails and I get no results at all. I get this at the first occurrence of the missing view/table:

SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

I would like be able to handle this without breaking. I'd like to simply skip over missing tables and return the remaining rows (though I know this is poor design to try to query without knowing if the table exists). It's ok if I end up including error messages as these will be skipped by the parser eventually.

I have something like this. If any of the views do not exist, everything fails and I get no results:

select col1 || col2 || ... from view1
union
select col1|| col2 || ... from view2
union
select name from view3
.  
.
select ... from view30;

I can switch to PL/SQL if that helps. I don't have much experience with PL/SQL but I can use it if it can solve the problem. I did some reading on PL/SQL but could not figure out some straightforward way of doing this.

Any help is highly appreciated. Let me know if I can provide any other details.

Thanks.

Answer 1

Colleagues don't say that you CAN'T do that - they are saying you SHOULDN'T, cause of the fundamental problems. But the world isn't perfect - it often gives you a saw and tells you to hammer a nail. There is a solution to your problem - it involves dynamic SQL and some custom PL/SQL code. Simplified solution:

Create a package and a function:

function does_tbl_exists(p_tbl_name in varchar2) return number is

  l_stmt varchar2(60);

begin

  l_stmt := 'select count(*) from ' ||p_tbl_name;
  execute immediate l_stmt;
return 1;
exception
  when others then
    return 0 ;
end;

Created a test table TEST_TBL with some data. And then we build up our SQL statement in a pl/sql block:

declare

  l_count number;
  l_stmt varchar2(400);
  l_tab_name_1 varchar2(30) := 'TEST_TBL';
  l_tab_name_2 varchar2(30) := 'TEST_TBL2';

begin

  if test_pck.does_tbl_exists(p_tbl_name => l_tab_name_1) = 1 then
    l_stmt := 'select count(*) count_number from ' || l_tab_name_1;
  end if;

  if test_pck.does_tbl_exists(p_tbl_name => l_tab_name_2) = 1 then

    if l_stmt is not null then
      l_stmt := l_stmt || ' union all' || chr(10);
    end if;

    l_stmt := l_stmt || 'select count(*) count_number from ' || l_tab_name_2;

  end if;

  if l_stmt is not null then

    l_stmt := 'select sum(tmp.count_number) from (' || l_stmt || ') tmp';

    dbms_output.put_line(l_stmt);
    execute immediate l_stmt into l_count;
    dbms_output.put_line('count: '||l_count);


  else

    dbms_output.put_line('Nothing todo.');

  end if;

end;

By this template, you can build up your final SQL statement. But use this as a last resort - I think you should still go to higher-ups and talk with them, that their current "requirement" is "bad" and they should feel "bad" (Insert meme here).