SQL- missing comma in insert statement line

Question

CREATE TABLE suppliers
(
    supplier_ID      NUMBER not null,
    supplier_Name    varchar2 (50) not null,
    supplier_Addr    varchar2 (100),
    supplier_Town    varchar2 (30),
    supplier_State   varchar2 (30),
    supplier_Pcode   varchar2 (15) not null,
    supplier_Phone1  varchar2 (15),
    supplier_Phone2  varchar2 (15),
    supplier_Contact varchar2 (50),
    supplier_Fax     varchar2 (15),
    supplier_Email   varchar2 (40),
    supplier_Renew   DATE,
    supplier_Creditlimit NUMBER,
    supplier_Comments varchar2 (500),
    PRIMARY KEY (supplier_ID)
);

INSERT INTO suppliers (supplier_ID, supplier_Name, supplier_Addr, supplier_Town, supplier_State, supplier_Pcode, supplier_Phone1, supplier_Phone2, supplier_Contact, supplier_Fax, supplier_Email, supplier_Renew, supplier_Creditlimit,supplier_Comments)
VALUES (010203, 'DSHK COMPANY', 'G 7 JLN TUNKU HASSAN ', 'Seremban', 'Negeri Sembilan', 70000, 604-42449268, 09-4265050, 'DSHK CO', 04-2224568, 'DSHKCOMPANY@GMAIL.COM', 2024-08-23, 1000, 'COMPANY WILL CALL BEFORE SENDING THE GOODS');

I keep getting missing comma error for insert statement. Please guide me, tq

That's because you are not quoting all your string literals. And note that the number 010203 is no different from 10203. — Stu, Apr 02 '22 at 20:32

score 0 · Answer 1 · answered Apr 02 '22 at 20:31

0

You need to make the phone numbers into strings as you have defined them as varchar.

70000,604-42449268,09-4265050

->

'70000', '604-42449268', '09-4265050'

I think you need to do tha same for date... that is 2024-08-23 -> '2024-08-23'

answered Apr 02 '22 at 20:31

corn_not_slapped

1

score 0 · Answer 2 · edited Apr 02 '22 at 21:11

Regarding the create statement: NUMBER is not a valid type. You can use e.g. int(11) unsigned.

Also, instead of varchara(500) you can use text. So the create statement should be:

CREATE TABLE suppliers
(
    supplier_ID int(11) not null, 
    supplier_Name varchar (50) not null, 
    supplier_Addr varchar (100), 
    supplier_Town varchar (30), 
    supplier_State varchar (30), 
    supplier_Pcode varchar (15) not null, 
    supplier_Phone1 varchar (15), 
    supplier_Phone2 varchar (15), 
    supplier_Contact varchar (50), 
    supplier_Fax varchar (15), 
    supplier_Email varchar (40), 
    supplier_Renew DATE, 
    supplier_Creditlimit int(11), 
    supplier_Comments text, 
    PRIMARY KEY (supplier_ID) 
);

For your insert statement you must enclose all non-numerical values in quotes, like this:

INSERT INTO suppliers (supplier_ID, supplier_Name, supplier_Addr, supplier_Town, supplier_State, supplier_Pcode, supplier_Phone1, supplier_Phone2, supplier_Contact, supplier_Fax, supplier_Email, supplier_Renew, supplier_Creditlimit,supplier_Comments) 
VALUES (010203, 'DSHK COMPANY', 'G 7 JLN TUNKU HASSAN ', 'Seremban', 'Negeri Sembilan', 70000, '604-42449268', '09-4265050', 'DSHK CO', '04-2224568', 'DSHKCOMPANY@GMAIL.COM', '2024-08-23', 1000, 'COMPANY WILL CALL BEFORE SENDING THE GOODS');

`NUMBER` and `VARCHAR2` are valid data types in Oracle. – MT0 Apr 02 '22 at 20:37 — MT0, Apr 02 '22 at 20:37

MT0 · Answer 3 · 2022-04-02T20:51:28.410

Assuming that you are using an Oracle database since you are using VARCHAR2 data types then use string and date literals for VARCHAR2 and DATE data types respectively:

insert into suppliers (
  supplier_ID,
  supplier_Name,
  supplier_Addr,
  supplier_Town,
  supplier_State,
  supplier_Pcode,
  supplier_Phone1,
  supplier_Phone2,
  supplier_Contact,
  supplier_Fax,
  supplier_Email,
  supplier_Renew,
  supplier_Creditlimit,
  supplier_Comments
) values (
  010203,
  'DSHK COMPANY',
  'G 7 JLN TUNKU HASSAN ',
  'Seremban',
  'Negeri Sembilan',
  '70000',
  '604-42449268',
  '09-4265050',
  'DSHK CO',
  '04-2224568',
  'DSHKCOMPANY@GMAIL.COM',
  DATE '2024-08-23',
  1000,
  'COMPANY WILL CALL BEFORE SENDING THE GOODS'
);

If you don't then, for example, 604-42449268 will be parsed as 604 subtract 42449268 which gives the result −42448664.

Do not use a string literal such as '2024-08-23' for the date as Oracle will implicitly try to convert the string literal to a date using the NLS_DATE_FORMAT session paramter. The default Oracle DATE format depends on the territory you are in and for most territories that implicit conversion will fail. Either use a DATE literal or explicitly call TO_DATE('2024-08-23', 'YYYY-MM-DD') with a format model.

db<>fiddle here

SQL- missing comma in insert statement line

3 Answers3