Fetch same cursor twice?

I'm trying to fetch the same cursor (c_claim) twice in the code below. Seems PLSQL des not allow this? If I fetch c_claim into m_rec  (commented out for the moment), then c_claim is empty for the second fetch into m_rec.  Is fetching the same cursor twice allowed? If so, how? Many thanks for any clues.
PROCEDURE createClaimBreakdown(v_type  IN  varchar2)
AS
 
CURSOR Claim_Detail(p_claim_id number)
IS
    SELECT	ecg.description,
            ecg.group_type,
            ecg.num,
            trim(to_char(ecg.total,'999999999999999.99')) as total,
            ecg.account_code
	FROM	examiner_claim_group ecg
	WHERE	ecg.claim_id = p_claim_id;
           
v_company varchar(2);
v_cost_centre varchar2(4);
v_account varchar2(4);
v_analysis_code varchar2(4);
v_service varchar2(5);
v_run_id number;
v_counter number := 0;
v_running_total number;
v_invoice_amount number := 0;
v_gl_code varchar2(50);
v_total_remainder number;
v_line_amount number;
v_line_amount_char  varchar2(100);
v_header_amount_char    varchar2(100);
v_recordcount number := 0;
m_recordcount number := 0;
c_claim ref_cursor;
claim_rec claim_rec_type;
m_rec claim_rec_type;
v_the_file_name varchar2(100);
v_checksum number := 0;
v_the_export_id number;
 
