Check for existence of table (or view) before upsert

Question

I am writing a deployment script that includes a MERGE into a view that may or may not exist in one of many target schemas (schemii? schemata?).

I am attempting this SQL:

DECLARE
BEGIN
    /* MERGE statement that will work if the view is available */
EXCEPTION
    WHEN OTHERS THAN
        DBMS_OUTPUT.put_line('warning: merge target is not available');
END;

When I run this block I get the standard "PL/SQL: ORA-00942: table or view does not exists" error.

How can I trap this error and generate a warning line instead?

You are getting that error when Oracle compiles / parses the pl/sql block, so it is not actually running the code. One way to around this would be to write the statement into a varchar2 string and run it with EXECUTE IMMEDIATE as that would raise the exception during runtime rather than at script compile time. — Michael Broughton, Jan 05 '16 at 18:31

Jeromy French · Answer 1 · 2016-01-06T21:53:17.293

4

Combining the valid answers with the suggested improvements, it all came together as:

SET SERVEROUTPUT ON;
PROMPT ...trying UPSERT;

DECLARE
    eTableNotExists exception;
    pragma exception_init(eTableNotExists, -00942);
BEGIN
    EXECUTE IMMEDIATE '<MERGE statement that will work if the view is available--no trailing ";"!>';

    DBMS_OUTPUT.put_line('insert worked');

EXCEPTION
    WHEN eTableNotExists THEN
        DBMS_OUTPUT.put_line('FYI: doesn''t exist on this schema');
END;
/

COMMIT;

SET SERVEROUTPUT OFF;

(SERVEROUTPUT sets were necessary to see results of DBMS_OUTPUT.put_line())

edited Jan 06 '16 at 21:53

answered Jan 05 '16 at 19:04

Jeromy French

11,812
19
76
129

1

I recommend removing the `WHEN OTHERS THEN ...`. If there's an error let Oracle use its default error propagation system, which lists all the errors and line numbers. It won't matter much for this little example, but on real production code it is infuriating when exceptions are caught and most of the trace information is lost. – Jon Heller Jan 06 '16 at 01:49

score 2 · Accepted Answer · answered Jan 05 '16 at 18:35

2

You need to use dynamic SQL:

BEGIN
    EXECUTE IMMEDIATE 'MERGE ...';
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('warning: merge target is not available');
END;

answered Jan 05 '16 at 18:35

Tony Andrews

129,880
21
220
259

4

For safety I'd trap and warn with the actual SQLERRM message in case something else is causing the problem besides table non-existance (Table locked, missing column, value error in the data, etc). WHEN OTHERS is best used with caution... – Michael Broughton Jan 05 '16 at 18:38
@MichaelBroughton yes I quite agree. – Tony Andrews Jan 06 '16 at 10:13

score 1 · Answer 3 · answered Jan 05 '16 at 18:35

1

You can use dynamic SQL to postpone such an error to run time and catch it

EXECUTE IMMEDIATE '<MERGE>'

instead of "static" MERGE

answered Jan 05 '16 at 18:35

Sergey Dmitriev

91
4

Check for existence of table (or view) before upsert

3 Answers3