Link to home
Start Free TrialLog in
Avatar of SharonBernal
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;

   /
ASKER CERTIFIED SOLUTION
Avatar of flow01
flow01
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
-- traditional loop cursor loop:
BEGIN    
open c_hlthcode;

LOOP    
 fetch  c_hlthcode  into ...
 exit when c_hlthcode%NOTFOUND;

 .. actions
end loop;

close c_hlthcode;

 END;
Avatar of SharonBernal
SharonBernal

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;
   /
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial