Home » SQL & PL/SQL » SQL & PL/SQL » cursor
cursor [message #187142] Fri, 11 August 2006 02:04 Go to next message
apoorvasharma80
Messages: 7
Registered: August 2006
Junior Member
Hi,

I am new to oracle. i was trying to write a cursor and it is giving me error. Please help. Here is the code and error........

SQL> declare
2 accnbr acct_bal_limit.ACCT_NBR%TYPE;
3 cursor test IS
4 select acct_nbr from acct_bal_limit;
5 open test;
6 LOOP
7 FETCH test INTO accnbr;
8 EXIT WHEN test%NOT_FOUND;
9 END LOOP;
10
11 /
FETCH test INTO accnbr;
*
ERROR at line 7:
ORA-06550: line 7, column 1:
PLS-00103: Encountered the symbol "FETCH" when expecting one of the following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char time timestamp interval date binary national character
nchar


regards
Apoorva kumar


Re: cursor [message #187144 is a reply to message #187142] Fri, 11 August 2006 02:09 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You were missing:
- BEGIN
- END

NOT_FOUND is incorrect, that should have been NOTFOUND.

DECLARE
  accnbr   acct_bal_limit.acct_nbr%TYPE;

  CURSOR TEST
  IS
    SELECT acct_nbr
    FROM   acct_bal_limit;
BEGIN
  OPEN TEST;

  LOOP
    FETCH TEST
    INTO  accnbr;

    EXIT WHEN TEST%NOTFOUND;
  END LOOP;
END;


MHE
Re: cursor [message #187156 is a reply to message #187144] Fri, 11 August 2006 02:39 Go to previous messageGo to next message
apoorvasharma80
Messages: 7
Registered: August 2006
Junior Member
Hi,

Thanks. it compiled successfully, but ididn't receive any output. I know there is some option in pl/sql to open screen output but I don't remember. can you please guide.

regards
Apoorva kumar
Re: cursor [message #187159 is a reply to message #187156] Fri, 11 August 2006 02:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're running this from SQL*Plus, use
SET SERVEROUTPUT ON SIZE 10000
Re: cursor [message #187162 is a reply to message #187159] Fri, 11 August 2006 02:57 Go to previous messageGo to next message
apoorvasharma80
Messages: 7
Registered: August 2006
Junior Member
Yes it's the same but even then it's not giving me any output!
Re: cursor [message #187163 is a reply to message #187162] Fri, 11 August 2006 03:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, you have added some calls to DBMS_OUTPUT.PUT_LINE into your code to output the data, haven't you.
Re: cursor [message #187182 is a reply to message #187163] Fri, 11 August 2006 04:16 Go to previous messageGo to next message
apoorvasharma80
Messages: 7
Registered: August 2006
Junior Member
No, and I am not aware of the syntax.
Re: cursor [message #187184 is a reply to message #187182] Fri, 11 August 2006 04:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
One of the nice things about Oracle is the fine and extensive documentation.
Re: cursor [message #187188 is a reply to message #187163] Fri, 11 August 2006 04:34 Go to previous messageGo to next message
apoorvasharma80
Messages: 7
Registered: August 2006
Junior Member
I tried using this .........

DBMS_OUTPUT.PUT_LINE(accnbr in varchar2);


but this is giving me the following error.......

DBMS_OUTPUT.PUT_LINE(accnbr in varchar2);
*
ERROR at line 9:
ORA-06550: line 9, column 32:
PLS-00103: Encountered the symbol "VARCHAR2" when expecting one of the
following:
(

Kindly guide.

regards
Apoorva Kumar
Re: cursor [message #187192 is a reply to message #187188] Fri, 11 August 2006 04:40 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Kindly read the documentation provided by JRowbottom.
Re: cursor [message #187197 is a reply to message #187192] Fri, 11 August 2006 04:54 Go to previous messageGo to next message
apoorvasharma80
Messages: 7
Registered: August 2006
Junior Member
Hi,

Thanks for the info. but i am still no where, my code is giving me another error...here is the code and the error........

SQL> declare
2 accnbr acct_bal_limit.ACCT_NBR%TYPE;
3 cursor test IS
4 select acct_nbr from acct_bal_limit;
5 BEGIN
6 LOOP
7 FETCH test INTO accnbr;
8 EXIT WHEN test%NOTFOUND;
9 DBMS_OUTPUT.PUT_LINE(accnbr);
10 END LOOP;
11 END;
12 /
declare
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 7


regards
Apoorva Kumar
Re: cursor [message #187200 is a reply to message #187197] Fri, 11 August 2006 05:01 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, so here's an example; but you should really, REALLY read the documentation. You won't get much further without educating yourself.
SQL> begin
  2    for cur_r in (select ename, job from emp)
  3    loop
  4      dbms_output.put_line(cur_r.ename ||' - '|| cur_r.job);
  5    end loop;
  6  end;
  7  /
SMITH - CLERK
ALLEN - SALESMAN
BURGLAR - SALESMAN
JONES - MANAGER
TIGER - SALESMAN
BLAKE - MANAGER
CLARK - MANAGER
SCOTT - ANALYST
KING - PRESIDENT
TURNER - SALESMAN
ADAMS - CLERK
JAMES - CLERK
FORD - ANALYST
MILLER - CLERK

PL/SQL procedure successfully completed.

SQL>


[EDIT]
As you can see, it is easier to use cursor FOR loop than declaring cursor and variable, open and close cursor, fetch data and take care about exiting the loop. The code below does the same as the one provided above, but - compare them and see which one is better (relatively speaking).
SQL> declare
  2    cursor c1 is select ename, job from emp;
  3    c1r c1%rowtype;
  4  begin
  5    open c1;
  6    loop
  7      fetch c1 into c1r;
  8      exit when c1%notfound;
  9      dbms_output.put_line(c1r.ename ||' - '|| c1r.job);
 10    end loop;
 11    close c1;
 12  end;
 13  /

[Updated on: Fri, 11 August 2006 05:05]

Report message to a moderator

Re: cursor [message #187203 is a reply to message #187200] Fri, 11 August 2006 05:16 Go to previous message
apoorvasharma80
Messages: 7
Registered: August 2006
Junior Member
Thank you so much for you help. But I think the right way is what you have suggested earlier, I have to sit with books for quite some time, then it would be better for me.

with warm regards
Apoorva kumar
Previous Topic: Not null constraint inside CTAS stmt
Next Topic: UTL_FILE_DIR problem
Goto Forum:
  


Current Time: Sat Dec 03 22:02:09 CST 2016

Total time taken to generate the page: 0.09226 seconds