2.1 Simplify SQLPlus for Yourself on Windows


2.1 Simplify SQL*Plus for Yourself on Windows

In this section we focus on methods that simplify SQL*Plus script file organization. You will eventually be creating, saving, and retrieving SQL*Plus scripts. You want the scripts to be separate from other files; you want to know exactly where they are and build and modify them without a lot of headaches .

SQL*Plus is a scripting language that includes command file support. This text encourages the use of command files, also called SQL scripts. Your SQL*Plus scripts will eventually contain multiple SQL DML statements, CREATE table statements with constraint clauses, and Oracle stored procedures and triggers written in PL/SQL.

When you install Oracle, whether it's the full enterprise licensed database or just the desktop client tools, you will be able to launch SQL*Plus from your desktop through START -> PROGRAMS. When you use this method to run SQL*Plus, your default directory (i.e., the directory for saved and retrieved scripts) is the same as the Oracle "bin" directory. This is not very convenient ”you want to keep your scripts in a separate working directory. You can override the default "bin" directory and save a SQL script to another directory ”this requires including the full pathname with the SQL*Plus SAVE command. You can run SQL scripts that exist in a nondefault directory if you type a full pathname in the run command. These override methods are inconvenient, and there are better techniques to be discussed shortly.

One approach to overriding the default directory is to create a desktop shortcut for SQL*Plus and designate a START-IN directory. The START-IN directory will be the new default directory for SQL*Plus scripts. Without specifying a start-in directory, all saved scripts default to the Oracle "bin" directory ”a directory with hundreds of other Oracle files.

Let's do this now. Using Windows Explorer, create a "working directory" where you wish to store your library of SQL*Plus scripts. For example, create the directory:

 
 C:\MY_SQLPLUS 

Next, create a desktop shortcut for SQLPLUSW.EXE. This program is in the Oracle "bin" directory. Then right-click the newly created shortcut icon from the desktop, select "Properties," and select "Short cut" ”this is where you set the "Start in" directory. Set the "Start in" field to the path of the directory you just created, (i.e., C:\MY_SQLPLUS). The shortcut creation step is shown in Figure 2-1.

Figure 2-1. SQL*Plus Shortcut.

graphics/02fig01.jpg

After you type the pathname in the "Start in" field, select the "Apply" button, then the "OK" button, and you're done. By doing this you are able to save and retrieve scripts into, and from, a dedicated "Start in" directory ”nothing else but your scripts exists here. You can go to this directory to copy and edit scripts. You have now made the directory C:\MY_SQLPLUS, your personal development code library, and integrated it into SQL*Plus.

The aforementioned process is not necessary in a UNIX environment. In UNIX you can "cd" (change directory command) to any UNIX directory, such as a dedicated SQL*Plus code library, and invoke SQL*Plus by typing just the executable name on the command line ”this works because the SQL*Plus program is in your $PATH. From this point, all files from your SQL*Plus session are automatically saved into the current working directory; that is, the directory from which you invoke SQL*Plus.

You will see two versions of SQL*Plus in your Windows Oracle "bin" directory: SQLPLUS.EXE and SQLPLUSW.EXE. Use SQLPLUSW ”this is for everyday interactive use. It begins with a popup box prompting you with login information. This version runs SQL*Plus in a scrollable window ”this is very easy to work with. The SQLPLUS.EXE version runs in a "DOS" window, black screen, and no scrollable window. You would use this version of SQL*Plus to launch a SQL script from a DOS batch file or call SQL*Plus with command line arguments from a scripting languages such as Perl ” illustrated in Section 2.12.

After you create the desktop shortcut and update the start-in directory, you have a desktop with the SQL*Plus icon showing. Figure 2-2 shows a desktop with not one, but three, SQL*Plus icons. Here, each icon is a shortcut to the same SQL*Plus executable; however, each has a different start-in directory, which means that each has a separate "working directory." This is one technique for "conquering and dividing" your work in a Windows environment. Again, this is not necessary in a UNIX environment because you simply "cd" to any working directory and once in that directory, you invoke SQL*Plus. In a Windows environment, you may be inclined to separate work among various directories and tie a SQL*Plus executable shortcut to these separate work directories.

Figure 2-2. SQL*Plus Shortcuts on the Desktop.

graphics/02fig02.jpg

The three boxes on the far right of Figure 2-2 represent distinct "Start in" directories. One directory might be used to store all the CREATE table scripts for your application. This would include scripts to create triggers, sequences, and indexes. One could be the directory where you maintain all your PL/SQL stored procedures. One directory would be used for application statement tuning ”this directory would include many SQL*Plus list files with the execution paths of the many SQL statements you are tuning.



Programming Oracle Triggers and Stored Procedures
Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)
ISBN: 0130850330
EAN: 2147483647
Year: 2003
Pages: 111
Authors: Kevin Owens

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net