Oracle SQL spool giving incorrect information

Question

The first ddl script I ran was telling me I had errors. Come to find out it was because I had spaces between my commands. I removed the spaces and it spools fine with no error messages in the spool file. Now, I am trying to insert data into the tables and I am getting a bunch of errors, like invalid date. Even though the dates are correct. I am getting a couple other errors also. Is there some other formatting issue I should know about?

SQL> set echo on
SQL> insert into distributor (distributor_id, distributor_name, address, phone)
  2      values('1','efd sales','3540 lennox st', '3015899389')
  3      /

1 row created.

SQL>         insert into distributor (distributor_id, distributor_name, address, phone)
  2      values('2','emd sales','3541 lennox st', '3025899389')
  3      /

1 row created.

SQL>     insert into distributor (distributor_id, distributor_name, address, phone)
  2      values('3','emo sales','3542 lennox st', '3035899389')
  3      /

1 row created.

SQL>         insert into distributor (distributor_id, distributor_name, address, phone)
  2      values('4','amd sales','3543 lennox st', '3045899389')
  3      /

1 row created.

SQL>     insert into distributor (distributor_id, distributor_name, address, phone)
  2      values('5','exo sales','3544 lennox st', '3055899389')
  3      /

1 row created.

SQL>     insert into genre(genre_id,genre)
  2      values('1101','horror')
  3      /

1 row created.

SQL>     insert into genre(genre_id,genre)
  2      values('1102','fantasy')
  3      /

1 row created.

SQL>     insert into genre(genre_id,genre)
  2      values('1103','sports')
  3      /

1 row created.

SQL>     insert into genre(genre_id,genre)
  2      values('1104','entertainment')
  3      /

1 row created.

SQL>     insert into genre(genre_id,genre)
  2      values('1105','adventure')
  3      /

1 row created.

SQL>     insert into movie(movie_id,genre_id,title,release_date,duration,movie_type,rating,rent_price)
  2      values('1','1101','fly','02/09/92','45:00','horror','5/10','5.00')
  3      /
    values('1','1101','fly','02/09/92','45:00','horror','5/10','5.00')
                            *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>      insert into movie(movie_id, genre_id, title, release_date, duration,movie_type,rating,rent_price)
  2      values('2','1102','fly2','02/12/93','45:00','horror','5/10','5.00')
  3      /
    values('2','1102','fly2','02/12/93','45:00','horror','5/10','5.00')
                             *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>      insert into movie(movie_id, genre_id, title, release_date, duration,movie_type,rating,rent_price)
  2      values('3','1103','fly3','02/12/94','45:00','horror','5/10','5.00')
  3      /
    values('3','1103','fly3','02/12/94','45:00','horror','5/10','5.00')
                             *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>      insert into movie(movie_id, genre_id, title, release_date, duration,movie_type,rating,rent_price)
  2      values('4','1104','fly4','02/12/95','45:00','horror','5/10','5.00')
  3      /
    values('4','1104','fly4','02/12/95','45:00','horror','5/10','5.00')
                             *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>      insert into movie(movie_id, genre_id, title, release_date, duration,movie_type,rating,rent_price)
  2      values('5','1105','fly5','02/12/96','45:00','horror','5/10','5.00')
  3      /
    values('5','1105','fly5','02/12/96','45:00','horror','5/10','5.00')
                             *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>     insert into sells(transaction_id,distributor_id,movie_id,quantity,purchase_date)
  2      values('1','1','1','1','02/10/92')
  3      /
    values('1','1','1','1','02/10/92')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>     insert into sells(transaction_id,distributor_id,movie_id,quantity,purchase_date)
  2      values('2','2','1','2','02/10/92')
  3      /
    values('2','2','1','2','02/10/92')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>     insert into sells(transaction_id,distributor_id,movie_id,quantity,purchase_date)
  2      values('3','3','1','3','02/10/92')
  3      /
    values('3','3','1','3','02/10/92')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>     insert into sells(transaction_id,distributor_id,movie_id,quantity,purchase_date)
  2      values('4','4','1','4','02/10/92')
  3      /
    values('4','4','1','4','02/10/92')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>     insert into sells(transaction_id,distributor_id,movie_id,quantity,purchase_date)
  2      values('5','5','1','5','02/10/92')
  3      /
    values('5','5','1','5','02/10/92')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>     insert into dvd(
  2      dvd_movie_id, dvd_count
  3      )
  4      values('1','1')
  5      /

