SQL generated by Schema Compare to drop objects will not run in Oracle SQL Developer

I created a script using Red Gate Schema Compare and tried to run it in Oracle SQL Developer.

DROP TABLE schemaname.tablename;   schema name and table name are lower case
Error report -
Unknown Command

Adding double quotes around schema name and table name, i get the same error

drop table schemaname.tablename;
Error report - 
ORA-00933: SQL Command not properly ended

If I add double quotes around schema name and table name I get:
Error report - 
ORA-01741: illegal zero-length Identifier

I am not sure why these won’t run.  If I create the exact same statements manually, they run.  Have you ever seen anything like this?

It’s as if there are non-printable characters in the statements.  

Tagged:

Answers

  • mbruegelmbruegel Philadelphia, PAPosts: 29 Bronze 1
    Some things to try w/o any guarantee for success:

    1) try setting the Behavior option to Use forward Slash 
    2) try setting the Behavior option to Exclude Schema Names 
    3) are you executing the drop statement from within the schema that owns the object or are you running as a user w/ a DROP any PRIV?)
    4) check your encoding settings: Tools / Application Options
    -- default is UTF-16 (I haven't had issues w/ that).  Oracle SQL developer is a JAVA implementation so UTF16 would likely be most appropriate -- but you can try switching to ASCII

    (Sorry I use a different SQL IDE, but a SQL script is a SQL script and should behave similarly whether code generated or written from scratch -- check for any subtle syntax differences)

  • Eddie DEddie D Posts: 1,539 Rose Gold 3
    Hi, if you have copied and pasted into Oracle SQL Developer, have you missed any characters for example semi-colon at the end of your SQL statement?

    Are you able to provide an example of the syntax copied into Oracle SQL Developer?

    Many thanks
    Eddie
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
Sign In or Register to comment.