Solved

PL/SQL Block Not Looping (ORA-01427)

Posted on 2012-03-30
4
526 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

724 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