title: Function Created with compilation errors in Oracle with table / view doesn't exist
body: <p>I have created a function which is working on my local but giving compilation error while creating on specific user and schema.</p>
  
  <p>I have verified the tables and this function executed in my local machine and returning the values. but when I tried to log in to other user and tries to create the function, it's giving compilation error.</p>
  
  <pre><code>`/*Get_Accountdetails Functions*/
   create or replace function Get_Accountdetails(inpstr1 in 
   varchar2,inpstr2 in varchar2) return get_acnt_type
   as
  v_ret   get_acnt_type;   
  a  dbms_utility.uncl_array;
  b  dbms_utility.uncl_array; 
  len1  pls_integer;  
  len2  pls_integer;  
  cnt pls_integer :=1;
  inp_str1 varchar(32000) := regexp_replace(inpstr1,'[][]','"');
  inp_str2 varchar(32000) := regexp_replace(inpstr2,'[][]','"');
  inp_str3 varchar(32000) := replace(inp_str2,'"."','","');
  inp_val1 varchar(320):= '';
  inp_val2 varchar(320):= '';
  out_acval1 varchar(320):= '';
  out_aclval2 varchar(320):= '';
  out_dbval3 varchar(320):= '';
  v_aid varchar2(10);
  v_db_id  varchar2(10);
  v_lvl_id varchar2(10);
  sa_user_code  varchar2(100);    
  count1 pls_integer;  
  
  begin
  v_ret  := get_acnt_type();     
  dbms_utility.comma_to_table(inp_str1, len1, a); 
  dbms_utility.comma_to_table(inp_str3, len2, b);
  
  for j in 1..len2/2 loop
      sa_user_code := replace(b(cnt),'"','');  
      for i in 1..len1 loop                                 
          inp_val1 := a(i);                               
          v_aid := regexp_substr(inp_val1, '\d+', 1, 1);
          v_db_id  := regexp_substr(inp_val1, '\d+', 1, 2);
          v_lvl_id := regexp_substr(inp_val1, '\d+', 1, 3);
  
          IF v_lvl_id = '1' THEN
              SELECT COUNT(*)into count1
              from gdw.dim_cust_acnt                      
              where RCL_LVL_1_ACNT_ID = v_aid and RCL_SRC_DB_ID = v_db_id AND ROWNUM=1;       
  
          ELSE IF v_lvl_id = '2' THEN
              SELECT COUNT(*)into count1
              from gdw.dim_cust_DEPT                      
              where RCL_LVL_2_ACNT_ID = v_aid and RCL_SRC_DB_ID = v_db_id AND ROWNUM=1; 
          ELSE
              SELECT COUNT(*)into count1
              from gdw.dim_cust_dept                      
              where RCL_LVL_3_ACNT_ID = v_aid and RCL_SRC_DB_ID = v_db_id AND ROWNUM=1;
          END IF;
          END IF;
  
          IF count1 &gt; 0 THEN
              IF(UPPER (sa_user_code) = 'SA') THEN                           
                  CASE v_lvl_id
  
                      when 1 then --If Level 1 then check GDW.Dim_Cust_Acnt                     
                      select dim_cust_key,RCL_LVL_1_ACNT_ID,RCL_SRC_DB_ID into out_acval1,out_aclval2,out_dbval3 from gdw.dim_cust_acnt                                   
                      where RCL_LVL_1_ACNT_ID = v_aid and RCL_SRC_DB_ID = v_db_id AND ROWNUM=1;
  
                      when 2 then  --If Level 2  then check GDW.Dim_Cust_Dept
                      select dim_cust_dept_key,RCL_LVL_2_ACNT_ID,RCL_SRC_DB_ID into out_acval1,out_aclval2,out_dbval3 from gdw.dim_cust_dept                   
                      where RCL_LVL_2_ACNT_ID = v_aid and RCL_SRC_DB_ID = v_db_id AND ROWNUM=1;
  
                      else --If Level 3 then check GDW.Dim_Cust_Dept
                       select dim_cust_dept_key,RCL_LVL_3_ACNT_ID,RCL_SRC_DB_ID into out_acval1,out_aclval2,out_dbval3 from gdw.dim_cust_dept 
                       where RCL_LVL_3_ACNT_ID = v_aid and RCL_SRC_DB_ID = v_db_id AND ROWNUM=1;
  
                  END CASE; 
  
                  v_ret.extend; 
                  v_ret(v_ret.count) := Get_acnt_obj(out_acval1,out_aclval2,out_dbval3,v_lvl_id);
  
              ELSE
                  CASE v_lvl_id--If Level 1 then check GDW.Dim_Cust_Acnt
                      when 1 then 
                          select dca.dim_cust_key,dca.RCL_LVL_1_ACNT_ID,dca.RCL_SRC_DB_ID into out_acval1,out_aclval2,out_dbval3 from gdw.dim_cust_acnt dca
                          INNER JOIN bi_rpt.dtl_usr_acnt_scrty fc ON fc.SRC_DB_ID =dca.RCL_SRC_DB_ID 
                          AND fc.acnt_cd=dca.CUST_ID
                          where dca.RCL_LVL_1_ACNT_ID = v_aid and dca.RCL_SRC_DB_ID = v_db_id and fc.SRC_USR_CD=sa_user_code AND ROWNUM=1;          
  
                      when 2 then --If Level 3 then check GDW.Dim_Cust_Dept
                          select dca.dim_cust_dept_key,dca.RCL_LVL_2_ACNT_ID,dca.RCL_SRC_DB_ID into out_acval1,out_aclval2,out_dbval3 from gdw.dim_cust_dept dca                              
                          INNER JOIN bi_rpt.dtl_usr_acnt_scrty fc  ON fc.SRC_DB_ID =dca.RCL_SRC_DB_ID 
                          AND fc.ACNT_LVL_1_CD=dca.CUST_DIV_CD
                          where dca.RCL_LVL_2_ACNT_ID = v_aid and dca.RCL_SRC_DB_ID = v_db_id and fc.SRC_USR_CD=sa_user_code AND ROWNUM=1;                    
                      else --If Level  3 then check GDW.Dim_Cust_Dept
                          select dca.dim_cust_dept_key,dca.RCL_LVL_3_ACNT_ID,dca.RCL_SRC_DB_ID into out_acval1,out_aclval2,out_dbval3 from gdw.dim_cust_dept DCA                             
                          INNER JOIN bi_rpt.dtl_usr_acnt_scrty fc  ON fc.SRC_DB_ID =dca.RCL_SRC_DB_ID 
                          AND fc.ACNT_LVL_2_CD=dca.CUST_DEPT_CD
                          where dca.RCL_LVL_3_ACNT_ID = v_aid and dca.RCL_SRC_DB_ID = v_db_id and fc.SRC_USR_CD=sa_user_code AND ROWNUM=1;                      
                  END CASE; 
                  v_ret.extend; 
                  v_ret(v_ret.count) := Get_acnt_obj(out_acval1,out_aclval2,out_dbval3,v_lvl_id);
  
              End IF;
  
          ELSE
              CONTINUE;
          END IF;     
      END LOOP; 
      cnt := cnt+2;       
  END LOOP;
  return v_ret;
  END;
   /
  </code></pre>
  
  <p>object Type which I have created in working fine.</p>
  
  <pre><code>/
  /* Define the structure of the object that is returned from function 
  Get_AccountDetails*/
  create or replace type Get_acnt_obj as object (
  
   dim_cust_key number,
  
   level_account_id number,
  
   database_id number,
  
   level_number number
  
   );
   /
  
  
  
   /* Inserting the data into the object that returned from function 
   Get_AccountDetails*/
   create or replace type get_acnt_type as table of Get_acnt_obj; 
  </code></pre>
  
  <p>This type function is succesfully created on any user.</p>