Home » SQL & PL/SQL » SQL & PL/SQL » Failed insertion of values into a table. (merged) (iSQL*Plus)
Failed insertion of values into a table. (merged) [message #428564] Wed, 28 October 2009 21:04 Go to next message
someoney3000
Messages: 2
Registered: October 2009
Junior Member
Sorry, I've been trying to figure out what went wrong with this insertion for some time.

This is the table:

create table employee_activities (

        empno                   number(6) not null,
        projno                  char(6) not null,
        time_spent              number(3, 2),
        date_start              date,
        date_end                date,

        constraint time_spent_check check (time_spent >= 0 and time_spent <= 1),
        constraint date_start_check check (to_number(to_char(date_start), 'YYYY') > 1950),
        constraint date_end_check check (date_start <= date_end),
--        foreign key(empno) references employees(empno),
--        foreign key(projno) references project_information(projno)

);


I commented out the unnecessary parts.

I attempted to insert:

insert into employee_activities values (000010, 'AD3100', 0.50, to_date('2007-01-01', 'YYYY-MM-DD'), to_date('2007-07-01', 'YYYY-MM-DD'));


But I keep getting:
ERROR at line 1:
ORA-01481: invalid number format model

Looking up the error message gives me that I'm misusing to_char or to_number, but I'm not using those functions in the insert at all.

What am I doing wrong?
Re: Failed insertion of values into a table. [message #428568 is a reply to message #428564] Wed, 28 October 2009 21:28 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
SQL> create table employee_activities (
  2  	     empno		     number(6) not null,
  3  	     projno		     char(6) not null,
  4  	     time_spent 	     number(4, 2),
  5  	     date_start 	     date,
  6  	     date_end		     date,
  7  	     constraint time_spent_check check (time_spent >= 0 and time_spent <= 1),
  8  	     constraint date_start_check check (to_number(to_char(date_start, 'YYYY')) > 1950),
  9  	     constraint date_end_check check (date_start <= date_end)
 10  );

Table created.

SQL> insert into employee_activities values (000010, 'AD3100', 0.50, to_date('2007-01-01', 'YYYY-MM-DD'), to_date('2007-07-01', 'YYYY-MM-DD'));

1 row created.



Works with a minor tweak!
Re: Failed insertion of values into a table. [message #428569 is a reply to message #428568] Wed, 28 October 2009 21:50 Go to previous messageGo to next message
someoney3000
Messages: 2
Registered: October 2009
Junior Member
BlackSwan wrote on Wed, 28 October 2009 21:28
SQL> create table employee_activities (
  2  	     empno		     number(6) not null,
  3  	     projno		     char(6) not null,
  4  	     time_spent 	     number(4, 2),
  5  	     date_start 	     date,
  6  	     date_end		     date,
  7  	     constraint time_spent_check check (time_spent >= 0 and time_spent <= 1),
  8  	     constraint date_start_check check (to_number(to_char(date_start, 'YYYY')) > 1950),
  9  	     constraint date_end_check check (date_start <= date_end)
 10  );

Table created.

SQL> insert into employee_activities values (000010, 'AD3100', 0.50, to_date('2007-01-01', 'YYYY-MM-DD'), to_date('2007-07-01', 'YYYY-MM-DD'));

1 row created.



Works with a minor tweak!


Ummm... unless I missed something, you changed time_spend from (3,2) to (4,2).

So I changed mine as well to match.

But it didn't worked. I then used your code instead of mines; that did work.

OOOOH, I see! I had a paren on the constraint in the wrong place!

Whew, thanks BlackSwan!

Re: Failed insertion of values into a table. [message #428570 is a reply to message #428569] Wed, 28 October 2009 21:55 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
My clue was as follows:

01481, 00000, "invalid number format model"
// *Cause:  The user is attempting to either convert a number to a string
//          via TO_CHAR or a string to a number via TO_NUMBER and has 
//          supplied an invalid number format model parameter.
// *Action:  Consult your manual.
Re: Failed insertion of values into a table. [message #428579 is a reply to message #428564] Thu, 29 October 2009 00:41 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
SQL>create table employee_activities (
  2           empno                   number(6) not null,
  3           projno                  char(6) not null,
  4           time_spent              number(3, 2),
  5           date_start              date,
  6           date_end                date,
  7           constraint time_spent_check check (time_spent >= 0 and time_spent <= 1),
  8           constraint date_start_check check (to_number(to_char(date_start), 'YYYY') > 1950),
  9           constraint date_end_check check (date_start <= date_end)
 10  --        foreign key(empno) references employees(empno),
 11  --        foreign key(projno) references project_information(projno)
 12  );

Table created.

SQL>insert into employee_activities values (000010, 'AD3100', 0.50, to_date('2007-01-01', 'YYYY-MM-D
D'), to_date('2007-07-01', 'YYYY-MM-DD'));
insert into employee_activities values (000010, 'AD3100', 0.50, to_date('2007-01-01', 'YYYY-MM-DD'),
                               *
ERROR at line 1:
ORA-01481: invalid number format model


SQL>drop table employee_activities;

Table dropped.

SQL>create table employee_activities (
  2            empno                   number(6) not null,
  3            projno                  char(6) not null,
  4            time_spent              number(3, 2),
  5            date_start              date,
  6            date_end                date,
  7            constraint time_spent_check check (time_spent >= 0 and time_spent <= 1),
  8            constraint date_start_check check (to_number(to_char(date_start,'YYYY')) > 1950),
  9           constraint date_end_check check (date_start <= date_end)
 10  --        foreign key(empno) references employees(empno),
 11  --        foreign key(projno) references project_information(projno)
 12  );

Table created.

SQL>insert into employee_activities values (000010, 'AD3100', 0.50, to_date('2007-01-01', 'YYYY-MM-D
D'), to_date('2007-07-01', 'YYYY-MM-DD'));

1 row created.


See difference here.
constraint date_start_check check (to_number(to_char(date_start,'YYYY')) > 1950),


regards,
Delna
Re: Failed insertion of values into a table. [message #428624 is a reply to message #428579] Thu, 29 October 2009 03:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
delna.sexy wrote on Thu, 29 October 2009 06:41

See difference here.
<SNIP>
regards,
Delna

How is this different from the change the OP already received (and recognized as the culprit) three hours earlier?
Re: Failed insertion of values into a table. [message #428628 is a reply to message #428624] Thu, 29 October 2009 04:13 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
How is this different from the change the OP already received (and recognized as the culprit) three hours earlier?


Frank sir,
Notice that two threads are merged.
When I replied to the thread which was having no reply, the other thread contained reply which were same as me.
And when I noticed that, they were merged by moderator.
That's all.

regards,
Delna
Re: Failed insertion of values into a table. [message #428635 is a reply to message #428628] Thu, 29 October 2009 04:26 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
OP has already found out the bug.
Quote:

OOOOH, I see! I had a paren on the constraint in the wrong place!


constraint date_start_check check (to_number(to_char(date_start), 'YYYY') > 1950),

the ')' followed by date_start column

[Updated on: Thu, 29 October 2009 04:30]

Report message to a moderator

Re: Failed insertion of values into a table. [message #428656 is a reply to message #428624] Thu, 29 October 2009 05:22 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello Frank sir,

Did you get what I said?
I don't care about others.

regards,
Delna
Re: Failed insertion of values into a table. [message #428657 is a reply to message #428628] Thu, 29 October 2009 05:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
delna.sexy wrote on Thu, 29 October 2009 10:13

Notice that two threads are merged.

Ok, I am sorry. I did not see that.
Re: Failed insertion of values into a table. [message #428660 is a reply to message #428657] Thu, 29 October 2009 05:50 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Nothing to say sorry. Its ok. Smile

regards,
Delna
Previous Topic: Can this be done with a REGEXP
Next Topic: Getting duplicates from pay_grade_rules_f
Goto Forum:
  


Current Time: Thu Sep 29 00:33:08 CDT 2016

Total time taken to generate the page: 0.07543 seconds