Oracle Forms

Hi guys,

I'm relatively new to Oracle and PL/SQL in general, however I've run into something that's baffling me and I hope someone can help. I have a simple function that takes in a phone number, checks if it exists in the table and if not, creates a new customer with that phone number.

I've got about 100,000 entered without a problem. However I have about 10 phone numbers where they keep duplicating because the function thinks the phone number doesn't exist, but it actually exists what is now multiple times in the same table.


SELECT CUSTOMERID
INTO outCustomerID
FROM O_CUSTOMER
WHERE CUSTOMERSTN=inSTN

if I set inSTN to one of these duplicating phone numbers, it will bring up NO DATA FOUND, and thus create a new one which isn't what I want to happen. If I debug and enter the phone number (of type Number) manually, it still brings up No_Data_Found when the number is mostly definitely in the table.

This works properly when I try selecting manually:

SELECT CUSTOMERID
FROM O_Customer
WHERE CUSTOMERSTN=<troubled phone number>

And this works also:

SELECT count(CUSTOMERID)
INTO outCustomerId
FROM O_CUSTOMER
WHERE CUSTOMERSTN=inSTN; <-where inSTN is the phone number that previously got me No_Data_Found

It pulls up a count of 18 due to No_Data_Found triggering the creation of a new customer.


Here is my function in its entirety


CREATE OR REPLACE FUNCTION BE.GetCustomerID (
inSTN IN number,
inTASKID in number,
inCompletionDate in date default null
)

RETURN Number

IS
outCustomerID number;
numCustomerSTN number;
BEGIN


SELECT CUSTOMERID
INTO outCustomerID
FROM O_CUSTOMER
WHERE CUSTOMERSTN=inSTN;

RETURN outCustomerID;

EXCEPTION
WHEN No_Data_Found THEN
outCustomerID := CreateNewCustomer(inSTN,inTASKID,inCompletionDate);

RETURN outCustomerID;

END;
/

Edited by: 978409 on Dec 23, 2012 8:35 AM

Edited by: 978409 on Dec 23, 2012 2:09 PM