?
Solved

Help please!

Posted on 2004-09-13
7
Medium Priority
?
421 Views
Last Modified: 2013-12-11
I try to update a ORDLNE table when i insert or update another table called DELLNE but I always get errors messages like

no_data_found,

or

ERROR at line 1:
ORA-20200: ORA-01407: cannot update ("S201525"."ORDLNE"."NUMBFILLED") to NULL
ORA-06512: at "S201525.NUMBFILLEDINCRE", line 50
ORA-04088: error during execution of trigger 'S201525.NUMBFILLEDINCRE'

all the times, what i have done wrong, can u help, please??

Here is the table structure

/*--*********************************************************************/          
/*--  Premier Products Database                             */          
/*--*********************************************************************/          
/*--                                                                    */          
/*--     The following DDL creates the tables                           */          
/*--     and fixed-format constraints.                   */          
/*--                                                                    */          
           

DROP TABLE DELLNE;
DROP TABLE DELIVERY;
DROP TABLE ORDLNE;
DROP TABLE ORDERS;
DROP TABLE PART;
DROP TABLE CUSTOMER;
DROP TABLE SLSREP;

/*--     Salesrep Table                                   */  
CREATE TABLE  SLSREP                                                
      (SLSRNUMB      DECIMAL(2),                          
       SLSRNAME      VARCHAR2(15),                          
       SLSRADDR      VARCHAR2(25),                          
       TOTCOMM       DECIMAL(7,2),                          
       COMMRATE      DECIMAL(3,2) CONSTRAINT CK_commrate CHECK( commrate BETWEEN 0 AND 1),                        
       ONORD         DECIMAL(9,2),                          
       CONSTRAINT PK_SLSREP PRIMARY KEY  (SLSRNUMB));
       
                                                                               
/*--     Customer Table                                                 */
CREATE TABLE  CUSTOMER                                                  
      (CUSTNUMB      DECIMAL(3),                          
       CUSTNAME      VARCHAR2(15)                ,                          
       CUSTADDR      VARCHAR2(25)                ,                          
       BALANCE       DECIMAL(7,2)            ,                          
       CREDLIM       DECIMAL(5)              ,                          
       SLSRNUMB      DECIMAL(2)              ,                          
       ONORD         DECIMAL(9,2)            ,                          
       CONSTRAINT PK_CUSTOMER PRIMARY KEY  (CUSTNUMB),                                        
       CONSTRAINT FK_slsrnumb_CUSTOMER FOREIGN KEY (SLSRNUMB) REFERENCES SLSREP,
       CONSTRAINT CK_credit CHECK (balance + onord <= credlim));
                 
/*--     Part Table   */                                                    
CREATE TABLE  PART                                                    
      (PARTNUMB      VARCHAR2(4),                          
       PARTDESC      VARCHAR2(10)                ,                          
       UNONHAND      DEC(4)       CONSTRAINT NN_unonhand NOT NULL,                          
       ITEMCLSS      VARCHAR2(2)                 ,                          
       WRHSNUMB      DEC(1)                  ,                          
       UNITPRCE      DECIMAL(5,2) CONSTRAINT NN_unitprce NOT NULL
                                  CONSTRAINT CK_unitprce CHECK(unitprce > 0),                          
       ALLOC         DEC(4)                  ,                          
       CONSTRAINT PK_PART PRIMARY KEY  (PARTNUMB),
       CONSTRAINT CK_stock CHECK( alloc <= unonhand));
       
                                                                               
/*--     Order Table      */                                                
CREATE TABLE  ORDERS                                                    
      (ORDNUMB       DECIMAL(5)      ,                          
       ORDDTE        DATE          CONSTRAINT NN_orddte NOT NULL,                          
       CUSTNUMB      DECIMAL(3)    CONSTRAINT NN_custnumb NOT NULL,                          
       CONSTRAINT PK_ORDERS PRIMARY KEY  (ORDNUMB),                                          
       CONSTRAINT FK_custnumb_ORDERS FOREIGN KEY  (CUSTNUMB) REFERENCES CUSTOMER);
                                                         
                                                                               
