Processing multiple commands

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).

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?

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.

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

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.

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