CODEWITHSUNDEEP
sqldatabaseoracle-database
sjr59
sjr59

Reputation: 93

Inserting into table

I have this table on an Oracle server with this structure:

Create table temp
(
    input number(10),
    str varchar(24),

    constraint L_PK PRIMARY KEY(input, str)
)

I made one alteration on it:

alter table temp add userID number(10);

Now I am trying to do an insert:

insert into temp values (9, 7, 'sure.');

But I am getting a error saying:

ORA-01722: Invalid Number

Any ideas? I'm pretty sure its coming from the 'sure' but I can't say for sure.

Upvotes: 0

Views: 62

Answers (6)

Ken White
Ken White

Reputation: 125620

So you created a table with two columns:

Create table temp(
input      number(10),
str      varchar(24),
constraint L_PK PRIMARY KEY(input,str),

Made one alteration to it:

alter table temp add userID number(10);

Which leaves you with a table with columns input, str, and userID in that order. You then try to insert

insert into temp values (9, 7, 'sure.');

which tries to insert 9 into input (fine), 7 into str (not fine), and sure into userID (again, not fine).

You need to either use the values in the proper order to match the column order:

insert into temp values (9, 'sure', 7);

or (much better and safer) specify the columns first, and then assign the values to match:

insert into temp (input, userID, str) values (7, 9, 'sure');

Upvotes: 4

Rasul
Rasul

Reputation: 24

After adding column,it is added at the end of the table

insert into temp values (9, 'sure.',7);

output:

input str userID 9 sure. 7

Upvotes: 0

Paul
Paul

Reputation: 1657

In your case:

insert into temp values (9, 7, 'sure.');

It means:

INSERT INTO temp (input, str, userId)
VALUES (9, 7, 'sure.');

As 'sure.' does not a number type, Oracle server certainly gets error.

Upvotes: 2

Lordran
Lordran

Reputation: 683

If you don't change the insert order,you should change the alteration SQL:

alter table temp add userID number(10) after str;

Yes,'after str' is appended. Or you could change the insert order like this:insert into temp values (9, 'sure', 7);

Whatever you must remember that column should match with the value,especially in datatype.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Specify the column-names when you are inserting so the appropriate data is inserted into the columns.

insert into temp (input, userid, str)
values (9, 7, 'sure.');

Upvotes: 4

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4572

You added userID at the end of the table. Try

insert into temp values (9, 'sure.', 7);

Upvotes: 0

Related Questions