Home » Other » Training & Certification » ORA-06502: PL/SQL: numeric or value error: character to number conversion error (ORACLE,9i,XP)
ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #376291] Tue, 16 December 2008 17:56 Go to next message
Anvesh REddy
Messages: 7
Registered: December 2008
Location: Charlotte,NC
Junior Member
Dear members

I am getting the the following error:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

I am trying to read the data from a flat file using UTL_FILE and populate a table after processing the data from the flat file.

the flat file structure is as follows:

Field	                  Position From	Position To
CUSTOMER_NAME	          1	30
MANUFACTURER	         31	70
PRODUCT_NAME	         71	90
QUANTITY	                   91	95
REQUESTED_SHIP_DATE    96	115
REQUESTED_PRICE	       116	120



Sample data from the flat file is as follows:
BESTBUY                       SONY ERICSSON                           W580i               25   1-AUG-2008          50
BESTBUY                       SAMSUNG                                 BLACKJACK           50   15-JUL-2008         150
BESTBUY                       APPLE                                   IPHONE 4GB          50   15-JUL-2008         
BESTBUY                       ATT                                     TILT                100  15-JUN-2008         
BESTBUY                       NOKIA                                   N73                 50   15-JUL-2008         200

the program code is as follows:


CREATE OR REPLACE PROCEDURE ANVESH.PROC_CONVERSION_API(FILE_PATH IN VARCHAR2,FILE_NAME IN VARCHAR2) 
IS
    v_file_type utl_file.file_type;
    v_buffer VARCHAR2(1000);
    V_CUSTOMER_NAME VARCHAR2(100);
    V_MANUFACTURER VARCHAR2(50);
    V_PRODUCT_NAME VARCHAR2(50);
    V_QUANTITY NUMBER(10);
    V_REQ_SHIP_DATE DATE;
    V_REQ_PRICE NUMBER(10);
    V_LOG_FILE utl_file.file_type;
    
    V_COUNT_CUST NUMBER;
    V_COUNT_PROD NUMBER;
    
     
BEGIN
    DBMS_OUTPUT.PUT_LINE('Inside begin 1');
    v_file_type := UTL_FILE.fopen(FILE_PATH, FILE_NAME, 'r',null);
        DBMS_OUTPUT.PUT_LINE('Inside begin 1.1');
 
    
    LOOP
    
        BEGIN
                DBMS_OUTPUT.PUT_LINE('Inside begin 2');
      
            UTL_FILE.GET_LINE (v_file_type,v_buffer); 
                DBMS_OUTPUT.PUT_LINE('Inside begin 2.1');
                
                     V_CUSTOMER_NAME := trim(substr(v_buffer, 1, 30));
                     DBMS_OUTPUT.PUT_LINE('Customer Name is '||V_CUSTOMER_NAME);
                     
                     V_MANUFACTURER  := trim(substr(v_buffer, 31, 40));
                     DBMS_OUTPUT.PUT_LINE('Manufacturer is '||V_MANUFACTURER);
 
                    V_PRODUCT_NAME  := trim(substr(v_buffer,  71, 20));
                     DBMS_OUTPUT.PUT_LINE('Product Name is '||V_PRODUCT_NAME);
 
                    V_QUANTITY      := to_number(trim(substr(v_buffer, 91, 5)));
                     DBMS_OUTPUT.PUT_LINE('Customer Name is '||V_QUANTITY);
 
                     V_REQ_SHIP_DATE     := to_date(trim(substr(v_buffer, 96, 20)), 'DD-MON-YYYY');
                      DBMS_OUTPUT.PUT_LINE('Requested Ship Date is '|| V_REQ_SHIP_DATE);
                     
                    V_REQ_PRICE        := to_number(trim(substr(v_buffer, 116, 5)));
                     DBMS_OUTPUT.PUT_LINE('Requested Price is '||V_REQ_PRICE);
               
            
            V_LOG_FILE := UTL_FILE.FOPEN(FILE_PATH, 'LOG_FILE.dat', 'A');
            
                IF (V_QUANTITY > 0)
                THEN
                   SELECT COUNT (*)
                   INTO V_COUNT_CUST
                   FROM CONVERSION_CUSTOMERS
                   WHERE CUSTOMER_NAME = V_CUSTOMER_NAME;
           
                   IF(V_COUNT_CUST > 0)
                   THEN
                       SELECT COUNT(*)
                       INTO V_COUNT_PROD
                       FROM conversion_products
                       WHERE PRODUCT_NAME = V_PRODUCT_NAME;
              
                      IF(V_COUNT_PROD >0)
                       THEN
                            INSERT INTO XXCTS_ORDER_DETAILS_STG VALUES (V_CUSTOMER_NAME, V_PRODUCT_NAME, V_MANUFACTURER, V_QUANTITY, V_REQ_SHIP_DATE, V_REQ_PRICE, 'ACTIVE', 'ORDER TAKEN');   
        
                       ELSE
                            DBMS_OUTPUT.PUT_LINE('PRODUCT SHOULD BE VALID');
                            UTL_FILE.PUT_LINE(V_LOG_FILE, 'PRODUCT SHOULD BE VALID');                    
        
                       END IF; 
                   ELSE
                      DBMS_OUTPUT.PUT_LINE('CUSTOMER SHOULD BE VALID');
                      UTL_FILE.PUT_LINE(V_LOG_FILE, 'CUSTOMER SHOULD BE VALID');
                   END IF;       
           
        
                ELSE
                    DBMS_OUTPUT.PUT_LINE('QUANTITY SHOULD BE VALID');
                    UTL_FILE.PUT_LINE(V_LOG_FILE, 'QUANTITY SHOULD BE VALID');
                END IF; 
 
        
                EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    EXIT;
                END;
        
    END LOOP;
    
END;
/ 



the procedure compiles fine but when I invoke the procedure from a PL/SQL block.I get an error stating

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "ANVESH.PROC_CONVERSION_API", line 46
ORA-06512: at line 5


he line 46 is
V_REQ_PRICE        := to_number(trim(substr(v_buffer, 116, 5)));


the line 5 is
V_CUSTOMER_NAME VARCHAR2(100);



the PL/SQL block is as follows:
declare
 
begin
 
PROC_CONVERSION_API('/usr/tmp' ,'BestBuy_Orders_062908.dat');
 
end;



Can anyone please point out what is wrong with the program


Thanks

Anvesh




[Updated on: Tue, 16 December 2008 18:27]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #376319 is a reply to message #376291] Tue, 16 December 2008 23:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
trim(substr(v_buffer, 116, 5)) is not a number.
Display it before converting it to number adn you will see.

Regards
Michel

[Updated on: Tue, 16 December 2008 23:16]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #376555 is a reply to message #376291] Wed, 17 December 2008 17:44 Go to previous message
Anvesh REddy
Messages: 7
Registered: December 2008
Location: Charlotte,NC
Junior Member
finally was able to resole the error.Error was caused due to the presence of null in the data file records.So had to change the code to

 
 V_REQ_PRICE        :=nvl(substr( trim(v_buffer), 116, length(v_buffer)-116),0);
 DBMS_OUTPUT.PUT_LINE('The requested price is  '||V_REQ_PRICE);



As somebody pointed out rightly it was data dependency error!!


Thanks
Anvesh
Previous Topic: Oracle RAC Traning in banglore
Next Topic: Oracle Functional Training Chennai
Goto Forum:
  


Current Time: Fri Dec 09 23:13:52 CST 2016

Total time taken to generate the page: 0.18022 seconds