Solved

PL/SQL Block Not Looping (ORA-01427)

Posted on 2012-03-30
4
520 Views
Last Modified: 2012-06-27
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
Comment
Question by:SharonBernal
  • 3
4 Comments
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 37789457
-- 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
 
LVL 20

Expert Comment

by:flow01
ID: 37789493
-- 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
 

Author Comment

by:SharonBernal
ID: 37796457
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
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 500 total points
ID: 37797838
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now