/*--     Orderline Table  */                                                
CREATE TABLE  ORDLNE                                                    
      (ORDNUMB       DECIMAL(5),                          
       PARTNUMB      VARCHAR2(4),                          
       NUMBORD       DECIMAL(3)         CONSTRAINT NN_numbord NOT NULL
                                        CONSTRAINT CK_numbord CHECK(numbord > 0),                          
       QUOTPRCE      DECIMAL(7,2)       CONSTRAINT CK_quotprce CHECK(quotprce > 0)
                                        CONSTRAINT NN_quotprce NOT NULL,    
       numbfilled    DECIMAL(3)             CONSTRAINT CK_numbfilled CHECK(numbfilled >= 0)
                                        CONSTRAINT NN_numbfilled NOT NULL,
       CONSTRAINT CK_overfilled CHECK(numbfilled <= numbord),                      
       CONSTRAINT PK_ORDLNE PRIMARY KEY  (ORDNUMB,PARTNUMB),                                                
       CONSTRAINT FK_ordnumb_ORDLNE FOREIGN KEY  (ORDNUMB) REFERENCES ORDERS
                                                ON DELETE CASCADE,                                        
       CONSTRAINT FK_partnumb_ORDLNE FOREIGN KEY  (PARTNUMB) REFERENCES PART);
                                                         
/*--     Delivery Table      */                                                
CREATE TABLE  DELIVERY                                                    
      (DELNUMB       DECIMAL(5)      ,                          
       DELDTE        DATE          CONSTRAINT NN_deldte NOT NULL,                          
       ORDNUMB       DECIMAL(5)    CONSTRAINT NN_ordnumb NOT NULL,                          
       CONSTRAINT PK_DELIVERY PRIMARY KEY  (DELNUMB),                                          
       CONSTRAINT FK_ordnumb_DELIVERY FOREIGN KEY  (ORDNUMB) REFERENCES ORDERS
                                                ON DELETE CASCADE,
       CONSTRAINT UC_Date_Ord UNIQUE(DELDTE,ORDNUMB));

/*--     Deliveryline Table  */                                                
CREATE TABLE  DELLNE                                                    
      (DELNUMB       DECIMAL(5),                          
       PARTNUMB      VARCHAR2(4),                          
       numbdel       DECIMAL(3)         CONSTRAINT NN_numbdel NOT NULL
                                        CONSTRAINT CK_numbdel CHECK(numbdel > 0),                
       CONSTRAINT PK_DELLNE PRIMARY KEY  (DELNUMB,PARTNUMB),                                                
       CONSTRAINT FK_delnumb_DELLNE FOREIGN KEY  (DELNUMB) REFERENCES DELIVERY
                                                ON DELETE CASCADE,                                        
       CONSTRAINT FK_partnumb_DELLNE FOREIGN KEY  (PARTNUMB) REFERENCES PART);
                                                                                                     
Here is the populate table

 DELETE FROM DELLNE;
