mjacobs2929
asked on
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;
ASKER
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.
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.
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.
SELECT count(*)
INTO your_variable
FROM.....
I think this could simplify a lot your code.
Regards.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works brilliantly! Thank you.
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.