Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, santhosh.

Asked: April 03, 2019 - 2:15 pm UTC

Last updated: April 16, 2019 - 11:46 pm UTC

Version: 12C

Viewed 10K+ times! This question is

You Asked

Hi,

The Buffer size we give for dbms_out.put_line is different from Data base buffer cache in SGA or same?

and can you show me am example on how to use dbms_out.getline function.


Thanks in Advance.

and Connor said...

The Buffer size we give for dbms_out.put_line is different from Data base buffer cache in SGA or same?


Yes, it is memory that is private to your session, so it comes out of the PGA.

Here's an example of using GET_LINE

https://asktom.oracle.com/pls/apex/asktom.search?tag=retrieving-dbms-outputput-line-from-jdbc

Rating

  (4 ratings)

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

Comments

DBMS_OUTPUT.ENABLE vs SET SERVEROUTPUT ON

A reader, April 11, 2019 - 7:21 am UTC

Hi,
I executed below block in sql * plus,sql developer and pl/sql developer.
<
Begin
dbms_output.enable;
dbms_output.put_line('Before SET SERVEROUTPUT ON');
end;
>
sql *plus and sql developer not displayed output but plsql developer did.
now i executed below block after SET SERVEROUTPUT ON
<
BEGIN
dbms_output.disable;
dbms_output.enable;
dbms_output.put_line('After SET SERVEROUTPUT ON');
end;
>
now three apps displayed information from buffer.
from above scenario i understood that DBMS_OUTPUT.ENABLE does not work directly with out executing SET SERVEROUTPUT ON and then DBMS_OUTPUT.DISABLE.
if i am correct why do we need dbms_output.enable
Connor McDonald
April 12, 2019 - 2:30 am UTC

As you're already aware, dbms_output just dumps information into a memory structure.

It it entirely up to the calling tool to decide what to do with it. In the case of our tools (sqlplus etc), it is the setting of "serveroutput" that tells is whether we should go inspect the buffer after the fact.

Other tools might just call "get_lines" to inpect the buffer.

enable/disable API's are there to let you potentially leave dbms_output calls within your code, and NOT have them do anything if needed, eg

exec dbms_output.disable
exec my_proc; (which has lots of dbms_output calls in it)

and so forth

SET SERVEROUTPUT ON Error.

A reader, April 15, 2019 - 2:23 pm UTC

Hi,
I got below error when i executed SET SERVEROUTPUT ON.

"SP2-0547: size option set serveroutput is on out of range (2000 through 1000000)"

What is the meaning of that and when will we get it?
Connor McDonald
April 16, 2019 - 11:46 pm UTC

Recent versions of Oracle allow unlimited, eg

SQL> SET SERVEROUTPUT ON SIZE UNLIMITED


Earlier versions are limited 1megabyte

SET SERVEROUTPUT ON Error.

A reader, April 17, 2019 - 5:45 am UTC

Thanks for your reply Conor.
But i am using 12C database and retested twice in SQL * Plus and SQL Developer.
Getiing this issue only on SQL Devloper.
What might be the issue?

To "A reader"

J. Laurindo Chiappa, April 17, 2019 - 12:05 pm UTC

Hi : as Connor said, usage of the buffer is a CLIENT responsability, so FOR SURE you are facing a bug/misbehavior IN YOUR CLIENT PROGRAM, SQL DEVELOPER in your case.... See https://dba.stackexchange.com/questions/156767/dbms-output-hard-limit-in-sql-developer , https://www.thatjeffsmith.com/archive/2012/03/dbms_output-in-sql-developer/ and https://github.com/utPLSQL/utPLSQL-SQLDeveloper/issues/2 reporting this EXACT DEFECT in SQL DEVELOPER til version 17.x : check and see if it works in v19 ....

Regards,

Chiappa
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library