ORA-01722 Error when running stored procedure

oraclestored-procedures

I am trying to run a stored procedure however when I run it with an a valid Number it gives me an ORA-01722 error. I checked to make sure that the columns in the where clause where the parameter is used is of NUMBER type. I checked to make sure that the record exists. Note: When I take out c.charges-c.payment the query runs fine.

create procedure patient_InfoNew(vpatientID NUMBER) IS
x VARCHAR2(300);
BEGIN
 select a.firstname || a.lastname || ' ' ||  a.ssn || ' ' || a.streetname || ' ' || a.phonenumber || ' ' || b.servicedate || ' ' ||  b.servicetype || ' ' || c.charges - c.payment || ' ' || e.name || ' ' || e.insnumber into x
 from patient a, patientaccount b, patient_info c, patientinsurance d, insurance e
 where a.id = vpatientID  AND e.id = d.insuranceid AND a.id = b.id AND a.id = c.id AND a.id = d.id
AND b.servicedate = 
   (Select MAX(servicedate)
    from patientaccount
    where id = vpatientID);

DBMS_OUTPUT.PUT_LINE(x);

EXCEPTION
    WHEN     VALUE_ERROR THEN                   
        DBMS_OUTPUT.PUT_LINE('Error '||SQLCODE||
SUBSTR(SQLERRM,1,80));  
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Divide by zero');
    WHEN OTHERS THEN
        IF SQL%NOTFOUND THEN
               DBMS_OUTPUT.PUT_LINE('No such record was found');
        END IF;
        DBMS_OUTPUT.PUT_LINE('Error '||SQLCODE || 
                        SUBSTR(SQLERRM,1,80));  

END;

This is the error I get below:

No such record was found 
Error -1722ORA-01722: invalid number 
PL/SQL procedure successfully completed.

Best Answer

You are getting that error because you have a row in patient_info where either charges or payment (or both) are NULL. To just make the error go away, you could replace

' ' || c.charges - c.payment || ' '

with

' ' || (c.charges - c.payment) || ' '

but I suppose you would want to handle the case where charges or payment are NULL in some meaningful way, something like

' ' || (nvl(c.charges, 0) - nvl(c.payment, 0)) || ' '

otherwise when any of charges, payment are null you'll get an empty string instead of a number (since any number - NULL is NULL and NULL is converted to an empty string when concatenated with other strings) and you probably don't want that.

Related Question