do a select from a potentiel non existing table

Question

I have a simple query to run a many databases. The query looks like:

select max(mydate) from mytable;

But my problem is sometime the table does not exist. In this case, I don't want to have the "ORA-00942: table or view does not exist" message but another date (for example 01/01/2000). I there possibility to do that with a single select?

Answer 1

From Oracle 12, you can define a PL/SQL function in a query and use a dynamic query within the function to try to find the maximum date and, if the table does not exist, catch the exception and return the default value:

WITH function max_date RETURN DATE IS
  v_date DATE;
  table_does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);
BEGIN
  EXECUTE IMMEDIATE 'SELECT MAX(mydate) FROM mytable' INTO v_date;
  RETURN v_date;
EXCEPTION
  WHEN table_does_not_exist THEN
    RETURN DATE '2000-01-01';
END;
SELECT max_date() FROM DUAL;

Which, if the table does not exist, outputs:

MAX_DATE()
2000-01-01 00:00:00

And if the table does exist:

CREATE TABLE mytable (mydate) AS
  SELECT SYSDATE FROM DUAL;

Outputs:

MAX_DATE()
2025-01-22 11:24:18

fiddle

---

In earlier versions you can create the PL/SQL function in the database using a CREATE FUNCTION statement and then query the function.