I am using a simple cursor in a procedure that receives a couple of parameters. I then make a cursor on a select query with a where clause with multiple conditions, which are equal to the received parameters. This cursor should only return 1 row, instead it returns multiple rows. I found this out because I'm using a for loop to go through this cursor and insert something into another table based on the values of this cursor.
When I run the query on the database statically(as in without pl/sql) I get what I expect, but when I do it from a cursor which should return only one row, and run it in a for loop, the loop does multiple iterations. How is this possible?
Thank you!
EDIT:
ID kind kolo kolo1 mjt salesman money date done
1 001 001 002 00013 00056 100,00 21-feb-12 N
I run a cursor like this:
Cursor linija IS
SELECT *
FROM table_x X
where x.mjt = mjt
and x.salesman = salesman
and x.kind = kind
and x.kolo1 = kolo1
and x.done = 'N';
This should return only one row, but instead my cursor returns %rowcount is 10.
You have a name conflict. You have called your local variables the same as your column names, and the column names are taking precedence, as noted in the documentation:
If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence.
Caution:
When a variable or parameter name is interpreted as a column name, data can be deleted, changed, or inserted unintentionally.
The first four checks are always going to be true (unless you have null values), so you'll get every row that has done = 'N'.
Change your local variable names to something else; it's fairly common to use a prefix to distinguish between local variables, parameters, and columns, something like:
Cursor linija IS
SELECT *
FROM table_x X
where x.mjt = l_mjt
and x.salesman = l_salesman
and x.kind = l_kind
and x.kolo1 = l_kolo1
and x.done = 'N';
If this is in a stored procedure, rather than an anonymous block, you could use the procedure/function name as a prefix, which some people prefer. If your procedure was called myproc, for example, you could do:
Cursor linija IS
SELECT *
FROM table_x X
where x.mjt = myproc.mjt
and x.salesman = myproc.salesman
and x.kind = myproc.kind
and x.kolo1 = myproc.kolo1
and x.done = 'N';
You could also give your cursor its own formal arguments, and reference those in the same way, with prefixed argument names:
Cursor linija (
p_mjt table_x.mjt%type, p_salesman table_x.salesman%type,
p_kind table_x.kind%type, p_kolo1 table_x.kolo1%type
) IS
SELECT *
FROM table_x X
where x.mjt = p_mjt
and x.salesman = p_salesman
and x.kind = p_kind
and x.kolo1 = p_kolo1
and x.done = 'N';
or prefixing with the cursor name:
Cursor linija (
mjt table_x.mjt%type, salesman table_x.salesman%type,
kind table_x.kind%type, kolo1 table_x.kolo1%type
) IS
SELECT *
FROM table_x X
where x.mjt = linija.mjt
and x.salesman = linija.salesman
and x.kind = linija.kind
and x.kolo1 = linija.kolo1
and x.done = 'N';
and either way then include arguments when you open the cursor, e.g. if you changed the variable names at procedure level anyway:
for x in linija (l_mjt, l_salesman, l_kind, l_kolo1) loop
or if you didn't:
for x in linija (myproc.mjt, myproc.salesman, myproc.kind, myproc.kolo1) loop