SQL Error: ORA-00933: SQL command not properly ended?-CodePudding

I am wanting to insert data or to populate a table with new data and have used the insert into command on its own as well as insert into with the columns and values underneath but keep getting the error in the title.

INSERT INTO A2_FILM (FILM_NO, FILM_NAME, CLASSIFICATION, DURATION, DESCRIPTION, YEAR_RELEASED)
VALUES (00948371, 'Lightyear', 'U', 105, 'Legendary space ranger Buzz Lightyear embarks on an intergalactic adventure alongside ambitious recruits Izzy, Mo, Darby, and his robot companion, Sox.', TO DATE('2022', 'YYYY'));

CodePudding user response：

With Oracle 19c and SqlDeveloper 21 i not received any error:

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 FILM_NO                                                        NUMBER(12)
 FILM_NAME                                                      VARCHAR2(100)
 CLASSIFICATION                                                 CHAR(1)
 DURATION                                                       NUMBER(5)
 DESCRIPTION                                                    VARCHAR2(1000)
 YEAR_RELEASED                                                  DATE


INSERT INTO A2_FILM (FILM_NO, FILM_NAME, CLASSIFICATION, DURATION, DESCRIPTION, YEAR_RELEASED)
VALUES (00948371, 
        'Lightyear', 
        'U', 
        105, 
        'Legendary space ranger Buzz Lightyear embarks on an intergalactic adventure alongside ambitious recruits Izzy, Mo, Darby, and his robot companion, Sox.', 
        TO_DATE('2022', 'YYYY')
       );
   
1 row inserted.

CodePudding user response：

If the populated value in YEAR_RELEASED column has to equal to YYYY, it looks like you have to change the datatype to NUMBER. Otherwise if you execute INSEERT INTO TO_DATE('2022', 'YYYY') will return DD-MM-YYYY which will be equal to SYSDATE, i.e. in this case the return value is 01-07-2022.

If you change the datatype to NUMBER for YEAR_RELEASED column you can use EXTRACT() and try this one:

INSERT INTO A2_FILM (FILM_NO, FILM_NAME, CLASSIFICATION, DURATION, DESCRIPTION, YEAR_RELEASED) VALUES (00948371, 'Lightyear', 'U', 105, 'Legendary space ranger Buzz Lightyear embarks on an intergalactic adventure alongside ambitious recruits Izzy, Mo, Darby, and his robot companion, Sox.', EXTRACT (YEAR FROM TO_DATE('2022-07-01', 'YYYY-MM-DD')));

db<>fiddle: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=91ba7c0f3117bd6cfc814656a896856e

If the populated value has to be equal to DD-MM-YYYY you can use :

INSERT INTO A2_FILM (FILM_NO, FILM_NAME, CLASSIFICATION, DURATION, DESCRIPTION, YEAR_RELEASED) VALUES (00948371, 'Lightyear', 'U', 105, 'Legendary space ranger Buzz Lightyear embarks on an intergalactic adventure alongside ambitious recruits Izzy, Mo, Darby, and his robot companion, Sox.', TO_DATE('2022', 'YYYY'));

db<>fiddle: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=41aac71840efc45eaa62e9db42fb75c5