1 row created.

SQL>     insert into dvd(
  2      dvd_movie_id, dvd_count
  3      )
  4      values('2','2')
  5      /

1 row created.

SQL>     insert into dvd(
  2      dvd_movie_id, dvd_count
  3      )
  4      values('3','3')
  5      /

1 row created.

SQL>     insert into dvd(
  2      dvd_movie_id, dvd_count
  3      )
  4      values('4','4')
  5      /

1 row created.

SQL>     insert into dvd(
  2      dvd_movie_id, dvd_count
  3      )
  4      values('5','5')
  5      /

1 row created.

SQL>     insert into video(vid_mov_id
  2          )
  3      values('1')
  4       /

1 row created.

SQL>     insert into video(vid_mov_id)
  2      values('2')
  3       /

1 row created.

SQL>     insert into video(vid_mov_id)
  2      values('3')
  3       /

1 row created.

SQL>     insert into video(vid_mov_id)
  2      values('4')
  3       /

1 row created.

SQL>     insert into video(vid_mov_id)
  2      values('5')
  3      /

1 row created.

SQL>     insert into director(director_id,NAME,ADDRESS,city,state)
  2      values('1','john1','123 buck st','laurel','maryland')
  3      /

1 row created.

SQL>     insert into director(director_id,NAME,ADDRESS,city,state)
  2      values('2','john2','124 buck st','bowie','maryland')
  3      /

1 row created.

SQL>     insert into director(director_id,NAME,ADDRESS,city,state)
  2      values('3','john3','125 buck st','canton','maryland')
  3      /

1 row created.

SQL>     insert into director(director_id,NAME,ADDRESS,city,state)
  2      values('4','john4','126 buck st','wats','maryland')
  3      /

1 row created.

SQL>     insert into director(director_id,NAME,ADDRESS,city,state)
  2      values('5','john5','127 buck st','canne','maryland')
  3      /

1 row created.

SQL>     insert into movie_director(director_id,movie_id)
  2      values('1','1')
  3      /
    insert into movie_director(director_id,movie_id)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_MOVIE_DIRECTOR) violated - 
parent key not found 


SQL>     insert into movie_director(director_id,movie_id)
  2      values('2','2')
  3      /
    insert into movie_director(director_id,movie_id)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_MOVIE_DIRECTOR) violated - 
parent key not found 


SQL>     insert into movie_director(director_id,movie_id)
  2      values('3','3')
  3      /
    insert into movie_director(director_id,movie_id)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_MOVIE_DIRECTOR) violated - 
parent key not found 


SQL>     insert into movie_director(director_id,movie_id)
  2      values('4','4')
  3      /
    insert into movie_director(director_id,movie_id)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_MOVIE_DIRECTOR) violated - 
parent key not found 


SQL>     insert into movie_director(director_id,movie_id)
  2      values('5','5')
  3      /
    insert into movie_director(director_id,movie_id)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_MOVIE_DIRECTOR) violated - 
parent key not found 


SQL>     insert into member(member_id,first_name,last_name,address)
  2      values('1','james1','jones2','12 mirk st')
  3      /

1 row created.

SQL>     insert into member(member_id,first_name,last_name,address)
  2      values('2','james2','jones2','123 mirk st')
  3      /

1 row created.

SQL>     insert into member(member_id,first_name,last_name,address)
  2      values('3','james3','jones2','124 mirk st')
  3      /

1 row created.

SQL>     insert into member(member_id,first_name,last_name,address)
  2      values('4','james4','jones2','125 mirk st')
  3      /

1 row created.

SQL>     insert into member(member_id,first_name,last_name,address)
  2      values('5','james5','jones2','126 mirk st')
  3      /

