cursor problem

**mcstock** · Jun 27 '08, 05:34 PM

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@yahoo .comwrote in message
news:357eb048.0 311240822.2f3d7 4d@posting.goog le.com...
| I have tables:
| books(book_id, title, publisher_name) , branches(branch _id,
| branch_name, address), book_copies(boo k_id, branch_id, no_of_copies).
| I want to print out branch information, so I did:
|
| SQLcreate or replace procedure printbranchinfo (branchnum in integer)
| is
| 2 cbranchid branches.branch _id%type;
| 3 cbranchname branches.branch _name%type;
| 4 caddress branches.addres s%type;
| 5 ctitle books.title%typ e;
| 6 cbookid books.book_id%t ype;
| 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.bra nch_id
| 14 and book_copies.boo k_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.b ranch_id);
| 26 dbms_output.put .line('BRANCH NAME IS: '||
| branch_rec.bran ch_name);
| 27 dbms_output.put .line('BRANCH ADDRESS IS: '||
| branch_rec.addr ess);
| 28 dbms_output.put .line('BRANCH BOOK INFO: ');
| 29 dbms_output.put .line('BOOKID: '||branch_rec.b ook_id ||
| 'BOOK TITLE: '||branch_rec.t itle||
| 30 'no_of_copies '||branch_rec.n o_of_copies);
| 31 end loop;
| 32 end;
| 33 /
|
| Warning: Procedure created with compilation errors.
|
| SQLshow 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!

**Frank** · Jun 27 '08, 05:34 PM

Re: cursor problem

mcstock wrote:

read the error messages carefully -- you'll discover the problem on line 15;
the error should be self-explanatory
>
-- mcs
>
"joy" <joyfoely@yahoo .comwrote in message
news:357eb048.0 311240822.2f3d7 4d@posting.goog le.com...
| I have tables:
| books(book_id, title, publisher_name) , branches(branch _id,
| branch_name, address), book_copies(boo k_id, branch_id, no_of_copies).
| I want to print out branch information, so I did:
|
| SQLcreate or replace procedure printbranchinfo (branchnum in integer)
| is
| 2 cbranchid branches.branch _id%type;
| 3 cbranchname branches.branch _name%type;
| 4 caddress branches.addres s%type;
| 5 ctitle books.title%typ e;
| 6 cbookid books.book_id%t ype;
| 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.bra nch_id
| 14 and book_copies.boo k_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.b ranch_id);
| 26 dbms_output.put .line('BRANCH NAME IS: '||
| branch_rec.bran ch_name);
| 27 dbms_output.put .line('BRANCH ADDRESS IS: '||
| branch_rec.addr ess);
| 28 dbms_output.put .line('BRANCH BOOK INFO: ');
| 29 dbms_output.put .line('BOOKID: '||branch_rec.b ook_id ||
| 'BOOK TITLE: '||branch_rec.t itle||
| 30 'no_of_copies '||branch_rec.n o_of_copies);
| 31 end loop;
| 32 end;
| 33 /
|
| Warning: Procedure created with compilation errors.
|
| SQLshow 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!
>
>

I only see the missing comma on line 10 (or beginning of line 12)
--
Regards, Frank van Bortel

**Hans Forbrich** · Jun 27 '08, 05:34 PM

Re: cursor problem

Frank wrote:

>
mcstock wrote:
>

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

>
I only see the missing comma on line 10 (or beginning of line 12)
--
Regards, Frank van Bortel

There are a number of typos and other errors (eg: is it really
dbms_output.put .line). The one mcs refers to in particular:

from the code:
15 and branch_id = branchnum;

from the message:
15/15 PL/SQL: ORA-00918: column ambiguously defined

elsewhere in the code:
13 where branches.branch _id = book_copies.bra nch_id

Care to comment which branch_id line 15 refers to?

---------------------------
Almost seems like homework. And almost seems like someone really
doesn't understand it.

I'm curious though - why a procedure instead of:

select 'BRANCHID IS: '|| branch_id || chr(*) ||
'BRANCH NAME IS: '|| branch_name ||chr(*) ||
'BRANCH ADDRESS IS: '|| address ||chr(*) ||
'BRANCH BOOK INFO: ' ||chr(*) ||
'BOOKID: '|| book_id ||
' BOOK TITLE: '|| title||
'no_of_copies '|| no_of_copies
from branches, books, book_copies
where branches.branch _id = book_copies.bra nch_id
and book_copies.boo k_id = books.book_id
and branch_id = :branchnum;

(of course replacing the chr(*) with the appropriate cr/lf).

(If the assignment was to do this in a procedure, I'd probably be
looking for another school - the idea of teaching someone to even
consider a procedure instead of a straight select for this is
staggering.)

**Guido Konsolke** · Jun 27 '08, 05:34 PM

Re: cursor problem

"Frank" <fbortel@nescap e.netwrote...

>
I only see the missing comma on line 10 (or beginning of line 12)
--
Regards, Frank van Bortel
>

Hi Frank,

let me help you see it ;-)

| 15 and branch_id = branchnum;
| 15/15 PL/SQL: ORA-00918: column ambiguously defined

Got it?

Greetings,
Guido

**Frank** · Jun 27 '08, 05:34 PM

Re: cursor problem

Guido Konsolke wrote:

"Frank" <fbortel@nescap e.netwrote...
>

>>I only see the missing comma on line 10 (or beginning of line 12)
>>--
>>Regards, Frank van Bortel
>>

>
>
Hi Frank,
>
let me help you see it ;-)
>
>

>>>| 15 and branch_id = branchnum;
>>>| 15/15 PL/SQL: ORA-00918: column ambiguously defined

>
>
Got it?
>
Greetings,
Guido
>
>

OK - it wasn't the only error. Yours nor mine...

--
Regards, Frank van Bortel

cursor problem

cursor problem

Comment

Comment

Comment

Comment

Comment