Wednesday, December 19, 2012

Database: Oracle SQLPLUS command has vague behavior with comments.

In every project I am architecting, one of the main parts of architecture is the database architecture. Database architecture consists of developing ERD, meta-data document and finally the database script.

Database script is the organized and final set of files that allow DB administration, Testing team and Developers(runs script locally on their machines) to generate final database.

DB Scripts consist of a set of files. Each file contains related database types as separate .sql file. For example; tables has a file, triggers, types, packages, user and table space, views, and lookups ….. etc all of them has separate file.

The separation makes the final script maintainable.

Finally develop an OS (Windows, Mac, UNIX and Linux) specific script file to call all of the above described files in certain order the build final database from SQLPLUS.

One of interesting vague behavior I found while running the script is something like this:





This error "ERROR at line 1: ORA-00911: invalid character" prevents anything from being created, inserted, or compiled successfully.

To not fall in error like this again, which is inserting anything after the semicolon, don't code with the following code forms in the script (It will work from JDeveloper or SQL Developer):
  1. DDL OR DML STATEMENTS ; -- any comment.
  2. DDL OR DML STATEMENTS ; REM any comment.
  3. DDL OR DML STATEMENTS ; /*any comment.*/
The Solution is to put any comments before the semicolon as the following:
Bad example:
----------------


Good example:
------------------