CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Fulvio
Fulvio

Reputation: 965

Getting ORA-00001 and ORA-01438 errors when inserting data into a table

I have a problem trying to run this script where I got the error:

ORA-00001: unique constraint (constraint_name) violated

So, the thing is can't insert date into the table, and other error is:

ORA-01438: value larger than specified precision allowed for this column

CREATE TABLE TEST
(    TEST_ID        INT NOT NULL,
    COMPONENTS     VARCHAR2(30) NOT NULL,
    MATTER        VARCHAR2(2) NOT NULL,
    LIC            NUMBER(9, 2),
    MIC            NUMBER(9, 2),
    UIP            NUMBER(9, 2),
    CONSTRAINT TEST_pk PRIMARY KEY (TEST_ID));

--SEQUENCE TEST_ID

CREATE SEQUENCE SEQ_TEST_ID
 START WITH     1
 INCREMENT BY   1
 MINVALUE 1
 MAXVALUE 9999;

 --INSERTING VALUE TO TABLE

INSERT ALL

INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Food waste','OG','50.50','39.00','13.00')
INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Paper'     ,'OG','05.50','19.00','32.50')
INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Cardboard' ,'OG','00.00','00.00','10.00')
INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Plastic'       ,'OG','03.00','04.00','00.00')
INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Textiles'      ,'OG','03.00','06.00','04.00')
INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Ruber'     ,'OG','03.00','00.00','01.00')
INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Leather'       ,'OG','03.00','02.50','01.00')
INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Yard waste' ,'OG','00.00','00.00','15.00')
INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Wood'           ,'OG','03.00','05.50','02.50')
INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Misc. Organic','OG','03.00','04.00','00.00')

INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Glass'      ,'IG','05.50','05.50','08.00')
INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Tin Cans'       ,'IG','00.50','00.50','05.00')
INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Aluminum'       ,'IG','03.00','03.00','00.50')
INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Other metals'   ,'IG','00.00','00.00','02.50')
INTO TEST (TEST_ID,COMPONENTS, MATTER, LIC, MIC, UIP) VALUES (SEQ_TEST_ID.NEXTVAL,'Drit,ash,etc'   ,'IG','20.50','15.50','05.00')
SELECT * FROM DUAL;​

Upvotes: 0

Views: 508

Answers (3)

sure ruan
sure ruan

Reputation: 27

you can increaseur sequence cache size to 200,the default is 20.

alter sequence SEQ_TEST_ID cache 200;

try agian.

Upvotes: 1

user272735
user272735

Reputation: 10648

The error message is because of extra parentheses in int().

SQL> create table foo(f int());
create table foo(f int())
                      *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL> create table foo(f int);

Table created.

SQL>

Btw. the question has nothing to do with PL/SQL, it is a plain Oracle SQL issue.

Upvotes: 0

San
San

Reputation: 4538

If you are creating table in oracle, then AUTO_INCREMENT is not supported.

Create the table using MSW_ID as number and create a sequence to use its value in your table as.

CREATE TABLE MSW

( MSW_ID number, 
 COMPONENTS VARCHAR2(15) NOT NULL, 
 MATTER VARCHAR2(2) NOT NULL, 
 LIC NUMBER(4, 2), 
 MIC NUMBER(4, 2), 
 UIP NUMBER(4, 2), 
 CONSTRAINT NSW_pk PRIMARY KEY (MSW_ID));

You can learn more on sequences here;

CREATE SEQUENCE SEQ_MSW_ID
 START WITH     1
 INCREMENT BY   1
 MINVALUE 1
 MAXVALUE 99999999999;

and then you insert query becomes

INSERT  INTO MSW ( MSW_ID, COMPONENTS, MATTER, LIC, MIC, UIP) VALUES
                (SEQ_MSW_ID.NEXTVAL, 'Food waste'   ,'OG','50.50','39.00','13.00'); --> semicolon
INSERT  INTO MSW ( MSW_ID, COMPONENTS, MATTER, LIC, MIC, UIP) VALUES
                 (SEQ_MSW_ID.NEXTVAL, 'Paper'        ,'OG','05.50','19.00','32.50');  --> semicolon
.
.
.
INSERT  INTO MSW ( MSW_ID, COMPONENTS, MATTER, LIC, MIC, UIP) VALUES
                 (SEQ_MSW_ID.NEXTVAL, 'Drit,ash,etc' ,'IG','20.50','15.50','05.00');  --> semicolon

Or use insert all as a single statement as

INSERT ALL 
     INTO MSW ( MSW_ID, COMPONENTS, MATTER, LIC, MIC, UIP) VALUES
                (SEQ_MSW_ID.NEXTVAL, 'Food waste'   ,'OG','50.50','39.00','13.00')
     INTO MSW ( MSW_ID, COMPONENTS, MATTER, LIC, MIC, UIP) VALUES
                (SEQ_MSW_ID.NEXTVAL, 'Paper'        ,'OG','05.50','19.00','32.50')
     .
     .
     .
     INTO MSW ( MSW_ID, COMPONENTS, MATTER, LIC, MIC, UIP) VALUES
                (SEQ_MSW_ID.NEXTVAL, 'Drit,ash,etc' ,'IG','20.50','15.50','05.00')
SELECT * FROM DUAL;  --> semicolon

Upvotes: 0

Related Questions