Select from table that does not exist

Question

I have a question regarding ORACLE, I wrote a PLSQL CODE that checks if a table exists, if it exists then I select something from this table..pseudocode is like:

if (table exists) Select from table where....

the problem is that I always get an error if the table does not exist, even if the if condition is never met and the select statement is never executed.

I think it is because my code is checked at compile time: "select from.." and then it prints an error if the table does not exist. How can I solve such an issue?.. here is how my code looks like (I used generic names):

     DECLARE
          v_table_exists NUMBER;
       BEGIN

          SELECT NVL(MAX(1), 0)
            INTO v_table_exists
            FROM ALL_TABLES
           WHERE TABLE_NAME = 'TABLE_TEST';

          IF v_table_exists = 1 THEN
             INSERT INTO MY_TABLE(COLUMN1, COLUMN2, COLUMN3, COLUMN4)
             SELECT 1234,
                    5678,
                    T.COLUMN_TEST1,
                    T.COLUMN_TEST2


    FROM  TABLE_TEST T
          WHERE T.FLAG = 1;
      END IF;
   END;

Possible duplicate of [Using SQL query to determine if a table exists](http://stackoverflow.com/questions/4385346/using-sql-query-to-determine-if-a-table-exists) — Midwire, Jan 25 '17 at 15:35

Aleksej · Accepted Answer · 2017-01-25T17:03:23.430

The issue is exactly in the fact that your procedure con not be compiled as it refers to a non existing object; you may need some dynamic SQL for this; for example:

create or replace procedure checkTable is
    vCheckExists  number;
    vNum          number;
begin
    -- check if the table exists
    select count(1)
    into vCheckExists
    from user_tables
    where table_name = 'NON_EXISTING_TABLE';
    -- 
    if vCheckExists = 1 then
        -- query the table with dynamic SQL
        execute immediate 'select count(1) from NON_EXISTING_TABLE'
        into vNum;
    else
        vNum := -1;
    end if;

    dbms_output.put_line(vNum);
end;

The procedure compiles even if the table does not exist; if you call it now, you get:

SQL> select count(1) from NON_EXISTING_TABLE;
select count(1) from NON_EXISTING_TABLE
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> exec checkTable;
-1

PL/SQL procedure successfully completed.

Then, if you create the table and call the procedure again:

SQL> create table NON_EXISTING_TABLE(a) as select 1 from dual;

Table created.

SQL> exec checkTable;
1

PL/SQL procedure successfully completed.

The same way I showed a SELECT, you can do an UPDATE or whatever SQL query you need; if you do something different from a SELECT, the INTO clause has to be removed.

For example, say you need to insert into a different table, the above code should be edited this way:

if vCheckExists = 1 then
    execute immediate 'insert into target(a, b, c) select a, 1, 100 from NON_EXISTING_TABLE';
end if;

Can u look at my example (I editet my question)..So I need to write the whole statement with execute immediate.. — NoName123, Jan 25 '17 at 15:49

score 1 · Answer 2 · edited May 23 '17 at 12:33

1

Everything will need to be done in Dynamic SQL (DBMS_SQL) or EXECUTE_IMMEDIATE otherwise your code will never compile (or package will be invalided) if table does not exists.

DBMS_SQL Example

EXECUTE_IMMEDIATE Example

edited May 23 '17 at 12:33

Community

1
1

answered Jan 25 '17 at 15:41

Marco Polo

728
5
10

score 0 · Answer 3 · answered Jan 25 '17 at 15:39

0

According to this article, in Oracle Database Server static SQL is indeed checked at compile time to ensure referenced objects exist.

So I advise you to use dynamic SQL instead of static SQL, through a varchar for example.

answered Jan 25 '17 at 15:39

Fabian Pijcke

2,920
25
29

Can you look at my example? So I need to write my whole INSERT INTO STATEMENT in EXECUTE IMMEDIATE ('MY STATEMENT') right? – NoName123 Jan 25 '17 at 15:47

Select from table that does not exist

3 Answers3

Linked