Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Cursor in Oracle Procedure

Posted on 2009-05-05
5
Medium Priority
?
2,946 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
Let’s face it: one of the reasons your organization chose a SaaS solution (whether Microsoft Dynamics 365, Netsuite or SAP) is that it is subscription-based. The upkeep is done. Or so you think.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

604 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