This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Jan 2, 2013 1:26 PM by user346369 Go to original post RSS
  • 15. Re: Getting No_Data_Found when should be TOO MANY ROWS
    yoonas Expert
    Currently Being Moderated
    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.
    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
    Can you check the same on yours?
  • 16. Re: Getting No_Data_Found when should be TOO MANY ROWS
    981412 Newbie
    Currently Being Moderated
    Thanks Voonus,

    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)
    RETURN NUMBER
    IS
    outcustomerid NUMBER;
    numcustomerstn NUMBER;

    CURSOR c_customer
    IS
    (SELECT customerid
    FROM o_customer
    WHERE customerstn = instn);

    r_customer c_customer%ROWTYPE;

    BEGIN
    OPEN c_customer;

    FETCH c_customer INTO r_customer;

    IF c_customer%NOTFOUND
    THEN
    outcustomerid := createnewcustomer (instn, intaskid, incompletiondate);
    DBMS_OUTPUT.put_line ('createnewcustomer');
    ELSE
    outcustomerid := r_customer.customerid;
    DBMS_OUTPUT.put_line (outcustomerid);
    END IF;

    CLOSE c_customer;

    RETURN outcustomerid;
    END;
    /

    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.



    - Chris
  • 17. Re: Getting No_Data_Found when should be TOO MANY ROWS
    yoonas Expert
    Currently Being Moderated
    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 :) )
  • 18. Re: Getting No_Data_Found when should be TOO MANY ROWS
    user346369 Expert
    Currently Being Moderated
    978409 wrote:
    Hi Steve,

    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
    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:
    stevec@sfsu.edu

    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?
  • 19. Re: Getting No_Data_Found when should be TOO MANY ROWS
    user346369 Expert
    Currently Being Moderated
    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?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points