Why do I get an error when I try to insert data into my table?

Question

I've a homework from school to do and a big problem with inserting the data into my table. The sql-file has this query:

insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,SER_NR,PER_NR,AB_ZEIT) values
('06.07.13','LH-888','Boeing','B747','9fg-he-ztu8',10010071,11.23);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,SER_NR,PER_NR,AB_ZEIT) values
('08.10.13','LH-238','Airbus','A320','z3et-bwe7',10010072,22.06);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,SER_NR,PER_NR,AB_ZEIT) values
('13.11.13','LH-341','Boeing','B737','ba23-0012',10010001,10.23);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,SER_NR,PER_NR,AB_ZEIT) values
('14.11.13','LH-358','Boeing','B737','ba23-0012',10010001,08.17);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,SER_NR,PER_NR,AB_ZEIT) values
('13.11.13','LH-553','Boeing','B777','xv23-0889',10010002,16.53);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,SER_NR,PER_NR,AB_ZEIT) values
('15.11.13','LH-421','Boeing','B777','xv56-3142',10010002,14.45);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,SER_NR,PER_NR,AB_ZEIT) values
('17.11.13','LH-789','Airbus','A330','45-6789',10010003,8.11);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,SER_NR,PER_NR,AB_ZEIT) values
('14.11.13','LH-112','Boeing','B737','ba23-0034',10010001,8.14);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,SER_NR,PER_NR,AB_ZEIT) values
('17.11.13','LH-421','Boeing','B777','xv23-0889',10010002,16.26);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,SER_NR,PER_NR,AB_ZEIT) values
('18.11.13','LH-223','Airbus','A380','ab-45-6xf',10010004,09.45);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,SER_NR,PER_NR,AB_ZEIT) values
('19.11.13','LH-634','Airbus','A350','5478-awe3',10010005,20.25);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,SER_NR,PER_NR,AB_ZEIT) values
('18.02.14','LH-238','Airbus','A320','z3et-bwe7',10010072,23.06);

Everytime I want to run it in the SQL Workshop on my Oracle Application Express 11g I am getting this error:

ORA-00911: invalid character

Is there an error in the syntax? Am I forgetting some parentheses? Or something else? This is the infos about the table, in which I want to insert the data into: enter image description here

I would be very thankful for your help. I am just starting to learn SQL at my class. Thank you!

Try inserting one row at a time. Do you get the error for each line, or just for one? — gimix, Aug 19 '22 at 14:10
when I insert just one line, I am getting this message: ORA-02291: integrity constraint (ORACLEKURS.FK_ABFLUG_MIT_FLUGZEUG) violated - parent key not found — DonDon, Aug 19 '22 at 14:15
Are you trying to run multiple statements at once in the 'SQL Command' area; or using 'SQL Scripts'? — Alex Poole, Aug 19 '22 at 14:20
Well, it worked after I've dropped the old table and rearranged it like this: create table abflug ( ab_datum varchar2(15), f_bez varchar2(10), herst varchar2(10), typ varchar2(10), per_nr varchar2(15), ser_nr integer, ab_zeit number(9,2) ); But only when I've inserted them line by line. — DonDon, Aug 19 '22 at 14:32
I'm not familiar with Apex SQL Worksheet, but it only accepts one command at a time. Maybe if you convert it into a single PL/SQL block by putting `begin` at the top and `end;` at the bottom, it will accept that. — William Robertson, Aug 20 '22 at 10:13
Does your assignment include the table definition? It seems like `ab_datum` should be `date`, not `varchar2(15)`. If it's a date column, you write dates like this: `date '2013-07-07'` (but don't try to put dates in a varchar2 column or strings in a date column). — William Robertson, Aug 20 '22 at 10:17

score 1 · Answer 1 · answered Aug 19 '22 at 14:14

Your column list for the INSERTs is incorrect. This works -

--drop table abflug purge;

create table abflug (
 ab_datum varchar2(15),
 f_bez varchar2(10),
 herst varchar2(10),
 typ varchar2(10),
 per_nr varchar2(15),
 ser_nr integer,
 ab_zeit number(9,2)
);



insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,PER_NR,SER_NR,AB_ZEIT) values
('06.07.13','LH-888','Boeing','B747','9fg-he-ztu8',10010071,11.23);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,PER_NR,SER_NR,AB_ZEIT) values
('08.10.13','LH-238','Airbus','A320','z3et-bwe7',10010072,22.06);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,PER_NR,SER_NR,AB_ZEIT) values
('13.11.13','LH-341','Boeing','B737','ba23-0012',10010001,10.23);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,PER_NR,SER_NR,AB_ZEIT) values
('14.11.13','LH-358','Boeing','B737','ba23-0012',10010001,08.17);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,PER_NR,SER_NR,AB_ZEIT) values
('13.11.13','LH-553','Boeing','B777','xv23-0889',10010002,16.53);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,PER_NR,SER_NR,AB_ZEIT) values
('15.11.13','LH-421','Boeing','B777','xv56-3142',10010002,14.45);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,PER_NR,SER_NR,AB_ZEIT) values
('17.11.13','LH-789','Airbus','A330','45-6789',10010003,8.11);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,PER_NR,SER_NR,AB_ZEIT) values
('14.11.13','LH-112','Boeing','B737','ba23-0034',10010001,8.14);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,PER_NR,SER_NR,AB_ZEIT) values
('17.11.13','LH-421','Boeing','B777','xv23-0889',10010002,16.26);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,PER_NR,SER_NR,AB_ZEIT) values
('18.11.13','LH-223','Airbus','A380','ab-45-6xf',10010004,09.45);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,PER_NR,SER_NR,AB_ZEIT) values
('19.11.13','LH-634','Airbus','A350','5478-awe3',10010005,20.25);
insert into abflug (AB_DATUM,F_BEZ,HERST,TYP,PER_NR,SER_NR,AB_ZEIT) values
('18.02.14','LH-238','Airbus','A320','z3et-bwe7',10010072,23.06);

You're trying to send strings to number columns.

Hello, thank you for your help and time! I've copied your query to create the new table and after trying to insert the data (copy&paste what you've put here), I am getting the same error message: ORA-00911: invalid character It seems that there is some syntax error in it? I have tried with only one line and it worked... The row was inserted. — DonDon, Aug 19 '22 at 14:22
APEX SQL Worksheet - you have to execute each statement, one at a time. Or upload a script and execute that. — thatjeffsmith, Aug 19 '22 at 14:56

Why do I get an error when I try to insert data into my table?

1 Answers1