what looping chain of synonyms means?

Question

I am trying to write a trigger when I am receiving this error, it the first I encounter such error, and none of my searches lead to my case, kindly check this is the trigger :

create or replace trigger TR_new_student    
for insert on Students
compound trigger
    IDs                 students.ID%type;
    ID_user             students.ID_u%type;
    password            users.password%type;
    cand_m1             master.ID_master%type;
    cand_m2             master.ID_master%type;
    cand_m3             master.ID_master%type;
    cand_password_m1    users.password%tyPe;
    cand_password_m2    users.password%type;
    cand_password_m3    users.password%type;
    cand_password_m4    users.password%type;
    username            users.username%type;
    type class_type     is TABLE OF class%rowtype index by PLS_integer;
    class_tab           class_type;
    part1 varchar2(12);
before each row 
is
begin
    :new.classes_accepte:='N';
    :new.n_download:=0;
    :new.n_msg_adm:=0;
    :new.n_msg_res:=0;
    :new.date_last_download:=sysdate;
    :new.date_last_m_a:=sysdate;
    :new.date_last_m_r:=sysdate;
    :new.year_insc:=get_annee_scolaire;
    :new.current_year:=get_annee_scolaire;
    select ID_M1,ID_M2,ID_M3,password_m1,password_m2,password_m3,password_m4
    into cand_m1,cand_m2,cand_m3,cand_password_m1,cand_password_m2,cand_password_m3,cand_password_m4
    from candidate c
    where c.ID_c=:new.ID_c and c.year=:new.year_insc;
    password :=
                CASE 
                        when cand_M1=:new.current_master    then    cand_password_m1
                        when cand_M2=:new.current_master    then    cand_password_m2
                        when cand_M3=:new.current_master    then    cand_password_m3
                        else cand_password_m4
                end;
    if password != :new.first_pass then raise_application_error(-20003,'Le mot de passe n''est pas valide');
    end if;
    SELECT SUBSTR(:new.Fname, 1, 12) into part1 FROM DUAL;
    username:=part1||TO_CHAR(:new.ID);
    ID_user:=ID_u_seq.currval;
    :new.ID_u:=ID_user;
    ID_s:=ID_seq.nextval;
    :new.ID:=ID_s;
    insert          
    into users 
    values (ID_user,username,password,1);

    insert 
    into inscriptions (year,date_insc,old_new,ID,master_ID,succeeded,password)
    values (:new.year_insc,sysdate,'N',:new.ID,:new.current_master,:new.current_master,'P',password);
    select *
    BULK COLLECT INTO   class_tab   
    from class d
    where d.ID_master=:new.current_master and d.optionnel='N' and d.available='Y';

    forall i in indices of class_tab
    insert into Grades
    values (class_tab(i).ID_class,:new.current_master,:new.ID,:new.year_insc,class_tab(i).graded,class_tab(i).credit,class_tab(i).semestre,class_tab(i).partial_flag,class_tab(i).final_1_flag,class_tab(i).project_flag,class_tab(i).final_2_flag,class_tab(i).partial_percent,class_tab(i).final_percent,class_tab(i).project_percent,'N','N','N','N','N',0,0,0,0,0,sysdate,:new.ID_u,'P',class_tab(i).description);
end before each row;
after statement 
is  
begin
    execute immediate 'Create or replace view vue_stu'||IDs||'
                    as select province,circonscription,city,region,fam_status,cur_province,cur_cir,cur_region,cur_city,street,property,floor,phone,picture
                    from students
                    where ID_u='||ID_user||'';
    execute immediate 'Grant update on vue_stu'||IDs||' to C##'||username||'';
end after statement;
end TR_new_student;
/

and this is the error I am receiving :
ORA-01775: looping chain of synonyms

can anyone tell me the possible source of this error or how I can fix it ? thank you in advance :)

See this thread to see if it pertains. Sounds like a circular reference of synonyms: http://stackoverflow.com/questions/11530043/ora-01775-looping-chain-of-synonyms — EdgeCase, Jan 02 '14 at 19:25
I tried to understand how the problem was resolved in your thread but I couldn't. I guess it is because I lack experience in oracle, can you please assist me or give me some steps o details that I can follow to solve this ? — El GaGa, Jan 03 '14 at 06:38

score 1 · Answer 1 · answered Jan 04 '14 at 03:50

1

You should execute the following statement before compiling the trigger:

alter session set events '1775 trace name errorstack level 3;

Then compile the trigger. This will create a trace file. Provide a link to the created trace file.

answered Jan 04 '14 at 03:50

steve

5,870
1
21
22

what looping chain of synonyms means?

1 Answers1