This discussion is archived
14 Replies Latest reply: Jun 2, 2009 8:24 AM by 481319 RSS

check if table exists

538299 Newbie
Currently Being Moderated
hi

how to write this check in oracle:

if table X exists, select * from X else select ' table X does not exist'

thanks
  • 1. Re: check if table exists
    Justin Cave Oracle ACE
    Currently Being Moderated
    This sort of check is a bit of a red flag-- if you don't know that the table exists in advance, you cannot use static SQL to reference it. So everything would have to be dynamic SQL. You could potentially do something like
      l_cnt PLS_INTEGER;
    BEGIN
      SELECT COUNT(*)
        INTO l_cnt
        FROM dba_tables
       WHERE owner = <<table owner>>
         AND table_name = <<table name>>;
    
      IF( l_cnt > 0 )
      THEN
         EXECUTE IMMEDIATE 'SELECT col1 FROM x' 
            BULK COLLECT INTO some_collection;
      ELSE
        SELECT 'table X does not exist'
          INTO some_variable
          FROM dual;
      END IF;
    END;
    Justin
  • 2. Re: check if table exists
    538299 Newbie
    Currently Being Moderated
    I am getting SP2-0223 and SP2-0224

    SQL> l_cnt PLS_INTEGER;
    SP2-0223: No lines in SQL buffer.


    SQL> l_cnt PLS_INTEGER;
    SP2-0224: invalid starting line number


    thanks
  • 3. Re: check if table exists
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    With all the necessary warning to build such code, all the objects should exists befoer calling them, otherwise sql dynamic is required, you could try to manage exception :
    SQL> declare
      2      ct number;
      3      tbl_does_not_exists exception;
      4      pragma exception_init(tbl_does_not_exists,-942);
      5  begin
      6      execute immediate 'select count(*) from &table_name' into ct;
      7      dbms_output.put_line(ct);
      8  exception when tbl_does_not_exists then dbms_output.put_line('table does not exists');
      9  end;
     10  /
    Enter value for table_name: non_existing_table
    old   6:     execute immediate 'select count(*) from &table_name' into ct;
    new   6:     execute immediate 'select count(*) from non_existing_table' into ct;
    table does not exists
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.00
    SQL> /
    Enter value for table_name: dual
    old   6:     execute immediate 'select count(*) from &table_name' into ct;
    new   6:     execute immediate 'select count(*) from dual' into ct;
    1
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.00
    SQL>
    Nicolas.
  • 4. Re: check if table exists
    181444 Expert
    Currently Being Moderated
    Justin has provided one method using pl/sql to deal with the situation but I want to know why you do not know if the tables you want to query against exist or not? What is the business problem?

    If may be you can just use SQL against the rdbms dictionary: all_tables, all_views, etc ... to generate the select * from ... table_name in your list that do exist or you could generate queries against all tables owned by a specific username.


    The following is not as nice as what Justin posted but might help you determine what you want to do.
    set echo on
    --
    -- 20020308  Mark D Powell  New - Save common forum question
    --
    set verify off
    set serveroutput on
    declare
    --
    v_return      boolean ;
    --
    function test_tbl_exists(
      p_owner      in all_tables.owner%type
     ,p_table_name in all_tables.table_name%type
     ) return BOOLEAN is
    --
    v_hold         varchar2(1) ;
    --
    begin
    --
    select 'X'
    into   v_hold
    from   sys.all_tables
    where  table_name = upper(p_table_name)
    and    owner      = upper(p_owner);
    --
    return TRUE ;
    --
    exception
      when no_data_found then
           return FALSE ;
    end test_tbl_exists ;
    --
    begin
    --
    v_return := test_tbl_exists('&OWNER','&TBL_NAME')     ;
    if v_return
       then dbms_output.put_line('Found Table ')        ;
       else dbms_output.put_line('Did not find Table ') ;
    end if ;
    end ;
    /
    HTH -- Mark D Powell --
    add code tags (Duh)

    Edited by: MarkDPowell on Jun 2, 2009 2:08 PM
  • 5. Re: check if table exists
    538299 Newbie
    Currently Being Moderated
    hi

    I need to display the output of the select when the table exists

    thank you
  • 6. Re: check if table exists
    181444 Expert
    Currently Being Moderated
    Justin left off the declare. See Nick's post. -- Mark --
  • 7. Re: check if table exists
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    user535296 wrote:
    hi

    I need to display the output of the select when the table exists
    Well, I'm not quite sure what you are looking for, but if you fire a simple SELECT statement on SQL*Plus, that'll return you all the expected rows if table exists, if the table does not exists then you'll get a very understandable error message.

    Nicolas.
  • 8. Re: check if table exists
    Justin Cave Oracle ACE
    Currently Being Moderated
    Just as an FYI, I also omitted the declaration of some_variable and some_collection since it wasn't clear what the end goal was.

    Justin
  • 9. Re: check if table exists
    181444 Expert
    Currently Being Moderated
    "I need to display the output of the select when the table exists"

    I think we all understand that. The question is why wouldn't the table exist? As a rule in Oracle you create a table once and it remains in existance untill the applicaiton no longer needs to use the table. Often this is the life of the application.

    Why do you not know what tables exist?

    Justin showed you the technique for dynamically generating the select statements.

    I mentioned a method for generating the select statements based on your having a list of desired targets. No if test would be necessary since the script would only generate the select if the table existed. Then the generated script is ran spooling the resutls.

    It is generally unnecessary to do what you are asking to do when working with Oracle which is why several posters have asked you why?

    HTH -- Mark D Powell --
  • 10. Re: check if table exists
    538299 Newbie
    Currently Being Moderated
    I want something like this

    DECLARE
    cnt NUMBER;
    BEGIN
    SELECT COUNT(*) INTO cnt FROM USER1.TABLE1;
    IF( cnt = 0 )
    THEN
    dbms_output.put_line('Did not find Table ');
    ELSE
    execute immediate ('select * from USER1.TABLE1');
    END IF;
    END;
    /

    but it is not working :(
  • 11. Re: check if table exists
    181444 Expert
    Currently Being Moderated
    You need a loop so that you process the execute immediate once for each table name returned. Go back and look at the code posted to the thread as there are example loops.

    HTH -- Mark D Powell --
  • 12. Re: check if table exists
    481319 Guru
    Currently Being Moderated
    How about this
    CREATE OR REPLACE FUNCTION check_table_exists(Ptab VARCHAR2)
      RETURN SYS_REFCURSOR IS
      p_cur SYS_REFCURSOR;
      tbl_nf EXCEPTION;
      sql_stmt VARCHAR2(2000);
      PRAGMA EXCEPTION_INIT(tbl_nf, -942);
    BEGIN
      sql_stmt := 'select * from ' || ptab;
      OPEN p_cur FOR sql_stmt;
      RETURN p_cur;
    EXCEPTION
      WHEN tbl_nf THEN
        OPEN p_cur FOR 'select ''Table:' || ptab || ' NOT FOUND!''  TABLE_NOT_FOUND from dual ';
        RETURN p_cur;
    END;
    /
    
    SQL> l
      1* select  check_table_exists('EMP') from dual
    SQL> /
    
    CHECK_TABLE_EXISTS('
    --------------------
    CURSOR STATEMENT : 1
    
    CURSOR STATEMENT : 1
    
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
          7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
          7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-81        950                    30
          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
    
    14 rows selected.
    
    SQL> select  check_table_exists('EMP5') from dual  ;
    
    CHECK_TABLE_EXISTS('
    --------------------
    CURSOR STATEMENT : 1
    
    CURSOR STATEMENT : 1
    
    TABLE_NOT_FOUND
    ---------------------
    Table:EMP5 NOT FOUND!
    SS
  • 13. Re: check if table exists
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    The next question will be : what the OP want to do with the output ?
    Not easy to manipulate the columns afterwards.

    Nicolas.
  • 14. Re: check if table exists
    481319 Guru
    Currently Being Moderated
    Yes I know it is never ending :(

    SS

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points