Simple PL/SQL to check if table exists is not working

1
votes

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!

The below code will not run:

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 := '<<< FAILED DROPPING table tbl_BSUK_PriceIssue >>>';
  dbms_output.put_line (v_line);
  WHEN OTHERS THEN
    v_line := '<<< Unknown Error >>>';
    dbms_output.put_line (v_line);
  END;


END;

I get the following error message, what am I doing wrong?!

Error starting at line : 17 in command - 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 := '<<< FAILED DROPPING table tbl_BSUK_PriceIssue >>>'; dbms_output.put_line (v_line); WHEN OTHERS THEN v_line := '<<< 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:

I'm actually trying to replace the following TSQL with a PL/SQL version:

-- 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 '<<< FAILED DROPPING TABLE dbo.tbl_BSUK_PriceIssue >>>'
    ELSE
        PRINT '<<< DROPPED TABLE dbo.tbl_BSUK_PriceIssue >>>'
END
go
2
sqloracleplsql
Do you execute it via sqlplus? – user3278460
I'm using SQL Developer. – Abu Dina

2 Answers

9
votes

try to remove END; in this section

 WHEN OTHERS THEN
    v_line := '<<< Unknown Error >>>';
    dbms_output.put_line (v_line);
 END;

UPD. Actually, you can do it a bit shorter, no need to check if table exists after drop

declare
  eTableNotExists exception;
  pragma exception_init(eTableNotExists, -00942);
begin
    EXECUTE IMMEDIATE 'DROP TABLE tbl_BSUK_PriceIssue';  
    dbms_output.put_line('<<< DROPPED TABLE dbo.tbl_BSUK_PriceIssue >>>');
exception 
  when eTableNotExists then null
  when others then    
    dbms_output.put_line ('<<< Unknown Error >>>' || sqlerrm);
end;
/
0
votes

I don't know about the error, but you can do what you want in a fraction of the code. You don't need the count variable if you use EXISTS() and you don't need EXECUTE IMMEDIATE because you don't have variable data in the command:

IF 1 = (SELECT 1 FROM user_tables WHERE table_name = 'TBL_BSUK_PRICEISSUE') THEN
  DROP TABLE tbl_BSUK_PriceIssue;
  IF 1 = (SELECT 1 FROM user_tables WHERE table_name = 'TBL_BSUK_PRICEISSUE') THEN
    RAISE EXCEPTION;
  END IF;
END IF;