Solved

PL/SQL Block Not Looping (ORA-01427)

Posted on 2012-03-30
4
522 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

803 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