Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Processing multiple commands

Posted on 2003-02-28
7
Medium Priority
?
1,398 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
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
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!

 

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

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month10 days, 9 hours left to enroll

572 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