What am i doing wrong?

Welshy · 30 Oct 2006 at 23:26

So ive created a table catalogue with the following command:

Code:

CREATE TABLE CATALOGUE
		(CATALOGUENUMBER VARCHAR2(6) NOT NULL,
		 TITLE VARCHAR2(30) NOT NULL,
		 GENRE VARCHAR2(20) NOT NULL,
		 SUPPLIER VARCHAR2(20) NOT NULL,
		 RELEASEDATE DATE,
		 CERTIFICATE VARCHAR2(3) NOT NULL,
		 RENTALCOST NUMBER(2,2) NOT NULL,
		 PRIMARY KEY (CATALOGUENUMBER));

and inserting a row with the following command:

Code:

INSERT INTO CATALOGUE VALUES ('C01001','X-Men 3: The Last Stand','Action','20th Century Fox',TO_DATE('02-OCT-2006', 'DD-MON-YYYY'),'12',4);

I've tried loads of different films, genres etc, but they all seem to result in the error message "ORA-01438: value larger than specified precision allowed for this column".

Now i really have no clue why it wont add the rows because as far as i can see the values im entering are within valid ranges.

Could anyone please enlighten me as ive only been doing SQL for a few weeks

Haircut · 30 Oct 2006 at 23:46

I think this may be a problem with the NUMBER column.

Try defining it as just a NUMBER(2) in your table (or NUMBER(4,2) if you need pence figures) and it should work.

joeyjojo · 30 Oct 2006 at 23:49

RENTALCOST NUMBER(2,2) is wrong.

To quote here

For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.

You want NUMBER(4,2) probably.

Damn, beat me, And I spent ages learning mysql to get that

Haircut · 30 Oct 2006 at 23:50

You beat me to a link though!

Was just looking for a good page to explain how Oracle datatypes work

joeyjojo · 30 Oct 2006 at 23:52

Haircut said:
You beat me to a link though!

Was just looking for a good page to explain how Oracle datatypes work

Go team!

Oh yeh, could you guys just check my mish-mash of code in xirokx's thread? Would appreciate it.

Welshy · 30 Oct 2006 at 23:57

ah i didnt know the first number was to include any decimal places! I'd kiss you both if you were here

thanks guys!