title: How to find out PLSQL Compilation errors
body: <pre><code>create or replace procedure address_insert 
  as
  
  CREATE type colorarray is varray(10) of varchar2(10);
  CREATE type cities is varray(6) of varchar2(20);
  CREATE type states is varray(6) of varchar2(15);
  CREATE type zipcodes is varray(6) of number(10);
  CREATE type countries is varray(6) of varchar2(15);
  
  city cities;
  Colour colorarray;
  zip zipcodes;
  state states;
  country countries;
  id1 number;
  x number;
  ca number;
  r number;
  
  begin
  x:=1;
  ca:=1;
  id1:=1;
  r:=1;
  city:=cities('Visakhapatnam','Hyderabad','Bangalore','Chennai','Kurnool','secunderabad');
  colour :=colorarray('Red', 'Blue', 'green', 'Dark blue', 'yellow', 'orange', 'brown', 'black', 'white', 'purple');
  state:=states('Telangana','Tamilnadu','Karnataka','Andhra Pradesh','Madya Pradesh','Kerala');
  zip:=zipcodes(530081,500072,316190,981272,717999,621896);
  country:=countries('India','Nepal','Pakistan','USA','Bangladesh','UK');
  
  while x&lt;(select count(persons_id) from person_data) loop
  
  if ca&gt;10 then
  ca:=1;
  end if;
  if r&gt;6 then
  r:=1;
  end if;
  
  insert into persons_addresses(Address_id,Persons_id,flatname,flatno,house_color,contact_person,address_line1,address_line2,address_line3,
  city,district,state,zipcode,country) values
  (id1,(select persons_id from (select persons_id,row_number()over (order by persons_id) as rn from person_data)tmp where rn=x),
  (SELECT dbms_random.string('L', 15) from dual),(SELECT round(dbms_random.value(100,1000)) num FROM dual),colour(ca),
  (SELECT dbms_random.string('L', 5)|| ' ' ||dbms_random.string('L', 7) from dual),
  (SELECT dbms_random.string('L', 9)|| ' ' ||dbms_random.string('L', 6)|| ' ' ||dbms_random.string('L', 8)|| ' ' ||dbms_random.string('L', 10) 
  FROM dual),(SELECT dbms_random.string('L', 9)|| ' ' ||dbms_random.string('L', 6)|| ' ' ||
  dbms_random.string('L', 8)|| ' ' ||dbms_random.string('L', 10) FROM dual),(SELECT dbms_random.string('L', 9)|| ' ' ||
  dbms_random.string('L', 6)|| ' ' ||dbms_random.string('L', 8)|| ' ' ||dbms_random.string('L', 10) FROM dual),city(r),(SELECT dbms_random.string('L', 9) from dual),
  state(r),zip(r),country(r));
  commit;
  id1:=id1+1;
  ca:=ca+1;
  r:=r+1;
  x:=x+1;
  end loop;
  EXCEPTION  -- exception handlers begin
    WHEN OTHERS THEN  -- handles all other errors
            DBMS_OUTPUT.PUT_LINE (SQLCODE|| ' ' || SQLERRM);
  end;
  </code></pre>
  
  <p>There is a compilation warning.</p>
  
  <blockquote>
    <p>Warning: execution completed with warning procedure address_insert
    Compiled.</p>
  </blockquote>
  
  <p>when executed:</p>
  
  <pre><code>execute address_insert
  </code></pre>
  
  <blockquote>
    <p>Error starting at line 1 in command: execute address_insert Error
    report: ORA-06550: line 1, column 7: PLS-00905: object
    DATAFOCUS_GROUP.ADDRESS_INSERT is invalid ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    06550. 00000 -  "line %s, column %s:\n%s"
    *Cause:    Usually a PL/SQL compilation error.
    *Action:</p>
  </blockquote>
  
  <p>Is there any way we can find out the compilation errors? </p>
  
  <pre><code>SELECT *
    FROM USER_ERRORS
    WHERE NAME = 'ADDRESS_INSERT'
  </code></pre>
  
  <p>The above query helped retrieve the errors.</p>
  
  <p>Errors:</p>
  
  <blockquote>
    <p>"PLS-00103: Encountered the symbol "CREATE" when expecting one of the
    following:</p>
    
    <p>begin function pragma procedure subtype type      current cursor delete    exists
    prior external language The symbol "CREATE" was ignored. "
    the above error have been encountered due to varray definition.
    Is there any alternative?</p>
  </blockquote>
  
  <p>other errors:</p>
  
  <blockquote>
    <p>"PLS-00103: Encountered the symbol "SELECT" when expecting one of the
    following:</p>
    
    <p>( - + case mod new not null          continue avg count current
    exists max min prior sql stddev    sum variance execute forall merge
    time timestamp interval    date       pipe     
    
    
    <p>, from "</p>
  </blockquote>