-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|