0

Could someone look at the code below and tell me why I get an invalid SQL statement when I run it?! What is exactly invalid about this?!

IF EXISTS(SELECT 1 FROM user_tables WHERE table_name = 'tbl_BSUK_PriceIssue') THEN
    DROP TABLE tbl_BSUK_PriceIssue;

    IF EXISTS(SELECT 1 FROM user_tables WHERE table_name = 'TBL_BSUK_PRICEISSUE') THEN
        SELECT '<<< FAILED DROPPING TABLE dbo.tbl_BSUK_PriceIssue >>>' FROM dual;
    ELSE
        SELECT '<<< DROPPED TABLE dbo.tbl_BSUK_PriceIssue >>>' FROM dual;
    END IF;
END IF;
Abu Dina
  • 207
  • 1
  • 5
  • 12
  • That doesn't answer the question I'm afraid. SELECTing 1 or COUNT(*) makes no difference as I expect a TRUE/FALSE result when I use the EXISTS operator. – Abu Dina May 20 '14 at 10:50
  • 2
    You apparently don't run this as a PL/SQL block. You can't use `IF` outside of PL/SQL (and anything not inside `begin ... end;` is SQL, not PL/SQL –  May 20 '14 at 10:54
  • I put a BEGIN and END around the above code and now I get an error about encountering the symbol DROP when expecting one of the following (begin case declare exit for goto if loop mod null pragma) ?!!!!! – Abu Dina May 20 '14 at 10:58
  • You can't do DDL (i.e. `drop`) natively in PL/SQL; you'd need to use dynamic SQL for that (`execute immediate 'DROP ...'`). But you seem to have your table name in mixed case in the initial check, which won't find anything unless the table was originally created with a quoted identifier; and then unquoted in the second check. If the `drop` failed then you'd get an exception so you'd never reach that check anyway. Your message `select` statements will also error as you need to select *into* something. Does the `dbo.` in the messages mean you just copied this from SQL Server? – Alex Poole May 20 '14 at 11:40
  • Yea that's right. This is coming from Sybase TSQL. I am finally beginning to understand the difference between PLSQL and TSQL!! – Abu Dina May 20 '14 at 12:46
  • [This might be useful](http://stackoverflow.com/a/1799215/266304). Doesn't answer the actual question you asked, so isn't quite a duplicate; but shows how to achieve the conditional drop. – Alex Poole May 20 '14 at 13:01

1 Answers1

1

I cant see the problem with your code but the following works for me

set serveroutput on;
declare
  l_res number;
begin
  select count('x') 
  into l_res
  from user_tables 
  WHERE table_name = 'JFTEST'; --'tbl_BSUK_PriceIssue';

  if l_res=0 then
    dbms_output.put_line('Table not found');
  else
    dbms_output.put_line('Table Exists');
  end if;
end;
Robert3452
  • 1,354
  • 2
  • 17
  • 39
  • Thanks. I'm using QweryBuilder (now that I got to connect to the Oracle instance) so maybe this is an issue with the editor? I need to use this as I am writing data edit script. The script needs to show the data BEFORE and AFTER the changes and I need this displayed in just one grid. – Abu Dina May 20 '14 at 10:54