Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4360
  • Last Modified:

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

0
mjacobs2929
Asked:
mjacobs2929
  • 2
  • 2
1 Solution
 
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
 
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
 
mjacobs2929Author Commented:
Works brilliantly! Thank you.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now