5 Replies Latest reply: Nov 15, 2009 11:43 PM by onkar.nath RSS

    PL/SQL: ORA-00942: table or view does not exist

    onkar.nath
      All,

      I am running below anonymous block which is acting weired. Below blocks checks for the existence of a table. If it exists then it deletes records from the table else creates the table. But it is throwing below error:

      Error
      *********************************************************************************
      delete from test;
      *
      ERROR at line 39:
      ORA-06550: line 39, column 48:
      PL/SQL: ORA-00942: table or view does not exist
      ORA-06550: line 39, column 36:
      PL/SQL: SQL Statement ignored
      *********************************************************************************


      Anonymous Block
      *********************************************************************************
      DECLARE
      my_table_name tab.tname%type;

      BEGIN
      select tname
      into my_table_name
      from tab
      where upper(tname) like 'test'
      ;

      if my_table_name is null
                     then
      dbms_output.put_line( 'Table NOT found!');
      execute immediate ('create table tests select empno from test1 where 1 = 0');
      else
      dbms_output.put_line( 'Table found!');
      delete from test;
      end if;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      Null;
      END;

      /

      *********************************************************************************

      Please suggest.

      TIA
        • 1. Re: PL/SQL: ORA-00942: table or view does not exist
          Karthick_Arp
          *********************************************************************************
          DECLARE
          my_table_name tab.tname%type;  
          
          BEGIN
          select   tname 
          into       my_table_name
          from      tab 
          where    upper(tname) like 'test'
          ;
          
          if my_table_name is null  
                         then
          dbms_output.put_line( 'Table NOT found!');
          execute immediate ('create table tests select empno from test1 where 1 = 0');
          else
          dbms_output.put_line( 'Table found!');
          delete from test;
          end if;
          EXCEPTION
          WHEN NO_DATA_FOUND THEN
          Null;
          END;
          Your select statement will always fail. Why? Check the where condition.
          where upper(tname) like 'test'
          You are converting tname to upper case and checking it with 'test'.

          So i am not sure how you reach to the delete statement.

          Remove this piece of code
          EXCEPTION
          WHEN NO_DATA_FOUND THEN
          Null;
          END;
          and then try again.
          • 2. Re: PL/SQL: ORA-00942: table or view does not exist
            Twinkle
            I have modified your code little bit..
            SQL> drop table test;
            
            Table dropped.
            
            SQL> DECLARE
              2  v_count number; 
              3  BEGIN
              4  select count(tname) 
              5  into v_count
              6  from tab 
              7  where upper(tname) like 'TEST';
              8  if v_count =1 then
              9  dbms_output.put_line( 'Table found!');
             10  execute immediate 'delete from test';
             11  else
             12  dbms_output.put_line( 'Table NOT found!');
             13  execute immediate 'create table test as select empno from emp where deptno=10';
             14  end if;
             15  EXCEPTION
             16  WHEN NO_DATA_FOUND THEN
             17  Null;
             18  END;
             19  /
            Table NOT found!
            
            PL/SQL procedure successfully completed.
            
            SQL> select * from test;
            
                 EMPNO
            ----------
                  7782
                  7839
                  7934
            
            SQL> DECLARE
              2  v_count number; 
              3  BEGIN
              4  select count(tname) 
              5  into v_count
              6  from tab 
              7  where upper(tname) like 'TEST';
              8  if v_count =1 then
              9  dbms_output.put_line( 'Table found!');
             10  execute immediate 'delete from test';
             11  else
             12  dbms_output.put_line( 'Table NOT found!');
             13  execute immediate 'create table test as select empno from emp where deptno=10';
             14  end if;
             15  EXCEPTION
             16  WHEN NO_DATA_FOUND THEN
             17  Null;
             18  END;
             19  /
            Table found!
            
            PL/SQL procedure successfully completed.
            
            SQL> select * from test;
            
            no rows selected
            Twinkle
            • 3. Re: PL/SQL: ORA-00942: table or view does not exist
              onkar.nath
              thanks for the response. It really did worked but one thing, I could not understand is: what was the problem when I was using VARCHAR datatype variable to read the table name in and use that variable in IF block.

              Any thought on this?
              • 4. Re: PL/SQL: ORA-00942: table or view does not exist
                Twinkle
                Hi,
                onkar.nath wrote:
                thanks for the response. It really did worked but one thing, I could not understand is: what was the problem when I was using VARCHAR datatype variable to read the table name in and use that variable in IF block.

                Any thought on this?
                There is no problem with the datatype.
                If the select statement fails to search then it throws NO_DATA_FOUND exception. It doenot check whether you have an IF statement.
                Check the below code
                SQL> set serveroutput on
                SQL> DECLARE
                  2  my_table_name tab.tname%type; 
                  3  
                  4  BEGIN
                  5  select tname 
                  6  into my_table_name
                  7  from tab 
                  8  where upper(tname) like 'TEST'
                  9  ;
                 10  
                 11  if my_table_name is null 
                 12  then
                 13  dbms_output.put_line( 'Table NOT found!');
                 14  execute immediate 'create table tests select empno from test1 where 1 = 0';
                 15  else
                 16  dbms_output.put_line( 'Table found!');
                 17  execute immediate 'delete from test';
                 18  end if;
                 19  EXCEPTION
                 20  WHEN NO_DATA_FOUND THEN
                 21  Null;
                 22  END;
                 23  
                 24  /
                
                PL/SQL procedure successfully completed.
                we are not able to see the TABLE NOT FOUND message even after setting serveroutput on.
                Because you have provided null in exception block.

                If you remove exception block then,
                SQL> DECLARE
                  2  my_table_name tab.tname%type; 
                  3  BEGIN
                  4  select tname 
                  5  into my_table_name
                  6  from tab 
                  7  where upper(tname) like 'TEST';
                  8  if my_table_name is null 
                  9  then
                 10  dbms_output.put_line( 'Table NOT found!');
                 11  execute immediate 'create table tests select empno from test1 where 1 = 0';
                 12  else
                 13  dbms_output.put_line( 'Table found!');
                 14  execute immediate 'delete from test';
                 15  end if;
                 16  end;
                 17  /
                DECLARE
                *
                ERROR at line 1:
                ORA-01403: no data found
                ORA-06512: at line 4
                Execute immediate donot run in exception block
                SQL> DECLARE
                  2  my_table_name tab.tname%type;
                  3  BEGIN
                  4  select tname
                  5  into my_table_name
                  6  from tab
                  7  where upper(tname) like 'TEST';
                  8  --if my_table_name is not null
                  9  --then
                 10  dbms_output.put_line( 'Table found!');
                 11  execute immediate 'delete from test';
                 12  --end if;
                 13  exception
                 14  when no_data_found then
                 15   dbms_output.put_line( 'Table NOT found!');
                 16  execute immediate 'create table tests select empno from test1 where 1 = 0';
                 17  END;
                 18  /
                Table NOT found!
                DECLARE
                *
                ERROR at line 1:
                ORA-00922: missing or invalid option
                ORA-06512: at line 16
                ORA-01403: no data found
                
                after commenting it
                
                SQL> ed
                Wrote file afiedt.buf
                
                  1  DECLARE
                  2  my_table_name tab.tname%type;
                  3  BEGIN
                  4  select tname
                  5  into my_table_name
                  6  from tab
                  7  where upper(tname) like 'TEST';
                  8  --if my_table_name is not null
                  9  --then
                 10  dbms_output.put_line( 'Table found!');
                 11  execute immediate 'delete from test';
                 12  --end if;
                 13  exception
                 14  when no_data_found then
                 15   dbms_output.put_line( 'Table NOT found!');
                 16  --execute immediate 'create table tests select empno from test1 where 1 = 0';
                 17* END;
                SQL> /
                Table NOT found!
                
                PL/SQL procedure successfully completed.
                Your requirement of create table is not fulfilled.
                Thats is the reason.
                In such situation we take help of count() function.

                Do support others by marking the post helpful/correct if it helped you. So they can save time.

                :)
                Twinkle
                • 5. Re: PL/SQL: ORA-00942: table or view does not exist
                  onkar.nath
                  Thanks Twinkle. Nice explanation. I am not very a DBA but not very good developer. Thats the reason I was unable to figure out so simple thing. Anyways thanks a lot once again..

                  Onkar