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

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

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;
/ 

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

V_REQ_PRICE        := to_number(trim(substr(v_buffer, 116, 5)));

V_CUSTOMER_NAME VARCHAR2(100);

declare
 
begin
 
PROC_CONVERSION_API('/usr/tmp' ,'BestBuy_Orders_062908.dat');
 
end;

 
 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);