accessing a stored procedure via dblink does not return set(oracle)

Hello guys,

i need a little help with a dblink issue. I have database A that has an dblink to a database B. In database B resides an stored procedure that returns a REF CURSOR AND also sends output via DBMS_OUTPUT.PUT_LINE from a select made in that stored procedure. When i try to access this stored procedure via dblink, i dont get the results set, nor the DBMS_OUTPUT shows me nothing (using SQL*Plus). I use set serveroutput on but nothing gets printed. Anyone can help me with this? what could be wrong?

thanks in advance,

dgc

DBMS_OUTPUT.PUT_LINE will store stuff in memory for the process running the procedure (in your case the session is in database B).
DBMS_OUTPUT.GET_LINES extracts the stuff from the memory into a local PL/SQL variables. Here's the kicker. You need to run the DBMS_OUTPUT.GET_LINES from the session in Database B. With a SET SERVEROUTPUT ON, SQL*Plus automatically does the GET_LINE stuff for the connected session (in your case on database A).
You are going to have to do a quick anonymous PL/SQL to handle GET_LINEs from database B :

begin
dbms_output.enable@link(10000);
dbms_output.put_line@link('test122');
end;
.
/

variable test char(200)

set serveroutput on size unlimited

declare
v_num number;
begin
dbms_output.get_line@link(:test,v_num);
dbms_output.put_line(v_num);
end;
.
/

print test

Quote:

---

Originally Posted by gamyers

begin
dbms_output.enable@link(10000);
dbms_output.put_line@link('test122');
end;
.
/

variable test char(200)

set serveroutput on size unlimited

declare
v_num number;
begin
dbms_output.get_line@link(:test,v_num);
dbms_output.put_line(v_num);
end;
.
/

print test

---

Wow. I didn´t try that one. Thank fou for that. But what about the remote package? Because this may solve mi testing via SQL*Plus, but the remote package does not return SET. I am not sure this is possible, i am reading documentation about it, and it seems it is not supported remote packages. Can someone clarify this matter????

Thank you very much for your help.

regards,

DGCampos