How to call function using dblink in oracle?

Question

Is it possible to call a function using DBLINK in oracle? I am calling the function like this and getting the error - ORA-00904: "MC"."GET_REFTYPES": invalid identifier

select column_value from table(mc.Get_REFTYPES@READ_MAIN_MCNAV(param1, param2, param3)));

function code

CREATE OR REPLACE FUNCTION "MC"."Get_REFTYPES"(
  param1 IN VARCHAR, 
  param2  IN NUMBER, 
  param3  IN DATE DEFAULT SYSDATE
  )
  RETURN RefType_T PIPELINED IS

  CURSOR cur_st (
    cur_param1 VARCHAR, 
    cur_param2  NUMBER,
    cur_param3  DATE
    ) IS
            select 
              TypeID
            FROM ......
          WHERE......... ;

  t_st Types_T;  -- Table variable to store return values       

 BEGIN

    OPEN cur_st(param1, param2 , param3 );
    FETCH cur_st BULK COLLECT INTO t_st;
    CLOSE cur_st;

    FOR i IN 1..t_st.COUNT LOOP
            PIPE ROW(t_st(i));
    END LOOP;
    RETURN;
END;

Thanks!

Answer 1

The function is declared with a quoted identifier:

CREATE OR REPLACE FUNCTION "MC"."Get_REFTYPES"(

You're calling it unquoted, as mc.Get_REFTYPES@READ_MAIN_MCNAV(...). One of the irritations that quoted identifiers cause is that you always have to refer to them with quotes and exactly the same case as the original definition, so you would need to do:

select column_value
from table(mc."Get_REFTYPES"@READ_MAIN_MCNAV(param1, param2, param3)));

You don't need to quote mc because a quoted uppercase identifier behaves like an unquoted one anyway.

However, even with that corrected this won't do what you want. As discussed in this answer you can't call a function that returns a user defined type like RefType_T, and will get "ORA-30626: function/procedure parameters of remote object types are not supported".