Why do I get 'No data found' when passing a string variable into PL/SQL where clause

Question

I'm trying to get a user's ID number based on their network ID, but for some reason when I try to pass in their network ID in the WHERE clause of the SELECT statement in this PL/SQL I get an error.

However if I output what is in user_rec.USER_NAME to the console there is a valid value, and if I hardcode a string ex: WHERE UserID = 'USERNAME' it also works as expected. It seems to only fail for string variables in the WHERE clause.....?

ORA-01403: no data found
ORA-06512 at line 12
01403.00000 - "no data found"
*Cause
*Action
Error at line 1

PL/SQL:

DECLARE
   v_addressbooknum number;
   v_addresstype nchar(3);
CURSOR SELECT_PORTALUSERS is
SELECT USER_NAME FROM PERSONS WHERE DEFAULT_GROUP <> 'Employees';
BEGIN
  FOR user_rec IN SELECT_PORTALUSERS LOOP
    -- Fetch Address Book # based on user ID
    SELECT ABNum INTO v_addressbooknum
    FROM OWNER.TABLE@DBLINK
    WHERE UserID = user_rec.USER_NAME;
  END LOOP;
END;

===================================================================== EDIT

Here is the new PL/SQL I ran based on DCookie's suggestion

DECLARE
   v_addressbooknum number;
   v_addresstype nchar(3);
CURSOR SELECT_PORTALUSERS is
SELECT USER_NAME FROM PERSONS WHERE DEFAULT_GROUP <> 'Employees';
BEGIN
  FOR user_rec IN SELECT_PORTALUSERS LOOP

    -- DEBUG --
    DBMS_OUTPUT.PUT_LINE('DEBUG Found User: ' || user_rec.USER_NAME);
    -- END DEBUG --
    BEGIN
        -- Fetch Address Book # based on user ID
        SELECT ABNum INTO v_addressbooknum
        FROM OWNER.TABLE@DBLINK
        WHERE UserID = user_rec.USER_NAME;
    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('User: '||user_rec.USER_NAME);
    END;
  END LOOP;
  -- DEBUG --
  DBMS_OUTPUT.PUT_LINE('DONE');
  -- END DEBUG --
END;

And here is the output I get, you can see if finds values in the user_rec.user_name. And if I hardcode one of the user IDs found into the where clause it works properly (meaning the User IDs exist in the tabled the fetch is being executed against).

DEBUG Found User: USER846
User: USER846
DEBUG Found User: USER241
User: USER241
DEBUG Found User: USER780
User: USER780
DEBUG Found User: USER783
User: USER783
DEBUG Found User: USER294
User: USER294
DONE

ProfessionalAmateur · Accepted Answer · 2011-04-20 20:17:32Z

3

If your FOR loop ever selects a user_name that is not in the remote table (ex: remote column has trailing blanks) you'll get this error, because you're not handling any exceptions on the select statement. I'd debug this by adding an exception handler and writing debug info:

BEGIN
  FOR user_rec IN SELECT_PORTALUSERS LOOP
    -- Fetch Address Book # based on user ID
    BEGIN
    SELECT ABNum INTO v_addressbooknum
    FROM OWNER.TABLE@DBLINK
    WHERE UserID = user_rec.USER_NAME; -- TRIM column or variable if necessary
    EXCEPTION
      WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('User: '||user_rec.user_name);
    END;
  END LOOP;
END;

edited Apr 20, 2011 at 20:17

ProfessionalAmateur

4,57310 gold badges50 silver badges63 bronze badges

answered Apr 19, 2011 at 20:44

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ProfessionalAmateur Over a year ago

So I added a DBMS_OUTPUT at the beginning of the loop (before the BEGIN), and added the EXCEPTION as you recommended. But now I get: "DEBUG USERNAME: USER1 \n User: USER1 \n DEBUG USERNAME: USER2 \n User: User2" So it finds a value, but still thinks there isn't one there.

DCookie Over a year ago

The fact that your exception handler is firing indicates the value is not there. Are you certain there isn't any sort of extra whitespace in the the username? Try: SELECT DUMP(USER_NAME,17) FROM PERSONS WHERE DEFAULT_GROUP <> 'Employees'; at a command prompt and make sure the values returned look reasonable.

ProfessionalAmateur Over a year ago

I'll post what Im running and the DBMS output in an edit above so I can use more characters and format it properly. The FOR loop is definitely looping for each user ID.

Shannon Severance Over a year ago

Another way to check for white space it to concatenate a marker on each end. For example: DBMS_OUTPUT.PUT_LINE('User: #' || user_rec.username || '#'); OTOH, DUMP is more exact.

HAL 9000 · Accepted Answer · 2011-04-20 08:03:36Z

2

It just seems that not all users that are in PERSONS are also in v_addressbooknum.

Try the following sql to find all users that exist in PERSONS but not in v_addressbooknum

SELECT USER_NAME FROM PERSONS
MINUS
SELECT userID FROM v_addressbooknum
;

Then use

Exception
  When NO_DATA_FOUND Then
    DBMS_OUTPUT.PUT_LINE('no data found');
End;

to handle this error condition.

Do not use "when others" to handle "no data found".

answered Apr 20, 2011 at 8:03

HAL 9000

3,9851 gold badge25 silver badges29 bronze badges

Comments

ProfessionalAmateur · Accepted Answer · 2011-04-20 12:07:57Z

0

Looks like it was a dataformatting issue, I TRIMed both the column and the variable in the WHERE clause and it seems like it properly returns data... Odd since they are both VARCHARs

DECLARE
   v_addressbooknum number;
   v_addresstype nchar(3);
CURSOR SELECT_PORTALUSERS is
SELECT USER_NAME FROM PERSONS WHERE DEFAULT_GROUP <> 'Employees';
BEGIN
  FOR user_rec IN SELECT_PORTALUSERS LOOP

    BEGIN
        -- Fetch Address Book # based on user ID
        SELECT ABNum INTO v_addressbooknum
        FROM OWNER.TABLE@DBLINK
        WHERE TRRIM(UserID) = TRIM(user_rec.USER_NAME);
    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('User: '||user_rec.USER_NAME);
    END;
  END LOOP;

END;

answered Apr 20, 2011 at 12:07

ProfessionalAmateur

4,57310 gold badges50 silver badges63 bronze badges

2 Comments

DCookie Over a year ago

If applying TRIM to your data solves your problem, then this is exactly what I was trying to tell you was wrong. In one of your schemas, the user_name field has whitespace in it. It is really not a data formatting issue, it is a data issue.

ProfessionalAmateur Over a year ago

@DCookie - Yes I misunderstood what you were saying, I read it as the value was blank/null, not that it was a data mismatch.

Alex Peta · Accepted Answer · 2011-04-20 17:28:20Z

0

could you please put the table definition and a couple of rows?

i think that

SELECT ABNum INTO v_addressbooknum
        FROM OWNER.TABLE@DBLINK
        WHERE UserID = user_rec.USER_NAME;

the column UserId from table@dblink is not the same column as user_rec.USER_NAME;

in the code that you have posted, the select goes with the exception with no_data_found on each row

answered Apr 20, 2011 at 17:28

Alex Peta

1,4051 gold badge15 silver badges27 bronze badges

1 Comment

ProfessionalAmateur Over a year ago

I cannot, I sanitized the actual table, link, & column info for security reasons. It did turn out to be a data match, where one table has trailing blanks and the other did not.

Collectives™ on Stack Overflow

Why do I get 'No data found' when passing a string variable into PL/SQL where clause

4 Answers 4

4 Comments

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related