weird looping chain of synonyms error on plsql package

Question

i can't compile this package body because i get "looping chain of synonyms"

i don't even reference any synonyms

i have checked the table dba_synonyms, there is nothing referencing the table in the select from

to be more specific, when i comment out the cursor select portion, it compiles fine

if you notice below, i am again selecting from the same table, only for count(*), this select works fine, but not the previous one

this is on oracle 12c

create or replace
package body dm_mnmi2407_pkg
is  
    procedure create_appl_process_log(
        l_user_err_message_capl in varchar2,
        process_status_code_capl in varchar2,
        linecnt_capl in int
    )
    is
        l_action_taken      varchar2(50) := 'DICE Provider Demographic Feed To BRIGHTER';
        l_module_name       varchar2(20) := 'dm_mnmi2407.sqr';
        l_p_key             int := 1;
        l_p_error           char := ' ';
    begin
        dm_common_pkg.application_process_log(  l_action_taken,
                                                l_module_name,
                                                process_status_code_capl,
                                                NULL,
                                                NULL,
                                                l_user_err_message_capl,
                                                linecnt_capl,
                                                NULL,
                                                NULL,
                                                l_p_key,
                                                l_p_error
                                            );
    end create_appl_process_log;

    procedure write_records(
        full_feed_flag_wr in char,
        last_run_tmstmp_wr in date,
        process_start_tmstmp_wr in date,
        output_dir_wr in varchar2
        )
    is
        out_file        utl_file.file_type;
        out_filename    varchar2(70) := 'cdhprovdem' || '_' || process_start_tmstmp_wr ||'.dat';
        header_record   varchar2(20) := 'H, ' || process_start_tmstmp_wr;
        trailer_record  varchar2(20);

        cursor recordset is
            select  prov_donas_id as a
                    ,replace(replace(prov_id,',',' '),' ',' ') as b
                    ,lpad(prov_type1_npi,10,'0') as c
                    ,to_char(prov_eff_date,'YYYYMMDD') as d
                    ,replace(replace(prov_fee_sched,',',' '),' ',' ') as e
                    ,dppo_advantage_network_ind as f
                    ,donas_primary_fee_sched_ind as g
                    ,to_char(prov_fee_sched_eff_date,'YYYYMMDD') as h
                    ,to_char(prov_fee_sched_inactive_date,'YYYYMMDD') as i
                    ,to_char(prov_inactive_date,'YYYYMMDD') as j
                    ,prov_type as k
                    ,replace(replace(prov_title_code,',',' '),' ',' ') as l
                    ,replace(replace(prov_first_name,',',' '),' ',' ') as m
                    ,replace(replace(prov_middle_initial,',',' '),' ',' ') as n
                    ,replace(replace(prov_last_name,',',' '),' ',' ') as o
                    ,replace(replace(prov_speclty_code,',',' '),' ',' ') as p
                    ,prov_board_cert_speclty_flag as q
                    ,Prov_board_elig_speclty_flag as r
                    ,prov_donas_license_num as s
                    ,replace(replace(prov_donas_license_state_code,',',' '),' ',' ') as t
                    ,to_char(prov_experience_start_date,'YYYYMMDD') as u
                    ,to_char(prov_birth_date,'YYYYMMDD') as v
                    ,nvl(rpad(prov_foreign_lang_string,50,' '),'') as w
                    ,prov_gender as x
                    ,replace(replace(prov_dental_school_name,',',' '),' ',' ') as y
                    ,prov_primary_license_num as z
                    ,replace(replace(prov_primary_license_state_cd,',',' '),' ',' ') as aa
                    ,prov_accepting_new_pats_flag as bb
                    ,rpad(lpad(prov_type2_npi_1,10,'0')||' '||lpad(prov_type2_npi_2,10,'0')||' '|| lpad(prov_type2_npi_3,10,'0')||' '||lpad(prov_type2_npi_4,10,'0')||' '|| lpad(prov_type2_npi_5,10,'0')||' '||lpad(prov_type2_npi_6,10,'0')||' '|| lpad(prov_type2_npi_7,10,'0')||' '||lpad(prov_type2_npi_8,10,'0')||' '|| lpad(prov_type2_npi_9,10,'0')||' '||lpad(prov_type2_npi_10,10,'0')||' '|| lpad(prov_type2_npi_11,10,'0')||' '||lpad(prov_type2_npi_12,10,'0')||' '|| lpad(prov_type2_npi_13,10,'0')||' '||lpad(prov_type2_npi_14,10,'0')||' '|| lpad(prov_type2_npi_15,10,'0'),164,' ') as cc
                    ,dental_ofc_id as kk
                    ,lpad(replace(replace(dental_ofc_tax_id,',',' '),' ',' '),9,'0') as ll
                    ,replace(replace(dental_ofc_group_name,',',' '),' ',' ') as mm
                    ,replace(replace(dental_ofc_loc_addr_1,',',' '),' ',' ') as nn
                    ,replace(replace(dental_ofc_loc_addr_2,',',' '),' ',' ') as oo
                    ,replace(replace(dental_ofc_loc_city,',',' '),' ',' ') as pp
                    ,replace(replace(dental_ofc_loc_zip_county_name,',',' '),' ',' ') as qq
                    ,replace(replace(dental_ofc_loc_state_code,',',' '),' ',' ') as rr
                    ,replace(replace(dental_ofc_loc_zip_code,',',' '),' ',' ') as ss
                    ,replace(replace(dental_ofc_loc_zip_plus_4,',',' '),' ',' ') as tt
                    ,dental_ofc_phone_num as uu
                    ,dental_ofc_elec_remit_flag as vv
                    ,nvl(rpad(dental_ofc_foreign_lang_string,50,' '),'') as ww
                    ,dental_ofc_handicap_accss_flag as xx
                    ,replace(replace(dental_ofc_pmt_addr_1,',',' '),' ',' ') as yy
                    ,replace(replace(dental_ofc_pmt_addr_2,',',' '),' ',' ') as zz
                    ,replace(replace(dental_ofc_pmt_city,',',' '),' ',' ') as aaa
                    ,replace(replace(dental_ofc_pmt_zip_county_name,',',' '),' ',' ') as bbb
                    ,replace(replace(dental_ofc_pmt_name,',',' '),' ',' ') as ccc
                    ,replace(replace(dental_ofc_pmt_state_code,',',' '),' ',' ') as ddd
                    ,replace(replace(dental_ofc_pmt_zip_code,',',' '),' ',' ') as eee
                    ,replace(replace(dental_ofc_pmt_zip_plus_4,',',' '),' ',' ') as fff
                    ,replace(replace(geo_area_code,',',' '),' ',' ') as ggg
                    ,DECODE(full_feed_flag_wr,'Y','A',last_action_ind) as hhh
            from    EXT_DICE_PROV_DEMOGRAPHIC_FEED
            where   (full_feed_flag_wr = 'Y' OR (z_update_tmstmp between last_run_tmstmp_wr and process_start_tmstmp_wr))
            and     (full_feed_flag_wr = 'N' OR last_action_ind !='D')
      ;
    begin

        out_file := utl_file.fopen(output_dir_wr,out_filename,'w');

        --write header record
        utl_file.put(out_file, header_record);

        --write detail records
        for detail_record in recordset
        loop
            detail_record :=
                a || ',' || 
                b || ',' || 
                c || ',' || 
                d || ',' || 
                e || ',' || 
                f || ',' || 
                g || ',' || 
                h || ',' || 
                i || ',' || 
                g || ',' || 
                k || ',' || 
                l || ',' || 
                m || ',' || 
                n || ',' || 
                o || ',' || 
                p || ',' || 
                q || ',' || 
                r || ',' || 
                s || ',' || 
                t || ',' || 
                u || ',' || 
                v || ',' || 
                w || ',' || 
                x || ',' || 
                y || ',' || 
                z || ',' || 
                aa || ',' || 
                bb || ',' || 
                cc || ',' || 
                kk || ',' || 
                ll || ',' || 
                mm || ',' || 
                nn || ',' || 
                oo || ',' || 
                pp || ',' || 
                qq || ',' || 
                rr || ',' || 
                ss || ',' || 
                tt || ',' || 
                uu || ',' || 
                vv || ',' || 
                ww || ',' || 
                xx || ',' || 
                yy || ',' || 
                zz || ',' || 
                aaa || ',' || 
                bbb || ',' || 
                ccc || ',' || 
                ddd || ',' || 
                eee || ',' || 
                fff || ',' || 
                ggg || ',' || 
                hhh || ',' || 
        utl_file.put_line (out_file, detail_record);
        END LOOP;

        --write trailer record
        select  count(*)
        into    linecnt
        from    EXT_DICE_PROV_DEMOGRAPHIC_FEED
        where   (full_feed_flag_wr = 'Y' OR (z_update_tmstmp between last_run_tmstmp_wr and process_start_tmstmp_wr))
        and     (full_feed_flag_wr = 'N' OR last_action_ind !='D');

        trailer_record := 'T, ' || linecnt;
        utl_file.put_line(out_file,trailer_record);

        --close file
        utl_file.fclose(out_file);

    end write_records;

    procedure create_output_file(
        full_feed_flag in char,
        last_run_tmstmp in date,
        process_start_tmstmp in date,
        output_dir in varchar2
        )
    is
        l_user_err_message  varchar2(40);
        process_status_code varchar2(5);      
    begin
        l_user_err_message  := 'Brighter Provider Feed started...';
        process_status_code := 'M';
        linecnt := 0;
        create_appl_process_log(l_user_err_message, process_status_code, linecnt);

        write_records(full_feed_flag,last_run_tmstmp,process_start_tmstmp, output_dir);

        l_user_err_message  := 'Brighter Provider Feed Completed';
        process_status_code := 'M';
        create_appl_process_log(l_user_err_message, process_status_code, linecnt);
    end create_output_file; 

