[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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;

   /
0
SharonBernal
Asked:
SharonBernal
  • 3
2 Solutions
 
flow01Commented:
-- the loop reference i can resolve
-- but your update probably is wrong
-- what do you want to achieve  ?

DECLARE
--  with an implicit cursor definition   instead of separat open, fetch until cursor not found, close of the cursor

BEGIN    

FOR R1 IN (select hlt_cd,srvc_id from varray_grp) 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);
    -- since thre is no where clause for the update
    -- each record in prov_avg_cst_stg_test will be updated with the value of the second select
    -- for every record in the first select
    -- only the last value of srv_id will be set in all records and because the first select has no order by
    -- the result is inpredictable and probably very wrong

END LOOP;


END;
0
 
flow01Commented:
-- 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;
0
 
SharonBernalAuthor Commented:
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;
   /
0
 
flow01Commented:
remove the lines

DECLARE CURSOR R1;
OPEN R1;
exit when R1%NOTFOUND;
close R1;

and remove the semicolon in
FOR R1 IN (select hlt_cd,srvc_id from varray_grp order by srvc_id); LOOP    
to get
FOR R1 IN (select hlt_cd,srvc_id from varray_grp order by srvc_id) LOOP    
the FOR LOOP .. END LOOP; -- construction  takes care of creating  the cursor,  opening en closing  en fetching the records until the last one is reached
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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