The question: Why doesn’t my script create tables?
The other day I was testing Oracle SQL scripts generated by Vertabelo. Roughly, this is the code that was generated:
... -- Table: book CREATE TABLE book ( id integer NOT NULL, title varchar2(120) NOT NULL, isbn varchar2(15) NOT NULL, PRIMARY KEY (id) ) ; ...
I used sqlplus to execute my script and see if it’s correct.
sqlplus (database-details) < create.sql
The script run without errors but the tables where NOT created.
SQL> select table_name from user_tables; no rows selected
I copied and pasted part of the script into a new file:
-- Table: book CREATE TABLE book ( id integer NOT NULL, title varchar2(120) NOT NULL, isbn varchar2(15) NOT NULL, PRIMARY KEY (id) ) ;
I executed it in sqlplus and this time the table was created.
OK, I scratched my head. What is the difference between the two? The only real difference is the blank line at the end of the command. Could it REALLY be the reason?
The answer: blank lines prevent the execution of a command in SQL*Plus
Yes, that was the reason:
A blank line in a SQL statement or script tells SQL*Plus that you have finished entering the command, but do not want to run it yet.
A subsequent SQL statement overwrites the previous command in the buffer. So my CREATE TABLE command was read, parsed, stored in the buffer and then it was overwritten by the next CREATE TABLE command.
If you’re writing or generating SQL scripts for SQL*Plus, make sure there are no blank lines in your SQL commands or the statements will not be executed. The scripts generated for Oracle by Vertabelo don’t have the blank lines anymore.
Which command-line tools do you use with your Oracle database?