CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlcursor
Beata Kozieł
Beata Kozieł

Reputation: 1

How to display cursor in procedure?

I have to display name and surname of everyone from team that have the same manager(his name and surname taken from parameter).

I tried 'IN' before VARCHAR2. I did use set serveroutput on. Furthermore select from cursor separately is working.

CREATE OR REPLACE PROCEDURE ex_2h(nazw VARCHAR2, imi VARCHAR2)
IS
CURSOR team IS SELECT * FROM people p WHERE p.id_manager= 
(SELECT id_ppl FROM people WHERE name= imi AND surname=nazw);
tmp people%ROWTYPE;
BEGIN
OPEN team;
LOOP
    FETCH team INTO tmp;
    EXIT WHEN team%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(NVL(tmp.name, '?'));
END LOOP;
CLOSE team;
END;

I don't know why without parameters it is working but if I add these the cursor is empty and it does not show anything.

Upvotes: 0

Views: 103

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

Who knows? The procedure itself looks OK (apart from the fact that you opened the TEAM cursor and fetched from ZESPOL (which was never declared).

  • may be the letter case (are those names written in lower/upper/mixed case)?
  • are you confused by parameters' order? Surname first, the first name next? Should it be vice versa (i.e. imi VARCHAR2, nazw VARCHAR2)
  • always use aliases, especially when you use the same table twice (or more times)

Here's a test case which shows that it works if you use it correctly. Note that I've switched to cursor FOR loop which is easier to maintain.

SQL> create table people
  2    (id_ppl     number,
  3     name       varchar2(10),
  4     surname    varchar2(10),
  5     id_manager number);

Table created.

SQL> insert all
  2    into people values (1, 'Little', 'Foot', 1)   --> the same ...
  3    into people values (2, 'Big'   , 'Foot', 1)   --> ... manager
  4    into people values (3, 'Stack' , 'Over', 2)
  5  select * from dual;

3 rows created.

SQL> set serveroutput on;

The procedure:

SQL> create or replace procedure ex_2h (p_nazw in varchar2, p_imi in varchar2)
  2  is
  3  begin
  4    for tmp in (select p.name from people p
  5                where p.id_manager = (select p1.id_ppl
  6                                      from people p1
  7                                      where p1.name = p_imi
  8                                        and p1.surname = p_nazw
  9                                     )
 10               )
 11    loop
 12      dbms_output.put_line(nvl(tmp.name, '?'));
 13    end loop;
 14  end;
 15  /

Procedure created.

Testing:

SQL> exec ex_2h('Foot', 'Little');
Little
Big

PL/SQL procedure successfully completed.

SQL>

I suggest you to try it that way. If it doesn't work, please, post some more info - CREATE TABLE and sample data so that we could see what you really have and how Oracle responded (and why).

Upvotes: 1

Related Questions