title: Simple PL/SQL to check if table exists is not working
body: <p>I'm in the process of converting some stored procedures from Sybase TSQL to Oracle PL/SQL and I've already come across a problem which I'm struggling to resolve!</p>
  
  <p>The below code will not run:</p>
  
  <pre><code>DECLARE
  
  t INT := 0;
  t_error EXCEPTION;
  v_line VARCHAR2(100);
  
  BEGIN
  
  SELECT COUNT(*) INTO t FROM user_tables WHERE table_name = UPPER('tbl_BSUK_PriceIssue');
  
  IF t = 1 THEN
    EXECUTE IMMEDIATE 'DROP TABLE tbl_BSUK_PriceIssue';
    t := 0;
    SELECT COUNT(*) INTO t FROM user_tables WHERE table_name = UPPER('tbl_BSUK_PriceIssue');
    IF t = 1 THEN
      RAISE t_error;
    END IF;
  END IF;
  
  EXCEPTION
    WHEN t_error THEN
    v_line := '&lt;&lt;&lt; FAILED DROPPING table tbl_BSUK_PriceIssue &gt;&gt;&gt;';
    dbms_output.put_line (v_line);
    WHEN OTHERS THEN
      v_line := '&lt;&lt;&lt; Unknown Error &gt;&gt;&gt;';
      dbms_output.put_line (v_line);
    END;
  
  
  END;
  </code></pre>
  
  <p>I get the following error message, what am I doing wrong?!</p>
  
  <blockquote>
    <p>Error starting at line : 17 in command - DECLARE</p>
    
    <p>t INT := 0; t_error EXCEPTION; v_line VARCHAR2(100);</p>
    
    <p>BEGIN</p>
    
    <p>SELECT COUNT(*) INTO t FROM user_tables WHERE table_name =
    UPPER('tbl_BSUK_PriceIssue');</p>
    
    <p>IF t = 1 THEN   EXECUTE IMMEDIATE 'DROP TABLE tbl_BSUK_PriceIssue';<br>
    t := 0;   SELECT COUNT(*) INTO t FROM user_tables WHERE table_name =
    UPPER('tbl_BSUK_PriceIssue');   IF t = 1 THEN
        RAISE t_error;   END IF; END IF;</p>
    
    <p>EXCEPTION   WHEN t_error THEN   v_line := '&lt;&lt;&lt; FAILED DROPPING table
    tbl_BSUK_PriceIssue >>>';   dbms_output.put_line (v_line);   WHEN
    OTHERS THEN
        v_line := '&lt;&lt;&lt; Unknown Error >>>';
        dbms_output.put_line (v_line);   END;
          END; Error report - ORA-06550: line 30, column 1: PLS-00103: Encountered the symbol "END" 
    06550. 00000 -  "line %s, column %s:\n%s"
    *Cause:    Usually a PL/SQL compilation error.
    *Action:</p>
  </blockquote>
  
  <p>I'm actually trying to replace the following TSQL with a PL/SQL version:</p>
  
  <pre><code>-- Create temp table for relevant trev_id's 
  IF OBJECT_ID('dbo.tbl_BSUK_PriceIssue') IS NOT NULL
  BEGIN
      DROP TABLE dbo.tbl_BSUK_PriceIssue
      IF OBJECT_ID('dbo.tbl_BSUK_PriceIssue') IS NOT NULL
          PRINT '&lt;&lt;&lt; FAILED DROPPING TABLE dbo.tbl_BSUK_PriceIssue &gt;&gt;&gt;'
      ELSE
          PRINT '&lt;&lt;&lt; DROPPED TABLE dbo.tbl_BSUK_PriceIssue &gt;&gt;&gt;'
  END
  go
  </code></pre>