Home » SQL & PL/SQL » SQL & PL/SQL » CURSORS
CURSORS [message #235764] Mon, 07 May 2007 23:45 Go to next message
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 #235766 is a reply to message #235764] Mon, 07 May 2007 23:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Please help
Yes.
Re: CURSORS [message #235767 is a reply to message #235764] Mon, 07 May 2007 23:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Both are wrong.
What do you want to do?

Btw, you must put your code BETWEEN the tags not after them. You have a button "Preview Message" you can use before clicking on "Create Topic" or "Submit Reply".

Regards
Michel


Re: CURSORS [message #235978 is a reply to message #235767] Tue, 08 May 2007 10:13 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #235994 is a reply to message #235992] Tue, 08 May 2007 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why did you write?
Quote:
cursor cursor_item,orders is select ...

Regards
Michel
Re: CURSORS [message #235998 is a reply to message #235994] Tue, 08 May 2007 11:51 Go to previous messageGo to next message
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 #235999 is a reply to message #235764] Tue, 08 May 2007 11:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
sasha400,
excuse me for butting into this exchange, by why are you using PL/SQL; as opposed to plain SQL?
Re: CURSORS [message #236003 is a reply to message #235764] Tue, 08 May 2007 12:03 Go to previous messageGo to next message
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 #236005 is a reply to message #235998] Tue, 08 May 2007 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
One question:
Did you read what is a cursor and what is the cursor syntax in "PL/SQL User's Guide and Reference"?
Or did you just try what may work?

Regards
Michel
Re: CURSORS [message #236006 is a reply to message #235999] Tue, 08 May 2007 12:05 Go to previous messageGo to next message
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.
Re: CURSORS [message #236008 is a reply to message #236006] Tue, 08 May 2007 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: CURSORS [message #236059 is a reply to message #236008] Tue, 08 May 2007 22:30 Go to previous message
sasha400
Messages: 17
Registered: May 2007
Junior Member
solved
Previous Topic: SQL * plus problem
Next Topic: rounding/ codums/ etc ahh!
Goto Forum:
  


Current Time: Tue Dec 03 05:31:18 CST 2024