title: I get an ORA-01775: looping chain of synonyms error when I use sqlldr
body: <p>I apologize for posting a question that seems to have been asked numerous times on the internet, but I can't quite fix it for some reason.</p>
  
  <p>I was trying to populate some tables using Oracle's magical <code>sqldr</code> utility, but it throws an ORA-01775 error for some reason.</p>
  
  <p>Everywhere I go on Google, people say something along the lines of: "Amateur, get your synonyms sorted out" (that was paraphrased) and that's nice and all, but I did not make any synonyms.</p>
  
  <p>Here, the following does not work on my system:</p>
  
  <pre><code>SQLPLUS user/password
  SQL&gt;CREATE TABLE test (name varchar(10), id number);
  SQL&gt;exit
  </code></pre>
  
  <p>Then, I have a <code>.ctl</code> file with the following contents:</p>
  
  <pre><code>load data
      characterset utf16
      infile *
      append
      into table test
      (name,
       id
      )
      begindata
      "GURRR"  4567
  </code></pre>
  
  <p>Then I run this command:</p>
  
  <pre><code>sqlldr user@localhost/password control=/tmp/controlfiles/test.ctl
  </code></pre>
  
  <p>The result:</p>
  
  <pre><code>SQL*Loader-702: Internal error - ulndotvcol: OCIStmtExecute()
  ORA-01775: looping chain of synonyms
  </code></pre>
  
  <p>Part of test.log:</p>
  
  <pre><code>Table TEST, loaded from every logical record.
  Insert option in effect for this table: APPEND
  
  Column Name                  Position   Len  Term Encl Datatype
  ------------------------------ ---------- ----- ---- ---- ---------------------
  NAME                                FIRST     2           CHARACTER            
  ID                                   NEXT     2           CHARACTER            
  
  SQL*Loader-702: Internal error - ulndotvcol: OCIStmtExecute()
  ORA-01775: looping chain of synonyms     
  </code></pre>
  
  <p>And, if I try to do a manual insert:</p>
  
  <pre><code>SQL&gt; insert into test values ('aa', 56);
  1 row created.
  </code></pre>
  
  <p>There is no problem.</p>
  
  <p>So, yeah, I am stuck!</p>
  
  <p>If it helps, I am using Oracle 11g XE on CentOS. </p>
  
  <p>Thanks for the help guys, I appreciate it. </p>
  
  <p>EDIT:</p>
  
  <p>I kind of, sort of figured out part of the problem. The problem was that somewhere along the line, maybe during a failed load or something, Oracle had given itself corrupt views and synonyms. </p>
  
  <p>The affected views were: <code>GV_$LOADISTAT</code>, <code>GV_$LOADPSTAT</code>, <code>V_$LOADISTAT</code> and <code>V_$LOADPSTAT</code>. I am not quite sure why the views got corrupt, but recompiling them resulted in <code>compiled with errors</code>errors. The synonyms used in the queries themselves were corrupt, namely the <code>gv$loadistat</code>, <code>gv$loadpstat</code>, <code>v$loadistat</code> and <code>v$loadpstat</code> synonyms. </p>
  
  <p>I wasn't sure about why this was happening and I didn't quite understand anything. So, I decided to drop the synonyms and recreate them. Unfortunately, I couldn't recreate them, as the view they pointed to (there is a bit of weird recursion going on here...) was corrupt. These views were the aforementioned <code>GV_$LOADISTAT</code> and other views. In other words, the synonyms pointed to the views that used those synonyms. Talk about a looping chain.</p>
  
  <p>So...I recreated the public synonyms but instead of specifying the view as <code>GV_$LOADISTAT</code>, I specified them as <code>sys.GV_$LOADISTAT</code>. e.g.</p>
  
  <pre><code>DROP PUBLIC synonym GV$LOADISTAT;
  CREATE PUBLIC synonym GV$LOADISTAT for sys.GV_$LOADISTAT;
  </code></pre>
  
  <p>Then, I recreated the user views to point to those public synonyms.</p>
  
  <pre><code>CREATE OR REPLACE FORCE VIEW "USER"."GV_$LOADISTAT" ("INST_ID", "OWNER", "TABNAME", "INDEXNAME", "SUBNAME", "MESSAGE_NUM", "MESSAGE")
   AS
   SELECT "INST_ID",
     "OWNER",
     "TABNAME",
     "INDEXNAME",
     "SUBNAME",
     "MESSAGE_NUM",
     "MESSAGE"
   FROM gv$loadistat;
  </code></pre>
  
  <p>That seemed to fix the views/synonyms. Yeah, it is a bit of a hack, but it somehow worked. Unfortunately, this was not enough to run SQL Loader. I got a <code>table or view does not exist</code> error. </p>
  
  <p>I tried granting more permissions to my regular user, but it didn't work. So, I gave up and ran SQL Loader as sysdba. It worked! It is not a good thing to do, but it is a development only system made for testing purposes, so, I didn't care.</p>