Solved

Help please!

Posted on 2004-09-13
7
385 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
7 Comments
 
LVL 47

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 47

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now