PL/SQL Procedure Error by dbms output function

Question

I have the following task: "Write a Print Siblings procedure (p person) that has as input the name or ID of the person from the family tree scheme and, as a result, outputs the list of siblings."

Table Relations which contains the information of the successor(Nachfolger) and predecessor(Vorgänger) of a person. Table Relations

My procedure is the following:

create or replace procedure PrintSiblings4(p_Person varchar2)
is
  cursor c_geschwister is select nachfolger
  from relations
  where nachfolger != p_Person and vorgänger = 
    (select vorgänger from relations
    Where nachfolger = p_Person and rownum = 1) ;
  v_geschwister  c_geschwister%rowtype;
begin
  open c_geschwister;
  loop
    fetch c_geschwister into v_geschwister;
    exit when c_geschwister%NOTFOUND;
    dbms_output.put_line('geschwister' || v_geschwister);
  end loop;
end;

I get the following error messages by the sqldeveloper if I compile the procedure:

Error (14,22): PLS-00306: Incorrect number or types of arguments in call to '||'

I can´t unterstand why it´s not working. I used an explicit cursor to handle the problem that i get more than 1 row, but its not working.

Additional recommendation: Always close the cursor. – Alfabravo Jan 09 '19 at 21:53 — Alfabravo, Jan 09 '19 at 21:53

hbourchi · Accepted Answer · 2019-01-09T22:32:25.133

2

change:

dbms_output.put_line('geschwister' || v_geschwister);

to:

dbms_output.put_line('geschwister' || v_geschwister.nachfolger);

Here a new (cleaner/compacter) version of your procedure. We get here all columns of table relations:

create or replace procedure print_siblings4(p_person varchar2)
is
  cursor c_geschwister is 
  select *
  from relations
  where nachfolger != p_person 
  and vorgänger = 
  (
  select vorgänger 
  from relations
  where nachfolger = p_person 
  and rownum = 1
  ) 
  ;

begin
  for r_geschwister in c_geschwister loop
    dbms_output.put_line('geschwister' || v_geschwister.nachfolger);
  end loop;
end;

edited Jan 09 '19 at 22:32

answered Jan 09 '19 at 21:52

hbourchi

309
2
8

At first thank you very much. Could you please explain why the column name is needed? I thought the cursor receives the rows from the table, only from the column "nachfolger". After your solution I would think that the cursor receives the whole row including all columns and with the information of ".nachfolger" he only takes values which are inside the "nachfolger" column. Is that right? – Maiwand Jan 09 '19 at 22:14
Because your variable v_geschwister is a rowtye variable. If you define this as relations.nachfolger%type, than your first version of codes should work. – hbourchi Jan 09 '19 at 22:16
To answer your second querstion: the cursor gets whatever columns you list in SELECT clause. So in this case just nachfolger. But since you have defined your variable as rowtype you should add column name whenever you need any column value. as an example I will put a new version of you procedure in a new answer. – hbourchi Jan 09 '19 at 22:27

PL/SQL Procedure Error by dbms output function

1 Answers1