ORA-01756: quoted string not properly terminated, do i need to add quote on the phone number, why?

Question

SQL> create table customer(
  2  customer_ID number(3) primary key,
  3  customer_Name varchar2(26),
  4  contact_Name  varchar2(26),
  5  phone number(10));

Table created.

SQL> desc customer
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                               NOT NULL NUMBER(3)
 CUSTOMER_NAME                                      VARCHAR2(26)
 CONTACT_NAME                                       VARCHAR2(26)
 PHONE                                              NUMBER(10)
SQL> insert into customer 
        (customer_ID, customer_Name, contact_Name, phone) 
        values (23, 'Dave's Sub Shop', 'David Logan', 555-333-4545);
ERROR:
ORA-01756: quoted string not properly terminated

Can anyone explain this error for me?

Answer 1

There are two issues here :

• you are inserting a string that contains an embedded single quote : 'Dave's Sub Shop'. The glitch shows up in any decent text editor (including SO's). You need to escape the quote : 'Dave''s Sub Shop'

• the phone_number column is declared as number(10), however what you are trying to insert does not look like one : 555-333-4545. You should change the column datatpe to varchar(12). Then you will need to quote the values that you insert.

Demo on DB Fiddle :

create table customer(
    customer_ID number(3) primary key,
    customer_Name varchar2(26),
    contact_Name  varchar2(26),
    phone varchar(12));

insert into customer 
        (customer_ID, customer_Name, contact_Name, phone) 
        values (23, 'Dave''s Sub Shop', 'David Logan', '555-333-4545');

1 rows affected

select * from customer;

CUSTOMER_ID | CUSTOMER_NAME   | CONTACT_NAME | PHONE       
----------: | :-------------- | :----------- | :-----------
         23 | Dave's Sub Shop | David Logan  | 555-333-4545

NB : another solution would be to convert the phone number to a number datatype before inserting it (by removing the embedded - for example), but I would not recommend it, since phone numbers are actually not numbers : they may have meaningful leading zeros, and may contain non-digit characters ((), +, ...)