ORA-00903:invalid table name

Question

I have the following code in PLSQL:

Declare
    tablename varchar2(20):='emp'; 
    drop_stmt  varchar2(2000);

begin
    drop_stmt:='drop table :1 ;';
    --dbms_output.put_line(drop_stmt); 
    execute immediate drop_stmt using tablename;
end;

Results in:

ORA-00903:invalid table name

ORA-06512: at line 8

However when I run:

drop table emp ;

it just successfully runs. What may be the cause of this error?

Answer 1

You must use this one:

drop_stmt:='drop table '||tablename; -- without ";" at the end of string
--dbms_output.put_line(drop_stmt); 
execute immediate drop_stmt;