Solved

Cursor in Oracle Procedure

Posted on 2009-05-05
5
2,938 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle - SQL Parse String 5 44
MULTIPLE DATE QUERY 15 90
Not able to drop or recreate an Oracle stored procedure 1 38
oracle collections 2 19
Salesforce.com’s Console is a great tool to view activities, leads, contacts, accounts and opportunities all in one screen. It is particularly effective during call blocks and working numerous activities at a time in a quick, repetitive fashion (suc…
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

789 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