ORA-04103 errors 1

[SmpTraining]

Hi All,

I'm quite new to PL/SQL and so may be missing something simple but... I get many errors when running the following PL/SQL code

create or replace
PACKAGE BODY OCC_POPULATE AS

-------------------------------------------------------

  procedure occ_main AS
  BEGIN
    dbms_output.enable(1000000);
    occ_populate.occ_foster_carers;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' - Error:' || sqlerrm );
  END occ_main;

--------------------------------------------------------

  procedure occ_foster_carers AS

    v_record_count number;
  
  begin
  
    select count(*) 
    into v_record_count 
    from occ_fos_enquiry;
    
    dbms_output.put_line(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') 
        || ' Deleting ['
        || v_record_count
        || '] rows from occ_fos_enquiry');
    
    delete from occ_fos_enquiry;
    commit;
    
    dbms_output.put_line(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') 
        || ' Inserting rows into occ_fos_enquiry');

    insert into occ_fos_enquiry
    (
      enquiry_id
      , app1_person_id
      , app1_first_name
      , app1_last_name
      , app1_address
      , app1_gender
      , app1_dob
      , app2_person_id
      , app2_first_name
      , app2_last_name
      , app2_address
      , app2_gender
      , app2_dob
      , res_age_range
      , res_short_term
      , res_long_term
      , res_fostering_plus
      , res_sibling_groups
      , res_disability_adoption
--      , res_disability_experience
--      , res_disabled_child
      , res_parent_child
--      , res_gender
      , res_number_of_children
      , res_uasc
      , res_family_friends
      , res_relief
      , res_shortbreaks_disabled
      , res_shortbreaks_nondisabled
      , res_mtfcp
      , res_undecided
      , enquiry_date
      , rec_internet
      , rec_word_of_mouth
      , rec_knows_fcarer
      , rec_knows_adopter
      , rec_leaflet
      , rec_tv_radio
      , rec_prev_enquiry
      , rec_cis
      , rec_occ_worker
      , rec_advert
      , rec_community_event
      , rec_fcare_fortnight
      , rec_other_agency
      , rec_other
      , rec_other_specify
    )
    select 
      occ.f_get_workflow_id(ep.id) "enquiry_id"
      , nvl(( select to_number(nvl(vfa.text_answer,'0'))
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'app1_ID'
          ),-1) as "app1_person_id"
      , nvl(( select vfa.text_answer
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'app1forename'
          ),'<null>') as "app1_first_name"
      , nvl(( select nvl(vfa.text_answer , '')
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'app1surname'
          ),'<null>') as "app1_last_name"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'app1address'
          ),'<null>') as "app1_address"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'app1gender'
          ),'<null>') as "app1_gender"
      , nvl(( select vfa.date_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'app1dob' 
          ),null) as "app1_dob"
      , nvl(( select to_number(nvl(vfa.text_answer,'0'))
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'app2_ID' 
          ),-1) as "app2_person_id"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'app2forename' 
          ),'<null>') as "app2_first_name"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'app2surname' 
          ),'<null>') as "app2_last_name"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'app2address' 
          ),'<null>') as "app2_address"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'app2gender' 
          ),null) as "app2_gender"
      , nvl(( select vfa.date_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'app2dob' 
          ),null) as "app2_dob"
    /*
      , ( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'adoption' 
          ) as "res_adoption"
    */
      , nvl(( select substr(vfa.text_answer, 1, 100)
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'agerange' 
          ),'<null>') as "res_age_range"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'shortterm' 
          ),'-') as "res_short_term"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'longterm' 
          ),'-') as "res_long_term"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'fosteringplus' 
          ),'-') as "res_fostering_plus"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'siblinggroups' 
          ),'-') as "res_sibling_groups"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'disabilityadoption' 
          ),'-') as "res_disability_adoption"
/*      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'disabilityexperience' 
          ),'-') as "res_disability_experience"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'disabledchild' 
          ),'-') as "res_disabled_child"
*/
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'parentchild' 
          ),'-') as "res_parent_child"