DELETE FROM DELIVERY;
delete from ordlne;
delete from orders;
delete from part;
delete from customer;
delete from slsrep;
--
-- Insert data into Premier Products Database
--
INSERT INTO SLSREP VALUES                                              
( 3, 'Jones, Mary   ','123 Main,Grant,MI       ',2150.00,0.05,567.44);
INSERT INTO SLSREP VALUES                                              
( 6, 'Smith, William','102 Raymond,Ada,MI      ',4192.50,0.07,815.90);
INSERT INTO SLSREP VALUES                                              
(12, 'Brown, Sam    ','419 Harper,Lansing,MI   ',2150.00,0.05,998.62);
INSERT INTO CUSTOMER VALUES                                            
(124, 'Adams,Sally','481 Oak,Lansing,MI',418.75, 1500, 3, 567.44);      
INSERT INTO CUSTOMER VALUES                                            
(256, 'Samuels,Ann','215 Pete,Grant,MI', 10.75, 800, 6, 115.90);      
INSERT INTO CUSTOMER VALUES                                            
(311, 'Charles,Don','48 College,Ira,MI',200.10, 1300,12, 714.94);      
INSERT INTO CUSTOMER VALUES                                            
(315, 'Daniels,Tom','914 Cherry,Kent,MI',320.75, 1300, 6, 700.00);      
INSERT INTO CUSTOMER VALUES                                            
(405, 'Williams,Al','519 Watson,Grant,MI',201.75, 800,12, 0.00);      
INSERT INTO CUSTOMER VALUES                                            
(412, 'Adams,Sally','16 Elm,Lansing,MI',908.75,1000, 3, 0.00);        
INSERT INTO CUSTOMER VALUES                                            
(522, 'Nelson,Mary','108 Pine,Ada,MI', 49.50, 800,12, 283.68);        
INSERT INTO CUSTOMER VALUES                                            
(567, 'Baker,Joe','808 Ridge,Harper,MI',201.20, 300, 6, 0.00);        
INSERT INTO CUSTOMER VALUES                                            
(587, 'Roberts,Judy','512 Pine,Ada,MI', 57.75, 500, 6, 0.00);          
INSERT INTO CUSTOMER VALUES                                            
(622, 'Martin,Dan','419 Chip,Grant,MI',575.50, 600, 3, 0.00);
INSERT INTO PART VALUES                                                
('AX12','IRON',104,'HW',3, 17.95, 11);                                
INSERT INTO PART VALUES                                              
('AZ52','SKATES', 20,'SG',2, 24.95, 2);                              
INSERT INTO PART VALUES                                              
('BA74','BASEBALL', 40,'SG',1,  4.95, 4);                            
INSERT INTO PART VALUES                                              
('BH22','TOASTER', 95,'HW',3, 34.95, 0);                              
INSERT INTO PART VALUES                                              
('BT04','STOVE', 11,'AP',2,402.99, 2);                                
INSERT INTO PART VALUES                                              
('BZ66','WASHER', 52,'AP',3,311.95, 1);                              
INSERT INTO PART VALUES                                              
('CA14','SKILLET',  2,'HW',3, 19.95, 0);                              
INSERT INTO PART VALUES                                              
('CB03','BIKE', 44,'SG',1,187.50, 4);                                
INSERT INTO PART VALUES                                              
('CX11','MIXER',112,'HW',3, 57.95, 2);                                
INSERT INTO PART VALUES                                              
('CZ81','WEIGHTS',208,'SG',2,108.99, 2);
INSERT INTO ORDERS VALUES                                            
(12489,'02-SEP-91',124);                                              
INSERT INTO ORDERS VALUES                                            
(12491,'02-SEP-91',311);                                              
INSERT INTO ORDERS VALUES                                            
(12494,'04-SEP-91',315);                                              
INSERT INTO ORDERS VALUES                                            
(12495,'04-SEP-91',256);                                              
INSERT INTO ORDERS VALUES                                            
(12498,'05-SEP-91',522);                                              
INSERT INTO ORDERS VALUES                                          
(12500,'05-SEP-91',124);                                          
INSERT INTO ORDERS VALUES                                        
(12504,'05-SEP-91',522);                                            
INSERT INTO ORDLNE VALUES                                          
(12489,'AX12',11, 14.95, 2);                                    
INSERT INTO ORDLNE VALUES                                    
(12491,'BT04', 1,402.99, 0);                                      
INSERT INTO ORDLNE VALUES                                      
(12491,'BZ66', 1,311.95, 0);                                        
INSERT INTO ORDLNE VALUES                                        
(12494,'CB03', 4,175.00, 0);                                        
INSERT INTO ORDLNE VALUES                                          
(12495,'CX11', 2, 57.95, 0);                                          
INSERT INTO ORDLNE VALUES                                        
(12498,'AZ52', 2, 22.95, 0);                                      
INSERT INTO ORDLNE VALUES                                          
(12498,'BA74', 4,  4.95, 0);                                        
INSERT INTO ORDLNE VALUES                                            
(12500,'BT04', 1,402.99, 0);                                          
-- completely filled
INSERT INTO ORDLNE VALUES                                      
(12504,'CZ81', 2,108.99, 2);
INSERT INTO DELIVERY VALUES (24890, SYSDATE, 12489);
INSERT INTO DELLNE VALUES (24890,'AX12',2);                      
commit;

