Re: Problem with DBMS_OUTPUT.PUT_LINE

A copy of this was sent to avbpk1_at_yahoo.com (if that email address didn't require changing) On Mon, 22 Nov 1999 15:47:38 GMT, you wrote:

>Dear Friends,
> I have installed Oracle8 and Oracle8i servers on two
>different Machines. I created a procedure with the following script in
>Oracle 8
> CREATE PROCEDURE pr_8 IS
> BEGIN
> DBMS_OUTPUT.PUT_LINE('Procedure in Oracle8');
> END;
>
>I created a similar procedure with the following script in Oracle 8i
> CREATE PROCEDURE pr_8i IS
> BEGIN
> DBMS_OUTPUT.PUT_LINE('Procedure in Oracle8i');
> END;
>
>I logged into Oracle8 thru sqlplus8
>Turned on serveroutput
>Now If I Executed Procedure pr_8i by using following command
>
>SQL> Execute pr_8
>
>procedure is getting executed successfully and output is shown. But If
>I Executed Procedure pr_8i by using Database Link (Oracle8i) with the
>following command
>
>SQL> Execute pr_8i_at_oracle8i
>
>The procedure gets executed successfully but Output is not displayed.
>
>I want to know why it is not showing the output and also a solution on
>how to display the same.
>

dbms_output is a package installed in each database. calls to put_line really just stuff the inputs into an array. pr_8 writes to the dbms_output package located in the 'local' database you are running from. pr_8i writes into the *OTHER* dbms_output package located in the oracle8i instance. Your output has gone to 2 different locations.

In theory you could have pr_8i_at_oracle8i write into the local dbms_output package by having it (pr_8i) write to dbms_output.put_line_at_oracle8 but I would recommand AGAINST this. Performance would be less then desirable with all the links and it would make that procedure be less useful.

I would have pr_8i return as output the information you want displayed and have pr_8 display the information after executing pr_8i (since dbms_output information is cached until the entire pr_8 procedure runs anyway -- this is not an issue)...

>Thanks in advance
>Kumar
>
>
>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Nov 22 1999 - 10:25:25 CST