This content has been marked as final. Show 19 replies
I did a testing with the code you provided, its just working fine with me.
The procedure never goes to when_nod_data_found exception at all.
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
Someone a lot more senior than myself got back from holidays just now and had a look at my test procedure and said he can't explain it either other than it's a bug. He did show me a another way to get the customer ID using a cursor set to the same SELECT I was using, and it doesn't have a problem even with the troubled numbers I was having issues with.
CREATE OR REPLACE FUNCTION BE.getcustomerid_new (instn IN NUMBER,
intaskid IN NUMBER,
incompletiondate IN DATE DEFAULT NULL)
WHERE customerstn = instn);
FETCH c_customer INTO r_customer;
outcustomerid := createnewcustomer (instn, intaskid, incompletiondate);
outcustomerid := r_customer.customerid;
When I set inSTN here to one of my troubled phone numbers..it doesn't have any problem getting me the customerID, even without the table prefix in the cursor definition. So I'll let it rest at that. Thanks to those, especially Yoonus who took the time to test/understand what was going on..it's appreciated :) have a great 2013 and hopefully if I post back again here it will be something a little more straight forward than this was.
Thank you very much Chris. If we could conclude fromt the issue that its a bug better you raise an SR so that oracle support will look into it and release a patch or bugfix.
If you can spend a little time to raise SR that may lead to bug fixing which will save a lot time for others (who are going to be stuck with the same issue you had)
Have a great 2013(with less bugs :) )
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
There is absolutely NO reason for me to dial those numbers, and I would not share them with anyone else. But I'd sure like to try to duplicate the problem. Without them, I cannot even start.
...also out of curiosity, what version of the Oracle database are you running?
Several more questions:
Is the function you are creating a database stored function, or is it a function created in a form?
Is the function within a package, or is it a stand-alone function?
It sounds like you are getting the error when you use the function, and others like it, when called from TOAD. Are you also trying to call the function from a form?
What version of Forms are you running?