I have created a function which is working on my local but giving compilation error while creating on specific user and schema.
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.
`/*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 > 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;
/
object Type which I have created in working fine.
/
/* 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;
This type function is succesfully created on any user.
See Question&Answers more detail:
os 
