cursor problem

From: joy <joyfoely_at_yahoo.com>
Date: 24 Nov 2003 08:22:08 -0800
Message-ID: <357eb048.0311240822.2f3d74d_at_posting.google.com>

I have tables:
books(book_id, title, publisher_name), branches(branch_id, branch_name, address), book_copies(book_id, branch_id, no_of_copies). I want to print out branch information, so I did:

SQL> create or replace procedure printbranchinfo(branchnum in integer) is

  2       cbranchid   branches.branch_id%type;
  3       cbranchname branches.branch_name%type;
  4       caddress    branches.address%type;
  5       ctitle      books.title%type;
  6       cbookid     books.book_id%type;
  7       cnumcopy    book_copies.no_of_copies%type;

  8
  9 cursor cur_branchinfo(branchnum in branches.branch_id%type) is

 10      select branch_id, branch_name, address, book_id,title, 
                no_of_copies
 11  --  into cbranchid, cbranchname, caddress, cbookid, ctitle, 
                cnumcopy
 12      from branches, books, book_copies
 13      where branches.branch_id = book_copies.branch_id 
 14            and book_copies.book_id = books.book_id
 15            and branch_id = branchnum;

 16 branch_rec cur_branchinfo%rowtype;  17
 18 begin
 19 if not cur_branchinfo%isopen then  20 open cur_branchinfo;
 21 end if;
 22
 23 fetch cur_branchinfo into branch_rec;  24 while branch_rec loop

 25      dbms_output.put.line('BRANCHID IS: '||branch_rec.branch_id);
 26      dbms_output.put.line('BRANCH NAME IS: '||
                               branch_rec.branch_name);
 27      dbms_output.put.line('BRANCH ADDRESS IS: '||
                               branch_rec.address);
 28      dbms_output.put.line('BRANCH BOOK INFO: ');
 29      dbms_output.put.line('BOOKID: '||branch_rec.book_id ||
                              'BOOK TITLE: '||branch_rec.title|| 
 30                       'no_of_copies '||branch_rec.no_of_copies);

 31 end loop;
 32 end;
 33 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE PRINTBRANCHINFO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/10 PLS-00341: declaration of cursor 'CUR_BRANCHINFO' is incomplete

         or malformed

10/5     PL/SQL: SQL Statement ignored
15/15    PL/SQL: ORA-00918: column ambiguously defined
16/14    PL/SQL: Item ignored
20/5     PLS-00306: wrong number or types of arguments in call to
         'CUR_BRANCHINFO'

20/5     PL/SQL: SQL Statement ignored
23/3     PL/SQL: SQL Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
23/29 PLS-00320: the declaration of the type of this expression is

         incomplete or malformed

24/3     PL/SQL: Statement ignored
24/9     PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

I do not know what is wrong about the cursor, please help and thanks! Received on Mon Nov 24 2003 - 17:22:08 CET