1 2 Previous Next 19 Replies Latest reply: Jan 2, 2013 3:26 PM by user346369 RSS

    Getting No_Data_Found when should be TOO MANY ROWS

    981412
      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
        • 1. Re: Getting No_Data_Found when should be TOO MANY ROWS
          981412
          A bit more info..if I Modify the function and pass it one of the troubled phone numbers in Debug for inSTN:


          SELECT count(CUSTOMERID) <--primary key
          INTO numCustomerCount
          FROM O_CUSTOMER
          WHERE CUSTOMERSTN=inSTN; <--set this to one of my 10 phone numbers that constantly get No_Data_Found despite being in the table

          <at this point numCustomerCount is now 18 for this single phone number>


          If NumCustomerCount < 1 then
          outCustomerID:=CreateNewCustomer(inSTN,inTASKID,inCompletionDate);
          Return outCustomerID;
          ELSE
          SELECT CUSTOMERID
          INTO outCustomerID
          FROM O_Customer
          WHERE CUSTOMERSTN=inSTN; <-- and yet here again I get ORA-1403 No_Data_Found, when it just found 18 records matching inSTN previously

          Return outCustomerID;

          Edited by: 978409 on Dec 23, 2012 2:24 PM
          • 2. Re: Getting No_Data_Found when should be TOO MANY ROWS
            981412
            Well..I figured it out. Sort of. I have to add the table prefix in my SELECT INTO

            SELECT O_Customer.CUSTOMERID
            INTO outCustomerID
            FROM O_Customer
            WHERE O_Customer.CUSTOMERSTN=inSTN;


            I'm still really curious as to why the other 100,000 TNs don't have this problem but ~10 of them require the table prefix to be present to locate CustomerID otherwise it returns No_Data_Found.
            • 3. Re: Getting No_Data_Found when should be TOO MANY ROWS
              O.Developer
              HI

              Based on your query information, i understood, you have tow stage, one is first checking -select into varialble. Then create if not exits.

              First SELECT CUSTOMERID
              INTO outCustomerID
              FROM O_CUSTOMER
              WHERE CUSTOMERSTN=inSTN --------wlll work only if it match single value. Are you handling exceptions on your pl/SQL , if so please paste here your full pl/sql to identify the error only for few rows.
              • 4. Re: Getting No_Data_Found when should be TOO MANY ROWS
                981412
                Hi there,

                I posted the whole thing in the original post near the end. I do handle No_Data_Found, in which case it passes the TN to a function to create a new customer.

                EXCEPTION

                WHEN No_Data_Found THEN

                outCustomerID := CreateNewCustomer(inSTN,inTASKID,inCompletionDate);

                RETURN outCustomerID;

                What's been happening however iis there are about 10 phone numbers out of 100,000 that constantly cause a No_Data_Found Exception and thus a new customer is created when they are already in the table (sometimes more than once now)

                So I would expect a Too_Many_Rows type of Exception, not No_Data_Found. When I add the table prefix to my SELECT INTO then I get a too many rows error which I'm fine with..that means at least it works as expected now.

                However I'm still baffled as to why it's No_Data_Found only on specific phone numbers unless I add that table prefix to my SELECT statement. None of the other 100k phone numbers have that problem, only these 10. It used to be one or two, but over time it's grown to 10 now. As far as I've read, I shouldn't have to add that table prefix to my statement either, but it somehow makes all the difference for these 10 guys.
                • 5. Re: Getting No_Data_Found when should be TOO MANY ROWS
                  user346369
                  Since adding the table name to the select: SELECT O_Customer.CUSTOMERID makes a difference, please check... do you have a local variable defined as CUSTOMERID?

                  How is O_Customer.CUSTOMERID defined in the O_Customer table? (Numeric or Varchar2?)
                  How is CUSTOMERSTN defined? Is it Number, the same as inSTN?

                  Give us examples of phone numbers that work and some that don't.
                  • 6. Re: Getting No_Data_Found when should be TOO MANY ROWS
                    O.Developer
                    HI

                    Your codes looks fine, and is strange to get such resulst , may our our others experts can reply for this.

                    However, can you pl rewrite your code like below

                    Begin
                    SELECT Count(CUSTOMERID)
                    INTO Cust_Cts
                    FROM O_CUSTOMER
                    WHERE CUSTOMERSTN=inSTN;

                    IF Cust_cts = 0Then

                    Call the Functiion

                    Elsif
                    Cust_cts > 1 Then Message('Too Many Rows');
                    Elsif
                    Cust_cts =1 Then Message('Cusotmer Already exits');

                    End if;

                    exception.

                    ...
                    End ;

                    ---------And paste here the result...
                    • 7. Re: Getting No_Data_Found when should be TOO MANY ROWS
                      981412
                      Thanks, the problem is once I get a count of 1 and actually want the primary key, it will still fail without the table prefix in the select.

                      I've noticed too that it's not only this function, any function where I'm selecting the primary key for a phone number doesn't work for those 10 phone numbers without the table prefix in the SELECT. Same error: No_Data_Found. So I've had to add the prefix to all my other functions/procs that are working just fine with everyone else so it works for these 10 guys. And I still have no idea why I need to heh but as long as it works I should be happy enough with that.
                      • 8. Re: Getting No_Data_Found when should be TOO MANY ROWS
                        yoonas
                        Hi,

                        This is not expected behavior
                        SELECT 
                          CUSTOMERID
                        FROM 
                         O_CUSTOMER
                        WHERE 
                         CUSTOMERSTN=inSTN
                        Can you run this query in sqlplus with any of those 10 guys and see the output.

                        Regards
                        Yoonas
                        • 9. Re: Getting No_Data_Found when should be TOO MANY ROWS
                          981412
                          I'm using TOAD, is SQL PLUS just where you create a new SQL tab? If so, it does work

                          This works properly when I try selecting manually:

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

                          The problem happens when I turn that into a SELECT INTO statement in my function, then I get No_Data_Found. And only on those 10 numbers.

                          I've had to turn all of my functions/procs into something like this when I need the primary key from the table now:


                          SELECT O_Customer.CustomerID
                          INTO numCustomerID
                          FROM O_Customer
                          WHERE O_Customer.CustomerSTN=inSTN;

                          Otherwise if one of those 10 TNs pass through the function, it will error out with NO_Data_Found. Before I changed this statement touse the table prefix though, easily 10's of thousands of other phone numbers have passed through here just fine
                          • 10. Re: Getting No_Data_Found when should be TOO MANY ROWS
                            yoonas
                            Hi,

                            Then run the complete function code in an anonymous block
                            DECLARE
                                 inSTN IN number,
                                 inTASKID in number,
                                 inCompletionDate in date default null
                                 outCustomerID number;
                                 numCustomerSTN number;
                            BEGIN
                            
                            SELECT CUSTOMERID
                            INTO outCustomerID
                            FROM O_CUSTOMER
                            WHERE CUSTOMERSTN=inSTN;
                            
                            DBMS_OUTPUT.PUT_LINE(outCustomerID);
                            
                            EXCEPTION
                            WHEN No_Data_Found THEN
                            DBMS_OUTPUT.PUT_LINE(outCustomerID);
                            --outCustomerID := CreateNewCustomer(inSTN,inTASKID,inCompletionDate);
                            
                            END;
                            • 11. Re: Getting No_Data_Found when should be TOO MANY ROWS
                              981412
                              I might not be doing it right, but I created a new proc and I tried pasting that in my procedure after BEGIN and it didn't like the IN parameters. So what I tired to do was hard code the troubled STN instead of using inSTN variable and that actually worked..no Exception occured. So then I went back to my original function and removed the table prefixes, then replaced inSTN with the hardcoded phone number and that worked.

                              However once I put it back to inSTN and pass it the phone number through DEBUG, that's when I get No_Data_Found. Until I add the Table Prefix again. So the problem seems to be with using inSTN without prefixing my SELECT INTO. So that's a bit more info than I had before at least :)


                              Here's how I tried to use your code block:

                              CREATE OR REPLACE Procedure BE.TestAnonymous


                              AS

                              BEGIN

                              DECLARE
                              inSTN IN number, <--Errors here saying it found 'Number' and expected ; or something else
                              inTASKID in number,
                              inCompletionDate in date default null
                              outCustomerID number;
                              numCustomerSTN number;
                              BEGIN

                              SELECT CUSTOMERID
                              INTO outCustomerID
                              FROM O_CUSTOMER
                              WHERE CUSTOMERSTN=inSTN;

                              DBMS_OUTPUT.PUT_LINE(outCustomerID);

                              EXCEPTION
                              WHEN No_Data_Found THEN
                              DBMS_OUTPUT.PUT_LINE(outCustomerID);
                              --outCustomerID := CreateNewCustomer(inSTN,inTASKID,inCompletionDate);

                              END;

                              End;
                              • 12. Re: Getting No_Data_Found when should be TOO MANY ROWS
                                yoonas
                                I am sorry while copying i forgot to comment out those in parameters lines that you have writter for function.

                                If its working when you are hard coding in the function and fialing when you are passing it using a parameter means something wrong with your parameter data type i guess.

                                Can you just paste your data sample for both records (wrong and correct) and your table structure and your function as it is

                                If the below code gives you proper result when you hardcode means you have to see what extra you are doing in your function
                                DECLARE
                                --inSTN IN number, 
                                --inTASKID in number,
                                --inCompletionDate in date default null
                                outCustomerID number;
                                numCustomerSTN number;
                                BEGIN
                                
                                SELECT CUSTOMERID
                                INTO outCustomerID
                                FROM O_CUSTOMER
                                WHERE CUSTOMERSTN=inSTN;
                                
                                DBMS_OUTPUT.PUT_LINE(outCustomerID);
                                
                                EXCEPTION
                                WHEN No_Data_Found THEN
                                DBMS_OUTPUT.PUT_LINE(outCustomerID);
                                --outCustomerID := CreateNewCustomer(inSTN,inTASKID,inCompletionDate);
                                
                                END;
                                • 13. Re: Getting No_Data_Found when should be TOO MANY ROWS
                                  user346369
                                  978409: You have completely ignored my post asking:
                                  How is O_Customer.CUSTOMERID defined in the O_Customer table? (Numeric or Varchar2?)
                                  How is CUSTOMERSTN defined? Is it Number, the same as inSTN?

                                  Give us examples of phone numbers that work and some that don't.
                                  You can write procedures and functions all day long, but if there is a datatype conversion going on, nothing you do will fix it.

                                  It would be extremely helpful if you would post a simple example of a create table with the columns you are having trouble with, and a few inserts to enter the good and bad data you are working with.

                                  SHOW US THE PHONE NUMBERS that don't work!!!! This is not an Oracle database problem -- Oracle works with numbers and data all over the world, and if it failed to find or return specific values, it would be a worthless database. The problem is either in your data or the way the data is defined.
                                  • 14. Re: Getting No_Data_Found when should be TOO MANY ROWS
                                    981412
                                    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.

                                    <snip>
                                    CREATE TABLE BE.O_CUSTOMER
                                    (
                                    CUSTOMERID NUMBER NOT NULL,
                                    CUSTOMERSTN NUMBER NOT NULL,

                                    )
                                    <snip>

                                    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


                                    CREATE OR REPLACE PROCEDURE BE.Testing_No_Data_Found

                                    AS

                                    inStn number;
                                    outCustomerID number;

                                    Begin

                                    SELECT CUSTOMERID
                                    INTO outCustomerID
                                    FROM O_Customer
                                    WHERE CUSTOMERSTN=1111111111;

                                    SELECT CustomerID
                                    INTO outCustomerID
                                    FROM O_Customer
                                    WHERE CUSTOMERSTN=2222222222;

                                    /* so far so good*/

                                    inSTN := 1111111111;

                                    SELECT CUSTOMERID
                                    INTO outCustomerID
                                    FROM O_Customer
                                    WHERE CUSTOMERSTN=inSTN;

                                    /* 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>
                                    */


                                    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; <-- now I correctly get the same CustomerID result as the second SELECT statement at the beginning of the procedure (where I hard coded the troubled phone number instead of setting inSTN to that number)

                                    END Testing_No_Data_Found;
                                    /


                                    I don't really want to post the entire table definition, so hopefully the working SELECTs at the start are enough to show that the data exists as type Number and work fine when hard coded, but somehow some phone numbers cause a problem when queried using inSTN without a table prefix, which is the crux of my issue.


                                    Edited by: 978409 on Dec 30, 2012 9:05 AM
                                    1 2 Previous Next