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..

CodePudding user response：

With some sample data like below:
A_TBL

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...