1* select * from book
SQL> /

       DDC AUTHOR
---------- --------------------
         1


SQL> select * from author;

       DDC NAME
---------- --------------------
         1 rita

SQL> ed
Wrote file afiedt.buf

  1* update book set book.author=author.name where book.ddc=author.ddc
SQL> /
update book set book.author=author.name where book.ddc=author.ddc
                                                       *
ERROR at line 1:
ORA-00904: "AUTHOR"."DDC": invalid identifier


SQL> ed
Wrote file afiedt.buf

  1* update book set author=(select name from author where book.ddc=author.ddc)
SQL> /

1 row updated.

SQL> select * from  book;

       DDC AUTHOR
---------- --------------------
         1 rita

SQL> select * from book;

       DDC AUTHOR
---------- --------------------
         1
         2
         3
         4
         2
         4

6 rows selected.

SQL> select * from author;

       DDC NAME
---------- --------------------
         1 ghf
         2 hjdfs
         3 tygy
         4 mkhy

SQL> update book set author=(select name from author where book.ddc=author.ddc);

6 rows updated.

SQL> select * from book;

       DDC AUTHOR
---------- --------------------
         1 ghf
         2 hjdfs
         3 tygy
         4 mkhy
         2 hjdfs
         4 mkhy

6 rows selected.

insert into author (ddc, name) values (1, 'Oops');

CREATE OR REPLACE FUNCTION all_authors(p_ddc NUMBER) RETURN varchar2
AS
  v_authors VARCHAR2(4000) := NULL;
BEGIN
  FOR c1 IN (SELECT * FROM author WHERE ddc = p_ddc) LOOP
    IF v_authors IS NULL THEN
       v_authors := c1.auth_name;
    ELSE
      v_authors := v_authors ||', '|| c1.auth_name;
    END IF;
  END LOOP;

  RETURN v_authors;
END;
/