Re: cursor problem

read the error messages carefully -- you'll discover the problem on line 15; the error should be self-explanatory

• mcs

"joy" <joyfoely_at_yahoo.com> wrote in message news: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 Tue Nov 25 2003 - 13:50:18 CET