/*      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'gender' 
          ),'-') as "res_gender"
*/
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'numberofchildren' 
          ),'-') as "res_number_of_children"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'uasc' 
          ),'-') as "res_uasc"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'familyfriends' 
          ),'-') as "res_family_friends"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'relief' 
          ),'-') as "res_relief"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'shortbreaks_disabled' 
          ),'-') as "res_shortbreaks_disabled"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'shortbreaks_nondisabled' 
          ),'-') as "res_shortbreaks_nondisabled"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'mtfcp' 
          ),'-') as "res_mtfcp"
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'undecided' 
          ),'-') as "res_undecided"
      , nvl(( select vfa.date_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'enquiry_date' 
          ),null) as "enquiry_date"

      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'B508E5F9-790F-B34B-58C9-34A5C2BB8C03' 
          ),'-') as "rec_internet"              
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = '00F83334-92CC-419D-A881-759B09D97E6A' 
          ),'-') as "rec_word_of_mouth"  
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = '6218B8B0-BA28-94F0-AF0C-F3E90B46A43C' 
          ),'-') as "rec_knows_carer"  
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = '7A8903F2-2454-05EE-4C73-B2366919677A' 
          ),'-') as "rec_knows_adopter"  
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'E67D22CD-39DF-5906-5687-ADF8F2FDB73A' 
          ),'-') as "rec_leaflet"  
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = '8B0DC0E0-C84A-E03F-0D50-0E9566AED630' 
          ),'-') as "rec_tv_radio"  
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'A4B4E24C-659E-F6B7-3529-FA6D564B9B7C' 
          ),'-') as "rec_prev_enquiry"  
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'EA1FE816-97BE-BC16-A120-CDB1E7408798' 
          ),'-') as "rec_cis"  
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'CB2BA87E-C304-2BB4-3DD8-0B407E6A0B70' 
          ),'-') as "rec_occ_worker"  
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'F68EBD5D-5331-A237-C9BB-F5FDA912E342' 
          ),'-') as "rec_advert"  
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = '92D1966B-0CA4-3488-09C4-B8CC16149345' 
          ),'-') as "rec_community_event"  
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'C978C551-A615-0079-D76F-DF9E6B4BD35C' 
          ),'-') as "rec_fcare_fortnight"  
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = 'DFEC409C-93FB-D87D-C579-3382BA770E90' 
          ),'-') as "rec_other_agency"  
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = '850E3275-C965-751A-A57C-5D951A99E0BF' 
          ),'-') as "rec_other"  
      , nvl(( select vfa.text_answer 
          from   vw_form_answers vfa 
          where  vfa.form_id = f.id
            and  vfa.form_answers_tag_id = '6D3B129B-F090-7AB1-1FAD-E0DE8CF55588' 
          ),'-') as "rec_other_specify"  

    from 

      episodes ep

      -- JOIN: Forms
      inner join forms f
      on f.episode_id = ep.id
  
      -- JOIN: Form types
      inner join form_types ft
      on f.form_type_id = ft.id

    where
      ft.id_code = 'FORM1154968748000';  -- Foster Carer or Adopter Enquiry Form

    select count(*) 
    into v_record_count 
    from occ_fos_enquiry;
    
    dbms_output.put_line(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') 
        || ' Successfully inserted ['
        || v_record_count 
        || '] rows into occ_fos_enquiry');

    dbms_output.put_line(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') 
        || ' Updating occ_fos_enquiry with closed enquiry details...started');

    dbms_output.put_line(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') 
        || ' Updating occ_fos_enquiry with closed enquiry details...completed');
      
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' - Error:' || sqlerrm);
    
  END occ_foster_carers;

-----------------------------------------------

END OCC_POPULATE;

The output I receive is:

02-DEC-2008 16:25:16 Deleting [112] rows from occ_fos_enquiry
02-DEC-2008 16:25:16 Inserting rows into occ_fos_enquiry
ORA-01403: no data found
ORA-01403: no data found
:
:
02-DEC-2008 16:25:26 Successfully inserted [112] rows into occ_fos_enquiry
02-DEC-2008 16:25:26 Updating occ_fos_enquiry with closed enquiry details...started
02-DEC-2008 16:25:26 Updating occ_fos_enquiry with closed enquiry details...completed
Process exited.

There are 112 ORA-01403 messages. I've tried a DATA_NOT_FOUND to the exception block but it doesn't trap this error.

Any help gratefully received. Thanks, Steve.

Steve Phillips, Crystal Trainer/Consultant

 

[SantaMufasa]

Steve,

Since I have no sample data set available against which to test your code, I'm not prepared presently to go through your code manually to troubleshoot the problem.

But I can tell you that in PL/SQL, if you are not using explicit or implicit cursors, that SELECT statements must return precisely one row...neither:[ul][li]zero rows (which throws the "ORA-04103: no data found" error) nor[/li][li]two or more rows (which throws the "ORA-01422: exact fetch returns more than requested number of rows" error).[/li][/ul]

Therefore, your code is attempting to SELECT data for which no row returns.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SmpTraining]

Hi Dave,

Thank you very much for that response. How should this type of code be written in PL/SQL?

Essentially I have an answers table VW_FORM_ANSWERS with many millions of rows and I am trying to pull together just those answers relating to one area (FOSTERING) via an overnight population script and storing this in a table called OCC_FOS_ENQUIRY.