1 row created.

SQL>     insert into rental_transaction(rental_id,rental_date,due_date,movie_id,member_id)
  2      values('1','08/08/15','08/08/15','1','1')
  3      /
    values('1','08/08/15','08/08/15','1','1')
               *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>     insert into rental_transaction(rental_id,rental_date,due_date,movie_id,member_id)
  2      values('2','09/08/15','09/08/15','2','2')
  3      /
    values('2','09/08/15','09/08/15','2','2')
               *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>     insert into rental_transaction(rental_id,rental_date,due_date,movie_id,member_id)
  2      values('3','10/08/15','10/08/15','3','3')
  3      /
    values('3','10/08/15','10/08/15','3','3')
               *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>     insert into rental_transaction(rental_id,rental_date,due_date,movie_id,member_id)
  2      values('4','11/08/15','11/08/15','4','4')
  3      /
    values('4','11/08/15','11/08/15','4','4')
               *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>     insert into rental_transaction(rental_id,rental_date,due_date,movie_id,member_id)
  2      values('5','12/08/15','12/08/15','5','5')
  3      /
    values('5','12/08/15','12/08/15','5','5')
               *
ERROR at line 2:
ORA-01843: not a valid month 


SQL>     insert into actor(actor_id, full_name)
  2      values('1','carl lime')
  3      /

1 row created.

SQL>     insert into actor(actor_id, full_name)
  2      values('2','carl lewis')
  3      /

1 row created.

SQL>     insert into actor(actor_id, full_name)
  2      values('3','john lewis')
  3      /

1 row created.

SQL>     insert into actor(actor_id, full_name)
  2      values('4','carl less')
  3      /

1 row created.

SQL>     insert into actor(actor_id, full_name)
  2      values('5','yerl wise')
  3      /

1 row created.

SQL>     insert into actor_role(role_type,movie_id,actor_id)
  2      values('lead','1','1')
  3      /
    insert into actor_role(role_type,movie_id,actor_id)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_ACTOR_ROLE) violated - parent 
key not found 


SQL>     insert into actor_role(role_type,movie_id,actor_id)
  2      values('lead','2','2')
  3      /
    insert into actor_role(role_type,movie_id,actor_id)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_ACTOR_ROLE) violated - parent 
key not found 


SQL>     insert into actor_role(role_type,movie_id,actor_id)
  2      values('lead','3','3')
  3      /
    insert into actor_role(role_type,movie_id,actor_id)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_ACTOR_ROLE) violated - parent 
key not found 


SQL>     insert into actor_role(role_type,movie_id,actor_id)
  2      values('lead','4','4')
  3      /
    insert into actor_role(role_type,movie_id,actor_id)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_ACTOR_ROLE) violated - parent 
key not found 


SQL>     insert into actor_role(role_type,movie_id,actor_id)
  2      values('lead','5','5')
  3      /
    insert into actor_role(role_type,movie_id,actor_id)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_ACTOR_ROLE) violated - parent 
key not found 


SQL>     insert into award(award_id,actor_id,director_id,movie_id, award_name,award_type)
  2      values('1','1','1','1','oscar','film')
  3       /
    insert into award(award_id,actor_id,director_id,movie_id, award_name,award_type)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_AWARD) violated - parent key 
not found 


SQL>     insert into award(award_id,actor_id,director_id,movie_id, award_name,award_type)
  2      values('2','2','2','2','oscar','film')
  3       /
    insert into award(award_id,actor_id,director_id,movie_id, award_name,award_type)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_AWARD) violated - parent key 
not found 


SQL>     insert into award(award_id,actor_id,director_id,movie_id, award_name,award_type)
  2      values('3','3','3','3','oscar','film')
  3       /
    insert into award(award_id,actor_id,director_id,movie_id, award_name,award_type)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_AWARD) violated - parent key 
not found 


SQL>     insert into award(award_id,actor_id,director_id,movie_id, award_name,award_type)
  2      values('4','4','4','4','oscar','film')
  3       /
    insert into award(award_id,actor_id,director_id,movie_id, award_name,award_type)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_AWARD) violated - parent key 
