CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
B Ashwin
B Ashwin

Reputation: 7

Error while executing a stored procedure to drop a table

Getting error while executing a stored procedure that drops a table.

I have compiled the procedure successfully (used dynamic SQL for the code). Tool used is SQL Developer.

CREATE OR REPLACE PROCEDURE sp_DROP(P_VAR IN VARCHAR2)
IS 
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE'||P_VAR;  -- passing table via parameter
END;

but I'm getting error while executing this procedure:

EXECUTE sp_DROP('CON1'); -- i have made sure CON1 is a legit table.

Error :

ORA-00950: invalid DROP option
ORA-06512: at "HR.SP_DROP", line 4
ORA-06512: at line 3
00950. 00000 - "invalid DROP option"
*Cause:
*Action:

Thanks for the help.

Upvotes: 0

Views: 8683

Answers (1)

Jared
Jared

Reputation: 2954

Add a space to the end of drop table.

CREATE OR REPLACE PROCEDURE sp_DROP(P_VAR IN VARCHAR2)
IS 
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE '||P_VAR;  -- passing table via parameter
END;

Upvotes: 1

Related Questions