Many of the answers will be missing.

The answers are linked to a FORM and I'm selecting (in the outer query) just the fostering form.

Each sub queries in my solution SELECTs the specific answer using the FORM_ANSWER_TAG_ID.

I would really appreciate your advice on this one.


Steve Phillips, Crystal Trainer/Consultant

 

[SantaMufasa]

Steve,

I re-wrote your code as I would have done it. I believe it is quite a bit tighter. And by using local functions (which I bolded and italicized) to obtain your column data, you have the luxury of having no rows found and returning no data, yet avoiding a runtime error due to no data:

create or replace
PACKAGE BODY OCC_POPULATE AS

-------------------------------------------------------

  procedure occ_main AS
  BEGIN
    dbms_output.enable(1000000);
    occ_populate.occ_foster_carers;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' - Error:' || sqlerrm );
  END occ_main;
[B][I]
--------------------------------------------------------
procedure get_text (id number, tag varchar2) return varchar2 is
        text_hold varchar2(32767);
    begin
        select text_answer into text_hold
          from   vw_form_answers
          where  form_id = id
            and  form_answers_tag_id = tag;
    exception
        when others then
            return null;
    end;
procedure get_date (id number, tag varchar2) return varchar2 is
        date_hold date;
    begin
        select date_answer into date_hold
          from   vw_form_answers
          where  form_id = id
            and  form_answers_tag_id = tag;
    exception
        when others then
            return null;
    end;
--------------------------------------------------------
[/I][/B]
  procedure occ_foster_carers AS

    v_record_count number;
  
  begin
  
    select count(*) 
    into v_record_count 
    from occ_fos_enquiry;
    
    dbms_output.put_line(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') 
        || ' Deleting ['
        || v_record_count
        || '] rows from occ_fos_enquiry');
    
    delete from occ_fos_enquiry;
    commit;
    
    dbms_output.put_line(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') 
        || ' Inserting rows into occ_fos_enquiry');

    insert into occ_fos_enquiry
    (
      enquiry_id
      , app1_person_id
      , app1_first_name
      , app1_last_name
      , app1_address
      , app1_gender
      , app1_dob
      , app2_person_id
      , app2_first_name
      , app2_last_name
      , app2_address
      , app2_gender
      , app2_dob
--    , res_adoption
      , res_age_range
      , res_short_term
      , res_long_term
      , res_fostering_plus
      , res_sibling_groups
      , res_disability_adoption
--    , res_disability_experience
--    , res_disabled_child
      , res_parent_child
--    , res_gender
      , res_number_of_children
      , res_uasc
      , res_family_friends
      , res_relief
      , res_shortbreaks_disabled
      , res_shortbreaks_nondisabled
      , res_mtfcp
      , res_undecided
      , enquiry_date
      , rec_internet
      , rec_word_of_mouth
      , rec_knows_fcarer
      , rec_knows_adopter
      , rec_leaflet
      , rec_tv_radio
      , rec_prev_enquiry
      , rec_cis
      , rec_occ_worker
      , rec_advert
      , rec_community_event
      , rec_fcare_fortnight
      , rec_other_agency
      , rec_other
      , rec_other_specify
    )
    select 
      occ.f_get_workflow_id(ep.id) "enquiry_id"
      , nvl(get_text(f.id,'app1_ID',0)) as "app1_person_id"
      , nvl(get_text(f.id,'app1forename','<null>')) as "app1_first_name"
      , nvl(get_text(f.id,'app1surname','<null>')) as "app1_last_name"
      , nvl(get_text(f.id,'app1address','<null>')) as "app1_address"
      , nvl(get_text(f.id,'app1gender','<null>')) as "app1_gender"
      , get_date(f.id,'app1dob') as "app1_dob"
      , nvl(get_text(f.id,'app2_ID',0)) as "app2_person_id"
      , nvl(get_text(f.id,'app2forename','<null>')) as "app2_first_name"
      , nvl(get_text(f.id,'app2surname','<null>')) as "app2_last_name"
      , nvl(get_text(f.id,'app2address','<null>')) as "app2_address"
      , nvl(get_text(f.id,'app2gender','<null>')) as "app2_gender"
      , get_date(f.id,'app2dob') as "app2_dob"
--    , nvl(get_text(f.id,'adoption','<null>')) as "res_adoption"
      , nvl(get_text(f.id,'agerange','<null>')) as "res_age_range"
      , nvl(get_text(f.id,'shortterm','-')) as "res_short_term"
      , nvl(get_text(f.id,'longterm','-')) as "res_long_term"
      , nvl(get_text(f.id,'fosteringplus','-')) as "res_fostering_plus"
      , nvl(get_text(f.id,'siblinggroups','-')) as "res_sibling_groups"
      , nvl(get_text(f.id,'disabilityadoption','-')) as "res_disability_adoption"
