here’s my cursor statement
cursor empCur(cname varchar2) is select empID, name from employee, country where lower(country_name) = lower(cname);
and call like this
countryName := '&countryname'; open empCur(countryName); fetch empCur into ...
The variable countryName is declared along with the cursors, and this query runs fine but no data is fetched. no idea what I’m missing here..
Advertisement
Answer
With some sample data like below:
A_TBL
ID | COL_A | COL_B |
---|---|---|
IE | 01-NOV-22 | 1 |
UK | 02-NOV-22 | 2 |
FR | 03-NOV-22 | 3 |
IT | 04-NOV-22 | 4 |
this code works…
SET SERVEROUTPUT ON Declare CURSOR empCur(cname VarChar2) IS Select ID, COL_A, COL_B From A_TBL Where Lower(ID) = Lower(cname); cSet empCur%ROWTYPE; countryName VarChar2(20); Begin countryName := '&country'; OPEN empCur(countryName); FETCH empCur Into cSet; CLOSE empCur; DBMS_OUTPUT.PUT_LINE('Country ID = ' || cSet.ID || ' * Date = ' || To_Char(cSet.COL_A, 'dd.mm.yyyy') || ' * Value = ' || cSet.COL_B); End; -- -- returning for input 'IE' -- -- anonymous block completed -- Country ID = IE * Date = 01.11.2022 * Value = 1
Did you check your cursor’s FROM clause. There is no join condition between the tables employee and country. That or where condition (or both) coud be the reason that no rows were fetched…
Regards…