The Environment in Font

Generating DataMatrix in Font The Environment

Most of the examples in this book are designed to run 100 percent in the SQL*Plus environment. Other than SQL*Plus, there is nothing else to set up and configure. I can make a suggestion, however, on using SQL*Plus. Almost all the examples in this book use DBMS_OUTPUT in some fashion. For DBMS_OUTPUT to work, the following SQL*Plus command must be issued: SQL> set serveroutput on If you are like me, typing in this command each and every time will quickly get tiresome. Fortunately, SQL*Plus allows us to set up a login.sql file a script that is executed each and every time we start SQL*Plus. Further, it allows us to set an environment variable, SQLPATH, so that it can find this login.sql script, no matter what directory it is in. The login.sql I use for all examples in this book is as follows: define _editor=vi set serveroutput on size 1000000 set trimspool on set long 5000 set linesize 100 set pagesize 9999 column plan_plus_exp format a80 column global_name new_value gname set termout off define gname=idle column global_name new_value gname

select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name from (select global_name, instr(global_name,'.') dot from global_name ); set sqlprompt '&gname> ' set termout on An annotated version of this is as follows: DEFINE _EDITOR=VI: This sets up the default editor SQL*Plus will use. You may set the default editor to be your favorite text editor (not a word processor) such as Notepad or emacs. SET SERVEROUTPUT ON SIZE 1000000: This enables DBMS_OUTPUT to be on by default (hence, you don t have to type it in each and every time). It also sets the default buffer size as large as possible. SET TRIMSPOOL ON: When spooling text, lines will be blank-trimmed and not fixed width. If this is set to OFF (the default), spooled lines will be as wide as your LINESIZE setting. SET LONG 5000: This sets the default number of bytes displayed when selecting LONG and CLOB columns. SET LINESIZE 100: This sets the width of the lines displayed by SQL*Plus to be 100 characters. SET PAGESIZE 9999: This sets the PAGESIZE, which controls how frequently SQL*Plus prints out headings, to a large number (you get one set of headings per page). COLUMN PLAN_PLUS_EXP FORMAT A80: This sets the default width of the explain plan output you receive with AUTOTRACE. A80 is generally wide enough to hold the full plan. The next bit in login.sql sets up the SQL*Plus prompt: define gname=idle column global_name new_value gname select lower(user) || '@' || substr( global_name,1, decode( dot, 0, length(global_name), dot-1) ) global_name from (select global_name, instr(global_name,'.') dot from global_name ); set sqlprompt '&gname> ' The directive COLUMN GLOBAL_NAME NEW_VALUE GNAME tells SQL*Plus to take the last value it retrieves for any column named GLOBAL_NAME and place it into the substitution variable GNAME. I then select the GLOBAL_NAME out of the database and concatenate this with the username I am logged in with. That makes my prompt look like this: ops$tkyte@ora10g> so I know who I am as well as where I am.

AUTOTRACE is a facility within SQL*Plus that shows you the explain plan of the queries you ve executed and the resources they used. This book makes extensive use of the AUTOTRACE facility. There is more than one way to get AUTOTRACE configured. This is what I like to do to get AUTOTRACE working: 1. cd [ORACLE_HOME]/rdbms/admin 2. log into SQL*Plus as SYSTEM 3. Run @utlxplan 4. Run CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE; 5. Run GRANT ALL ON PLAN_TABLE TO PUBLIC; You can replace the GRANT TO PUBLIC with some user if you want. By making the PLAN_ TABLE public, you let anyone trace using SQL*Plus (not a bad thing, in my opinion). This prevents each and every user from having to install his or her own plan table. The alternative is for you to run @utlxplan in every schema from which you want to use AUTOTRACE. The next step is creating and granting the PLUSTRACE role: 1. cd [ORACLE_HOME]/sqlplus/admin 2. Log in to SQL*Plus as SYS or as SYSDBA 3. Run @plustrce 4. Run GRANT PLUSTRACE TO PUBLIC; Again, you can replace PUBLIC in the GRANT command with some user if you want.