Skip to content
Advertisement

plsql parameterised cursor in which the parameter is user input

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…

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement