14 Replies Latest reply: Jun 2, 2009 10:24 AM by SanjayRs RSS

    check if table exists

    538299
      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
          JustinCave
          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
            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
              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
                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
                  hi

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

                  thank you
                  • 6. Re: check if table exists
                    181444
                    Justin left off the declare. See Nick's post. -- Mark --
                    • 7. Re: check if table exists
                      Nicolas.Gasparotto
                      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
                        JustinCave
                        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
                          "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
                            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
                              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
                                SanjayRs
                                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
                                  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
                                    SanjayRs
                                    Yes I know it is never ending :(

                                    SS