Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01003: no statement parsed on CREATE TABLE (Oracle Database 10g Express Edition Release 10.2.0.1.0, Windows XP Pro SP3)
icon5.gif  ORA-01003: no statement parsed on CREATE TABLE [message #443867] Wed, 17 February 2010 13:36 Go to next message
dolot
Messages: 11
Registered: July 2009
Junior Member
I have the following create table statement:

create table SELECTION_BOOKMARK(
INSTALLATION_ID SMALLINT NOT NULL,
BOOKMARK_ID SMALLINT NOT NULL,
NAME VARCHAR2(50) NOT NULL,
SORT_ORDER SMALLINT NULL
);

When I execute this statement as part of a SQL script, it throws the error "ORA-01003: no statement parsed". If I execute it as a stand alone sql command, it returns no error.

Here's the strange thing, though. Even when it throws the error message the table is created. Later on in the script, I execute the following command:

alter table SELECTION_BOOKMARK add primary key(INSTALLATION_ID,BOOKMARK_ID);

This command also throws the above mentioned error, but once again the command executes - the primary key is created.

So you may say "what's the problem?" Well, I don't like having a script that throws errors. Eventually I'll have to give this script to a dba in another organization and I don't want my script to be throwing errors - even if it's apparently working correctly despite the errors.

Thanks for any help you can give.
Re: ORA-01003: no statement parsed on CREATE TABLE [message #443868 is a reply to message #443867] Wed, 17 February 2010 13:46 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>When I execute this statement as part of a SQL script,
I suspect problem is with statement in script that preceeds CREATE TABLE.

post content of complete script file.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: ORA-01003: no statement parsed on CREATE TABLE [message #443869 is a reply to message #443867] Wed, 17 February 2010 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How can you expect someone can debug a script he can't see?

Regards
Michel
Re: ORA-01003: no statement parsed on CREATE TABLE [message #443870 is a reply to message #443868] Wed, 17 February 2010 13:51 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The CREATE TABLE works just fine when part of an SQL script (tested on Oracle 10g XE):
SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> get p.sql
  1  select count(*) from tab;
  2  create table SELECTION_BOOKMARK(
  3  INSTALLATION_ID SMALLINT NOT NULL,
  4  BOOKMARK_ID SMALLINT NOT NULL,
  5  NAME VARCHAR2(50) NOT NULL,
  6  SORT_ORDER SMALLINT NULL
  7  );
  8* select count(*) from tab;
  9
SQL>
SQL> @p

  COUNT(*)
----------
        20


Table created.


  COUNT(*)
----------
        21

SQL>
Re: ORA-01003: no statement parsed on CREATE TABLE [message #443871 is a reply to message #443870] Wed, 17 February 2010 13:56 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
no error.
no problem.

case closed!

By the way, it is a bad idea to use a KEYWORD such as NAME for a object name.

[Updated on: Wed, 17 February 2010 14:12]

Report message to a moderator

Re: ORA-01003: no statement parsed on CREATE TABLE [message #443874 is a reply to message #443867] Wed, 17 February 2010 15:35 Go to previous messageGo to next message
dolot
Messages: 11
Registered: July 2009
Junior Member
Attached is the complete script. Sorry for not including it earlier.
Re: ORA-01003: no statement parsed on CREATE TABLE [message #443887 is a reply to message #443874] Wed, 17 February 2010 19:32 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
I get NO error while running posted SQL file using sqlplus.
Re: ORA-01003: no statement parsed on CREATE TABLE [message #443890 is a reply to message #443874] Wed, 17 February 2010 22:00 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
This kind of error may Occur if the user (executing the script) does not have sufficient privileges.....(explicit "create table" privilege ).

But this is not your case as the table created...

Why can`t you show us the screen shot or any spooled file of the out put you have.....
And the privileges granted to the user
Because the script which you have sent/attached works perfect(without no such errors)....


sriram Smile

[Updated on: Wed, 17 February 2010 22:01]

Report message to a moderator

Re: ORA-01003: no statement parsed on CREATE TABLE [message #444059 is a reply to message #443867] Thu, 18 February 2010 10:27 Go to previous messageGo to next message
dolot
Messages: 11
Registered: July 2009
Junior Member
I run the script using Application Express that comes with my version of Oracle.

BlackSwan: What version were you running the script against so that you got no errors?


I've attached a file showing a screenshot of a test user I created to run the script. I'll attach the detailed output in another post.

Maybe this will be of some use.

Thanks!
  • Attachment: User.JPG
    (Size: 121.72KB, Downloaded 460 times)
Re: ORA-01003: no statement parsed on CREATE TABLE [message #444060 is a reply to message #443867] Thu, 18 February 2010 10:28 Go to previous messageGo to next message
dolot
Messages: 11
Registered: July 2009
Junior Member
Detailed output...
Re: ORA-01003: no statement parsed on CREATE TABLE [message #444061 is a reply to message #444059] Thu, 18 February 2010 10:31 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 08:30:13 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Re: ORA-01003: no statement parsed on CREATE TABLE [message #444063 is a reply to message #443867] Thu, 18 February 2010 10:40 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks like it's a bug in Application Express.
Re: ORA-01003: no statement parsed on CREATE TABLE [message #444065 is a reply to message #444063] Thu, 18 February 2010 10:45 Go to previous message
dolot
Messages: 11
Registered: July 2009
Junior Member
If that's the case, then that's perfectly acceptable. If it isn't going to be seen in a production environment I'm fine - we can just ignore it during development.
Previous Topic: Sql Query
Next Topic: Using For Update in Cursor
Goto Forum:
  


Current Time: Mon Sep 26 16:19:13 CDT 2016

Total time taken to generate the page: 0.14312 seconds