Styjun

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.

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