ALTER TABLE DELLNE ENABLE ALL TRIGGERS;
ALTER TABLE ORDLNE ENABLE ALL TRIGGERS;
ALTER TABLE CUSTOMER ENABLE ALL TRIGGERS;
ALTER TABLE PART ENABLE ALL TRIGGERS;
               
here is the code!

/* When a part is picked for a delivery of an order line, the number of parts filled in the corresponding order line must be incremented by the number of parts supplied. */

CREATE OR REPLACE TRIGGER numbfilledincre
AFTER INSERT OR UPDATE ON DELLNE
FOR EACH ROW

DECLARE
delornum     DELIVERY.ordnumb%TYPE;
orordnum     ORDERS.ordnumb%TYPE;
papartnum    PART.partnumb%TYPE;
numbdel_zero EXCEPTION;
err_num NUMBER(6);         
err_msg VARCHAR2(100);


BEGIN

SELECT ordnumb
INTO delornum
FROM DELIVERY
WHERE delnumb = :NEW.delnumb;

SELECT ordnumb
INTO orordnum
FROM ORDERS
WHERE ordnumb = delornum;

SELECT partnumb
INTO papartnum
FROM PART
WHERE partnumb = :NEW.partnumb;

UPDATE ORDLNE
SET  numbfilled = numbfilled + (:NEW.numbdel - :OLD.numbdel)
WHERE ordnumb = orordnum
AND partnumb = papartnum;

IF :NEW.numbdel <= 0 THEN
 RAISE numbdel_zero;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20200, 'Part number or Order number does not exist');
WHEN numbdel_zero THEN
RAISE_APPLICATION_ERROR (-20222, 'Number of delivery has to be more than 0');

WHEN OTHERS THEN
      err_num := SQLCODE;
        err_msg := SUBSTR(SQLERRM, 1, 100);

      IF err_msg like '%CK_OVERFILLED%' THEN
            RAISE_APPLICATION_ERROR( -20111, 'Order cannot be overfilled');
      
ELSE
            RAISE_APPLICATION_ERROR (-20200, err_msg);
      END IF;
END;
/
0
Comment
Question by:mangvee
  • 3
  • 2
6 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 12044534
The ORA-20200 error is an internally generated error.
But looking at the definition of ORDLNE it is forbiden to inser records without values in ORDLNE
column or to put NULL there. The first reason is the most important.
Check all INSERT and UPDATE statements.



CREATE TABLE  ORDLNE                                                    
     (ORDNUMB       DECIMAL(5),                          
      PARTNUMB      VARCHAR2(4),                          
      NUMBORD       DECIMAL(3)         CONSTRAINT NN_numbord NOT NULL
                                       CONSTRAINT CK_numbord CHECK(numbord > 0),                          
      QUOTPRCE      DECIMAL(7,2)       CONSTRAINT CK_quotprce CHECK(quotprce > 0)
                                       CONSTRAINT NN_quotprce NOT NULL,    
      numbfilled    DECIMAL(3)            CONSTRAINT CK_numbfilled CHECK(numbfilled >= 0)
                                       CONSTRAINT NN_numbfilled NOT NULL,
      CONSTRAINT CK_overfilled CHECK(numbfilled <= numbord),                      
      CONSTRAINT PK_ORDLNE PRIMARY KEY  (ORDNUMB,PARTNUMB),                                                
       CONSTRAINT FK_ordnumb_ORDLNE FOREIGN KEY  (ORDNUMB) REFERENCES ORDERS
                                       ON DELETE CASCADE,                                        
       CONSTRAINT FK_partnumb_ORDLNE FOREIGN KEY  (PARTNUMB) REFERENCES PART);

Look at this:

