代码之家 › 专栏 › 技术社区 › Josh E

plsql包上奇怪的同义词循环链错误

oracle-sqldeveloper plsql oracle

Josh E · 技术社区 · 8 年前

我无法编译这个包体,因为我得到了“同义词循环链”

我甚至没有提到任何同义词

我已经检查了表dba\u同义词,在select from中没有任何引用该表的内容

更具体地说,当我注释掉游标选择部分时,它编译得很好

这是在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;

2 回复 | 直到 8 年前

ishando 8 年前

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

我猜你真的想要更像

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

William Robertson 8 年前

像 ORA-01775: looping chain of synonyms 是在删除基础对象后尝试引用公共同义词的标准Oracle错误(并且对象和同义词具有相同的名称,因此现在唯一保留该名称的对象是同义词本身),我将首先查找这些。以下查询是否返回任何可能的结果?

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' 是基于问题的尝试和错误,但似乎是什么区别 ORA-00980: synonym translation is no longer valid 从…起 ORA-01775:同义词循环链 在某些边缘情况下,我不太理解。)

顺便说一句,我对此有点困惑:

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')