Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
EAN: 2147483647
Year: 2005
Pages: 41
Pages: 41
Authors: Richard Laermer, Michael Prichinello
In this section I will describe how to set up an environment capable of executing the examples in this book. I will cover:
How to set up the SCOTT/TIGER demonstration schema
The environment you need to have up and running
How to configure AUTOTRACE, a SQL*PLUS facility
The C compiler set up
The coding conventions I use in this book
Most of the examples in this book are designed to run 100 percent in the SQL*PLUS environment. The notable exceptions are the C-based examples where, of course, you require a C compiler, separate from Oracle (see the C Compilers section a little later). Other than that, SQL*PLUS is the only thing that you need to set up and configure. SQL*PLUS provides many useful options and commands that we'll make fequent use of throughout this book. For example, almost all of the examples in this book use DBMS_OUTPUT in some fashion. In order for DBMS_OUTPUT to work, the following SQL*PLUS command must be issued:
SQL> set serveroutput on If you are like me, you will soon get tired of typing this in each and every time. Fortunately, SQL*PLUS allows us to set up a login.sql file, a script that is executed each and every time we start a SQL*PLUS session. Further, it allows us to set an environment variable, SQLPATH, so that it can find this start-up script, regardless of the directory in which it is stored.
The login.sql script that I used 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 select lower(user) || '@' || decode(global_name, 'ORACLE8.WORLD', '8.0', 'ORA8I.WORLD', '8i', global_name ) global_name from global_name; set sqlprompt '&gname> ' set termout on
Where:
DEFINE EDITOR=VI sets the default editor for SQL*PLUS. You may set this to be your favorite text editor (not a word processor) such as Notepad or EMACs.
SET SERVEROUTPUT ON SIZE 1000000 enables DBMS_OUTPUT to be on by default (hence we don't have to type it in each and every time). Also set the default buffer size to be as large as possible.
SET TRIMSPOOL ON ensures that, when spooling text, lines will be blank-trimmed and not fixed width. If this is set to off (the default setting), spooled lines will be as wide as your linesize setting
SET LONG 5000 sets the default number of bytes displayed when selecting LONG and CLOB columns.
SET LINESIZE 100 sets the width of the lines displayed by SQL*PLUS to be 100 characters.
SET PAGESIZE 9999 sets the pagesize, which controls how frequently SQL*PLUS prints out headings, to a big number (we get one set of headings per page).
COLUMN PLAN_PLUS_EXP FORMAT A80 sets the default width of the EXPLAIN PLAN output we receive with AUTOTRACE. A width of a80 is generally enough to hold the full plan.
The next section of the login.sql sets up my SQL*PLUS prompt, starting with the line:
column global_name new_value gname
This directive tells SQL*PLUS to take the last value it retrieves for any column named GLOBAL_NAME, and place it into the substitution variable GNAME. We then have the following query:
select lower(user) || '@' || decode(global_name, 'ORACLE8.WORLD', '8.0', 'ORA8I.WORLD', '8i', global_name ) global_name from global_name;
This selects the GLOBAL_NAME from the database, using the DECODE function to assign familiar names to some of my more common database instances, and concatenates it with the username with which I am currently logged in. Finally, we reflect this information in the SQL*PLUS prompt:
set sqlprompt '&gname> '
Thus my prompt will look something like this:
tkyte@TKYTE816> In this manner, I know who I am, as well as where I am. Another very handy script to have in the same directory as login.sql is this connect.sql script:
set termout off connect &1 @login set termout on
SQL*PLUS will only execute the login.sql script when it initially starts up. Generally, we want it to execute every time we connect. I have just trained myself to use:
tkyte@TKYTE816> @connect scott/tiger instead of just CONNECT SCOTT/TIGER. This way, my prompt is always set properly, as are all other settings, such as SERVEROUTPUT.
Throughout the book it will be useful for us to monitor the performance of the queries we execute by getting a report of the execution plan used by the SQL optimizer, along with other useful execution statistics. Oracle provides a tool called EXPLAIN PLAN that, with use of the EXPLAIN PLAN command, allows us to generate this execution plan output.
| Note | For information about interpreting the output of EXPLAIN PLAN, see the Oracle8i Designing and Tuning for Performance guide. |
However, SQL*PLUS provides an AUTOTRACE facility that allows us to see the execution plans of the queries we've executed, and the resources they used, without having to use the EXPLAIN PLAN command. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE, and INSERT) statements. This book makes extensive use of this facility. There is more than one way to to configure the AUTOTRACE facility. These are the steps that I use:
cd [ORACLE_HOME]/rdbms/admin
log into SQL*PLUS as SYSTEM
run @utlxplan
run CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
run GRANT ALL ON PLAN_TABLE TO PUBLIC;
If you wish, you can replace the GRANT...TO PUBLIC with a GRANT to a specific user. By granting the privelege to the PUBLIC role, you are effectively letting anyone trace using SQL*PLUS. This is not a bad thing in my opinion as it prevents each and every user from having to install their own plan table. The alternative is for you to run @UTLXPLAN in every schema where you want to use the AUTOTRACE facility.
The next step is to create and grant the PLUSTRACE role:
cd [ORACLE_HOME]/sqlplus/admin
log into SQL*PLUS as SYS
run @plustrce
run GRANT PLUSTRACE TO PUBLIC;
Again, if you wish, you can replace PUBLIC in the GRANT with a specific user.
You can control the information displayed in the execution plan report by setting the AUTOTRACE system variable.
| SET AUTOTRACE OFF | No AUTOTRACE report is generated. This is the default. |
| SET AUTOTRACE ON EXPLAIN | The AUTOTRACE report shows only the optimizer execution path. |
| SET AUTOTRACE ON STATISTICS | The AUTOTRACE report shows only the SQL statement execution statistics. |
| SET AUTOTRACE ON | The AUTOTRACE report includes both the optimizer execution path, and the SQL statement execution statistics. |
| SET AUTOTRACE TRACEONLY | Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. |
The execution plan shows the SQL optimizer's query execution path. Each line of the Execution Plan has a sequential line number. SQL*PLUS also displays the line number of the parent operation.
The execution plan consists of four columns displayed in the following order:
| Column Name | Description |
|---|---|
| ID_PLUS_EXP | Shows the line number of each execution step. |
| PARENT_ID_PLUS_EXP | Shows the relationship between each step, and its parent. This column is useful for large reports. |
| PLAN_PLUS_EXP | Shows each step of the report. |
| OBJECT_NODE_PLUS_EXP | Shows the database links or parallel query servers used. |
The format of the columns may be altered with the COLUMN command. For example, to stop the PARENT_ID_PLUS_EXP column being displayed, enter:
SQL> column parent_id_plus_exp noprint
The Oracle-supported compiler varies by operating system. On Microsoft Windows, I used Microsoft Visual C/C++. I used only the command line portion of this tool (nmake and cl). None of my examples use the GUI development environment. However, they may also be developed in this environment if you wish. It would be up to you to configure the appropriate include files, and link in the proper libraries. Each makefile contained in this book are very small and simple - it is obvious what include files and libraries are necessary.
On Sun Solaris, the supported C compiler is the Sun SparcsWorks compiler. Again, I've used only the command line tools, make and cc, in order to compile the scripts.
The only coding convention used in this book that I would like to explicitly point out, is how I name variables in PL/SQL code. For example, consider a package body like this:
create or replace package body my_pkg as g_variable varchar2(25); procedure p( p_variable in varchar2 ) is l_variable varchar2(25); begin null; end; end; /
Here I have three variables, a global package variable G_VARIABLE, a formal parameter to the procedure, P_VARIABLE, and finally a local variable, L_VARIABLE. I name my variables according to their scope - all globals begin with G_, parameters with P_, and local variables with L_. The main reason for this is to distinguish PL/SQL variables from columns in a database table. For example, a procedure such as:
create procedure p( ENAME in varchar2 ) as begin for x in ( select * from emp where ename = ENAME ) loop Dbms_output.put_line( x.empno ); end loop; end;
would always print out every row in the EMP table. SQL sees ename = ENAME, and compares the ename column to itself (of course). We could use ename = P.ENAME - that is, qualify the reference to the PL/SQL variable with the procedure name, but this is too easy to forget, leading to errors.
I always name my variables after the scope. That way, I can easily distinguish parameters from local variables and globals, in addition to removing any ambiguity with respect to column names and variable names.
Each chapter in this book is fairly self-contained. At the beginning of each chapter, I dropped my testing account, and recreated it. That is, each chapter started with a clean schema - no objects. If you work the examples from start to finish in each chapter, you will want to do the same. When I query the data dictionary and such, to see what objects have been created as a side effect of some command or another, it might get confusing if you have left over objects from other examples. Also, I tended to reuse table names (especially the table T), so if you don't clean up the schema between chapters, you may hit a conflict there.
Additionally, if you attempt to manually drop the objects created by the example (as opposed to just dropping the user via drop user USERNAME cascade, and recreating it), you must be aware that the Java objects are all in mixed case. So, if you run the example in Chapter 19, Java Stored Procedures:
tkyte@TKYTE816> create or replace and compile 2 java source named "demo" 3 as 4 import java.sql.SQLException; ...
you will find that in order to drop it, you need to:
tkyte@TKYTE816> drop java source "demo"; Java dropped.
Remember to use the double quotes around the identifier for Java objects as they are created and stored in mixed case.