UPDATE ORDLNE
SET  numbfilled = numbfilled + (:NEW.numbdel - :OLD.numbdel)
WHERE ordnumb = orordnum
AND partnumb = papartnum;

If one of the component of the expression numbfilled + (:NEW.numbdel - :OLD.numbdel)
is NULL, then the expression will be NULL and the update will fail.

To prevent this try:

IF numbfilled IS NOT NULL and  :NEW.numbdel IS NOT NULL and  :OLD.numbdel IS NOT NULL)
THEN
UPDATE ORDLNE
SET  numbfilled = numbfilled + (:NEW.numbdel - :OLD.numbdel)
WHERE ordnumb = orordnum
AND partnumb = papartnum;
ELSE
...
END IF;
0
 

Author Comment

by:mangvee
ID: 12050570
I tried your solutions but it said that I have to declare numbfilled.  Do I need to do select statement or only just put :NEW or :OLD.  But It wouldn't work when I put :NEW or :OLD.

Please help!

Thanks
0
 
LVL 48

Expert Comment

by:schwertner
ID: 12052321
One of the colimns numbfilled, :NEW.numbdel, :OLD.numbdel
is NULL and this causes the error. the whole expression is NULL.
To avoid this use NVL function
UPDATE ORDLNE
SET  numbfilled = NVL(numbfilled + (:NEW.numbdel - :OLD.numbdel)),NULL_DATA)

where NULL_DATA is a special value which you will put if the expression is NULL.

Another possibility is to check which value is NULL and to take a decision what to do, but
only you can take the decision becaus e you know the business rules.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:mangvee
ID: 12052369
NVL???? that is a little too high for my level  -_- ' (sorry)
I solved it out by the following solution already, thanks for all help!!!

--1--
/* When a part is picked for a delivery of an order line, the number of parts filled in the corresponding order line must be incremented by the number of parts supplied. */

CREATE OR REPLACE TRIGGER numbfilledincre
AFTER INSERT OR UPDATE ON DELLNE
FOR EACH ROW

DECLARE
delornum     DELIVERY.ordnumb%TYPE;
orordnum     ORDERS.ordnumb%TYPE;
papartnum    PART.partnumb%TYPE;
numbdel_zero EXCEPTION;
err_num NUMBER(6);         
err_msg VARCHAR2(100);


BEGIN

SELECT ordnumb
INTO delornum
FROM DELIVERY
WHERE delnumb = :NEW.delnumb;

SELECT ordnumb
INTO orordnum
FROM ORDERS
WHERE ordnumb = delornum;

SELECT partnumb
INTO papartnum
FROM PART
WHERE partnumb = :NEW.partnumb;

IF UPDATING THEN
UPDATE ORDLNE
SET  numbfilled = numbfilled + (:NEW.numbdel - :OLD.numbdel)
WHERE ordnumb = orordnum
AND partnumb = papartnum;

ELSIF INSERTING THEN
UPDATE ORDLNE
SET  numbfilled = (numbfilled + :NEW.numbdel)
WHERE ordnumb = orordnum
AND partnumb = papartnum;
END IF;


IF :NEW.numbdel <= 0 THEN
 RAISE numbdel_zero;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20200, 'Part number or Order number does not exist');
WHEN numbdel_zero THEN
RAISE_APPLICATION_ERROR (-20222, 'Number of delivery has to be more than 0');

WHEN OTHERS THEN
      err_num := SQLCODE;
        err_msg := SUBSTR(SQLERRM, 1, 100);

      IF err_msg like '%CK_OVERFILLED%' THEN
            RAISE_APPLICATION_ERROR( -20111, 'Order cannot be overfilled');
      
ELSE
            RAISE_APPLICATION_ERROR (-20200, err_msg);
      END IF;
END;
/
0
 
LVL 48

Expert Comment

by:schwertner
ID: 12053234
NVL substitutes the NULL value of the expression with the value given by the second argument.
You put NULLs in the database - this is the reason
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 12317982
PAQed, with points refunded (250)

Computer101
E-E Admin
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question