CODEWITHSUNDEEP
sqloracle-database
Roger
Roger

Reputation: 69

Getting an error ORA-00933: SQL command not properly ended

Im trying to insert data into an existing table but i getting an error. the table was defined as follows.

ITEM_NUM CHAR(4)

DESCRIPTION CHAR(30)

ON_HAND NUMBER(4,0)

CATEGORY CHAR(3)

PRICE NUMBER(6,2)

here is the SQL that is presenting the error

INSERT INTO NONGAME
    VALUES ("CD33", "Wood Block Set (48 piece)",36,"TOY",89.49),
               ("DL51", "Classic Railway Set",  12, "TOY", 107.95),
               ("DR67","Giant Star Brain Teaser",24,"PZL",31.95),
               ("FD11", "Rocking Horse",8,"TOY",124.95),
               ("FH24","Puzzle Gift Set",65,"PZL",38.95),
               ("KD34","Pentominoes Brain Teaser",60,"PZL",14.95),
               ("MT03","Zauberkasten Brain Teaser",45,"PZL",45.79),
               ("NL89", "Wood Block Set (62 piece)",32,"TOY",119.75),
               ("TW35","Fire Engine",30,"TOY",118.95);

Upvotes: 1

Views: 509

Answers (2)

connectedsoftware
connectedsoftware

Reputation: 7097

You need to change your syntax to use INSERT ALL and replace the double quotes with single.

INSERT ALL
   INTO NONGAME (ITEM_NUM, DESCRIPTION, ON_HAND, CATEGORY, PRICE) VALUES ('CD33', 'Wood Block Set (48 piece)',36,'TOY',89.49)
   INTO NONGAME (ITEM_NUM, DESCRIPTION, ON_HAND, CATEGORY, PRICE) VALUES  ('DL51', 'Classic Railway Set',  12, 'TOY', 107.95)
   -- ADD AS MENU RECORDS AS YOU WANT HERE
SELECT 1 FROM DUAL;

The SELECT 1 FROM DUAL is required as INSERT ALL requires a SELECT sub-query and this gives a single row of dummy data.

INSERT ALL gives performance improvements over individual insert statements so if you are inserting many records like this definitely use this syntax rather than inserting one at a time, see Oracle Insert All

Regarding your comment:

" i get ORA-00984: column not allowed here"

Because in SQL double quote marks are used for database object names: literals need single quote marks. (Code sample corrected to reflect this fact.)

Upvotes: 1

Srihari Karanth
Srihari Karanth

Reputation: 2167

Oracle supports single quotes and not double. Try this:

INSERT INTO NONGAME
    VALUES ('CD33', 'Wood Block Set (48 piece)',36,'TOY',89.49),
               ('DL51', 'Classic Railway Set',  12, 'TOY', 107.95),
               ('DR67','Giant Star Brain Teaser',24,'PZL',31.95),
               ('FD11', 'Rocking Horse',8,'TOY',124.95),
               ('FH24','Puzzle Gift Set',65,'PZL',38.95),
               ('KD34','Pentominoes Brain Teaser',60,'PZL',14.95),
               ('MT03','Zauberkasten Brain Teaser',45,'PZL',45.79),
               ('NL89', 'Wood Block Set (62 piece)',32,'TOY',119.75),
               ('TW35','Fire Engine',30,'TOY',118.95);

Upvotes: 1

Related Questions