Home » SQL & PL/SQL » SQL & PL/SQL » cursor
cursor [message #262760] Tue, 28 August 2007 04:24 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
unable to complie where iam going wrong

CREATE OR REPLACE PROCEDURE ep
is
begin
sn    s.sn%TYPE;
CURSOR pg IS
SELECT sn FROM s;
BEGIN
OPEN pg;
if pg%isopen then
LOOP
FETCH pg  INTO sn,;
exit when pg%NOTFOUND
dbms_ouput.put_line('name: ' ||sn);
END LOOP;
CLOSE pg;
ELSE
dbms_ouput.put_line('name: ' ||sn);
end if;
end;
/
show error
/
Re: cursor [message #262762 is a reply to message #262760] Tue, 28 August 2007 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't either but I can post:
SQL> CREATE OR REPLACE PROCEDURE ep
  2  is
  3  begin
  4  sn    s.sn%TYPE;
  5  CURSOR pg IS
  6  SELECT sn FROM s;
  7  BEGIN
  8  OPEN pg;
  9  if pg%isopen then
 10  LOOP
 11  FETCH pg  INTO sn,;
 12  exit when pg%NOTFOUND
 13  dbms_ouput.put_line('name: ' ||sn);
 14  END LOOP;
 15  CLOSE pg;
 16  ELSE
 17  dbms_ouput.put_line('name: ' ||sn);
 18  end if;
 19  end;
 20  /

Warning: Procedure created with compilation errors.

SQL> show error
...

And you?

Regards
Michel
Re: cursor [message #262763 is a reply to message #262760] Tue, 28 August 2007 04:26 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
INTO sn,


extra , is here... What error comes?
Re: cursor [message #262769 is a reply to message #262763] Tue, 28 August 2007 04:43 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
i have taken sn,(extra ,)
and added declare in place of begin

3/4PLS-00103: Encountered the symbol "DECLARE
13/4PLS-00103: Encountered the symbol "DBMS_OUPUT"
Re: cursor [message #262772 is a reply to message #262769] Tue, 28 August 2007 04:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
oracle_coorgi wrote on Tue, 28 August 2007 11:43
i have taken sn,(extra ,)
and added declare in place of begin


why have you done that?

OPEN pg;
if pg%isopen then


You should trust Oracle to either open the cursor succesfully, or raise an exception.
This if-clause will never return false.

[Updated on: Tue, 28 August 2007 04:51]

Report message to a moderator

Re: cursor [message #262774 is a reply to message #262769] Tue, 28 August 2007 04:53 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
There are several things wrong.
1. If you change the code, show us the changes don't just tell us what you did (we are very un-trusting and might not believe you Smile )
2. Why would you replace BEGIN with DECLARE. BEGIN is mandatory (i.e. not optional)
3. Look up the syntax for declaring variables. Pay special attention as to WHERE they should be declared.
4. Do not declare variables with the same name as other objects (columns, tables etc)
5. Why OPEN a cursor and THEN test to see if it is open on the next line. This is just bizarre.
Re: cursor [message #262784 is a reply to message #262774] Tue, 28 August 2007 05:03 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
CREATE OR REPLACE PROCEDURE ep
is
DECLARE
empno emp.empno%TYPE;
ename emp.ename%TYPE;
CURSOR pg IS
SELECT empno,ename FROM emp;
BEGIN
OPEN pg;
if pg%isopen then
LOOP
FETCH pg INTO empno,ename;
exit when pg%NOTFOUND
dbms_ouput.put_line('name: ' || empno ||' '||ename);
END LOOP;
CLOSE pg;
ELSE
dbms_ouput.put_line('name: ' || empno ||' '||ename);
end if;
end;
show error
/
Re: cursor [message #262786 is a reply to message #262784] Tue, 28 August 2007 05:06 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
get rid of declare and change the variable names to
l_ename and l_empno
(The variable names bit is not essential for this particular piece of code, but if you continue to name variables after columns, it will come back to bite you in the butt Smile )

Also, in future, please surround your code with [code] [/code] tags

Edit, and you might want to look into cursor for loops and the correct implementation of Cursor%ISOPEN

[Updated on: Tue, 28 August 2007 05:09]

Report message to a moderator

Re: cursor [message #262790 is a reply to message #262786] Tue, 28 August 2007 05:12 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
CREATE OR REPLACE PROCEDURE ep
is
DECLARE
c_empno emp.empno%TYPE;
c_ename emp.ename%TYPE;
CURSOR pg IS
SELECT empno,ename FROM emp;
BEGIN
OPEN pg;
if pg%isopen then
LOOP
FETCH pg INTO c_empno,c_ename;
exit when pg%NOTFOUND
dbms_ouput.put_line('name: ' || c_empno ||' '||c_ename);
END LOOP;
CLOSE pg;
ELSE
dbms_ouput.put_line('name: ' || empno ||' '||ename);
end if;
end;
Re: cursor [message #262791 is a reply to message #262784] Tue, 28 August 2007 05:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Again, why did you add the declare?
It didn't complain when you did not have it there, so why add it?
Why use c_ instead of l_ as pablolee suggested?
l_ is commonly used as a prefix for local variables whereas c_ is often used for cursors

AND GET RID OF THE SILLY CHECK if the cursor is opened.
The else will NEVER EVER be reached.

[Updated on: Tue, 28 August 2007 05:16]

Report message to a moderator

Re: cursor [message #262794 is a reply to message #262790] Tue, 28 August 2007 05:16 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Also, why take and act on only 1 piece of advice (the bit that isn't completely necessary at that!)

Quote:
[snip]...The else will NEVER EVER be reached.
and even if it did (which it wouldn't) it would fail anyway.

[Updated on: Tue, 28 August 2007 05:18]

Report message to a moderator

Re: cursor [message #262799 is a reply to message #262760] Tue, 28 August 2007 05:22 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
why take and act on only 1 piece of advice (the bit that isn't completely necessary at that!)


Because it was the simplest to do.
Re: cursor [message #262802 is a reply to message #262799] Tue, 28 August 2007 05:25 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Because it was the simplest to do.
Of course Laughing
Re: cursor [message #262810 is a reply to message #262760] Tue, 28 August 2007 05:36 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Set Serveroutput On

CREATE OR REPLACE PROCEDURE ep
is
v_sn s.sn%TYPE;
CURSOR pg IS
SELECT sn FROM s;
BEGIN
OPEN pg;
IF pg%isopen then
LOOP
FETCH pg INTO v_sn;
Exit when pg%NOTFOUND;
dbms_output.put_line('name: '||v_sn);
END LOOP;
CLOSE pg;
Else
dbms_output.put_line('cursor is not open');
End if;
End;
/

Exec Ep

SQL> exec ep
name: SMITH
name: ALLEN
name: WARD
name: JONES
name: MARTIN
name: BLAKE
name: CLARK
name: SCOTT
name: KING
name: TURNER
name: ADAMS
name: JAMES
name: FORD
name: MILLER

PL/SQL procedure successfully completed.

Thanks for Giveing chance
Regards,
Thani.....
Re: cursor [message #262814 is a reply to message #262810] Tue, 28 August 2007 05:39 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Thani, is this meant to be your suggested solution to the OP? If so, you might want to look at the other posts and see if you can pick out the various mistakes that you have propagated in your code. Also, when posting code, please put in [code][/code] tags. Also try not to jump in with full solutions, we are trying to get the op to do the work himself so that he will learn rather than simply being spoon fed.
Re: cursor [message #262838 is a reply to message #262814] Tue, 28 August 2007 06:22 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
thanx for the solution it worked
if iam not troubling u guys i have a small calrification
this is done through the for loop also
...
FOR r IN ( SELECT ename FROM emp )
LOOP ....

what is the differnce between these two and which is better


Re: cursor [message #262842 is a reply to message #262760] Tue, 28 August 2007 06:27 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

For loop is better.

It gives much flexibility.
You need not to handle about cursor open or close. For loop automatically handle this one.
You need not to declare variable for cursor for loop.

[Updated on: Tue, 28 August 2007 06:32]

Report message to a moderator

Re: cursor [message #262845 is a reply to message #262814] Tue, 28 August 2007 06:31 Go to previous message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Thanks.......
Previous Topic: PL/SQL Tables
Next Topic: Refresh on MATERIALIZED VIEW
Goto Forum:
  


Current Time: Sat Dec 03 11:47:09 CST 2016

Total time taken to generate the page: 0.15345 seconds