Oracle: Cluster with example

Clustering is a method of storing tables that are intimately related and often joined together into the same area on disk. The cluster key is the column or columns by which the tables are usually joined in a query

By storing the field comprising the Cluster Key once instead of multiple times, storage is saved. The arguably more significant advantage to Clustering is to expidite join queries. When a query is fired that joins these 2 tables by Cluster Key, the joined rows would be fetched with a single IO operation.
A cluster is a data structure that improves retrieval performance

Example:
----------------------------------------------------------
create cluster empdept (did number(2));
----------------------------------------------------------
create index empdept_indx on cluster empdept;
----------------------------------------------------------
create table emp
(
eid number(10),
ename varchar2(100),
did number(2)
)
cluster empdept(did);
----------------------------------------------------------
create table dept
(
did number(2),
dname varchar2(100)
)
cluster empdept(did);
----------------------------------------------------------

Overview of Table Clusters

A table cluster is a group of tables that share common columns and store related data in the same blocks. When tables are clustered, a single data blockcan contain rows from multiple tables. For example, a block can store rows from both the employees and departments tables rather than from only a single table.

The cluster key is the column or columns that the clustered tables have in common. For example, the employees and departments tables share thedepartment_id column. You specify the cluster key when creating the table cluster and when creating every table added to the table cluster.

The cluster key value is the value of the cluster key columns for a particular set of rows. All data that contains the same cluster key value, such asdepartment_id=20, is physically stored together. Each cluster key value is stored only once in the cluster and the cluster index, no matter how many rows of different tables contain the value.

For an analogy, suppose an HR manager has two book cases: one with boxes of employees folders and the other with boxes of departments folders. Users often ask for the folders for all employees in a particular department. To make retrieval easier, the manager rearranges all the boxes in a single book case. She divides the boxes by department ID. Thus, all folders for employees in department 20 and the folder for department 20 itself are in one box; the folders for employees in department 100 and the folder for department 100 are in a different box, and so on.

You can consider clustering tables when they are primarily queried (but not modified) and records from the tables are frequently queried together or joined. Because table clusters store related rows of different tables in the same data blocks, properly used table clusters offer the following benefits over nonclustered tables:

• Disk I/O is reduced for joins of clustered tables.
• Access time improves for joins of clustered tables.
• Less storage is required to store related table and index data because the cluster key value is not stored repeatedly for each row.

Typically, clustering tables is not appropriate in the following situations:

