473,324 Members | 2,257 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

cursor problem

joy
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!
Jul 19 '05 #1
5 12716
read the error messages carefully -- you'll discover the problem on line 15;
the error should be self-explanatory

-- mcs

"joy" <jo******@yahoo.com> wrote in message
news:35*************************@posting.google.co m...
| 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!
Jul 19 '05 #2
mcstock wrote:
read the error messages carefully -- you'll discover the problem on line 15;
the error should be self-explanatory

-- mcs

"joy" <jo******@yahoo.com> wrote in message
news:35*************************@posting.google.co m...
| 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!


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

Jul 19 '05 #3
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.branch_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.branch_id
and book_copies.book_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.)
Jul 19 '05 #4
"Frank" <fb*****@nescape.net> wrote...

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
Jul 19 '05 #5
Guido Konsolke wrote:
"Frank" <fb*****@nescape.net> wrote...
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

Jul 19 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: DarthMacgyver | last post by:
Hello, I recently wrote a survey application. Each question is very similar. The first questions gives me a problem when there are multiple people taking the survey (The Database connection...
4
by: Oliver Stratmann | last post by:
Hello All! We have a cursor in an application which fetches some data out of a DB2-DB. Normally it all works kind of well. We inserted a routine at certain places in order to do some...
0
by: Prashanth | last post by:
I am getting this error from BEA DB2 Driver in weblogic console. java.sql.SQLException: CURSOR C02 NOT IN A PREPARED STATE java.sql.SQLException: CURSOR C02 NOT IN A PREPARED STATE We keep...
1
by: ilPostino | last post by:
I'm using this function to load a custom cursor from a .cur file; public static extern IntPtr LoadCursorFromFile(String str); It works great but if I popup a ContextMenu or Control like a...
5
by: Vlad Simionescu | last post by:
Hello I'm trying to let my Windows Form application perform a lengthy operation while displaying a progress bar in a modal dialog window; the dialog has a cancel button. The dialog is displayed...
2
by: Craig | last post by:
I'm trying to do this within a control I've created: Cursor.Current = Cursors.WaitCursor; During the initialization of the parent form, my control gets added to the form, and while the control...
10
by: Just Me | last post by:
Does Me.Cursor.Current=Cursors.WaitCursor set the current property of Me.Cursor to Cursors.WaitCursor And Me.Cursor.Current=Cursors.Default set the Me.Current property to something (default)...
12
by: Lucky | last post by:
Hi guys! i want to create one cursor in the t-sql. the problem is i want to use stored procedure instead of select command in cursor. can anyone tell me how can i use stored procedure's o/p to...
6
by: Tomek | last post by:
In my page I reload JavaScript. When it is reloading the cursor change to "wait" (hourglass). After javascript loaded it does not change back to the "hand" if cursor stay on hyperlinks. How can I...
4
by: mike | last post by:
I have the opportunity to rescue a project that uses a mouse to sense the relative position of a machine. The hardware is built...just needs to be programmed. Stop snickering!!! I didn't do it...I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.