end dm_mnmi2407_pkg;
/
show errors;

Possible duplicate of [How to debug ORA-01775: looping chain of synonyms?](https://stackoverflow.com/questions/247090/how-to-debug-ora-01775-looping-chain-of-synonyms) — Kaushik Nayak, Nov 14 '17 at 19:00
First thing I'd do is add aliases to all the columns and variables referred to in the SQL query. At the moment it's not explicit whether the query is referring to variables in the PL/SQL code, or columns from the table. — Jeffrey Kemp, Nov 15 '17 at 05:45

score 2 · Accepted Answer · answered Nov 15 '17 at 09:39

This bit doesn't look right, are you really trying to redefine your cursor iterator value?

...
    --write detail records
    for detail_record in recordset
    loop
        detail_record :=
            a || ',' || 
            b || ',' || 
            c || ',' ||
...

I'm guessing that you really want something more like

procedure write_records ...
is
  detail_record varchar2(32000);
  ...
begin
  ...
  --write detail records
  for dr in in recordset loop
    detail_record := dr.a || ',' || dr.b || ',' ....

yeah, the problem was with the for loop referencing itself – Josh E Nov 15 '17 at 21:07 — Josh E, Nov 15 '17 at 21:07

William Robertson · Answer 2 · 2017-11-15T19:42:35.013

As ORA-01775: looping chain of synonyms is the standard Oracle error for an attempt to reference a public synonym after dropping the underlying object (and the object and synonym had the same name, so now the only object left with that name is the synonym itself), I would look for those first. Does the following query return anything likely?

select s.table_owner, s.synonym_name
from   all_synonyms s
       join dba_objects o
            on  o.owner= s.owner
            and o.object_name = s.synonym_name
where  s.owner = 'PUBLIC'
and    s.table_owner <> 'SYS'
and    s.table_name = s.synonym_name
and    s.synonym_name not like '%/%'
and    o.object_type = 'SYNONYM'
and    o.sharing = 'NONE'
minus
select o.owner, o.object_name
from   dba_objects o
where  o.subobject_name is null;

(dba_objects.sharing = 'NONE' is quesswork based trial and error, but seems to be what distinguishes ORA-00980: synonym translation is no longer valid from ORA-01775: looping chain of synonyms in some edge cases I don't quite understand.)

By the way, I'm a bit puzzled by this:

where   (full_feed_flag_wr = 'Y' OR (z_update_tmstmp between last_run_tmstmp_wr and process_start_tmstmp_wr))
and     (full_feed_flag_wr = 'N' OR last_action_ind !='D')

I'm trying to work out what combination of factors are needed for those both to be true. I think it's possible but thinking about it is making my head hurt.

weird looping chain of synonyms error on plsql package

2 Answers2