• The tables are frequently updated.
• The tables frequently require a full table scan.
• The tables require truncating.
  
  

  Overview of Indexed Clusters

  An indexed cluster is a table cluster that uses an index to locate data. The cluster index is a B-tree index on the cluster key. A cluster index must be created before any rows can be inserted into clustered tables.

  Assume that you create the cluster employees_departments_cluster with the cluster key department_id, as shown in Example 2-8. Because the HASHKEYSclause is not specified, this cluster is an indexed cluster. Afterward, you create an index named idx_emp_dept_cluster on this cluster key.

  Example 2-8 Indexed Cluster

  CREATE CLUSTER employees_departments_cluster
     (department_id NUMBER(4))
  SIZE 512;
  
  CREATE INDEX idx_emp_dept_cluster ON CLUSTER employees_departments_cluster;
  

  You then create the employees and departments tables in the cluster, specifying the department_id column as the cluster key, as follows (the ellipses mark the place where the column specification goes):

  CREATE TABLE employees ( ... )
     CLUSTER employees_departments_cluster (department_id);
   
  CREATE TABLE departments ( ... )
     CLUSTER employees_departments_cluster (department_id);
  

  
  

  Finally, you add rows to the employees and departments tables. The database physically stores all rows for each department from the employees anddepartments tables in the same data blocks. The database stores the rows in a heap and locates them with the index.

  
  

  Figure 2-6 shows the employees_departments_cluster table cluster, which contains employees and departments. The database stores rows for employees in department 20 together, department 110 together, and so on. If the tables are not clustered, then the database does not ensure that the related rows are stored together.

  Figure 2-6 Clustered Table Data

  
  Description of "Figure 2-6 Clustered Table Data"

  The B-tree cluster index associates the cluster key value with the database block address (DBA) of the block containing the data. For example, the index entry for key 20 shows the address of the block that contains data for employees in department 20:

  20,AADAAAA9d
  

  The cluster index is separately managed, just like an index on a nonclustered table, and can exist in a separate tablespace from the table cluster.

  
  

  Overview of Hash Clusters

  
  
  A hash cluster is like an indexed cluster, except the index key is replaced with a hash function. No separate cluster index exists. In a hash cluster, the data is the index.

  With an indexed table or indexed cluster, Oracle Database locates table rows using key values stored in a separate index. To find or store a row in an indexed table or table cluster, the database must perform at least two I/Os:

  • One or more I/Os to find or store the key value in the index
  • Another I/O to read or write the row in the table or table cluster

  
  

  To find or store a row in a hash cluster, Oracle Database applies the hash function to the cluster key value of the row. The resulting hash value corresponds to a data block in the cluster, which the database reads or writes on behalf of the issued statement.

  
  

  Hashing is an optional way of storing table data to improve the performance of data retrieval. Hash clusters may be beneficial when the following conditions are met:

  
  

  • A table is queried much more often than modified.
  • The hash key column is queried frequently with equality conditions, for example, WHERE department_id=20. For such queries, the cluster key value is hashed. The hash key value points directly to the disk area that stores the rows.
  • You can reasonably guess the number of hash keys and the size of the data stored with each key value.

  
  

  Hash Cluster Creation

  The cluster key, like the key of an indexed cluster, is a single column or composite key shared by the tables in the cluster. The hash key values are actual or possible values inserted into the cluster key column. For example, if the cluster key is department_id, then hash key values could be 10, 20, 30, and so on.

  Oracle Database uses a hash function that accepts an infinite number of hash key values as input and sorts them into a finite number of buckets. Each bucket has a unique numeric ID known as a hash value. Each hash value maps to the database block address for the block that stores the rows corresponding to the hash key value (department 10, 20, 30, and so on).

  To create a hash cluster, you use the same CREATE CLUSTER statement as for an indexed cluster, with the addition of a hash key. The number of hash values for the cluster depends on the hash key. In Example 2-9, the number of departments that are likely to exist is 100, so HASHKEYS is set to 100.

  
  

  Example 2-9 Hash Cluster

  CREATE CLUSTER employees_departments_cluster
     (department_id NUMBER(4))
  SIZE 8192 HASHKEYS 100;
  

  After you create employees_departments_cluster, you can create the employees and departments tables in the cluster. You can then load data into the hash cluster just as in the indexed cluster described in Example 2-8.

  
  

  Hash Cluster Queries

  
  

  The database, not the user, determines how to hash the key values input by the user. For example, assume that users frequently execute queries such as the following, entering different department ID numbers for p_id:

  
  

  SELECT *
  FROM   employees
  WHERE  department_id = :p_id;
   
  SELECT * 
  FROM   departments 
  WHERE  department_id = :p_id;
  
  SELECT * 
  FROM   employees e, departments d 
  WHERE  e.department_id = d.department_id
  AND    d.department_id = :p_id;
  

  
  

  If a user queries employees in department_id=20, then the database might hash this value to bucket 77. If a user queries employees in department_id=10, then the database might hash this value to bucket 15. The database uses the internally generated hash value to locate the block that contains the employee rows for the requested department.

  
  

  Figure 2-7 depicts a hash cluster segment as a horizontal row of blocks. As shown in the graphic, a query can retrieve data in a single I/O.

  Figure 2-7 Retrieving Data from a Hash Cluster

  
  Description of "Figure 2-7 Retrieving Data from a Hash Cluster"

  A limitation of hash clusters is the unavailability of range scans on nonindexed cluster keys (see "Index Range Scan"). Assume that no separate index exists for the hash cluster created in Example 2-9. A query for departments with IDs between 20 and 100 cannot use the hashing algorithm because it cannot hash every possible value between 20 and 100. Because no index exists, the database must perform a full scan.

  
  

  Hash Cluster Variations

  A single-table hash cluster is an optimized version of a hash cluster that supports only one table at a time. A one-to-one mapping exists between hash keys and rows. A single-table hash cluster can be beneficial when users require rapid access to a table by primary key. For example, users often look up an employee record in the employees table by employee_id.

  A sorted hash cluster stores the rows corresponding to each value of the hash function in such a way that the database can efficiently return them in sorted order. The database performs the optimized sort internally. For applications that always consume data in sorted order, this technique can mean faster retrieval of data. For example, an application might always sort on the order_date column of the orders table.

  
  

  Hash Cluster Storage

  
  

  Oracle Database allocates space for a hash cluster differently from an indexed cluster. In Example 2-9, HASHKEYS specifies the number of departments likely to exist, whereas SIZE specifies the size of the data associated with each department. The database computes a storage space value based on the following formula:

  
  

  HASHKEYS * SIZE / database_block_size
  

  
  

  Thus, if the block size is 4096 bytes in Example 2-9, then the database allocates at least 200 blocks to the hash cluster.

  
  

  Oracle Database does not limit the number of hash key values that you can insert into the cluster. For example, even though HASHKEYS is 100, nothing prevents you from inserting 200 unique departments in the departments table. However, the efficiency of the hash cluster retrieval diminishes when the number of hash values exceeds the number of hash keys.

  
  

  To illustrate the retrieval issues, assume that block 100 in Figure 2-7 is completely full with rows for department 20. A user inserts a new department withdepartment_id 43 into the departments table. The number of departments exceeds the HASHKEYS value, so the database hashes department_id 43 to hash value 77, which is the same hash value used for department_id 20. Hashing multiple input values to the same output value is called a hash collision.

  
  

  When users insert rows into the cluster for department 43, the database cannot store these rows in block 100, which is full. The database links block 100 to a new overflow block, say block 200, and stores the inserted rows in the new block. Both block 100 and 200 are now eligible to store data for either department. As shown in Figure 2-8, a query of either department 20 or 43 now requires two I/Os to retrieve the data: block 100 and its associated block 200. You can solve this problem by re-creating the cluster with a different HASHKEYS value.

  
  

  Figure 2-8 Retrieving Data from a Hash Cluster When a Hash Collision Occurs

  

