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!

Yes... but I don't see how this function is used in this select statement. `from table...` what's the function doing outside of a where, having, group by or select? What do you expect this function to return and how would the query use it? as a limit, for display what? Plus I see to open ('s and 3 close )'s — xQbert, Mar 08 '16 at 18:48
Is `mc` a schema or a package? If it's a schema, what user does the database link connect as, and if that isn't `mc`, does that user have privileges on the function? What data type does the function return - you might hit [this problem](http://stackoverflow.com/a/7246141/266304) next. Actually, this might be a duplicate of that question... — Alex Poole, Mar 08 '16 at 18:52
@Alex Poole mc is a schema and I do have full access to it and the function, I might be getting the error due to second issue. The function looks like this - — Kaur, Mar 08 '16 at 19:26
You will hit that linked issue because of the `SD_SchoolType_T` UDT. That doesn't explain the ORA-00904 you're getting before you reach that point though. — Alex Poole, Mar 08 '16 at 19:37
@Alex I messed up trying to edit the code....the names are correct. — Kaur, Mar 08 '16 at 19:38
So is the function name actually using a quoted identifier as you've shown? Does `mc."Get_REFTYPES"@READ_MAIN_MCNAV()` work? — Alex Poole, Mar 08 '16 at 19:38

score 7 · Accepted Answer · edited May 23 '17 at 11:59

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".

How to call function using dblink in oracle?

1 Answers1