ClaimBreakdownFile utl_file.file_type;
NonTaxBreakdownFilename varchar2(50) := to_char(sysdate,'YYYYMMDD_HH24MISS')||'_Non_tax_fees_breakdown.csv'; 
UKTaxBreakdownFilename varchar2(50) := to_char(sysdate,'YYYYMMDD_HH24MISS')||'_UK_tax_fees_breakdown.csv'; 
OverseasTaxBreakdownFilename varchar2(50) := to_char(sysdate,'YYYYMMDD_HH24MISS')||'_Overseas_tax_fees_breakdown.csv'; 
file_location varchar2(50) := 'ECF'; -- For Dev 
Claim_Detail_Rec Claim_Detail%RowType;
        
 BEGIN
 
    SELECT export_id_number.nextval INTO v_the_export_id
    FROM dual;
    
    IF v_type = 'UKTAX'
    THEN
        v_the_file_name := UKTaxBreakdownFilename;
    ELSIF v_type = 'OVERSEASTAX'
    THEN
        v_the_file_name := OverseasTaxBreakdownFilename;
    ELSIF v_type = 'NONTAX'
    THEN
        v_the_file_name := NonTaxBreakdownFilename;
    END IF;
    
    INSERT INTO examiner_claim_export
    (export_id, export_type, file_path, file_name, date_submitted, date_created, line_amount, checksum)
    VALUES
    (v_the_export_id, v_type||'_BREAKDOWN', file_location, v_the_file_name, sysdate, null, null, null);
 
    SELECT  ECFExportRuntimeID.nextval
    INTO  v_run_id
    FROM  dual;
    
    IF v_type = 'NONTAX'
    THEN
                 
      BEGIN
 
        OPEN c_claim FOR    
        SELECT 'C'||substr(ech.examiner_code,2,5) as examiner_code,
               ech.year as claim_year, 
               ech.month as claim_month,
               ech.claim_id,
               ech.date_submitted,
               p.firstname,
               p.lastname,
               nvl(ech.total_protected_value, ech.total_exchange_value) as total_value,
               ech.examiner_currency,
               trim(to_char(round(nvl(ech.total_protected_value, ech.total_exchange_value),2),'99999999999.99')) as total
	      FROM address a,
               examiner_claim_header ech, 
               person p, 
               person_bank_account pba
		 WHERE a.person_code = ech.examiner_code
		   AND a.type = 'PERSON'
		   AND a.address_type = 'MAIN'
		   AND p.person_code = a.person_code
		   AND pba.person_code = p.person_code
		   AND ech.sent_to_deloitte = 'N'
		   AND pba.uk_taxable = 'N'
		   AND ech.status = 'SUBMITTED';
                 
      END;
 
 
    ELSIF v_type = 'OVERSEASTAX'
    THEN
 
 
      BEGIN
 
        OPEN c_claim FOR
        SELECT 'C'||substr(ech.examiner_code,2,5) as examiner_code,
               ech.year as claim_year,
               ech.month as claim_month,
               ech.claim_id,
               ech.date_submitted,
               p.firstname,
               p.lastname,
               nvl(ech.total_protected_value, ech.total_exchange_value) as total_value,
               ech.examiner_currency,
               trim(to_char(round(nvl(ech.total_protected_value, ech.total_exchange_value),2),'99999999999.99')) as total
          FROM address a,
               examiner_claim_header ech,
               person p,
               person_bank_account pba
         WHERE a.country_code != 'GB'
           AND a.person_code = ech.examiner_code
           AND a.type = 'PERSON'
           AND a.address_type = 'MAIN'
           AND p.person_code = a.person_code
           AND pba.person_code = p.person_code
           AND ech.sent_to_deloitte = 'N'
           AND pba.uk_taxable = 'Y'
           AND ech.status = 'SUBMITTED';
 
      END;
 
 
    ELSIF v_type = 'UKTAX'
    THEN
 
 
      BEGIN
 
        OPEN c_claim FOR
        SELECT 'C'||substr(ech.examiner_code,2,5) as examiner_code,
               ech.year as claim_year,
               ech.month as claim_month,
               ech.claim_id,
               ech.date_submitted,
               p.firstname,
               p.lastname,
               nvl(ech.total_protected_value, ech.total_exchange_value) as total_value,
               ech.examiner_currency,
               trim(to_char(round(nvl(ech.total_protected_value, ech.total_exchange_value),2),'99999999999.99')) as total
          FROM address a,
               examiner_claim_header ech,
               person p, 
               person_bank_account pba
         WHERE a.country_code = 'GB'
           AND a.person_code = ech.examiner_code
           AND a.type = 'PERSON'
           AND a.address_type = 'MAIN'
           AND p.person_code = a.person_code
           AND pba.person_code = p.person_code
           AND ech.sent_to_deloitte = 'N'
           AND pba.uk_taxable = 'Y'
           AND ech.status = 'SUBMITTED';
 
      END;
 
    END IF;
 
 
    /* LOOP
      FETCH c_claim INTO m_rec;
      EXIT WHEN c_claim%NOTFOUND;
      dbms_output.put_line(c_claim%ROWCOUNT);
      m_recordcount := m_recordcount + 1;        
    END LOOP; */
 
   dbms_output.put_line('m_recordcount1: '||m_recordcount);
 
   --IF m_recordcount > 0 THEN
     
    /*  Define the file handle, file name and location */
    IF utl_file.is_open(ClaimBreakdownFile)
    THEN
        utl_file.fclose(ClaimBreakdownFile);
    END IF;
        
    IF v_type = 'NONTAX'
    THEN
      ClaimBreakdownFile := utl_file.fopen(file_location, NonTaxBreakdownFilename, 'W');
    ELSIF v_type = 'OVERSEASTAX'
    THEN
      ClaimBreakdownFile := utl_file.fopen(file_location, OverseasTaxBreakdownFilename, 'W'); 
    ELSIF v_type = 'UKTAX'
    THEN
      ClaimBreakdownFile := utl_file.fopen(file_location, UKTaxBreakdownFilename, 'W'); 
    END IF;
      
    utl_file.put_line(ClaimBreakdownFile,'B|'||v_run_id);
        
    v_counter := v_counter + 1;
 
   dbms_output.put_line('m_recordcount2: '||m_recordcount);
   --dbms_output.put_line('c_claim%ROWCOUNT 1: '||c_claim%ROWCOUNT);
 
        
    LOOP
        FETCH c_claim INTO c;claim_re
        EXIT WHEN c_claim%NOTFOUND;
            
        dbms_output.put_line('c_claim%ROWCOUNT 2: '||c_claim%ROWCOUNT);
        
            v_running_total := 0;
            
            IF claim_rec.total = 0
            THEN
                v_header_amount_char := '0.00';
            ELSE
                v_header_amount_char := to_char(claim_rec.total, '99999999999.99');
            END IF;
            
            utl_file.put_line(ClaimBreakdownFile,'H|'||v_run_id||'|'||claim_rec.claim_id||'|'||claim_rec.examiner_code||'|'||trim(v_header_amount_char)||'|'||claim_rec.examiner_currency||'|'||claim_rec.claim_year||' '||claim_rec.claim_month||'|'||to_char(claim_rec.date_submitted, 'DD-MON-YYYY'));
            
            v_checksum := v_checksum + claim_rec.total;
            
            IF v_type = 'UKTAX'
            THEN
                utl_file.put_line(ClaimBreakdownFile,'D|'||v_run_id||'|'||claim_rec.claim_id||'|'||'Tax'||'|'||'31|0000|8104|00000|0000|00|00000'||'|'||'0.00');
                v_counter := v_counter + 1;
            ELSIF v_type = 'OVERSEASTAX'
            THEN
                utl_file.put_line(ClaimBreakdownFile,'D|'||v_run_id||'|'||claim_rec.claim_id||'|'||'Tax'||'|'||'31|0000|8160|00000|0000|00|00000'||'|'||'0.00');
                v_counter := v_counter + 1;
            END IF;
            
            
            v_invoice_amount := v_invoice_amount + 1;
            v_counter := v_counter + 1;
            
            OPEN Claim_Detail(claim_rec.claim_id);
                LOOP
                    FETCH Claim_Detail INTO Claim_Detail_Rec;
                    EXIT WHEN Claim_Detail%NOTFOUND; 
                         
                    v_recordcount := v_recordcount + 1;
 
                END LOOP;
            CLOSE Claim_Detail;
            
            OPEN Claim_Detail(claim_rec.claim_id);
                LOOP
                    FETCH Claim_Detail INTO Claim_Detail_Rec;
                    EXIT WHEN Claim_Detail%NOTFOUND;
                
                        SELECT  company, cost_centre, account, analysis_code, service
                          INTO  v_company, v_cost_centre, v_account, v_analysis_code, v_service
                          FROM  subject_fee_type
                         WHERE  year = claim_rec.claim_year
                           AND  month = claim_rec.claim_month
                           AND  fee_type = Claim_Detail_Rec.group_type;
 
                    
                        v_gl_code := v_company||'|'||v_cost_centre||'|'||v_account||'|'||Claim_Detail_Rec.account_code||'|'||v_analysis_code||'|00|'||v_service;
                    
                        v_running_total := v_running_total + Claim_Detail_Rec.total;
					
                        IF Claim_Detail%ROWCOUNT = v_recordcount
                        THEN 
    			            v_total_remainder := claim_rec.total - v_running_total;
    			            v_line_amount := Claim_Detail_Rec.total + v_total_remainder;
    			        ELSE
    			            v_line_amount := Claim_Detail_Rec.total;
    			        END IF;
                    
                        IF v_line_amount = 0
                        THEN
                            v_line_amount_char := '0.00';
                        ELSE
                            v_line_amount_char := to_char(v_line_amount, '99999999999.99');
                        END IF;
                            
                        utl_file.put_line(ClaimBreakdownFile,'D|'||v_run_id||'|'||claim_rec.claim_id||'|'||Claim_Detail_Rec.description||' (Amount: '||Claim_Detail_Rec.num||')'||'|'||v_gl_code||'|'||trim(v_line_amount_char));
                    
                        v_counter := v_counter + 1;    
                            
                END LOOP;
            CLOSE Claim_Detail;
            
          EXAMINERCLAIM.statusFlagTransferred(claim_rec.claim_id);
            
      END LOOP;
 
        
      v_counter := v_counter + 1;
        
      utl_file.put_line(ClaimBreakdownFile,'T|'||v_run_id||'|'||v_counter);	
	
      utl_file.fclose (ClaimBreakdownFile);
      
      UPDATE    examiner_claim_export
      SET       date_created = sysdate,
                line_amount = v_counter,
                checksum = v_checksum
      WHERE     export_id = v_the_export_id;
 
   -- END IF;
 
 END;

