0

I've got a lot of stored procedure working on a view on a database-link.

I would like to check if the view is available before to execute all the procedures so I've created a check() function in an object oriented programming way.

Now I can make: if(checked()=1) then ..work.. else null; end if;

The function is: 

create or replace FUNCTION CHECK_MYVIEW RETURN NUMBER IS 
CHECKED NUMBER;  
BEGIN

  BEGIN 

    select 1
    into CHECKED 
    from MYVIEW 
    where rownum = 1
    ;

  EXCEPTION WHEN OTHERS THEN 
    CHECKED:=0;
  END;

  RETURN CHECKED;

END CHECK_MYVIEW;

I've written the check query after some test and the result is fine. With this kind of select I can tell if the view have some record and even if there is some connection problem with the remote database. But this is my own solution.

Is there an optimized oracle query to obtain the same feature? For any kind of exception don't work without the raise of an exception...

Jap Jap
  • 1
  • 1

1 Answers1

0

You could try to check metadata:

DECLARE 
  checked INT;
BEGIN

   SELECT COUNT(*)
   INTO checked
   FROM all_views@dblink
   WHERE name = 'xxx'
     AND owner = 'yyy';

   --checked 0 -- not exists, 1 - exists

END;
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275