Can you check the same on yours?
CREATE TABLE O_CUSTOMER ( CUSTOMERID NUMBER NOT NULL, CUSTOMERSTN NUMBER NOT NULL ) insert into o_customer values(1000,1111111111) ; insert into o_customer values(2000,2222222222); CREATE OR REPLACE PROCEDURE Testing_No_Data_Found AS inStn number; outCustomerID number; Begin SELECT CUSTOMERID INTO outCustomerID FROM O_Customer WHERE CUSTOMERSTN=1111111111; dbms_output.put_line('direct in where 111111 -' || outcustomerid); SELECT CustomerID INTO outCustomerID FROM O_Customer WHERE CUSTOMERSTN=2222222222; dbms_output.put_line('direct in where 222222 -' || outcustomerid); /* so far so good*/ inSTN := 1111111111; SELECT CUSTOMERID INTO outCustomerID FROM O_Customer WHERE CUSTOMERSTN=inSTN; dbms_output.put_line('var in where 111111 -' || outcustomerid); /* we're still good..but now the same statement with the troublesome tn.. */ inSTN :=2222222222; SELECT CUSTOMERID INTO outCustomerID FROM O_Customer WHERE CUSTOMERSTN=inSTN; /* <-- Now we get a No_Data_Found Exception */ /* Other phone numbers that produce this result ( far as I know, these are the only other ones that have this problem) <snipped 7 other phone numbers out of ~130,000 that don't have a problem> */ dbms_output.put_line('var in where 2222222 -' || outcustomerid); EXCEPTION when no_data_found then /* However adding the table prefix makes the statement work fine with the affected TN */ SELECT O_Customer.CUSTOMERID INTO outCustomerID FROM O_Customer WHERE O_Customer.CUSTOMERSTN=inSTN; dbms_output.put_line(' no dat in wher 2222 ' || outcustomerid); END Testing_No_Data_Found; / Procedure created. SQL> set serveroutput on ; SQL> exec testing_no_data_found; direct in where 111111 -1000 direct in where 222222 -2000 var in where 111111 -1000 var in where 2222222 -2000
978409 wrote:Actually, it would be VERY_ helpful if you would tell me the exact phone numbers that don't work. If you don't want to post them publicly, email them to me:
Sorry, I didn't realize I missed your question, they're of Type Number in the table. There's no data type conversion going on. I've never tried recreating the table and adding those phone numbers back in. It would likely work, but I already have a work around (using the table prefix), I'm just understanding WHY it works..like WHY I need the table prefix for only some phone numbers.
CREATE TABLE BE.O_CUSTOMER
CUSTOMERID NUMBER NOT NULL,
CUSTOMERSTN NUMBER NOT NULL,
I created a Test procedure that illustrates this problem exactly. For privacy I'm subbing the phone numbers though..I don't think anyone needs to actually know the specific phone numbers.
Number that works: 1111111111
Number that doesn't work: 2222222222