not found 


SQL>     insert into award(award_id,actor_id,director_id,movie_id, award_name,award_type)
  2      values('5','5','5','5','oscar','film')
  3      /
    insert into award(award_id,actor_id,director_id,movie_id, award_name,award_type)
*
ERROR at line 1:
ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_AWARD) violated - parent key 
not found 


SQL>     insert into movie_awd(mov_awd_id)
  2      values('1')
  3      /

1 row created.

SQL>     insert into movie_awd(mov_awd_id)
  2      values('2')
  3      /

1 row created.

SQL>     insert into movie_awd(mov_awd_id)
  2      values('3')
  3      /

1 row created.

SQL>     insert into movie_awd(mov_awd_id)
  2      values('4')
  3      /

1 row created.

SQL>     insert into movie_awd(mov_awd_id)
  2      values('5')
  3      /

1 row created.

SQL>     insert into director_awd(dir_awd_id)
  2      values('1')
  3      /

1 row created.

SQL>     insert into director_awd(dir_awd_id)
  2      values('2')
  3      /

1 row created.

SQL>     insert into director_awd(dir_awd_id)
  2      values('3')
  3      /

1 row created.

SQL>     insert into director_awd(dir_awd_id)
  2      values('4')
  3      /

1 row created.

SQL>     insert into director_awd(dir_awd_id)
  2      values('5')
  3      /

1 row created.

SQL>     insert into act_awd(act_awd_id)
  2      values('1')
  3      /

1 row created.

SQL>     insert into act_awd(act_awd_id)
  2      values('2')
  3      /

1 row created.

SQL>     insert into act_awd(act_awd_id)
  2      values('3')
  3      /

1 row created.

SQL>     insert into act_awd(act_awd_id)
  2      values('4')
  3      /

1 row created.

SQL>     insert into act_awd(act_awd_id)
  2      values('5')
  3      /

1 row created.

SQL>     spool off

it says `ORA-01843: not a valid month` , please check your date format, try using `'12-DEC-1996'`instead of `'01/12/96'` and other error is relating to primary key, you cant insert duplicate data into the table — anudeepks, May 04 '15 at 06:37

Lalit Kumar B · Answer 1 · 2015-05-04T07:30:01.047

ORA-01843: not a valid month

'02/12/93' is a STRING and not a DATE. Always use TO_DATE to explicitly convert the literal into date along with required format mask.

You might just be lucky to insert if Oracle is successful to do an implicit data type conversion based on the exact match of the locale-specific NLS_DATE_FORMAT.

Let's reproduce the error:

SQL> CREATE TABLE t(a DATE);

Table created.

SQL> INSERT INTO t(a) VALUES('02/12/93');
INSERT INTO t(a) VALUES('02/12/93')
                        *
ERROR at line 1:
ORA-01843: not a valid month


SQL>

The NLS_DATE_FORMAT in my client settings is:

SQL> show parameter nls_date_format;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
nls_date_format                      string      DD-MON-RR

So, the format 'DD-MON-RR' as used by my client is not same as '02/12/93'

The correct way is:

SQL> INSERT INTO t(a) VALUES(to_date('02/12/1993', 'MM/DD/YYYY'));

1 row created.

Also, be aware of the format for year. If you are using YY instead of YYYY then you are reinventing the Y2K bug. Either use RR format or better use *YYYY** and mention the complete year in 4 digits.

ORA-02291: integrity constraint (DAVE.FK_MOVIE_ID_AWARD) violated - parent key not found

It means there is no referencing parent key found in the parent table for the value you are trying to insert into the column referenced as foreign key. First, insert the rows into the parent table, and then insert into the child table.

score 0 · Answer 2 · answered May 04 '15 at 12:22

0

my suggestion is to either user 'DD-MON-YYYY' or to_date(date,format) formats in the insert statement.

answered May 04 '15 at 12:22

Smart003

1,119
2
16
31

Oracle SQL spool giving incorrect information

2 Answers2