CODEWITHSUNDEEP
oracle-databasecursor
Brian
Brian

Reputation: 1896

Oracle cursor returning all rows in table despite where clause

I am using the following query to return a single value: 

select er.elig_code
from idm_elig_rule er 
where ER.ROLE_CODE = role_code and ER.SERVICE_CODE = service_code;

When I substitute values for the variables and run it as a single SQL statement it will return just a single value like I want it too. However, when I place the statement in a cursor and loop through the results, it returns all rows in the table. I've also tried a "select into" statement but that still returns all 85 rows in the table. I have a unique index in the idm_elig_rule table that is a combination of the role_code, service_code and another column.

EDIT: Here's how I'm testing it out - using an anonymous block:

declare
    role_code_in IDM_ELIG_RULE.ROLE_CODE%type := 'CEMP';
    service_code_in IDM_ELIG_RULE.SERVICE_CODE%type := 'PORTL'; 
    cursor get_elig_code is 
        select ER.ELIG_CODE
        from idm_elig_rule er 
        where ER.ROLE_CODE = role_code_in and ER.SERVICE_CODE = service_code_in; 
begin 
    for r in get_elig_code 
    loop
        DBMS_OUTPUT.PUT_LINE(r.elig_code);
    end loop;  
end;

EDIT: I changed the names of the variables in the where clause and that resolved the issue.

Upvotes: 0

Views: 1853

Answers (3)

Ollie
Ollie

Reputation: 17538

From the limited information in your question i'd say that your parameters are not being instantiated correctly and your select is resolving to:

select er.elig_code
  from idm_elig_rule er
 where ER.ROLE_CODE = role_code 
   and ER.SERVICE_CODE = service_code;

Where the DB is seeing role_code and service_code as the columns and not variables so it will always return all rows from your table.

Change the names of your variables to something different from your column names to see if this is the case.

Upvotes: 3

Dave Costa
Dave Costa

Reputation: 48121

Since your variable names are the same as the column names, it is possible that the parser is seeing them as the column names, not as variable, and therefore you are getting all the rows where the columns are equal to themselves, which would only exclude rows with NULL values.

I'd suggest always using different names for your variables than for the columns, to avoid any confusion. A common technique is to use some prefix on the variable name, such as l_ for local variable, p_ for parameters, or g_ for globals.

In this case, it may be better to parameterize the cursor:

CURSOR code_cur (p_role_code INTEGER, p_service_code INTEGER) IS
select er.elig_code
from idm_elig_rule er 
where ER.ROLE_CODE = p_role_code and ER.SERVICE_CODE = p_service_code;

Then you would pass values when opening the cursor, e.g.:

FOR code_rec IN code_cur ( my_role_code_value, my_service_code_value ) LOOP

Upvotes: 3

tawman
tawman

Reputation: 2498

You have a Name Resolution issue with your variable names since the bind variable names are the same as the column names. Change your variable names to avoid the PL/SQL compiler from deciding for you.

Oracle PL/SQL Variable Name Resolution

Upvotes: 9

Related Questions