Returning Multiple Values from Oracle Function over Database Link

Question

I have two oracle databases: A and B. On database A, I have a function which returns user defined type:

create or replace type my_type as object
     ( id number,
        f2 varchar2(4000),
        f3 varchar2(4000),
        f4 varchar2(4000)
      );

and

 CREATE OR REPLACE FUNCTION my_function(p_id NUMBER)   
      RETURN my_type IS   
      v_f1 VARCHAR2;   
      v_f2 VARCHAR2;   
      v_f3 VARCHAR2; 
    BEGIN
    
      SELECT a.f1, a.f2, a.f3
           INTO v_f1, v_f2, v_f3   
      FROM my_table a  
      WHERE a.id= p_id;
    
      RETURN my_type (p_id, v_f1, v_f2, v_f3);
    
    END my_function;

When I call this function from Oracle A it returns

BUT, when I call it from Oracle B it throws ERROR:

ORA-30626: function/procedure parameters of remote object types are not supported

Can you tell me, how to get my SQL query results(f1, f2, f3) from database A to database B. I don't want to call the query thrice, because of the performance issue.

The only solution I came up was to concatenate these three fields in one value on database A and then substring it on database B, do you have any better idea?

Does the data have to come from a function? Could you instead use a procedure with four OUT parameters? — Jon Heller
– Jon Heller, Commented Dec 8, 2021 at 5:45

Andreas Hauser · Accepted Answer · 2021-12-07 12:07:38Z

0

Your object "my_type" should have "SERIALIZE" & "DESRIALIZE" functionality, then you should be able to call your function "my_function()" remotely without an error.

See also this question: ORA-30626: function/procedure parameters of remote object types are not supported

And the oracle doc: https://www.oracle.com/java/technologies/jpl2-serial.html

answered Dec 7, 2021 at 12:07

Andreas Hauser

928 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Returning Multiple Values from Oracle Function over Database Link

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related