Note: I do not have this problem when using Oracle SQL Developer:: - But it is not the Standard here. So i must find a way to do this in PL/SQL Developer
When attempting to use PL/SQL developer (PL/SQL Developer - the program) to dynamically drop tables then create new ones using a create statement I consistently run into the error:
PLS-00103: ENCOUNTERED THE SYMBOL "/" THE SYMBOL "/" WAS IGNORED PLSQL
This is due to the "/" at the end of the dynamic sql.
If I remove the "/" from the end I receive an error: ENCOUNTERED THE SYMBOL "CREATE"
What is the best method to get around this error inside PL/SQL Developer?
Thank You:
DECLARE
VE_TABLENOTEXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT(VE_TABLENOTEXISTS, -942);
PROCEDURE DROPTABLE(PIS_TABLENAME IN VARCHAR2) IS
VS_DYNAMICDROPTABLESQL VARCHAR2(1024);
BEGIN
VS_DYNAMICDROPTABLESQL := 'DROP TABLE ' || PIS_TABLENAME;
EXECUTE IMMEDIATE VS_DYNAMICDROPTABLESQL;
EXCEPTION
WHEN VE_TABLENOTEXISTS THEN
DBMS_OUTPUT.PUT_LINE(PIS_TABLENAME || ' NOT EXIST, SKIPPING....');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
END DROPTABLE;
BEGIN
DROPTABLE('foo.foo_table');
END DROPTABLE;
/
CREATE TABLE foo.foo_table AS
(
SELECT STUFF, MORE_STUFF FROM not_foo_table
)
;
SELECT * FROM foo.foo_table
;
I have too PLSQL Developer. I try to compile your procedure the way you post it and I get the same error, but if I remove the blank spaces before the "/" it works fine; like it don't recognize the "/".
So, I recommend you to change this:
END DROPTABLE;
/
For this:
END DROPTABLE;
/