Monday, September 1, 2014

sqlplus vs sqldeveloper

When I run a DDL script in SQLDeveloper, it just runs perfect. Same script in SQL throws plenty of errors like:

ORA-00955: name is already used by an existing object

Incidentally, SQLPlus runs me completely crazy because it never tells you in a single line which TABLE is actually giving trouble, so it becomes really difficult to grep and report for errors...

Anyway, it turns out that if you run this in SQLPlus:
  CREATE TABLE "BUILD_POINTS" 
   ( "ENV" VARCHAR2(2 BYTE), 
 "SUCCESS" NUMBER, 
 "POINTS" NUMBER
   ) ;
/
it interprets the final / as a "repeat last command", which obviously fails because the table was already created.

The problem is that I generate this DDL with a SQLDeveloper export, so I have little control on how this is being generated. I cannot unconditionally remove all / , because in the "create package" statement they are absolutely necessary. I should write a parsing script to conditionally remove the / when they belong to a "create table".

Being able to edit tables in SQLDeveloper and then export them is too convenient....I don't want to resort to having to code SQL manually...

One workaround could be inserting a table comment for each table, so that the / would be inserted after the "COMMENT ON TABLE" statement and not after the "CREATE TABLE" statement. Repeating a "COMMENT ON TABLE" doesn't generate any error.

See also this and better still this

An excellent solution is to upgrade to SQLDeveloper 4, which doesn't generate the ";\" sequence.

Check also the (quoting) checkbox called "Terminator" which when selected uses semicolons to terminate each statement (unquoting). It's in Tools/Database/Utilities/Export.



No comments: