Dbms Output is not Displaying The Output in Toad

Question

I execute a procedure that has db link to other db and dbms output is not displaying the output. The button is green. When I click right, only "Toogle Output" and "Polling" are enable.

Answer 1

If I understood it correctly, this is how it goes:

• there are two databases, let's call them DB_LOCAL and DB_REMOTE

• there's a procedure that resides in DB_REMOTE database and - simplified - looks like this:

  create or replace procedure p_remote as
  begin
    dbms_output.put_line('In remote procedure');
  end;
  

  When executed, it prints the "In remote procedure" message.

• there's a database link in the DB_LOCAL database which lets you access the DB_REMOTE, e.g.

  create database link db_link_to_remote
    connect to remote_user
    identified by remote_password
    using 'db_remote';
  

• you are connected as a local user, in DB_LOCAL database, and call the remote procedure as

  connect local_user/local_password
  begin
    p_remote@db_link_to_remote;
  end;
  

  You expect to see the "In remote procedure" message, but there's none.

If the above is correct, here's why: a procedure runs in remote database, on a remote server. It is executed and it does print the message, but it is displayed on the remote server, not on your local server.

So, what to do? One option is to create a procedure which has an OUT parameter and returns the message as a parameter. You'd then be able to display when connected locally. Shortly, in remote database:

create or replace procedure p_remote (par_msg out varchar2) is
begin
  par_msg := 'In remote procedure';
end;

In local database:

declare
  l_msg varchar2(200);
begin
  p_remote@db_link_to_remote(l_msg);
  dbms_output.put_line(l_msg);
end;

Answer 2

You need to enable DBMS_OUTPUT on remote system and manually get lines:

If on remote system there is a procedure named "pr_remote":

create or replace procedure pr_remote as  
begin    
  dbms_output.put_line('In remote procedure on:');    
  dbms_output.put_line('SERVER_HOST='||sys_context('userenv','SERVER_HOST'));  
  dbms_output.put_line('DB_NAME='||sys_context('userenv','DB_NAME'));  
end pr_remote;  

There's a database link in the DB_LOCAL database which lets you access the DB_REMOTE:

create database link **db_link_to_remote**
  connect to remote_user
  identified by remote_password
  using 'db_remote';

You need to enable remote DBMS_OUTPUT and get every line with a loop:

Declare
  /* how to get DBMS_OUTPUT from a remote system */
  sbLine    varchar2(1000);
  nuStatus  integer;
Begin
  Dbms_Output.Put_Line('My local is on         SERVER_HOST='||sys_context('userenv','SERVER_HOST')||'|DB_NAME='||sys_context('userenv','DB_NAME'));
  --
  Dbms_Output.Put_Line('Enable DBMS_OUTPUT remote');
  dbms_output.enable@db_link_to_remote;
  --
  Dbms_Output.Put_Line('Call remote procedure');
  pr_remote@db_link_to_remote;
  loop
     DBMS_OUTPUT.GET_LINE@db_link_to_remote(sbLine,
                                nustatus);
     --
     Dbms_Output.Put_Line(sbLine);
     exit when nustatus<>0;
  end loop;
  dbms_output.disable@db_link_to_remote;
End;