Solved

Cursor in Oracle Procedure

Posted on 2009-05-05
5
2,944 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

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

Salesforce.com is a cloud-based customer relationship management (CRM) system. In this article, you will learn how to add and map custom lead and contact fields to your Salesforce instance.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

718 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