April 24, 2009 at 7:29 am
Can anyone explain to an Oracle novice why both of these create table statements run fine individually, but give an error when run in one batch
CREATE TABLE "TEST45" (ID NUMBER(5,0) NOT NULL ENABLE, DUMMY VARCHAR2(10), CONSTRAINT "TEST45_PK" PRIMARY KEY ("ID") ENABLE);
CREATE TABLE "TEST46" (ID NUMBER(5,0) NOT NULL ENABLE, DUMMY VARCHAR2(10), CONSTRAINT "TEST46_PK" PRIMARY KEY ("ID") ENABLE);
When run separately they work. When run in a single batch I get
ORA-00911: invalid character
Oracle 10g Express Edition
I have another 200 of these to create and I don't fancy doing it one by one. Is there a termination character that I've left out somewhere?
Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2009 at 11:29 am
It shouldn't fail; my wild guess is that the batch file has some bad character, look:
oracle [6]:
oracle [6]: cat createtable.sql
CREATE TABLE "TEST45" (ID NUMBER(5,0) NOT NULL ENABLE, DUMMY VARCHAR2(10), CONSTRAINT "TEST45_PK" PRIMARY KEY ("ID") ENABLE);
CREATE TABLE "TEST46" (ID NUMBER(5,0) NOT NULL ENABLE, DUMMY VARCHAR2(10), CONSTRAINT "TEST46_PK" PRIMARY KEY ("ID") ENABLE);
oracle [7]:
oracle [7]: sqlplus /
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Apr 24 17:27:11 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
SQL> @createtable.sql
Table created.
Table created.
SQL> select table_name from dba_tables where table_name like 'TEST4%';
TABLE_NAME
------------------------------
TEST46
TEST45
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
oracle [8]:
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 24, 2009 at 11:44 am
No odd characters, I checked, deleted and retyped pieces by hand. There's just a new line between the two (CR LF). I was wondering if I was missing a batch termination command or something.
Not using SQLPlus, I'm using the web interface that Oracle Express comes with. Shouldn't make a difference though
Not important. I'll finish running them one by one tomorrow.
Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2009 at 11:59 am
I'm not that familiar with Oracle express interface.
I would set the proper ORACLE_HOME and ORACLE_SID then open a SQLplus session and do it in one shot.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 30, 2009 at 3:08 pm
Hey Gail,
What client tool r u using to run ur Oracle create scripts? SQL Plus work sheet, Toad or SQL scratch Pad. I just ran ur scripts in SQL Plus worksheet and they ran fine. I know SQL Scratch pad doesnt allows u to execute multiple scripts
April 30, 2009 at 3:23 pm
Oracle Express has a web interface. i was using that. I think I'll find something else to use now.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 4, 2009 at 3:49 am
The character that is making a problem is (;)
It is used in Oracle when you make a script to end a command.
The problem is that some of the Oracle tools have separate execution modes "Execute a statement" and "Run Script".
Basically you receive an error when you try to run two or more statements separated by (;) in "Execute a statement" mode.
Run it as script and there will be no problem.
SQL Plus is running as script processor.
You have to put (;) at end of any SQL command, single or in script.
I hope I've been clear enough.
BR,
Aleksandar
May 4, 2009 at 4:00 am
Thanks. That'll be useful in the future.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2009 at 6:29 am
May 21, 2009 at 1:20 am
Yes you are right when using SQL*Plus.
(/) has e meaning of "run the script", or GO in MSSQL, and you have to use it at the end in order to execute the script by hitting "Enter".
(;) is used to end a SQL statement.
(/) in a file is used to group statements in executable batches.
For other programs like ApEx, SQL Tools you don't have to use (/) to start the execution. It's usually done by hitting F5 or F9 or some button named "execute" or "run"
May 22, 2009 at 2:55 am
Hi,
are you using TOAD (Tool for Application Developers) with you oracle database? I'm also new to Oracle, and have realised you can't execute more that one select statement in a query window at the same time, i think that might be the same problem with the two tables youre trying to create!
Regards
Whenever I get sad, I stop being sad and be awesome instead… true story!
May 22, 2009 at 3:12 am
Not using Toad. As I mentioned already in the thread, I was using the web interface that Oracle Express has.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2009 at 3:29 am
Yes, the web interface of the oracle is very nice environment to work!
The TOAD has many tools, and sometimes you didn't know which one to use!
May 29, 2009 at 9:26 am
If you are using TOAD or any other tool, there is a menu option (under editor) 'execute as acript' (lightning symbol) which will execute all the selected statements as one script.
May 29, 2009 at 9:29 am
As stated about four times already, I was using the web interface that comes with Oracle Express.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply