calling function returning user type from different schema in Oracle -

i trying call function inside package returning user defined type present in different schema.

the function , package declaration ---

create or replace package pa_webhook    authid current_user    type gcur_table_data ref cursor;     type typ_tab_updated_ids table of orders.order_sid%type; function fn_order_customer_get (p_str_order_id in orders.order_id%type) return gcur_table_data; end pa_webhook; / 

as evident function returning ref cursor output selected number of rows.

now in order achieve this, have followed following steps--

1) creating role --

create role ecommerce_app_role; 

2) providing privilege role ---

grant execute on  pa_webhook ecommerce_app_role; 

3) assigning role user schema

grant ecommerce_app_role ecomm_app; 

4) creating synonym package in other schema

although able call function different schema getting null in result set, whenever executing query mentioned in function can see results.

is because of type created inside package, if yes can lead me way correct result set

your package defined authid current_user when call pa_webhook.fn_order_customer_get source schema works fine, when call ecomm_app schema uses ecomm_app's privileges when running, not defining schema's privs, , uses ecomm_app schema during name resolution instead of defining schema when objects aren't qualified.

all means if pa_webhook.fn_order_customer_get attempts return ref cursor orders table (not qualified owners schema name) return refcursor different tables depending on schema it's called from. if schema has orders table containing data you'll data back, if ecomm_app has orders table no data won't data back. if ecomm_app doesn't have referenced table function throw error. it's possible function has error handler may masking situation.

alternatively if pa_webhook.fn_order_customer_get uses qualified table references, perhaps ecomm_app schema doesn't have select privs on them. again error handler masking lack of privs.

without seeing package body lot of conjecture though. can read on invoker's , definer's rights in oracle's docs more info.