accessing a stored procedure via dblink does not return set(oracle)
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,
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 :
variable test char(200)
set serveroutput on size unlimited
Originally Posted by gamyers
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.
Click Here to Expand Forum to Full Width