0

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.
1
  • Well, what's the type of c.charges and c.payment?
    – Mat
    Commented Apr 21, 2015 at 4:43

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.