Calling SQL*Plus commands in PL/SQL block

Question

Is there any way to set the serveroutput on/off using pl/sql procedures/packages. I want to do some changes in displaying of my data on SQL*PLUS screen. like for my previous post

No you cannot use sql*plus commands inside PL/SQL block. Please give more information about what you are trying to accomplish — Nick Krasnov
– Nick Krasnov, Commented Sep 5, 2012 at 15:21

Justin Cave · Accepted Answer · 2012-09-05 15:22:20Z

2

You cannot call SQL*Plus commands (which run only on the client) from PL/SQL (which runs only on the server).

In the particular case where you simply want to enable and disable message output, however, you can call the PL/SQL procedures dbms_output.disable and dbms_output.enable.

If you are depending on the data being written via dbms_output to be displayed to a human user, however, you are almost certainly doing something wrong. Production processes should be writing important data to some other location (i.e. a table somewhere), not writing to dbms_output and hoping that the client application happens to be configured to display the data.

answered Sep 5, 2012 at 15:22

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mallikarjun Over a year ago

I am writing number of records processed data into SQL*PLUS screen, Using dbms_output.putline . And i am writting all exceptions and data in a file. If oracle server has set serveroutput off then i want to make it on. And if any error occurred while processing files(i am checking existence of file and then returning a message to console), as mentioned in previous post i want to set feedback off.

Justin Cave Over a year ago

@mallikarjun - It doesn't make sense for a production process to write the number of rows processed to the dbms_output buffer. You would write that sort of thing to a table that tracks each execution of your extract. As I said in my answer, you can enable and disable dbms_output from PL/SQL. You cannot set feedback off from PL/SQL since that is a client-side setting. And your server-side PL/SQL cannot force the client application to have enabled serveroutput.

Collectives™ on Stack Overflow

Calling SQL*Plus commands in PL/SQL block

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related