plsql parameterised cursor in which the parameter is user input

Question

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

The snippets look valid individually. Can you add a complete test case showing how you call it and what tools you are using? — William Robertson
– William Robertson, Commented Nov 5, 2022 at 15:15
If everything is OK but there's no result (BTW how do you know that nothing is fetched?), then WHERE clause prevents anything to be returned (i.e. there's no country with that name. Note that you've produced Cartesian product between EMPLOYEE and COUNTRY (there's no join between them), but then you're filtering rows on country name so result - if any - will still be wrong. — Littlefoot
– Littlefoot, Commented Nov 5, 2022 at 18:21

d r · Accepted Answer · 2022-11-06 09:41:14Z

1

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

answered Nov 6, 2022 at 9:41

d r

8,6722 gold badges9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

were Over a year ago

Thank u!!! all thanks to u i finished this on time:):) million thanks

d r Over a year ago

@were - You are wellcome. Glad you got it done.

Collectives™ on Stack Overflow

plsql parameterised cursor in which the parameter is user input

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related