BITAND function in Oracle/PLSQL

BITAND

Definition:

In Oracle/PLSQL the BITAND function is used to determine whether a particular bit is set or not. It is most commonly used with the DECODE function. The function takes 2 arguments and performs these steps:

• Converts the 2 arguments to binary (n-bit two's complement binary integer value)
• Performs a standard bitwise AND operation on the two strings
• Converts the binary result back to a decimal format

The BITAND function essentially does a logical AND of two bit strings. If the values in any position are both '1', then the result will have a '1' in that position, otherwise the result will have a '0' in that position. This is a very fast, very efficient way of checking if a particular bit has been set. It's efficient because it makes use of a technique called 'bit masking'.

Syntax:

bitand( expr1, expr2 )

BITAND computes an AND operation on the bits of expr1 and expr2, both of which must resolve to non-negative integers. BITAND returns an integer value.

NOTE: The BITAND function does not determine the datatype of the value returned. Therefore, in SQL*Plus, you must specify BITAND in a wrapper, such as TO_NUMBER, which returns a datatype.

Applies To

·         Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

For Example

Bitand(5,3)          would return 1

Bitand(15,7)       would return 7

Bitand(5,2)          would return 0

Bitand(5,0)          would return 0

Bitand(6,2)          would return 2

Examples:-

1. BITAND(1,1)  — returns 1
2. BITAND(1,0)  — returns 0
3. BITAND(0,1)  — returns 0
4. BITAND(-1,-1)– returns -1
5.  BITAND(null,null) – returns NULL

6. BITAND(24,18) – returns 8

Explanation:-

Binary representation of 24 is 11000
Binary representation of 15 is 1111

24	15	BITAND	Result	Explanation
1
1	1	====>	1	(1 AND 1) is 1
0	1	====>	0	(0 AND 1) is 0
0	1	====>	0	(0 AND 1) is 0
0	1	====>	0	(0 AND 1) is 0

(Note that that bits are considered from right, least significant first)

Result is 1000 in binary format. If you convert 1000 binary into decimal format we get 8.


So BITAND(24,18) = 8

7. BITAND(6,2) - returns 2

Binary representation of 6 is 110
Binary representation of 2 is  10


BITAND returns 10 in Binary, which is 2

8. BITAND(6,3) – returns 2

Binary representation of 6 is 110
Binary representation of 2 is  11


BITAND returns 10 in Binary, which is 2

Batch file creation

                                        Batch file creation

Batch file ABCs

Batch files allow MS-DOS and Microsoft Windows users to create a lists of commands to run in sequence once the batch file has been executed. For example, a batch file could be used to run frequently run commands, deleting a series of files, moving files, etc. A simple batch file does not require any special programming skills and can be done by users who have a basic understanding of MS-DOS commands.

A good example of a batch file for someone who is more familiar with Windows or the MacOS is to think of a batch file as a shortcut in Windows or an icon on the MacOS. Much like a shortcut, batch files could be used to run one or more commands or programs through the command line.

Another example of a very well known batch file is the autoexec.bat, which is a boot batch file loaded each time the computer boots into MS-DOS and early versions of Windows. This batch file contained all the necessary commands and programs used to run MS-DOS and Windows each time the computer booted.

Creating a batch file

MS-DOS users
Microsoft Windows and other users

MS-DOS users

To create a basic batch file in MS-DOS, follow the below steps that give you an example of how to create a basic batch file.

1. Open an MS-DOS command window or get to MS-DOS.
2. At the MS-DOS prompt, type: edit test.bat and press enter.
3. If typed properly, you should now be in a blue screen. Within the screen, type:
   
   pause
   dir c:\windows
   dir c:\windows\system

Once the above three lines have been typed in, click File and choose exit; when prompted to save, click "Yes." Users who do not have a mouse cursor can accomplish this same task by pressing ALT+F to access the file menu, then pressing "X" to exit, and pressing enter to save changes.

Once you are back at the MS-DOS prompt, type: test and press enter. This will execute the test.bat file and begin running the file. Because the first line is pause, you will first be prompted to press a key. Once you press a key the batch file will run line-by-line; in this case, listing the files in the windows and windows\system directories.

If you wish to add more lines to this batch file you would type "edit test.bat" to edit the file again.

Additional information about the MS-DOS edit command can be found on our edit command page. Some versions of MS-DOS and bootable diskettes may not have the edit command; if this the case, you would either need to obtain the edit.com file to access this file or use the copy con command.

Microsoft Windows and other users

A Windows user can still use the above MS-DOS steps if they wish to create a batch file. If, however, you're more comfortable using Microsoft Windows or your operating system, you can use any text editor, such as Notepad or Word pad, to create your batch files, as long as the file extension ends with .bat. In the below example we use the Windows notepad to create a batch file.

Click Start

Click Run

Type: notepad and press enter.

Once notepad is open, type the below lines in the file or copy and paste the below lines into notepad.

@echo off
echo Hello this a test batch file
pause
dir c:\windows

Click File and click Save; browse to where you want to save the file. For the file name, type "test.bat", and if your version of Windows has a "Save as type" option, choose "All files", otherwise it will save as a text file. Once all of this has been done click the Save button and exit notepad.

Now, to run the batch file, double-click or run the file like any other program. Once the batch file has completed running it will close the window automatically.

Batch commands

Just like all MS-DOS commands, all batch file commands are not case sensitive. However, in the below listing we have listed all commands in all caps to help you identify what is a command and what is not.

@	Does not echo back the text after that symbol. This most often used as @ECHO OFF to prevent any of the commands in the batch file from being displayed, just the information needed
%1	The percent followed by a numeric value, beginning with one, allows users to add variables within a batch file. The below line is an example of what can be used in a batch file. ECHO Hello %1 When the above one-line batch file is created, add your name after the batch file. For example, typing myname (being the name of the bat file) and then your name: myname bob would output: Hello bob Note: This can be extended to %2, %3, and so on.
::	One of two ways of adding remarks into the batch file without displaying or executing that line when the batch file is run. Unlike REM this line will not show regardless if ECHO off is in the batch file.
:LABLE	By adding a colon in front of a word, such as LABEL, you create a category, more commonly known as a label. This allows you to skip to certain sections of a batch file such as the end of the batch file. Also see GOTO.
CALL	This used to run another batch file within a batch file. When the batch file that is called is completed, the remainder of the original batch file is completed. Note if the batch file does not exist it will give an error message.
Choice	Allows for batch files and scripts to wait for the user to choose a set of choices choice [/C[:]choices] [/N] [/S] [/T[:]c,nn] [text] Options: /C[:]choices         Specifies allowable keys. Default is YN /N                       Do not display choices and ?at end of prompt string. /S                       Treat choice keys as case sensitive. /T[:]c,nn             Default choice to c after nn seconds Text                    Prompt string to display
CLS	Just like the DOS command would clear your screen.
ECHO	Will echo a message in the batch file. Such as ECHO Hello World will print Hello World on the screen when executed. However, without @ECHO OFF at the beginning of the batch file you'll also get "ECHO Hello World" and "Hello World." Finally, if you'd just like to create a blank line, type ECHO. adding the period at the end creates an empty line.
EXIT	Exits out of the DOS window if the batch file is running from Windows.
GOTO LABLE	Used to go to a certain label, such as LABEL. An example of GOTO would be to GOTO END. Directs Windows to a labeled line in a batch program. GOTO label    Label---Specifies a text string used in the batch program as a label. You type a label on a line by itself, beginning with a colon. GOTO END ECHO SKIPPING THIS :END ECHO DONE
IF	Used to check for a certain condition if the condition exists. If that condition exists it will perform that function.
PAUSE	Prompt the user to press any key to continue.
SHIFT	Changes the position of replaceable parameters in a batch program. Changes the position of replaceable parameters in a batch file. SHIFT [/n] If Command Extensions are enabled the SHIFT command supports the /n switch that tells the command to start shifting at the nth argument, where n may be between zero and eight. For example: SHIFT /2 would shift %3 to %2, %4 to %3, etc. and leave %0 and %1 unaffected. Examples The below example would be done in a batch file; in this example we are naming the batch file test.bat and it contains the below lines. @ECHO OFF ECHO - %1 SHIFT ECHO - %1 After creating the above example test.bat file, if you were to type the below command at the MS-DOS prompt, it would print "- ONE" and then "- TWO"; this command is commonly used to work through each of the command extensions or remove command extensions. TEST ONE TWO
START	Used for Windows 95, Windows 98, and Windows NT 4.0 and above to start a windows application; such as  START C:\WINDOW\CALC would run the Windows Calculator. Users running Windows 3.x need to utilize the WIN command.  For example, WIN C:\Windows\CALC.EXE would run Windows and then Calculator after Windows has finished loading.

Syntax

Starts a separate window to run a specified program or command.

START ["title"] [/Dpath] [/I] [/MIN] [/MAX] [/SEPARATE | /SHARED]
[/LOW | /NORMAL | /HIGH | /REALTIME | /ABOVENORMAL | /BELOWNORMAL]
[/WAIT] [/B] [command/program]
[parameters]

"title"	Title to display in window title bar
path	Starting directory
/B	Start application without creating a new window. The application has ^C handling ignored. Unless the application enables ^C processing, ^Break is the only way to interrupt the application
/I	The new environment will be the original environment passed to the cmd.exe and not the current environment
/MIN	Start window minimized
/MAX	Start window maximized
/SEPARATE	Start 16-bit Windows program in separate memory space
/SHARED	Start 16-bit Windows program in shared memory space
/LOW	Start application in the IDLE priority class
/NORMAL	Start application in the NORMAL priority class
/HIGH	Start application in the HIGH priority class
/REALTIME	Start application in the REALTIME priority class
/ABOVENORMAL	Start application in the ABOVENORMAL priority class
/BELOWNORMAL	Start application in the BELOWNORMAL priority class
/WAIT	Start application and wait for it to terminate
command/program	f it is an internal cmd command or a batch file then the command processor is run with the /K switch to cmd.exe. This means that the window will remain after the command has been run. If it is not an internal cmd command or batch file then it is a program and will run as either a windowed application or a console application.
parameters	These are the parameters passed to the command/program

If Command Extensions are enabled, external command invocation

through the command line or the START command changes as follows:

non-executable files may be invoked through their file association just by typing the name of the file as a command. (e.g. WORD.DOC would launch the application associated with the .DOC file extension). See the ASSOC and FType commands for how to create these associations from within a command script.

When executing an application that is a 32-bit GUI application, CMD.EXE does not wait for the application to terminate before returning to the command prompt. This new behavior does NOT occur if executing within a command script.

When executing a command line whose first token is the string "CMD " without an extension or path qualifier, then "CMD" is replaced with the value of the COMSPEC variable. This prevents picking up CMD.EXE from the current directory.

When executing a command line whose first token does NOT contain an extension, then CMD.EXE uses the value of the PATHEXT environment variable to determine what extensions to look for and in what order. The default value for the PATHEXT variable is:

.COM;.EXE;.BAT;.CMD

Notice the syntax is the same as the PATH variable, with semicolons separating the different elements.

When searching for an executable, if there is no match on any extension, then looks to see if the name matches a directory name. If it does, the START command launches the Explorer on that path. If done from the command line, it is the equivalent to doing a CD /D to that path.

Examples

start notepad myfile.txt

Start a new instance of notepad with the file myfile.txt open in it.  

start /MAX notepad

Start the notepad window with the screen maximized.

start /MIN mybatch.bat

The above example would start the batch file mybatch.bat in a minimized window.

start c:\music\"my song.mp3"

If the file or folder has a space in it you must surround it with quotes. In the above example we're starting the MP3 song file "my song.mp3". Without the quotes surrounding the file name with a space you would get a windows cannot find the file error.

start http://www.computerhope.com/

Open the Computer Hope web page from the command line.

Batch file examples

Running different programs
How to start Windows files and programs from a batch file
Creating a batch file delay
How to make a time log

Running different programs

Below is an example of how you can implement the choice options into your batch files. Each line that is in red can be left out of the batch file. They have been included to help explain some of what the batch file means. Windows 2000 andWindows XP users will need to substitute the choice command with the set command; see the set command page for additional help and information with this command.

@ECHO OFF
REM - LABEL INDICATING THE BEGINNING OF THE DOCUMENT.
:BEGIN
CLS
REM - THE BELOW LINE GIVES THE USER 3 CHOICES (DEFINED AFTER /C:)
CHOICE /N /C:123 /M "PICK A NUMBER (1, 2, or 3)"%1
REM - THE NEXT THREE LINES ARE DIRECTING USER DEPENDING UPON INPUT
IF ERRORLEVEL ==3 GOTO THREE
IF ERRORLEVEL ==2 GOTO TWO
IF ERRORLEVEL ==1 GOTO ONE
GOTO END
:THREE
ECHO YOU HAVE PRESSED THREE
GOTO END
:TWO
ECHO YOU HAVE PRESSED TWO
GOTO END
:ONE
ECHO YOU HAVE PRESSED ONE
:END

How to start Windows files and other programs from a batch file

To run Microsoft Windows programs or files use the START command. The below example would run Windows Notepad.

START /MAX NOTEPAD

You can also specify the direct location of the file by typing the below command.

START /MAX C:\Windows\NOTEPAD.EXE

*Windows users who have a different directory (e.g. Windows 2000 users) would need to substitute WINNT or the name of their directory in place of Windows in the above example.

The /m representing it to start the window Maximized. See the start command page for additional information about this command.

Creating a batch file delay

Below is an example of how to delay a batch file anywhere from 5 to 99 seconds. In the below example we illustrate a 5 second delay.

Type NUL | CHOICE.COM /N /CY /TY,5 >NUL

Additionally, you could use the sleep file found on our utility download section.

How to make a time log in a batch file

The below example demonstrates how to create a time log of when the batch file is loaded, or for example, this could be used in the autoexec.bat when someone logs into a computer that supports this file.

ECHO. |TIME > TIME
COPY LOG +TIME

An alternate, slightly more complicated method that, to our knowledge, cannot be used in Windows NT, Windows 2000 or Windows ME would be the following:

echo @prompt set date=$d$_set time=$t$h$h$h > {a}.bat
%comspec% /e:2048 /c {a}.bat > {b}.bat
for %%v in ({b}.bat del) do call %%v {?}.bat
echo %date% %time% >> log

Another alternative is:

echo. |time |find "current" >> log

For the above batch file to work properly you must create a file called log, by typing edit log and then save and exit the file, creating a 0 bytes file. If this file is not created or not created properly you will receive the error message Content of destination lost before copy.

How to Create a DOS Batch File

Command Line Invocation

Running programs in a DOS Command Window straight from the command line has the limitation that you can only issue one command - you type the name of the program, then any control parameters and options, and then terminate the command line by pressing the Enter key at the end. This runs the program with the options you specified.

For example, NetMailBot is invoked directly on the command line ("C:\>") according to the following:

NetMailBot -to {To} -from {From} -subject {Subject} -server {Name of your SMTP Server} [Optional Parameters] (Enter)

Note that there is no newline (press of the Enter key) until the end; if the above example takes two physical lines on your screen, it's due to word wrap.

If you just want to use NetMailBot in simple situations, this might be acceptable. But if you use a lot of parameters and options, you don't want to have to type them all in each time you want to run NetMailBot. Also, if you have more complex situations, when the context of NetMailBot's use depends on other factors and decisions that have to be made "on the fly", then you need a "little program" that calls NetMailBot within the context of its own execution.

 Thus, you need a batch file!

What is a batch file?

Batch files are special files, often called scripts, that allow you to run programs from the DOS Command Window. Batch files are useful for storing sets of commands that are always executed together, in sequence, because you can simply enter the name of the batch file as the "command" instead of entering each command individually. 

In DOS, batch files end with a .BAT extension. The process of creating them is often called DOS batch scripting.

A Simple Example

Suppose the following text is saved in a file called "mybat.bat":

@ECHO ON

REM This is an example .BAT file output...

@ECHO OFF

REM Nothing fancy...

dir

This batch file contains two comments (or remarks, indicated by "REM") that just provide documentation for either the author or the user of the file. The first one is "echoed" to the screen because it is embedded inside two @ECHO commands that first turn on echoing and then turn it off. Everything between the @ECHO commands is displayed on the screen during the script's execution. After the second comment, there is a simple DOS "dir" command which, as usual, lists the contents of the current directory.

Note that the individual commands or comments are separated by newlines, that is, pressings of the Enter key.

Running the batch file executes the commands in "mybat.bat" sequentially.

Invoking the Batch File

A batch file can be run in two ways:

·         Straight from the command line

·         Double-clicking the file's icon in a Windows Explorer window (or via "My Computer")

For our example above, typing "mybat" or "mybat.bat" at the DOS command line yields the following output:

Double-clicking the batch file icon (see below) will open up a DOS Command Window, invoke NetMailBot within it according to the batch file, and then close the window when everything is done. Depending on how short your script is, if you blink, you might miss it!

A batch file icon:

@echo off
:LOOP
set cTime=%time:~0,-3%

if '%cTime%' equ '13:15' call "C:\Program Files\SI_SCOPE\close.bat"
if '%cTime%' equ '13:16' call "C:\Program Files\SI_SCOPE\copy.bat"
if '%cTime%' equ '13:17' call "C:\Program Files\SI_SCOPE\Hard Fault Captures\RenameFileToDate.bat"
if '%cTime%' equ '13:20' call "C:\WINDOWS\reboot.bat"

goto LOOP

if '%cTime%' equ '10:06' call Copy.bat
if '%cTime%' equ '10:07' call \Hard Fault Captures\RenameFileToDate.bat
if '%cTime%' equ '10:08' call C:\WINDOWS\reboot.bat

another program

CD \Program Files\SI_SCOPE\
set cTime=%time:~0,-3%

if '%cTime%' equ '10:06' call Copy.bat
if '%cTime%' equ '10:07' call \Hard Fault Captures\RenameFileToDate.bat
if '%cTime%' equ '10:08' call C:\WINDOWS\reboot.bat

pause

Advanced Windows batch example - conditional shutdown

@echo off

color 0E

title Conditional Shutdown.

:start

echo Welcome, %USERNAME%

echo What would you like to do?

echo.

echo 1. Shutdown in specified time

echo 2. Shutdown at a specified time

echo 3. Shutdown now

echo 4. Restart now

echo 5. Log off now

echo 6. Hibernate now

echo.

echo 0. Quit

echo.

set /p choice="Enter your choice: "

if "%choice%"=="1" goto shutdown

if "%choice%"=="2" goto shutdown-clock

if "%choice%"=="3" shutdown.exe -s -f

if "%choice%"=="4" shutdown.exe -r -f

if "%choice%"=="5" shutdown.exe -l -f

if "%choice%"=="6" shutdown.exe -h -f

if "%choice%"=="0" exit

echo Invalid choice: %choice%

echo.

pause

cls

goto start

:shutdown

cls

set /p min="Minutes until shutdown: "

set /a sec=60*%min%

shutdown.exe -s -f -t %sec%

echo Shutdown initiated at %time%

echo.

goto cancel

:shutdown-clock

echo.

echo the time format is HH:MM:SS (24 hour time)

echo example: 14:30:00 for 2:30 PM

echo.

set /p tmg=enter the time that you wish the computer to shutdown on:

schtasks.exe /create /sc ONCE /tn shutdown /st %tmg% /tr "shutdown.exe -s -t 00"

echo shutdown initiated at %tmg%

echo.

:cancel

set /p cancel="Type cancel to stop shutdown: "

if not "%cancel%"=="cancel" exit

shutdown.exe -a

cls

schtasks.exe /end /tn shutdown

cls

schtasks.exe /delete /tn shutdown

cls

echo Shutdown is cancelled.

echo.

pause

exit

Batch File Examples

Password Example

The following batch file can be used to establish a password for running a program. The batch file is named START.BAT and calls the program named CALC.EXE.

@ECHO OFF

IF %1==ExeScript GOTO OK

ECHO WRONG PASSWORD

GOTO END

:OK

ECHO PASSWORD IS ACCEPTED...STARTING

CALC.EXE

:END

Below you'll see the response of the computer to various commands.

First the bad password. At the prompt type START 123.
C:/>
C:/>START 123
C:/>BAD PASSWORD

Now use the correct password. Type the correct command at the prompt.
C:/>
C:/>START ExeScript
C:/>PASSWORD IS ACCEPTED...STARTING

At this point the CALC.EXE program starts.

Backup your .doc files (Windows NT/2000/XP version)

usage: backbat backupdir
where: backupdir is the directory to copy your .doc files

@echo off

if not "%1"=="" goto argsok

echo usage:  %0 backupdir

echo where: backupdir is the directory to copy your .doc files

goto end

:argsok

  setlocal

  set backupdir=%1

  if not exist %backupdir% goto notfile

  echo %backupdir% is a file

  goto end

:notfile

  rem  If the directory does not exist, create it.

  if exist %backupdir%\nul goto skipdir

  md %backupdir%

  if "%errorlevel%"=="0" goto skipdir

  echo Error creating backup directory

  goto end

:skipdir

  rem  Copy each .doc file one at a time.

  rem  Note:  the for loop variable (%%b) must be contain only one letter.

  for %%b in ( *.doc ) do copy %%b %backupdir% > nul

  rem  Use the for loop again to check if each file was copied (since it is

  rem  difficult to run multiple commands in a for loop).

  for %%b in ( *.doc ) do if not exist %backupdir%\%%b echo %%b was not copied

:end

rem  Clean up

endlocal

How to make a time log

In the following example, you can create a time log of when the batch file is loaded or, for example, in the autoexec.bat file when someone logs into a computer.

ECHO. |TIME > TIME

COPY LOG +TIME

An alternate slightly more complicated method that, to our knowledge, cannot be used in Windows NT, Windows 2000 or Windows ME would be the following:

echo @prompt set date=$d$_set time=$t$h$h$h > {a}.bat

%comspec% /e:2048 /c {a}.bat > {b}.bat

for %%v in ({b}.bat del) do call %%v {?}.bat

echo %date% %time% >> log

Another alternative is:

echo. |time |find "current" >> log

For the above batch file to work properly, you must create a file called log by typing "edit log" (without the quotes) and then save and exit the file which will create the file at 0 bytes. If this file is not created or not created properly, you will receive the error message, "Content of destination lost before copy".