Open in new window

mjacobs2929Asked:
Who is Participating?
 
yuchingCommented:
you can do like Claim_Detail cursor, pass in the variable example as attach code
Declare
 
Cursor c_claim(uk_taxable Char,ex_cntry Varchar2,in_cntry Varchar2)  FOR    
SELECT 'C'||substr(ech.examiner_code,2,5) as examiner_code,
  ech.year as claim_year, ech.month as claim_month,
  ech.claim_id, ech.date_submitted, p.firstname,
  p.lastname, nvl(ech.total_protected_value, 
  ech.total_exchange_value) as total_value, ech.examiner_currency,
  trim(to_char(round(nvl(ech.total_protected_value,   
  ech.total_exchange_value),2),'99999999999.99')) as total
FROM address a, examiner_claim_header ech, person p,   
  person_bank_account pba
WHERE a.person_code = ech.examiner_code AND a.type = 'PERSON'
  AND a.address_type = 'MAIN' AND p.person_code = a.person_code
  AND pba.person_code = p.person_code AND ech.sent_to_deloitte = 'N'
  AND pba.uk_taxable = uk_taxable 'N' AND ech.status = 'SUBMITTED'
  And (ex_cntry Is Null Or a.country_code != ex_cntry )           
  And (in_cntry  Is Null Or a.country_code = in_cntry );   
