SharonBernal
asked on
PL/SQL Block Not Looping (ORA-01427)
I am able to execute the update statement below when I hard code in the values. The values from the cursor are needed in the update statement and I don't know how to get that value in the loop.
I can output a list of the values, but I'm not able to use them.
declare
v_etl_aud_id number(10);
v_src_fl_nm varchar2(100);
v_src_fl_nm2 varchar2(100);
v_srvc_typ_grp_cd varchar(20);
v_line varchar2(40);
v_line2 varchar2(40);
v_line3 varchar2(40);
v_line4 varchar2(40);
v_hlth_srvc_cd varchar2(5);
v_mod_cd varchar2(5);
BEGIN
-- Get audit ID of records in staging table
SELECT max(etl_aud_id) INTO v_etl_aud_id
FROM prov_avg_cst_stg_test;
v_line := 'Audit ID: '||v_etl_aud_id;
dbms_output.put_line (v_line);
--Get the file name of the records from the audit table using the audit id from above
SELECT src_fl_nm into v_src_fl_nm
from hccecst.etl_aud
where etl_aud_id = v_etl_aud_id;
v_line2 := 'Source File Name: '||v_src_fl_nm;
dbms_output.put_line (v_line2);
--Trim the file name to find the match in the hcce srv_typ_grp table
SELECT substr(src_fl_nm,1,5) into v_src_fl_nm2
from hccecst.etl_aud
where etl_aud_id = v_etl_aud_id;
v_line3 :='Trimmed Source File Name: '|| v_src_fl_nm2;
dbms_output.put_line (v_line3);
--Special File Handling
If v_src_fl_nm in ('Radiology.txt')
then
--EXECUTE IMMEDIATE--
delete from hcceetl.varray_grp;
commit;
--EXECUTE IMMEDIATE
insert into varray_grp
(SELECT ltrim(substr(dspl_nm,-5)) as dspl_nm, srvc_typ_grp_cd
from hcce.srvc_typ_grp
where
dspl_nm like '%Rad%');
commit;
DECLARE
CURSOR c_hlthcode is select hlt_cd,srvc_id from varray_grp;
BEGIN
open c_hlthcode;
LOOP
update prov_avg_cst_stg_test
set
srvc_typ_grp_cd=(select a.srvc_id *****I need the srvc_id from the loop here****
from
varray_grp a,
hccecst.etl_aud e,
hccecst.prov_avg_cst p
where a.hlt_cd= ****I need the hlt_cd from the loop here*****
and e.src_fl_nm like v_src_fl_nm2||'%'
and p.hlth_srvc_cd=a.hlt_cd
and p.srvc_typ_grp_cd=v_src_fl _nm);
exit when c_hlthcode%NOTFOUND;
end loop;
close c_hlthcode;
END;
END IF;
commit;
END;
/
I can output a list of the values, but I'm not able to use them.
declare
v_etl_aud_id number(10);
v_src_fl_nm varchar2(100);
v_src_fl_nm2 varchar2(100);
v_srvc_typ_grp_cd varchar(20);
v_line varchar2(40);
v_line2 varchar2(40);
v_line3 varchar2(40);
v_line4 varchar2(40);
v_hlth_srvc_cd varchar2(5);
v_mod_cd varchar2(5);
BEGIN
-- Get audit ID of records in staging table
SELECT max(etl_aud_id) INTO v_etl_aud_id
FROM prov_avg_cst_stg_test;
v_line := 'Audit ID: '||v_etl_aud_id;
dbms_output.put_line (v_line);
--Get the file name of the records from the audit table using the audit id from above
SELECT src_fl_nm into v_src_fl_nm
from hccecst.etl_aud
where etl_aud_id = v_etl_aud_id;
v_line2 := 'Source File Name: '||v_src_fl_nm;
dbms_output.put_line (v_line2);
--Trim the file name to find the match in the hcce srv_typ_grp table
SELECT substr(src_fl_nm,1,5) into v_src_fl_nm2
from hccecst.etl_aud
where etl_aud_id = v_etl_aud_id;
v_line3 :='Trimmed Source File Name: '|| v_src_fl_nm2;
dbms_output.put_line (v_line3);
--Special File Handling
If v_src_fl_nm in ('Radiology.txt')
then
--EXECUTE IMMEDIATE--
delete from hcceetl.varray_grp;
commit;
--EXECUTE IMMEDIATE
insert into varray_grp
(SELECT ltrim(substr(dspl_nm,-5)) as dspl_nm, srvc_typ_grp_cd
from hcce.srvc_typ_grp
where
dspl_nm like '%Rad%');
commit;
DECLARE
CURSOR c_hlthcode is select hlt_cd,srvc_id from varray_grp;
BEGIN
open c_hlthcode;
LOOP
update prov_avg_cst_stg_test
set
srvc_typ_grp_cd=(select a.srvc_id *****I need the srvc_id from the loop here****
from
varray_grp a,
hccecst.etl_aud e,
hccecst.prov_avg_cst p
where a.hlt_cd= ****I need the hlt_cd from the loop here*****
and e.src_fl_nm like v_src_fl_nm2||'%'
and p.hlth_srvc_cd=a.hlt_cd
and p.srvc_typ_grp_cd=v_src_fl
exit when c_hlthcode%NOTFOUND;
end loop;
close c_hlthcode;
END;
END IF;
commit;
END;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm still not able to access this loop. What I'm doing is as follows:
There is a master table that has data that I need to update a target table. The data in the master is desgined as long strings, so I have to pick out the codes so that I can perform the update on the target table.
The following executes and comes back with:
Execution (52: 1): ORA-06550: line 52, column 1:
PLS-00103: Encountered the symbol "FOR" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
The symbol "begin" was substituted for "FOR" to continue.
ORA-06550: line 52, column 67:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop
ORA-06550: line 66, column 3:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
loop
Here's the statement:
declare
v_etl_aud_id number(10);
v_src_fl_nm varchar2(100);
v_src_fl_nm2 varchar2(100);
v_srvc_typ_grp_cd varchar(20);
v_line varchar2(40);
v_line2 varchar2(40);
v_line3 varchar2(40);
v_line4 varchar2(40);
v_hlth_srvc_cd varchar2(5);
v_mod_cd varchar2(5);
BEGIN
-- Get audit ID of records in staging table
SELECT max(etl_aud_id) INTO v_etl_aud_id
FROM prov_avg_cst_stg_test;
v_line := 'Audit ID: '||v_etl_aud_id;
dbms_output.put_line (v_line);
--Get the file name of the records from the audit table using the audit id from above
SELECT src_fl_nm into v_src_fl_nm
from hccecst.etl_aud
where etl_aud_id = v_etl_aud_id;
v_line2 := 'Source File Name: '||v_src_fl_nm;
dbms_output.put_line (v_line2);
--Trim the file name to find the match in the hcce srv_typ_grp table
SELECT substr(src_fl_nm,1,5) into v_src_fl_nm2
from hccecst.etl_aud
where etl_aud_id = v_etl_aud_id;
v_line3 :='Trimmed Source File Name: '|| v_src_fl_nm2;
dbms_output.put_line (v_line3);
If v_src_fl_nm in ('Radiology.txt')
then
--EXECUTE IMMEDIATE--
delete from hcceetl.varray_grp;
commit;
--EXECUTE IMMEDIATE
insert into varray_grp---------------- ---------- -----I pull out the records with the same file name
(SELECT ltrim(substr(dspl_nm,-5)) as dspl_nm, srvc_typ_grp_cd
from hcce.srvc_typ_grp
where
dspl_nm like '%Rad%');
commit;
BEGIN
DECLARE CURSOR R1;
OPEN R1;
FOR R1 IN (select hlt_cd,srvc_id from varray_grp order by srvc_id); LOOP
update prov_avg_cst_stg_test
set
srvc_typ_grp_cd= (select R1.srv_id
from
varray_grp a,
hccecst.etl_aud e,
hccecst.prov_avg_cst p
where a.hlt_cd = R1.hlt_cd
and e.src_fl_nm like v_src_fl_nm2||'%'
and p.hlth_srvc_cd=a.hlt_cd
and p.srvc_typ_grp_cd=v_src_fl _nm);
exit when R1%NOTFOUND;
end loop;
close R1;
END;
END IF;
commit;
END;
/
There is a master table that has data that I need to update a target table. The data in the master is desgined as long strings, so I have to pick out the codes so that I can perform the update on the target table.
The following executes and comes back with:
Execution (52: 1): ORA-06550: line 52, column 1:
PLS-00103: Encountered the symbol "FOR" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
The symbol "begin" was substituted for "FOR" to continue.
ORA-06550: line 52, column 67:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop
ORA-06550: line 66, column 3:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
loop
Here's the statement:
declare
v_etl_aud_id number(10);
v_src_fl_nm varchar2(100);
v_src_fl_nm2 varchar2(100);
v_srvc_typ_grp_cd varchar(20);
v_line varchar2(40);
v_line2 varchar2(40);
v_line3 varchar2(40);
v_line4 varchar2(40);
v_hlth_srvc_cd varchar2(5);
v_mod_cd varchar2(5);
BEGIN
-- Get audit ID of records in staging table
SELECT max(etl_aud_id) INTO v_etl_aud_id
FROM prov_avg_cst_stg_test;
v_line := 'Audit ID: '||v_etl_aud_id;
dbms_output.put_line (v_line);
--Get the file name of the records from the audit table using the audit id from above
SELECT src_fl_nm into v_src_fl_nm
from hccecst.etl_aud
where etl_aud_id = v_etl_aud_id;
v_line2 := 'Source File Name: '||v_src_fl_nm;
dbms_output.put_line (v_line2);
--Trim the file name to find the match in the hcce srv_typ_grp table
SELECT substr(src_fl_nm,1,5) into v_src_fl_nm2
from hccecst.etl_aud
where etl_aud_id = v_etl_aud_id;
v_line3 :='Trimmed Source File Name: '|| v_src_fl_nm2;
dbms_output.put_line (v_line3);
If v_src_fl_nm in ('Radiology.txt')
then
--EXECUTE IMMEDIATE--
delete from hcceetl.varray_grp;
commit;
--EXECUTE IMMEDIATE
insert into varray_grp----------------
(SELECT ltrim(substr(dspl_nm,-5)) as dspl_nm, srvc_typ_grp_cd
from hcce.srvc_typ_grp
where
dspl_nm like '%Rad%');
commit;
BEGIN
DECLARE CURSOR R1;
OPEN R1;
FOR R1 IN (select hlt_cd,srvc_id from varray_grp order by srvc_id); LOOP
update prov_avg_cst_stg_test
set
srvc_typ_grp_cd= (select R1.srv_id
from
varray_grp a,
hccecst.etl_aud e,
hccecst.prov_avg_cst p
where a.hlt_cd = R1.hlt_cd
and e.src_fl_nm like v_src_fl_nm2||'%'
and p.hlth_srvc_cd=a.hlt_cd
and p.srvc_typ_grp_cd=v_src_fl
exit when R1%NOTFOUND;
end loop;
close R1;
END;
END IF;
commit;
END;
/
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BEGIN
open c_hlthcode;
LOOP
fetch c_hlthcode into ...
exit when c_hlthcode%NOTFOUND;
.. actions
end loop;
close c_hlthcode;
END;