?
Solved

Processing multiple commands

Posted on 2003-02-28
7
Medium Priority
?
1,395 Views
Last Modified: 2013-12-12
It's probably the easiest question on this site but I just can't seem to find a solution (and I only have 210 points!!) :

How can I issue multiple commands to Oracle without having to execute each one seperatley? I've checked out many web pages and they all indicate that a simple semicolon should work to terminate each line but Oracle simply returns

ORA-00911:invalid character

The code I'm attempting to run is as follows

DROP TABLE CC_CSCR; -- Control Record

CREATE TABLE CC_CSCR (
     KEY_CSCR                              VARCHAR2 (01),
     REFNO_CSCR                         NUMERIC   (06),
     PASSWD_CSCR                         VARCHAR2   (15),
     YEAR_CSCR                           NUMERIC   (04),
      PERD_CSCR                           NUMERIC   (04)
     );

INSERT INTO CC_
(KEY_CSCR, REFNO_CSCR, PASSWD_CSCR, YEAR_CSCR, PERD_CSCR
)
VALUES
(
'C','000000','STEVE','2003','05'
)

ANALYZE TABLE CC_CSCR COMPUTE STATISTICS;

CREATE UNIQUE INDEX
CC_CSCR_CC01 ON CC_CRCR
(
KEY_CSCR
);

ANALYZE INDEX CC_CSCR_CC01 ESTIMATE STATISTICS SAMPLE 20 PERCENT;

GRANT SELECT ON CC_CSCR TO ROLIVEDB;
GRANT UPDATE ON CC_CSCR TO ROLIVEDB;
GRANT INSERT ON CC_CSCR TO ROLIVEDB;
GRANT DELETE ON CC_CSCR TO ROLIVEDB;  

-- Now sign onto the read only version of this db and create the synonym
-- CREATE SYNONYM CC_CSCR FOR LIVEDB.CC_CSCR;
0
Comment
Question by:yerdaman
[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
7 Comments
 
LVL 15

Expert Comment

by:andrewst
ID: 8040923
Are you running this as a SQL Plus script exactly as shown?  Then there are several problems I can see:

1) You cannot use the -- comment syntax on the DROP TABLE command, that only works in PL/SQL

2) You are missing a semi-colon at the end of the INSERT statement.

3) The INSERT has table name CC_ which doesn't exist (did you mean CC_CSCR?)

4) The CREATE INDEX has table name CC_CRCR which doesn't exist (again, did you mean CC_CSCR?)

When I corrected all those problems, it ran fine for me (apart from the GRANTS because I don't have a role called ROLIVEDB, but they are fine).
0
 

Author Comment

by:yerdaman
ID: 8040975
Sorry about that Andrewst, I'll qualify my question more:

1) Yes - I am using PL/SQL
2)3) and 4) Yes, yes and yes

I didn't realise that PL/SQL couldn't use the ';' terminator. To be specific I'm actually using a product called 'PL/SQL developer' which I test my code on prior inclusion into VB.

Even with the bugs corrected I get the same result. Is there any PL/SQL equivalent to the semicolon terminator?
0
 
LVL 15

Expert Comment

by:andrewst
ID: 8041089
Now I am confused!  Where did I say PL/SQL can't use the ';' terminator?  Yes it can and must!

Actually, the code you have shown me is not PL/SQL, it is just SQL.  Most of the commands (create table, create index, analyze table - i.e. DML) cannot be run in PL/SQL directly anwway, only in SQL.  PL/SQL code always includes a "BEGIN" and an "END;"

So: what is the exact text of the script you are now running, and on which line does the error occur?

I am not familiar with PL/SQL Developer, so the problem may be with that somehow (maybe it can only handle one command at a time?).

Your (corrected) code works fine for me in SQL Plus (except the GRANTS as I said):

SQL> DROP TABLE CC_CSCR;

Table dropped.

SQL> CREATE TABLE CC_CSCR (
  2      KEY_CSCR                              VARCHAR2 (01),
  3      REFNO_CSCR                         NUMERIC   (06),
  4      PASSWD_CSCR                         VARCHAR2   (15),
  5      YEAR_CSCR                           NUMERIC   (04),
  6       PERD_CSCR                           NUMERIC   (04)
  7      );

Table created.

SQL> INSERT INTO CC_CSCR
  2  (KEY_CSCR, REFNO_CSCR, PASSWD_CSCR, YEAR_CSCR, PERD_CSCR
  3  )
  4  VALUES
  5  (
  6  'C','000000','STEVE','2003','05'
  7  );

1 row created.

SQL> ANALYZE TABLE CC_CSCR COMPUTE STATISTICS;

Table analyzed.

SQL> CREATE UNIQUE INDEX
  2  CC_CSCR_CC01 ON CC_CSCR
  3  (
  4  KEY_CSCR
  5  );

Index created.

SQL> ANALYZE INDEX CC_CSCR_CC01 ESTIMATE STATISTICS SAMPLE 20 PERCENT;

Index analyzed.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:yerdaman
ID: 8041566
The following is the code I am now attempting:

DROP TABLE CC_CSCR;

CREATE TABLE CC_CSCR (
     KEY_CSCR                              VARCHAR2  (01),
     REFNO_CSCR                         NUMERIC   (06),
     PASSWD_CSCR                         VARCHAR2  (15),
     YEAR_CSCR                           NUMERIC   (04),
      PERD_CSCR                           NUMERIC   (02)
     );

INSERT INTO CC_CSCR
(KEY_CSCR, REFNO_CSCR, PASSWD_CSCR, YEAR_CSCR, PERD_CSCR
)
VALUES
(
'C','000000','STEVE','2003','05'
);


ANALYZE TABLE CC_CSCR COMPUTE STATISTICS;

CREATE UNIQUE INDEX
CC_CSCR_CC01 ON CC_CSCR
(
KEY_CSCR
);

ANALYZE INDEX CC_CSCR_CC01 ESTIMATE STATISTICS SAMPLE 20 PERCENT;

As mentioned in original question, once I attempt to execute the following appears with the cursor placed immediatly before the first semicolon:

ORA-00911 : invalid character

Ahhhhh!!

I've just pasted the code into an SQL*Plus session and it worked!! I thought I had already tried this. It seems that if I paste the code directly into the 'SQL>' prompt it works but if I paste it into the editor and then attempt to execute it using the '/' command it fails.

Either way, I can now do the multiple commands in one go.

Thanks Andrewst
0
 
LVL 3

Expert Comment

by:rramineni
ID: 8042530
The reason for this is the way commands are executed from the buffer.

If you use the buffer to execute multiple commands, each command need to be terminated by "/". e.g. if you open the buffer using ed command and enter multiple command, you get this error Where as if you start the file the errors are not returned. This is exactly what is happening here.
This is not an issue when you copy paste from some other place.
0
 
LVL 9

Accepted Solution

by:
konektor earned 90 total points
ID: 8079480
it has a sense to run multiple command simultaneously only when each of them takes a long time and each of them does not colide with another.
u can user DBMS_JOB package to create jobs, which can do it together (note u have init.ora parameter job_queue_processes at value how many of processes u want to run simultaneously and job_queue_interval equal nonzero value)
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10152856
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ/No Refund.

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Henka
EE Cleanup Volunteer
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

771 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