...
uk_taxable Char(1);
ex_cntry Varchar2(2);
in_cntry Varchar2(2);
 
Begin
....
  IF v_type = 'UKTAX' THEN
    v_the_file_name := UKTaxBreakdownFilename;
    in_cntry := 'GB';
    uk_taxable := 'Y';
  ELSIF v_type = 'OVERSEASTAX' THEN
    v_the_file_name := OverseasTaxBreakdownFilename;
    ex_cntry := 'GB';
    uk_taxable := 'Y';
  ELSIF v_type = 'NONTAX' THEN
    v_the_file_name := NonTaxBreakdownFilename;
    uk_taxable := 'N';
  END IF;
 
   ...
   OPEN c_claim(uk_taxable, ex_cntry, in_cntry);
   LOOP     
 
        FETCH c_claim INTO c;claim_re
        EXIT WHEN c_claim%NOTFOUND;
     ...
 
End;

Open in new window

0
 
gplanaCommented:
First of all, I recommend you to simplify as much as possible the  example code when you put a question.

To fetch twice the same record is not a normal practice in a cursos (is not recommended and, in some cases, it's not allowed). Normally you use a cursor for making some kind of treatment, so you should'nt to treat same record twice.

Could you please explain what do you want to do, as I can analyze which alternatives do you have ?

Thank you.
0
 
mjacobs2929Author Commented:
Well, the code is doing one of three select statements (cursor c_claim), based on the type passed in. Cursor c_claim is fetched (fetch2) and looped over to produce lines in a csv file.

I first wish to check that cursor c_claim is not empty (fetch1 - commented out) before creating the csv file at all. Doing fetch1 means fetch 2 is empty.

Dilemma - can't check number rows in cursor c_claim without doing fetch1. If I do fetch1, fetch2 is empty.
0
 
gplanaCommented:
If you want to know the number of rows a priori, you can raise a statement like:

SELECT count(*)
INTO your_variable
FROM.....

I think this could simplify a lot your code.

Regards.
0
 
mjacobs2929Author Commented:
Works brilliantly! Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.