?
Solved

Oracle SQL Script Errors

Posted on 2005-03-23
10
Medium Priority
?
1,529 Views
Last Modified: 2008-01-09
Hi,

I am new to Oracle and I have the following issue.....

I have been given a SQL script file that basically contains many 'CREATE TABLE' statements....

When I execute this script file using 'SQL *Plus' most of the SQL CREATE statments work O.K.....However near the end I get the following error messages.

SP2-0734: unknown command beginning "USING INDE..." - rest of line ignored.
SP2-0734: unknown command beginning "STORAGE(IN..." - rest of line ignored.
SP2-0734: unknown command beginning "PCTINCREAS..." - rest of line ignored.
SP2-0734: unknown command beginning "TABLESPACE..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning ") PCTFREE ..." - rest of line ignored.
SP2-0734: unknown command beginning "STORAGE(IN..." - rest of line ignored.
SP2-0734: unknown command beginning "PCTINCREAS..." - rest of line ignored.
SP2-0734: unknown command beginning "TABLESPACE..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.

I am not sure how best to track down which SQL statements are the one(s) causing the problem, can you help...?

Also what does the 'SP2-0044' code mean...?

Thanks Ian


0
Comment
Question by:ISC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13613549
First:
SP2-0044 For a list of known commands enter HELP and to leave enter EXIT

Cause: An unknown command was entered.

Action: Check the syntax of the command you used for the correct options.


Not for what's causing it:
Check for a blank line above a line starting with: USING INDE
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13613570
FYI:

In the script you can also add prompts or set echo on:

for example:
prompt Creating table tab1
create table tab1.....

or use:  set echo on

This will cause ALL commands to be echoed back to the screen.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 13613576
Post your script...

You're missing ";" (semi-colons)  somewhere  
------------------------------------------------------------
SP2-0044   For a list of known commands enter HELP and to leave enter EXIT

Cause: An unknown command was entered.

Action: Check the syntax of the command you used for the correct options.

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:ISC
ID: 13613661
Sorry the script file is to large to post.....
0
 

Expert Comment

by:dragonemp
ID: 13614487
post the script from several lines before the error start so we can check it, I bet you miss a ";" somewhere.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 13614847
20 points says it's a blank line!!!

try running the following (keep the blank line above "using tablespace".  I'm unable to get this with a missing semi-colon.

drop table tab1;
create table tab1
(
      col1 char(1)
)

using tablespace tables;
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 13616568
The problem could be a blank line, or a missing semi-colon, or it could be an out-of-space problem in the tablespace where the tables are being created, if the tablespace is not set to "autoextend".  Slightwv has given you a good way to find the line(s) with the problem, by adding "prompt ..." lines that indicate how far along the script is.  Of course, now if you re-run this script from the top, it will "fail" on all of the tables that it successfully created the first time, because they already exist now.  So, the simplest way to troubleshoot now may be to drop all of the tables that were created, then try to run the script with these "prompt..." lines so you can see exactly what it is trying to do when it fails.

You may also want to add a "spool" command at the top and a "spool off" command at the bottom to capture the output to a log file in case it is hard to catch the error(s) from the screen.  To do that just add a line like this as the first line of your script:

spool C:\temp\sql_log.txt

and add this line as the last line of your script:
spool off

Obviously the "C:\temp" part of this command must be a drive\directory that is valid on the machine where you run SQL*Plus.
0
 
LVL 7

Expert Comment

by:Mehul Shah
ID: 13619053
Yes I agree with markgeer.

There must be some blank line in the create table statement. eg

CREATE TABLE TEST (
NAME CHAR(4),
ID NUMBER(2))

USING INDEX
TABLESPACE "XXXXX"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
PCTFREE 10 INITRANS 2 MAXTRANS 255)

TABLESPACE "YYYYYY" PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS  2147483645 PCTINCREASE 0)


Check for the same and let us know.
0
 
LVL 5

Expert Comment

by:helpneed
ID: 13641889
hi

The command entered was invalid.

Check the syntax of the command you used for the correct options


somethink like blank line or any other problem can cause these kind of problem my suggession is to
look up the entire query and try to locate the place

'SP2-0044' code means one particular error number 'sp' stands for sqlplus...

regards
0
 
LVL 1

Author Comment

by:ISC
ID: 13696532
Hi,

Sorry I did not reply earlier but I have been on holiday....

2slightWV" was spot on there was a blank line in the script file once I took this out and re-ran the script everything was fine...!

Many thanks for all your help...!

Ian


0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question