Home » SQL & PL/SQL » SQL & PL/SQL » Encountered the symbol "FETCH" when expecting one of the following: begin function pac
Encountered the symbol "FETCH" when expecting one of the following: begin function pac [message #190452] Wed, 30 August 2006 18:04 Go to next message
chriswitt
Messages: 7
Registered: August 2006
Location: jam
Junior Member

why am i get this error help


is

cursor data_cursor1 is
select date_submitted from AISDBA.RX_TRANSACTION_DETAIL
WHERE PHARMACY_NABP LIKE 'JD%' AND PHARMACY_NABP in('JDAA04037','JDAE05055','JDBA04014')
AND CLIENT_ID = 'JDP'
AND TRANSACTION_STATUS IN ('P')
AND DATE_SUBMITTED BETWEEN '20060710' AND '20060731';
new2 data_cursor1%rowtype;

filename varchar2(255) := 'JOB201.dat';
directory_location varchar2(1024) := 'f:\jadep_inv';
open_mode char(1) := 'w';
out_buffer varchar2(4000);
ofile UTL_FILE.file_type;
counter number(10) :=100;
begin
filename := filename || to_char(sysdate,'ddmmyyyy') || '.txt';
ofile := UTL_FILE.FOPEN(directory_location,filename,'w');
OPEN data_cursor1;
--header data
out_buffer :='RECTYPE'||','||'SEQUENCENO'||','||'HDRDESC'||','||'TRANSDATE'||','||'FISCYEAR'||','||'FISCPERIOD';
UTL_FILE.PUT_LINE(ofile,out_buffer);
out_buffer :='RECTYPE'||','||'SEQUENCENO'||','||'LINENO'||','||'ITEMNO'||','||'LOCATION'||','||'QUANTITY';
UTL_FILE.PUT_LINE(ofile,out_buffer);
--out_buffer := '1'||','||'1'||','||'Daily Export SET 11'||','||'20060531'||','||to_char(add_months(sysdate,9),'yyyy')||','||to_char(add_months(sysdate,9),'mm');
--UTL_FILE.PUT_LINE(ofile,out_buffer);
loop
FETCH data_cursor1 INTO new2;
EXIT WHEN data_cursor1%NOTFOUND;
out_buffer := '1'||','||'1'||','||'Daily Export SET 11'||','||new2.date_submitted||','||to_char(add_months(sysdate,9),'yyyy')||','||to_char(add_months(sysdate,9),'mm');
UTL_FILE.PUT_LINE(ofile,out_buffer);
BEGIN
DECLARE
cursor data_cursor5 is
select PHARMACY_NABP,METRIC_DEC_QUANTITY, NDC,CLAIM_REFERENCE_NUMBER,to_char(date_filled) dates from AISDBA.RX_TRANSACTION_DETAIL
WHERE PHARMACY_NABP LIKE 'JD%' AND PHARMACY_NABP in('JDAA04037','JDAE05055','JDBA04014')
AND CLIENT_ID = 'JDP'
AND TRANSACTION_STATUS IN ('P')
AND DATE_SUBMITTED like new2.date_submitted;
new5 data_cursor5%rowtype;
open data_cursor5;

loop

counter := counter +1;
FETCH data_cursor5 INTO new5;
EXIT WHEN data_cursor5%NOTFOUND;

--record data
out_buffer :='2'||','||counter||','||new1.CLAIM_REFERENCE_NUMBER||','||new1.NDC||','||substr(new1.PHARMACY_NABP,3,2)||substr(new1.PHARMACY_NABP, 6,4)||','||new1.METRIC_DEC_QUANTITY ;
UTL_FILE.PUT_LINE(ofile,out_buffer);


end loop;
CLOSE data_cursor5;

end;

end loop;

CLOSE data_cursor1;
UTL_FILE.fclose(ofile);
end;
Re: Encountered the symbol "FETCH" when expecting one of the following: begin function [message #190453 is a reply to message #190452] Wed, 30 August 2006 18:10 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Your inner begin block goes straight from the declare to an executable statement without another begin.

Why all the explicit cursors (open/fetch/close) instead of cursor for loops?
Re: Encountered the symbol "FETCH" when expecting one of the following: begin function [message #190981 is a reply to message #190453] Sun, 03 September 2006 18:53 Go to previous message
chriswitt
Messages: 7
Registered: August 2006
Location: jam
Junior Member

thanks , your recommendation work,
Previous Topic: Records/Bulk Collects Problem
Next Topic: [stored procedure Oracle 8.0.6.0] deleting records with changes on specific columns
Goto Forum:
  


Current Time: Fri Dec 02 20:47:01 CST 2016

Total time taken to generate the page: 0.15293 seconds