Home » Developer & Programmer » Forms » cursors
cursors [message #283683] Tue, 27 November 2007 23:41 Go to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
How to use two cursors in a single declare statement?
I ahve to generate two text files in a form.
I am doing like this:
declare
  cursor1;
  cursor2;
begin
  open cursor1;
  loop
    fetch ....
    text_io.put_file('...');
    exit when cursor1%notfound;
  end loop;
  text_io.fclose(file1);
  begin
    open cursor2;
    loop
      fetch ...
      text_io.putfile(..);
      exit when cursor2%notfound;
    end loop;
    text_io.fclose(file2);
    forms_ddl('commit');
  end;
end;

When i run the form the text file corresponding to cursor1 gets generated with data but the file for cursor2 is empty with forms run time exception ora-06502.

I have run the cursors individually and they run fine and generate text files with data.
Its only when i try to combine them.
It seems i havent used the cursors appropriately.
Any advices?

[EDITED by LF: set the indentation to improve readability. Next time do it yourself, please.]

[Updated on: Wed, 28 November 2007 01:05] by Moderator

Report message to a moderator

Re: cursors [message #283687 is a reply to message #283683] Tue, 27 November 2007 23:56 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
this is what the error has to say:

ORA-06502: PL/SQL: numeric or value error string 
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. 
For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL,
or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). 
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints. 



regards,

[Updated on: Tue, 27 November 2007 23:58]

Report message to a moderator

Re: cursors [message #283691 is a reply to message #283683] Wed, 28 November 2007 00:04 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I know buddy what this error mean?
But when i run these cursors individually,they run fine.It is only when i combine the cursors this error occurs and only for cursor2.
Cursor1 runs fine and generate a text file.
That means iam somewhere wrong with the use of multiple cursors but don't no where.
The sample i provided is the outline of how i am using the cursors.
Re: cursors [message #283735 is a reply to message #283691] Wed, 28 November 2007 01:08 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the purpose of the inner BEGIN-END block?

You have, I believe, declared cursor variables as well. How are they declared?

What happens if you EXIT the loop before doing something with the fetched data? Something like
begin
  open cursor1;
  loop
    fetch ....
    exit when cursor1%notfound;    --> swithc those
    text_io.put_file('...');       --> two lines
  end loop;
  close cursor1;                   --> this is what you've forgotten
  ...

By the way, you forgot to CLOSE both cursors.
Re: cursors [message #283771 is a reply to message #283683] Wed, 28 November 2007 02:16 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Thanks for your concern Littlefoot.
I Tried with what you suggested but the same error.
only the first cursor works.
I am using the same cursor variables in both the fetch statements.
for ex.
declare
 tcode varchar2(9);
 cursor1;
 cursor2; 
begin
 open cursor1;
  loop
   fetch cursor1 into tcode;
   exit when cursor1%notfound;
   text_io.put_file(tcode);
   end loop;
   fclose(file1);
    begin
     open cursor2;
      loop
       fetch cursor2 into tcode;
        exit when cursor2%notfound;
        text_io.put_line(tcode); 
      end loop;  
      fclose(file2); 
      close cursor1;
      end;
 close cursor2;
 end;
Re: cursors [message #283774 is a reply to message #283771] Wed, 28 November 2007 02:19 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Again you forget to format your post with sql formatter
DECLARE
  tCode  VARCHAR2(9);
   
 CurSor1;
 CurSor2;
BEGIN
  OPEN CurSor1;
  
  LOOP
    FETCH CurSor1 INTO tCode;
    
    EXIT WHEN CurSor1%NOTFOUND;
    
    Text_io.Put_File(tCode);
  END LOOP;
  
  fClose(File1);
  
  BEGIN
    OPEN CurSor2;
    
    LOOP
      FETCH CurSor2 INTO tCode;
      
      EXIT WHEN CurSor2%NOTFOUND;
      
      Text_io.Put_Line(tCode);
    END LOOP;
    
    fClose(File2);
    
    CLOSE CurSor1;
  END;
  
  CLOSE CurSor2;
END;

http://www.orafaq.com/utilities/sqlformatter.htm
Re: cursors [message #283785 is a reply to message #283683] Wed, 28 November 2007 02:47 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Thanks Taj,posted it before reading Michel's reply
Now the makeup is done
So where am i wrong?
Re: cursors [message #287896 is a reply to message #283785] Fri, 14 December 2007 00:26 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you solved your problem?

Without seeing all the code for the separate cursor #2 and the combined cursor #2 I would say that you have a typo in the latter which is causing no records to be retrieved.

You are also not showing explicit 'open file' statements. Are both output files in the same directory? If different directories are they both defined at the database level? Have you inadvertently put a 'space' in one of the file names?

David
Previous Topic: Creating a job in Forms 6
Next Topic: FRM-32082 AND FRM-30085
Goto Forum:
  


Current Time: Sun Dec 04 14:29:37 CST 2016

Total time taken to generate the page: 0.06036 seconds