--    , nvl(get_text(f.id,'disabilityexperience','-')) as "res_disability_experience"
--    , nvl(get_text(f.id,'disabledchild','-')) as "res_disabled_child"
      , nvl(get_text(f.id,'parentchild','-')) as "res_parent_child"
--    , nvl(get_text(f.id,'gender','-')) as "res_gender"
      , nvl(get_text(f.id,'numberofchildren','-')) as "res_number_of_children"
      , nvl(get_text(f.id,'uasc','-')) as "res_uasc"
      , nvl(get_text(f.id,'familyfriends','-')) as "res_family_friends"
      , nvl(get_text(f.id,'relief','-')) as "res_relief"
      , nvl(get_text(f.id,'shortbreaks_disabled','-')) as "res_shortbreaks_disabled"
      , nvl(get_text(f.id,'shortbreaks_nondisabled','-')) as "res_shortbreaks_nondisabled"
      , nvl(get_text(f.id,'mtfcp','-')) as "res_mtfcp"
      , nvl(get_text(f.id,'undecided','-')) as "res_undecided"
      , get_date(f.id,'enquiry_date') as "enquiry_date"
      , nvl(get_text(f.id,'B508E5F9-790F-B34B-58C9-34A5C2BB8C03','-')) as "rec_internet"
      , nvl(get_text(f.id,'%','00F83334-92CC-419D-A881-759B09D97E6A')) as "rec_word_of_mouth"
      , nvl(get_text(f.id,'6218B8B0-BA28-94F0-AF0C-F3E90B46A43C','-')) as "rec_knows_fcarer"
      , nvl(get_text(f.id,'7A8903F2-2454-05EE-4C73-B2366919677A','-')) as "rec_knows_adopter"
      , nvl(get_text(f.id,'E67D22CD-39DF-5906-5687-ADF8F2FDB73A','-')) as "rec_leaflet"
      , nvl(get_text(f.id,'8B0DC0E0-C84A-E03F-0D50-0E9566AED630','-')) as "rec_tv_radio"
      , nvl(get_text(f.id,'A4B4E24C-659E-F6B7-3529-FA6D564B9B7C','-')) as "rec_prev_enquiry"
      , nvl(get_text(f.id,'EA1FE816-97BE-BC16-A120-CDB1E7408798','-')) as "rec_cis"
      , nvl(get_text(f.id,'CB2BA87E-C304-2BB4-3DD8-0B407E6A0B70','-')) as "rec_occ_worker"
      , nvl(get_text(f.id,'F68EBD5D-5331-A237-C9BB-F5FDA912E342','-')) as "rec_advert"
      , nvl(get_text(f.id,'92D1966B-0CA4-3488-09C4-B8CC16149345','-')) as "rec_community_event"
      , nvl(get_text(f.id,'C978C551-A615-0079-D76F-DF9E6B4BD35C','-')) as "rec_fcare_fortnight"
      , nvl(get_text(f.id,'DFEC409C-93FB-D87D-C579-3382BA770E90','-')) as "rec_other_agency"
      , nvl(get_text(f.id,'850E3275-C965-751A-A57C-5D951A99E0BF','-')) as "rec_other"
      , nvl(get_text(f.id,'6D3B129B-F090-7AB1-1FAD-E0DE8CF55588','-')) as "ec_other_specify"
    from 
      episodes ep
      -- JOIN: Forms
      inner join forms f
      on f.episode_id = ep.id
        -- JOIN: Form types
      inner join form_types ft
      on f.form_type_id = ft.id
    where
      ft.id_code = 'FORM1154968748000';  -- Foster Carer or Adopter Enquiry Form

    select count(*) 
    into v_record_count 
    from occ_fos_enquiry;
    
    dbms_output.put_line(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') 
        || ' Successfully inserted ['
        || v_record_count 
        || '] rows into occ_fos_enquiry');

    dbms_output.put_line(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') 
        || ' Updating occ_fos_enquiry with closed enquiry details...started');

    dbms_output.put_line(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') 
        || ' Updating occ_fos_enquiry with closed enquiry details...completed');
      
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' - Error:' || sqlerrm);
    
  END occ_foster_carers;

-----------------------------------------------

END OCC_POPULATE;

I could have alternately created "over loaded" procedures to accomplish this task, but this method is, perhaps, more straightforward and easy to understand (if you have not seen/used overloaded procedures before).

Let us know how you like this approach. Also, let us know if this eliminates the ORA-1403 errors.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”