CURSORS [message #235764] |
Mon, 07 May 2007 23:45 |
sasha400
Messages: 17 Registered: May 2007
|
Junior Member |
|
|
declare
cursor itemCursor
cursor ordersCursor
is select o_id,item_id,item_desc
from item,orders;
or
declare
cursor item,ordersCursor
is select o_id,item_id,item_desc
from item,orders;
Please help
|
|
|
|
|
Re: CURSORS [message #235978 is a reply to message #235767] |
Tue, 08 May 2007 10:13 |
sasha400
Messages: 17 Registered: May 2007
|
Junior Member |
|
|
This works but i need to select a column from another table to get the output i want
declare
cursor itemCursor
is select item_id,item_desc
from item;
item_id Number(8);
item_desc varchar2(30);
begin
open itemCursor;
fetch itemCursor into item_id,item_desc ;
dbms_output.put_line('Items that were ordered.');
dbms_output.put_line('--------------------------------');
while itemCursor%FOUND loop
dbms_output.put_line(item_id||' '||item_desc);
fetch itemCursor into item_id,item_desc;
end loop;
close itemCursor;
end;
/
|
|
|
Re: CURSORS [message #235982 is a reply to message #235978] |
Tue, 08 May 2007 10:33 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
So select the column from the other table
Basicly it's
cursor cursor_name is
select tab1.colX, tab1.colX, tab2.colX
from tab1, tab2
where tab2.colX = tab1.colX
or
cursor cursor_name is
select tab1.colX, tab1.colX, tab2.colX
from tab1
join tab2 on tab2.colx = tab1.colx
|
|
|
Re: CURSORS [message #235992 is a reply to message #235982] |
Tue, 08 May 2007 11:33 |
sasha400
Messages: 17 Registered: May 2007
|
Junior Member |
|
|
I tried both didn't work what am I doing wong?
declare
cursor cursor_item,orders
is select item.item_id,item.item_desc,orders.o_id
from item
join orders on orders.o_id = item.item_id;
o_id Number(8);
item_id Number(8);
item_desc varchar2(30);
begin
open cursor cursor_item,orders;
fetch cursor cursor_item,orders into o_id,item_id,item_desc;
dbms_output.put_line('Items that were ordered.');
dbms_output.put_line('--------------------------------');
while cursor cursor_item,orders%FOUND loop
dbms_output.put_line(item_id||' '||item_desc);
fetch cursor cursor_item,orders into o_id,item_id,item_desc;
end loop;
close cursor cursor_item,orders;
end;
/
ORA-06550: line 2, column 19:
PLS-00103: Encountered the symbol "," when expecting one of the following:
( ; is return
The symbol "return was inserted before "," to continue.
ORA-06550: line 5, column 8:
PLS-00103: Encountered the symbol "ORDERS" when expecting one of the following:
, ; for group having intersect minus order start union where
connect1. declare
2. cursor cursor_item,orders
3. is select item.item_id,item.item_desc,orders.o_id
4. from item
|
|
|
|
Re: CURSORS [message #235998 is a reply to message #235994] |
Tue, 08 May 2007 11:51 |
sasha400
Messages: 17 Registered: May 2007
|
Junior Member |
|
|
the 2 tables are orders and item
I tried it this way but i didn't work
cursor cursor_item
cursor cursor_orders
|
|
|
|
Re: CURSORS [message #236003 is a reply to message #235764] |
Tue, 08 May 2007 12:03 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
See the syntax of cursor declaration. WHY do you use comma (',') in cursor_name. It is error.
ThomasG already pointed it out in his post and wrote example of CORRECT cursor declaration.
So, for the second (and hope the last) time: do not use comma in cursor_name.
|
|
|
|
Re: CURSORS [message #236006 is a reply to message #235999] |
Tue, 08 May 2007 12:05 |
sasha400
Messages: 17 Registered: May 2007
|
Junior Member |
|
|
we are suppose to use PL/SQL (in oracle )I am new to this few weeks pardon my ignorance.The 2 books I am using give only the basic basic stuff.
|
|
|
|
|