Configuring SQL*Plus environment for Oracle application developer.

Article

It is very important for a professional developer (programmer) to be able to work effectively in the SQL*Plus environment. It is important to be able to perform competent configuration and configuration of SQL*Plus.

However, I have some advice on how to use SQL*Plus. Almost all examples use the DBMS_OUT PUT package in one way or another in textbooks and books. To be able to work with DBMS_OUTPUT you need to issue the following SQL*Pius command:

SQL> set serveroutput on

Entering this command frequently becomes tedious quite quickly. SQL*Plus allows you to create a login. sql file, a script that runs every time SQL*Plus is started. Moreover, you can define an SQLPATH environment variable, which will allow you to find this script no matter what directory it is stored in.

For all the following examples in my blog, this script login.sql is used:

define _editor=vi

set serveroutput on Size unlimited

set trimspool on

set long 5000

set linesize 100

set pagesize 9999

column plan_plus_exp format a80

set sqlprompt '&_user.@&_connect_identifier.> '.

Below is an annotated version of this script:

1. Define _editor=vi. Defines the text editor that SQL*Plus will use by default. You can specify any preferred text editor (not a word processor) such as Notepad or emacs.

2. Set server output on size unlimited. By default, it includes the DBMS_ OUTPUT package (therefore you will not have to enter the set server output on command each time). Also, sets the default buffer size to the maximum possible value.

3. Set trimspool on. When buffering text strings will be truncated with spaces, so they will not have a fixed length. If trimspool is set to off (by default), the width of the buffered strings will be equal to the value of linesize.

4. Set long 5000. Sets the standard number of bytes displayed when LONG and CLOB columns are selected.

5. Set linesize 100. Sets the width of lines displayed by SQL*Plus to 100 characters.

6. Set pagesize 9999. Sets the pagesize parameter which controls how often SQL*Plus outputs headers to a large value (we will get one set of headers per page).

7. Column plan_plus_exp format a80. Sets the standard line width in the output of the execution plan obtained with AUTOTRACE. The value of a80 is quite enough to display the complete plan.

The last snippet of the login.sql script sets the prompt to enter SQL*Plus commands:

set sqlprompt '&_user.@&_connect_identifier.> '.

As a result, the interface invitation takes the following form, which allows you to see the user name and connection ID:

E0DA@0RA12CR1>

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.