Home » SQL & PL/SQL » Client Tools » variable vs set serveroutput on (oracle 10g)
variable vs set serveroutput on [message #429550] Wed, 04 November 2009 08:41 Go to next message
pointers
Messages: 404
Registered: May 2008
Senior Member
Hi,

I have a script file like

set serveroutput on
variable aa number;
begin
:aa=100;
dbms_output.put_line('1 here we go ahah'||:aa);
end;
/
connect gapc/s0lut_1on@apchcp1_cihcispdb019
--set serveroutput on
begin
dbms_output.put_line('2 here  we go ahah'||:aa);
end;
/


I am running the above script from a batch file which directly connect to the db 'abcd' then from the script i am connecting to other db let say 'jklm'.
I am able to persist the value of "aa" for the first db and second db as well.

But the observed thing is 'set serveroutput on'
if i enable 'set serveroutput on' for the first time then that is not reflecting for the second db connection. I need to explicitly write 'set serveroutput on' for the second connection also. Otherwise it is not printing.

what could be the reason by comparing the two cases.
one more thing, when i connect to new db from the script am i connecting to the new session. little confused..

Please help me

Regards
Pointers
Re: variable vs set serveroutput on [message #429551 is a reply to message #429550] Wed, 04 November 2009 08:45 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Of course, when you connect to the second server, how do you expect the second server to know whether you told the first server to switch his output on or off?

And of course when you connect to a new server, you get a new session.
Re: variable vs set serveroutput on [message #429552 is a reply to message #429550] Wed, 04 November 2009 08:50 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
No matter what tools you use, you must always deal with multiple layers of software.

In your case you have a client application open (sqlplus) connected to a server application (database). Each layer has its own environment and corresponding environmental context.

variable is set in your client environment (sqlplus environment)
serveroutput sets up your server environment (database environment)

Thus when you connect to a database, your sqlplus environment is unchanged, but your database environment is brand new. That is why your variable values remain, but your serveroutput setting seems to change.

You have assumed that both the variable definition and the serveroutput setting are in your client enviroment and they are not.

Kevin
Re: variable vs set serveroutput on [message #429559 is a reply to message #429550] Wed, 04 November 2009 09:06 Go to previous message
pointers
Messages: 404
Registered: May 2008
Senior Member
Thanks Kevin you got me correctly..and cleared my doubts...thanks..

Regards,
Pointers
Previous Topic: Using Unix environment variables in login.sql
Next Topic: What's the camera icon on the pl/sql developer tool bar?
Goto Forum:
  


Current Time: Sat Sep 24 23:09:02 CDT 2016

Total time taken to generate the page: 0.11726 seconds