Timestamp Oracle SQL ORA-01756

Question

Hello just started doing my first SQL project and using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0.

I am trying to do something like this:

INSERT INTO appointment VALUES(  
  000000001, 
  123456789, 
  ‘2008-09-27 09:12:47’, 
  '22-JUN-1964’,  
  00001, 
  123000000);

I even tried doing this first then the values:

INSERT INTO appointment (visitID, pSSN, time, bdate, deptID, empSSN)

this is the table I created

CREATE TABLE appointment( 
    visitID NUMBER(9) NOT NULL, 
    pSSN NUMBER(9),
    time TIMESTAMP, 
    bdate DATE, 
    deptID NUMBER(5), 
    empSSN NUMBER(9),
  PRIMARY KEY(visitID), FOREIGN KEY( pSSN ) REFERENCES patient( pSSN ),
  FOREIGN KEY( deptID ) REFERENCES department(deptID), 
  FOREIGN KEY (empSSN ) REFERENCES employee( empSSN ) 
 );

I am still getting ORA-01756: quoted string not properly terminated Not sure if it something to do with the timestamp or date as I inserted values into date like this into other tables. Thank you for any help.

Answer 1

You have a mix of and normal quote marks and curly single quotes, possibly from cuting and pasting from a non-plain-text document:

INSERT INTO appointment VALUES(  000000001, 123456789,
  ‘2008-09-27 09:12:47’, '22-JUN-1964’,  00001, 123000000);
  ^                   ^              ^

With the code formatting used by SO you can see three curly quotes (also marked with ^) and the lone straight quote are different colours. Change them all to straight quotes:

INSERT INTO appointment VALUES(000000001, 123456789,
  '2008-09-27 09:12:47', '22-JUN-1964', 00001, 123000000);

It is good practice to include the column names you're inserting into, not least to make it easier to spot when you have values in the wrong order. But you're also relying on your NLS date and timestamp formats. You should use explicit format masks:

INSERT INTO appointment (visitID, pSSN, time, bdate, deptID, empSSN)
VALUES(000000001, 123456789,
  TO_TIMESTAMP('2008-09-27 09:12:47', 'YYYY-MM-DD HH24:MI:SS'),
  TO_DATE('22-JUN-1964', 'DD-MON-YYYY'),
  00001, 123000000);

Or with the fixed data you're using, date and timestamp literals:

INSERT INTO appointment (visitID, pSSN, time, bdate, deptID, empSSN)
VALUES(000000001, 123456789,
  TIMESTAMP '2008-09-27 09:12:47', DATE '1964-06-22',
  00001, 123000000);