what does the error "ORA-00933:" mean in my situation

Question

I am just a student and I am trying to get a table working but have tried everything I could only to end up with an error.

ORA-00933: SQL command not properly ended

Could anyone please help me understand what I'm doing wrong?

CREATE TABLE student
(
    idnumber VARCHAR2(8),
    firstname VARCHAR2(20),
    lastname VARCHAR2(20),
    dateofbirth DATE,
    address VARCHAR2(20),
    email VARCHAR2(20),
    programme VARCHAR2(5),
    points number(3),

    PRIMARY KEY (idnumber)
);

INSERT INTO student 
VALUES ('D1234567', 'Student ONE', 'TWO THREE', DATE '2000-05-10',
        'Thetown, the address', 'd1234567@mydit.ie', 'DT228', '380'),
       ('D2345678', 'Student TWO', 'FOUR FIVE', DATE '2000-04-10',
        'Thetown, the address', 'd2345678@mydit.ie', 'DT228', '280');

SELECT 
    firstname, lastname
FROM 
    student
WHERE 
    points > 300;

I don't think that is a valid `INSERT` statement. I think it would be easier to just write two separate `INSERT` statements. — Reinis Verbelis, Sep 21 '21 at 16:36
You have several commands there, try them one at a time to find the problem one — Hans Keﬆing, Sep 21 '21 at 16:36
If you change your `ECHO` setting with `set echo on`, then your console output will make it obvious which command is the cause. Then it's a simple matter of looking at the railroad diagrams in the docs. The blank lines in the middle of the `select` look troublesome, btw. — Jeff Holt, Sep 21 '21 at 16:46

score 3 · Answer 1 · answered Sep 21 '21 at 16:44

3

That is not valid insert syntax. You can only insert one row at a time that way, separating by comma will not work. To do multi-row inserts, use the syntax:

INSERT ALL
   INTO tbl (col1, col2) VALUES ('value', 'another value')
   INTO tbl (col1, col2) VALUES ('value', 'another value')
   INTO tbl (col1, col2) VALUES ('value', 'another value')
SELECT 1 FROM DUAL;

The select 1 from dual is required for the subquery needing a select statement. It's no different than doing multiple insert statements, so you might as well just break this into multiple statements unless you are doing thousands of inserts at once.

answered Sep 21 '21 at 16:44

Austin

2,203
3
12
28

1

"That is not valid insert syntax." is only partly correct. It is valid in standard SQL and most other DBMS I guess, but not in Oracle, because Oracle does not support the full `VALUES` clause. This support has been requested years ago in the Oracles feature requests here: https://community.oracle.com/tech/apps-infra/discussion/4391390/add-support-for-the-values-constructor. It got only few upvotes, because Oracle is successfully hiding their feature requests they call "Database ideas" in the bigger forum, where it is unlikely to be found and hard to search :-( – Thorsten Kettner Sep 21 '21 at 16:52
2

@ThorstenKettner I understand this request, but I also understand why Oracle does not want to implement it. Unlike from other databases Oracle's bind variables also support vector values so you can insert thousands of rows even using single INSERT having single VALUES clause. Also all SQLs have to be parsed and stored in shared pool. such construct might result into SQL having tens of megabytes. or even more. When parsing such a SQL you need to lock and free huge chunk of shared pool. These situations occasionally lead to outages on production systems, like `cursor: pin S wait on X ` event – ibre5041 Sep 21 '21 at 17:10
@ThorstenKettner Upvoting ibre5041's comment due to Oracle's *shared everthing* architecture. Promoting this answer in an Oracle context is promoting a solution that would lead to some having protracted response times for multiple reasons. Bad for customer, good for expensive consultant. – Jeff Holt Sep 21 '21 at 17:29
@ibre5041, @Jeff Holt: That is a point I hadn't considered. On the other hand, I doubt very much that above solution or the very common `select 1, 2 from dual union all select 3, 4 from dual union all ...` to circumvent the lack of the full values clause is any better regarding the shared pool. – Thorsten Kettner Sep 22 '21 at 05:09
`values` is a good shorthand for row generation as long as Oracle doesn't allow to select from nothing. You may insert thousands of rows via single prepared statement in other databases and languages also and it is a common pattern, so no one will use multiple `values` for insert as it may vary (if it is not a SQL-injected code). But as was mentioned above `select 'constant' from dual *` is not much better option and does not require much imagination to circumvent the limitation. – astentx Sep 22 '21 at 07:56

score 2 · Accepted Answer · answered Sep 21 '21 at 16:43

Run each of the statements one by one:

Create your table

CREATE TABLE student
(
idnumber VARCHAR2(8),
firstname VARCHAR2(20),
lastname VARCHAR2(20),
dateofbirth DATE,
address VARCHAR2(20),
email VARCHAR2(20),
programme VARCHAR2(5),
points number(3),
PRIMARY KEY (idnumber)
);

Insert the 1st row

INSERT INTO student VALUES
(  
'D1234567',
'Student ONE',
'TWO THREE',
DATE '2000-05-10',
'Thetown,thedress',
'd1234567@mydit.ie',
'DT228',
'380'
);

Insert the 2nd row

INSERT INTO student VALUES 
(
'D2345678',
'Student TWO',
'FOUR FIVE',
DATE '2000-04-10',
'Thetown,the adress',
'd2345678@mydit.ie',
'DT228',
'280'
);

Run the select

SELECT firstname, lastname
FROM student
WHERE points > 300;

Examples:

score -4 · Answer 3 · answered Sep 21 '21 at 16:37

-4

semicolon (;) is the problem, remove semicolon from last statement and you can get rid of this error, your code's last statement will become

SELECT firstname, lastname
FROM student
WHERE points > 300

Hope this will work

answered Sep 21 '21 at 16:37

Himanshu kumar

79
3

2

This sounds awfully wrong. The semicolon is *meant* to end SQL statements. – Thorsten Kettner Sep 21 '21 at 16:54
i found a similar answer on internet https://stackoverflow.com/a/33291713/16895959 you can check – Himanshu kumar Sep 21 '21 at 17:05
@Himanshukumar yes, and the solution there was not to remove a semicolon. Tools often use semicolons as statement terminators (or in SQL\*Plus, the SQL terminator character defaults to a semicolon, although it can be set to something else). The issue here is the malformed `values()` clause. – William Robertson Sep 21 '21 at 22:20

what does the error "ORA-00933:" mean in my situation

3 Answers3