You Asked

Hi,
Im running a procedure in DB A which calls a procedure internally which is in DB B. When I turn on set server output on it only displays the DBMS output of DB A and not in DB B.

I want all the DBMS outputs regardless of DB....

THANKS

and Connor said...

dbms_output stores information in a buffer, hence, for the remote procedure call, that buffer belongs to the session on the remote database.

So after the remote call, your calling proc would need to call the GET_... routines in DBMS_OUTPUT (on the remote side) to retrieve the data from the remote buffer, and then re-issue dbms_output on the local side to output them.

SQL> desc DBMS_OUTPUT
PROCEDURE DISABLE
PROCEDURE ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BUFFER_SIZE                    NUMBER(38)              IN     DEFAULT
PROCEDURE GET_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINE                           VARCHAR2                OUT
 STATUS                         NUMBER(38)              OUT
PROCEDURE GET_LINES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINES                          TABLE OF VARCHAR2(32767) OUT
 NUMLINES                       NUMBER(38)              IN/OUT
PROCEDURE GET_LINES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINES                          DBMSOUTPUT_LINESARRAY   OUT
 NUMLINES                       NUMBER(38)              IN/OUT
PROCEDURE NEW_LINE
PROCEDURE PUT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 A                              VARCHAR2                IN
PROCEDURE PUT_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 A                              VARCHAR2                IN

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

error

Rajeshwaran, April 24, 2017 - 11:56 am UTC

....
So after the remote call, your calling proc would need to call the GET_... routines in DBMS_OUTPUT (on the remote side) to retrieve the data from the remote buffer, and then re-issue dbms_output on the local side to output them.
....
Tried invoking the GET_xxxx routines on the remote side and got this error.

demo@ORA11G> create or replace procedure p
  2  as
  3  begin
  4     dbms_output.put_line('I am in Oracle 11g database');
  5  end;
  6  /

Procedure created.

demo@ORA11G> exec p ;
I am in Oracle 11g database

PL/SQL procedure successfully completed.

demo@ORA11G> conn demo/demo@ora12c
Connected.
demo@ORA12C> create or replace procedure p
  2  as
  3     l_line long;
  4     l_status int;
  5  begin
  6     p@backto11g;
  7     dbms_output.get_line@backto11g(l_line,l_status);
  8     dbms_output.put_line('l_status ='||l_status);
  9     dbms_output.put_line('l_line ='||l_line);
 10  end;
 11  /

Procedure created.

demo@ORA12C> exec p ;
BEGIN p ; END;

*
ERROR at line 1:
ORA-04062: timestamp of procedure "DEMO.P" has been changed
ORA-06512: at "DEMO.P", line 6
ORA-06512: at line 1


demo@ORA12C> exec p ;
l_status =1
l_line =

PL/SQL procedure successfully completed.

demo@ORA12C> select * from dual@backto11g;

D
-
X

demo@ORA12C>

Is that something got missed out here?

additional info

Rajeshwaran, April 27, 2017 - 7:34 am UTC

Team - could you help us to understand this ?

April 28, 2017 - 12:29 am UTC

Think about what you do to get dbms_output output in SQLPlus in a local database

1) you *enable* it (set serverout on)
2) then make calls to dbms_output

So you need to do the *same* for the db link session

SQL> declare
  2    x varchar2(1000);
  3    status int;
  4  begin
  5    dbms_output.enable@db1;    <<<< ======
  6    p@np12;
  7    loop
  8      dbms_output.get_line@db1( x,status);
  9      exit when status != 0;
 10      dbms_output.put_line(x)
 11    end loop;
 12  end;
 13  /

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

error

additional info

More to Explore

PL/SQL demos

PL/SQL docs

Display DBMS output from another DB

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

error

additional info

More to Explore

PL/SQL demos

PL/SQL docs