Re: newbie having woes with dbms_output in functions

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 27 Feb 2007 08:13:01 -0800
Message-ID: <1172592779.894492@bubbleator.drizzle.com>

Anurag Varma wrote:

> On Feb 26, 8:19 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> Anurag Varma wrote:
>>> On Feb 26, 4:22 pm, sean nakasone <seannakas..._at_yahoo.com> wrote:

>>>> Hello,
>>>> I have the following function:
>>>> create or replace function seanFunc1(val varchar2)
>>>> return varchar2 is
>>>> begin
>>>> dbms_output.enable(1000000);
>>>> dbms_output.put_line('hello');
>>>> return 'at the end';
>>>> end;
>>>> /
>>>> When I call this function from the sqlplus command line, it does not print
>>>> 'hello'.
>>>> i.e.
>>>> select seanFunc1('the') from dual;
>>>> But when I call the function in a code block as shown below, it then
>>>> prints.
>>>> declare
>>>> var varchar2(50);
>>>> begin
>>>> select seanFunc1('the') into var from dual;
>>>> end;
>>>> /
>>>> Why is this? What's the simplest way to get output from a function?

>>> In sqlplus you should issue
>>> set serveroutput on size <nnnnnnn>
>>> (e.g. set serveroutput on size 100000)
>> This is now version dependent and not required in 10gR2.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org
> 
> Daniel,
> 
> Not sure what you are talking about .. but I tested it
> in 10.2.0.3 and it still requires "set serveroutput on".
> You might not require to specify size .. since default is
> unlimited .. but you still have to specify "set serveroutput on"
> 
> Anurag

I should have been clearer.

SET SEVEROUTPUT ON is required.

SIZE ... is not. By default the size is now essentially unlimited.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Feb 27 2007 - 10:13:01 CST