Solved

Cursor in Oracle Procedure

Posted on 2009-05-05
5
2,935 Views
Last Modified: 2013-12-07
I'm trying to create a procedure that retrieves from a table, the register with the highest value (without using Max() function :) )for certain field, but i keep getting an error:

CREATE OR REPLACE PROCEDURE OBTENERDNIMASALTO AS
cursor c1 is
SELECT * from clientes
curregister CLIENTES%rowtype;
maxdni dni%type;
curdni dni%type;
BEGIN
open c1
loop
fetch c1 into curregister;
curdni:=curregister.dni;
if(curdni > maxdni) THEN
  maxdni:= curdni;
end if;
end loop;
if curregister%ISOPEN then
close curregister;
end if;
  DBMS_OUTPUT.PUT_LINE(maxdni);
END OBTENERDNIMASALTO;



CLIENTES is the table name, dni is the field where i woud like to extract the hightest value.
0
Comment
Question by:axtur
5 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 24311015
Hi axtur,

Why don't you want to use a MAX()? ... and what error are you getting?

Also, please try the below...

lwadwell
CREATE OR REPLACE PROCEDURE OBTENERDNIMASALTO AS
  cursor c1 is 
    SELECT * from clientes;
  maxdni dni%type  := 0;
  curdni dni%type;
BEGIN
  FOR curregister IN c1
  LOOP
    curdni := curregister.dni;
    IF (curdni > maxdni) THEN
      maxdni := curdni;
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(maxdni);
END OBTENERDNIMASALTO;

Open in new window

0
 
LVL 12

Accepted Solution

by:
jwahl earned 500 total points
ID: 24311671
there are two ";" missing:


CREATE OR REPLACE PROCEDURE obtenerdnimasalto AS

    CURSOR c1 IS

        SELECT *

        FROM   clientes;  -- <-- missing ;

    curregister clientes%ROWTYPE;

    maxdni dni%TYPE;

    curdni dni%TYPE;

BEGIN

    OPEN c1;-- <-- missing ;

    LOOP

        FETCH c1

        INTO  curregister;
 

        curdni := curregister.dni;
 

        IF (curdni > maxdni) THEN

            maxdni := curdni;

        END IF;

    END LOOP;
 

    IF curregister%ISOPEN THEN

        CLOSE curregister;

    END IF;
 

    DBMS_OUTPUT.put_line (maxdni);

END obtenerdnimasalto;

/

Open in new window

0
 

Author Comment

by:axtur
ID: 24313278
I dont want to use max, to train myself in pl/sql :) I'm checking out now the code :)
0
 
LVL 18

Expert Comment

by:sventhan
ID: 24313358
where do you use MAX()? I do not see it in the code snippet.
You could try row_number or rank analytical funtions if you do not want to use max.
 
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php 
0
 

Author Comment

by:axtur
ID: 24313974
Ok, now it looks to compile, I'm trying to execute it, I'll open another question for that :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello All, In previous article we used Hortonworks sandbox to work with Hadoop. Now, lets think to create own single node Hadoop on Linux. Here we Install and Configure Apache Hadoop on UI based Oracle Linux. I assume, you have VMware installe…
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now