UNIX, Oracle and OS/2: An Unlikely Combination? Part V: Output By Robert Simpson deck: Using Oracle's SQL*Plus to execute a script produces copyright and startup messages that often cause important information to scroll of the screen. However, a simple C program can clean up this output and generate reports. This article concludes this series on managing UNIX and Oracle using OS/2. The first four parts presented an approach for managing UNIX systems and the Oracle DBMS with OS/2. This article will present a simple C language program which cleans up the output and allows generating and printing reports using the approach presented in the first four parts. Although the program presented in this article was designed for and tested with HP LaserJet printers, it may be used with other printers or with no printer at all. The program itself contains only one printer-specific command so it should be easy to make it work with other laser or dot matrix printers. Other printer commands contained in the examples in this article will need to be translated into valid codes for the printer to be used. Purpose When Oracle's SQL*Plus is used to execute a script, it displays copyright and startup messages. Other utilities also produce unneeded messages that just clutter the output from the script, often causing more relevant information to scroll off the screen. A program can be used to remove the extraneous text. A program can be designed to simply read data from the standard input file (stdin) and write it to the standard output file (stdout) while performing a specific function. Programs which operate in this manner are called "filters", because they filter the data as it passes through the program. We want our filter program to somehow eliminate the unnecessary output. Rather than trying to scan for the specific messages we want to eliminate, we'll add some code to the scripts to send special flag characters to tell the program when to start and stop echoing the output. Elimination of text can be controlled by changing where the flag characters are sent from the script, rather than changing and recompiling the program. This approach greatly simplifies the logic of the filter program and, as we'll see in the examples, allows more flexibility. The Program Figure 1 shows the source for the C program, FOF.C. FOF stands for Formatted Output Filter. To create the program for OS/2, you will need a C or C++ compiler for OS/2. The program was created and tested using the C Set ++ compiler, but the program is generic enough that just about any C or C++ compiler should do. It could be compiled for UNIX instead, but the UNIX shell scripts would need to be modified to use the program, instead of modifying the OS/2 command procedures as described in the next section. Since we don't want to take input from the keyboard, the program will be executed with redirection of the standard input file. The input could be redirected from a file using "<" or from another command using the pipe character "|". Output could also be redirected using ">" if we want the output to go to a file instead of the screen. Modifying SQLP.CMD The REXX command procedure which executes SQL*Plus, either directly or indirectly through a UNIX shell script, needs to be modified to use the FOF program. The new SQLP.CMD in Figure 2 combines the functions of BINO.CMD (Figure 3 in Part II) and the old SQLP.CMD using SQL*Plus for UNIX (Figure 4 in Part III). The SQLP.CMD which uses SQL*Plus for OS/2 (Figure 3 in Part III) could be modified in a similar manner if that is the one you are using. The first modification is to add: tempdir = value('TMP',,'OS2ENVIRONMENT') if tempdir \= '' then tempdir = tempdir || '\' call value 'LAST_SCRIPT',script,'OS2ENVIRONMENT' These lines get the path for a temporary directory and save the name of the script being executed. The name of the temporary directory is specified by setting the "TMP" environment variable, which can be done automatically by adding a line such as "SET TMP=d:\TEMP" to CONFIG.SYS, if it is not already there. You should create the specified directory on your hard disk with an OS/2 command like "MKDIR d:\TEMP". The second modification is to redirect the output from the "echo" command and from executing our SQL*Plus scripts to a file using: '>' tempdir || script'.uo' This appears the first time using a single ">" to create or overwrite the file and the second time using a double ">>" to append to the file. Alternatively, the output could be redirected to the FOF program using: '| fof' This would avoid the need for temporary files. However, putting the output in a file allows redisplaying or printing the output without re-executing the script. Now that the raw, unformatted output is in a file, the FOF program is used to filter out the unwanted text. One more line has been added: 'fof <' tempdir || script'.uo' This executes the FOF program, taking input from the unformatted temporary file and sending the output to the standard output device, the screen. Redisplaying and Printing Output The script output was saved in a temporary file to allow redisplaying or printing the output. The REXX command procedure in Figure 3 can be used to create the files for six different commands: o FO - redisplay the formatted output; o FOM - redisplay the formatted output with "MORE"; o FOP - print the formatted output; o UO - display the unformatted output; o UOM - display the unformatted output with "MORE"; and o UOP - print the unformatted output. These files can be executed simply by typing the name of the command shown above. By default, they redisplay or print the output of the last script executed. If the name of a script is included after the command name, they show the output from the last time the specified script was executed. Modifying the SQL*Plus Scripts Next we need to modify the SQL*Plus scripts to control the output. Figure 4 shows the "sysprivs.sql" script (Figure 6 from Part III) with the necessary modifications. The single "echo" line at the beginning of the script has been replaced with: set escape off set newpage 0 host /bin/echo '\033\033System privileges ...' host /bin/echo '\033\014' The last two commands have been changed from "host echo" to "host /bin/echo" in order to ensure that the binary file "echo" in the "/bin" directory is used rather than a built-in shell command with the same name. The UNIX "echo" command allows unprintable control characters to be included in the string by specifying the character as an octal constant. The octal constant is coded as a backslash followed by three digits from 0 through 7. An octal value of "\033" is the character Escape and "\014" is a Form Feed. The line "set escape off" is included to keep SQL*Plus from treating the backslash as the escape character. One other line has been added near the end of the script: host /bin/echo '\033\033' The first and last host commands contain the Escape-Escape flag "\033\033" to indicate the beginning and end of the report. The second host command contains the Escape-FormFeed flag "\033\014" which tells FOF to suppress all output up to and including the next Form Feed character in the output. The "set newpage 0" command causes SQL*Plus to generate a form feed before each page of the report, including the first page. When processed by the FOF program, the output will be "turned off" by the Escape-FormFeed sequence and "turned on" just after the first SQL*Plus-generated FormFeed. Figure 5 shows the "roles.sql" script (Figure 7 from Part III) which has been modified in the same manner. Both of these scripts use Escape-Escape, the delimiter intended for screen output. You can try running these scripts separately, or modify USERP.CMD described as follows. Modifying Other REXX Command Procedures If you have created other REXX command procedures, such as USERP.CMD (Figure 9 in Part IV), they will also need to be modified. Figure 6 shows an updated version of USERP.CMD. The output from each script is being filtered by "piping" to the FOF program, using "|fof". Compare Figures 7 and 8 to see the difference between the unformatted output produced by the USERP.CMD from Part IV and the filtered output from this version. Sending Reports to a Laser Printer The FOF program is also useful for cleaning up the output of reports which are to be sent to a printer. When creating SQL*Plus scripts for reports which are to be printed on an HP LaserJet, the beginning and end of the report can be indicated by an Escape-E sequence instead of Escape-Escape. As far as the FOF program is concerned, the two are equivalent, except that the Escape-E is passed through rather than being filtered out. The Escape-E resets the HP LaserJet to make sure the output will not be affected by settings changed by previous users. The command to change the orientation must be coded after the Escape-E since resetting the printer changes the orientation and all other settings back to their defaults. Figure 9 shows an example of a script "extents.sql" which can produce a printed report listing the number of extents for tables and indexes. The "set linesize" command sets the number of characters per line to 80. The "set pagesize" command sets the number of lines per page to 57. These are the appropriate values for output in portrait orientation at 10 characters per inch and 6 lines per inch. Using SQL*Plus "ttitle" The example in Figure 9 also shows how the SQL*Plus "ttitle" command can be used to produce slightly more sophisticated headings. The title is centered and is made bold using HP LaserJet Printer Control Language (PCL) commands. Most PCL commands begin with the Escape character. Editors usually provide a special way to enter control characters such as the Escape character. To enter an Escape character in the OS/2 Enhanced Editor (EPM), hold down the Alt key, press 2 then 7 on the numeric keypad, then release the Alt key. The Escape character shows up as an arrow pointing to the left. The PCL commands are documented in the HP LaserJet User Manuals. Changing the Orientation Often, a report will not fit on a page oriented vertically (portrait) but will if the page is printed horizontally (landscape). We could create a UNIX shell script in "/util/bin" called "land" with one line: /bin/echo '\033&l1O' | cat - $* "\033" is the "Escape" character, which indicates to an HP LaserJet that a command follows. The next four characters, an ampersand, lower case letter "l", numeral one, and upper case letter "O", is the PCL command to change the page orientation to landscape. The hyphen in the "cat" command tells it to copy the string being piped from the "echo" command before processing the list of files passed in as arguments. The command: bino land myfile > prn will print the UNIX file "myfile" with landscape orientation. Since output from executing a script comes from SQL*Plus itself, not a UNIX file, a slightly different approach is needed. The PCL command to change the orientation and any other printer commands can be included in the SQL*Plus script. The example in Figure 10, "synonyms.sql", shows how to do this. To prevent the ampersand (&) from being interpreted as the beginning of a substitution variable, it must be preceded by the escape character "\" and "set escape on" must be specified. In addition, all other backslash characters must be replaced by a pair (\\). Therefore the octal constants in the echo commands now appear as "\\033" for the Escape character and "\\014" for FormFeed. With SQL*Plus, the escape character can be changed to another unused character, to avoid this special treatment of the backslash character if desired. In this example, the linesize is set to 105 characters and the pagesize is set to 40 lines which are the appropriate values for output in landscape orientation at 10 characters per inch and 6 lines per inch. Adding UNIX Utilities to SQL*Plus Scripts Figure 11 shows an example of a script "counts.sql" which uses the UNIX "grep" utility to help filter the output. In this example, all SQL*Plus output is suppressed by including "\033\014" at the end of the first echo command. Output is turned back on by the line "host /bin/echo '\014'" so that only the output from the last "grep" command is displayed. In this example, "set pagesize 0" is used to suppress all page breaks, otherwise, the output for each table would appear on a separate page. Building an SQL*Plus Script It may occasionally be useful to generate a script containing data from UNIX commands such as "ls". Figure 12 shows a UNIX shell script "allscripts" which executes all of the SQL*Plus scripts in a specific directory. This script could be executed using BINO.CMD (Figure 3 in Part II) and FOF: bino allscripts /util/procs | fof If the scripts in the "/util/procs" directory contained the source for a set of stored procedures then executing the command above would recompile all of the procedures. This example shows how the number of backslash ("\") escape characters can get out of hand. Note how four backslash characters are needed in the "/bin/echo" commands. When the script is interpreted by the UNIX shell, each pair of backslash characters will be replaced by a single backslash, reducing the four characters to two. The unquoted "/bin/echo" commands replace every two remaining backslashes with a single backslash, leaving: host /bin/echo '\033\033' in the resulting script. The quoted "/bin/echo" commands interpret each "\033" string as an octal constant, resulting in an Escape character in the output. If "set escape on" was used instead of "set escape off" then SQL*Plus would also treat the "\" in these commands as an escape character and two backslash characters would be needed for each octal constant in the resulting script. Since one backslash character results from every four in "allscripts", backslash characters would need to be coded in groups of eight! When creating scripts like this, it may be necessary to examine and debug the generated script, then modify the shell script to generate a script with the correct syntax. Putting It All Together One final example will serve to demonstrate all of the techniques described throughout this series of articles. Figure 13 shows a script, "recrtpkindex.sql" which can be used to recreate the primary key index on a given table. The size of the new index is calculated by increasing the index's current allocation by 20 percent and specifying a next extent size of 10 percent of that value. Test this script on a sample table with a primary key before using it on a good table. Documenting These Scripts The documentation for the examples in this article is included in Figure 14. If appended to the "scripts.doc" file in the previous articles, it can be used with the "man" command (Figure 9 in Part II). Security and Prompts In Part I (Technical Support, March 1994), I mentioned that the passwords could be omitted from the REXX command procedures and supplied from the keyboard. Once you start redirecting the output from the "rexec" command to a file or to the "fof" program, this does not work very well because the prompt for the password is also redirected rather than being displayed on the screen. The same problem affects any prompts which require a response from the keyboard. For example, if the database is down, you will have to press return three times to respond to the prompts for the username. For another example, remove the final "exit" command from any of the SQL*Plus scripts and execute it with the SQLP.CMD presented in the third part of this series. When SQL*Plus finishes the script, it will sit and wait at the "SQL>" prompt until you type "exit" and press Enter. If you execute the same script with the SQLP.CMD presented in this article, the same thing happens but since the output is being redirected, you will not be able to see what is happening. This is more of an issue while testing your scripts than once those scripts are working properly. Rather than adding the "fof" program to the REXX command files, you may prefer to use the REXX command procedures from Part III and add the pipe manually, like this: sqlp myscript|fof Another alternative is to keep both versions of SQLP.CMD by renaming one or both of them. During testing, use the one which does not redirect the script output. What Do You Think? If you have tried the approach presented here, I hope you have found it to be useful. If so, or if you have found ways to improve on it, please let me know. Was this article of value to you? If so, please circle Reader Response Card No. NaSPA member Robert Simpson has more than 15 years computing experience, specializing in systems software support. He is experienced in installing and supporting OS/2 and related communications software, as well as data base and communications software on the MVS/ESA platform. He can be reached via CompuServe ID: 71520 ,737 or Internet address 71520.737@compuserve.com. callout: When Oracle's SQL*Plus is used to execute a script, it displays copyright and startup messages that just clutter the output from the script, often causing more relevant information to scroll off the screen. A program can be used to remove the extraneous text. callout: Elimination of text can be controlled by changing where the flag characters are sent from the script, rather than changing and recompiling the program. This approach greatly simplifies the logic of the filter program and allows more flexibility. Figure 1: FOF.C - Source Code for Formatted Output Filter The purpose of this program is to allow formatting output which is produced by UNIX and Oracle. Special codes can be added to the UNIX or Oracle scripts using /bin/echo which this program can then act upon. The codes for screen or printed output are: \033\033 Esc Esc Start or stop displaying output \033\014 Esc FF Stop displaying output until next form feed Additional codes for HP LaserJets include: \033E Esc E Start or stop displaying output and reset printer -- to be used only at the beginning or end of output directed to an HP LaserJet printer. Resetting the printer at the beginning prevents fonts, orientation and other printer commands in previous output from affecting your output. Resetting the printer at the end prevents printer commands in your output from affecting the next person's output. (This is just courteous printer etiquette!) \033&l1O Landscape orientation Any other HP LaserJet PCL printer commands may also be used. */ #include main(int argc, char *argv[], char *envp[]) { int Copying = 0, Discarding = 0, foundEscE = 0; int ch1, ch2, Esc = '\x1B', FormFeed = '\x0C'; /* Read the first character */ discard_two: ch1 = getchar(); /* Loop reading characters from stdin */ while ( ch1 != EOF ) { /* Get one extra character from input */ ch2 = getchar(); /* Check for Esc E or Esc Esc */ if ( ch1 == Esc && ( ch2 == 'E' || ch2 == Esc ) ) { /* Determine if this is the first or second one of a pair */ if ( Copying ) { /* second Esc E */ /* Stop copying characters */ Copying = 0; } else { /* first Esc E */ /* Start copying characters */ Copying = 1; Discarding = 0; /* If it's an Esc E, copy the Esc E itself */ if ( ch2 == 'E' ) { foundEscE = 1; putchar( ch1 ); putchar( ch2 ); } /* endif */ } /* endif */ /* Go read next character from input */ goto discard_two; } /* endif */ /* Check for Esc FormFeed */ if ( ch1 == Esc && ch2 == FormFeed ) { /* Turn on "copying" flag, if not already on */ Copying = 1; /* Discard all characters up to and including next form feed */ Discarding = 1; /* Go read next character from input */ goto discard_two; } /* endif */ /* Check for FormFeed without Esc */ if ( Discarding && ch1 == FormFeed ) { /* Start copying again */ Discarding = 0; /* but discard the form feed */ goto discard_one; } /* endif */ /* Copy characters from stdin to stdout */ if ( Copying && ! Discarding ) { putchar( ch1 ); } /* endif */ /* Save the character for the next iteration */ discard_one: ch1 = ch2; } /* endwhile */ /* If there was an Esc E, reset at end also */ if ( foundEscE ) { putchar( Esc ); putchar( 'E' ); } /* endif */ /* Return to operating system */ return 0; } Figure 2: OS/2 REXX Command Procedure u:\cmd\sqlp.cmd Using UNIX Script Which Executes SQL*Plus for UNIX /* SQLP.CMD */ /* revised to use the FOF formatting program */ parse arg script parms sqlpath = 'u:\dba\' file = sqlpath || script'.sql' if stream(file,'c','query exists') == '' then do say script 'is not a valid SQL*Plus script name' exit end /* Do */ tempdir = value('TMP',,'OS2ENVIRONMENT') if tempdir \= '' then tempdir = tempdir || '\' call value 'LAST_SCRIPT',script,'OS2ENVIRONMENT' '@echo off' 'echo sqlp.cmd' script parms '>' tempdir || script'.uo' 'rexec yourHostname -l yourUsername -p yourPassword + csh /usr/yourUsername/bin/sqlp' script parms + '>>' tempdir || script'.uo' /* change "sqlp" to another UNIX shell script name */ /* to run this file against other Oracle databases */ 'fof <' tempdir || script'.uo' exit Note: + indicates a line which is shown as a separate line but should be typed as a continuation of the previous line Figure 3: OS/2 REXX Command Procedures to Redisplay or Print Output /***************************************************/ /* Create six command procedures from this figure: */ /* */ /* For first three, F*.CMD, use "begcmd = '@fof<'" */ /* */ /* For FO.CMD, use "endcmd = ''" (as shown below) */ /* For FOM.CMD, use "endcmd = '|more'" */ /* For FOP.CMD, use "endcmd = '>prn'" */ /* */ /* For UO.CMD, use "begcmd = '@type'" */ /* For UOM.CMD, use "begcmd = '@more<'" */ /* For UOP.CMD, use "begcmd = '@print'" */ /* */ /* For the last three, U*.CMD, use "endcmd = ''" */ /***************************************************/ begcmd = '@fof<' endcmd = '' binpath = 'u:\bin\' sqlpath = 'u:\dba\' parse arg script parms if script = '' then do script = value('LAST_SCRIPT',,'OS2ENVIRONMENT') if script = '' then do say 'Please specify the name of the script' exit end /* Do */ end /* Do */ tempdir = value('TMP',,'OS2ENVIRONMENT') if tempdir \= '' then tempdir = tempdir || '\' file = tempdir||script'.uo' if stream(file,'c','query exists') == '' then do file = sqlpath || script'.sql' if stream(file,'c','query exists') == '' then do file = binpath || script if stream(file,'c','query exists') == '' then say script 'is not a valid script name' else say 'There is no output from UNIX shell script' script end /* Do */ else say 'There is no output from SQL*Plus script' script exit end /* Do */ begcmd tempdir || script'.uo' endcmd exit Figure 4: SQL*Plus Script /util/dba/sysprivs.sql set escape off set newpage 0 host /bin/echo '\033\033System privileges for user name(s) beginning with &1' host /bin/echo '\033\014' SELECT grantee "User", privilege "Privilege" FROM dba_sys_privs WHERE grantee LIKE UPPER('&1.%') ORDER BY grantee, privilege; host /bin/echo '\033\033' exit Figure 5: SQL*Plus Script /util/dba/roles.sql set escape off set newpage 0 host /bin/echo '\033\033Roles for user name(s) beginning with &1' host /bin/echo '\033\014' SELECT grantee "User", granted_role "Granted Role", default_role || ' ' "Default" FROM dba_role_privs WHERE grantee LIKE UPPER('&1.%') ORDER BY grantee, default_role DESC, granted_role; host /bin/echo '\033\033' exit Figure 6: OS/2 REXX Command Procedure u:\cmd\userp.cmd /* USERP.CMD */ /* Display roles and privileges for a production user */ /* revised to use the FOF formatting program */ parse arg user rest '@echo off' 'rexec yourHostname -l yourUsername -p yourPassword + csh /usr/yourUsername/bin/sqlp roles' user '|fof' 'rexec yourHostname -l yourUsername -p yourPassword + csh /usr/yourUsername/bin/sqlp sysprivs' user '|fof' Note: + indicates a line which is shown as a separate line but should be typed as a continuation of the previous line Figure 7: Unformatted Output From u:\cmd\userp.cmd sqlp @roles rsimpson SQL*Plus: Release 3.1.3.4.1 - Production on Thu Mar 23 20:11:53 1995 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Connected to: Oracle7 Server Release 7.1.3.2.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.3.2.0 - Production Roles for user name(s) beginning with rsimpson old 4: WHERE grantee LIKE UPPER('&1.%') new 4: WHERE grantee LIKE UPPER('rsimpson%') User Granted Role Default ------------------------------ ------------------------------ ------- RSIMPSON CONNECT YES RSIMPSON DBA YES RSIMPSON RESOURCE YES Disconnected from Oracle7 Server Release 7.1.3.2.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.3.2.0 - Production sqlp @sysprivs rsimpson SQL*Plus: Release 3.1.3.4.1 - Production on Thu Mar 23 20:11:56 1995 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Connected to: Oracle7 Server Release 7.1.3.2.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.3.2.0 - Production System privileges for user name(s) beginning with rsimpson old 3: WHERE grantee LIKE UPPER('&1.%') new 3: WHERE grantee LIKE UPPER('rsimpson%') GRANTEE PRIVILEGE ------------------------------ ---------------------------------------- RSIMPSON UNLIMITED TABLESPACE Disconnected from Oracle7 Server Release 7.1.3.2.0 - Production Release with the distributed and parallel query options L/SQL Release 2.1.3.2.0 - Production Figure 8: Formatted Output From u:\cmd\userp.cmd Roles for user name(s) beginning with rsimpson User Granted Role Default ------------------------------ ------------------------------ ------- RSIMPSON CONNECT YES RSIMPSON DBA YES RSIMPSON RESOURCE YES System privileges for user name(s) beginning with rsimpson User Privilege ------------------------------ ---------------------------------------- RSIMPSON UNLIMITED TABLESPACE Figure 9: SQL*Plus Script /util/dba/extents.sql set escape off set linesize 80 set newpage 0 set pagesize 57 column sysdate noprint new_value date_var column owner noprint new_value owner_var column "Bytes" format 999,999,999 column "Blocks" format 999,999 ttitle center '(s3BExtents for tables owned by &1 and beginning with &2' skip 2 - left 'Creator: ' owner_var '(s0B' right 'Date: ' date_var ' Page:' format 999 SQL.PNO skip 2 host /bin/echo '\033E\033\014' select sysdate, substr(owner,1,7) "Owner", substr(segment_name,1,25) "Table Name", substr(tablespace_name,1,8) "Tsp.Name", count(*) "#ext.", sum(bytes) "Bytes", sum(blocks) "Blocks" from dba_extents where segment_type = 'TABLE' and owner = upper('&1') and segment_name like upper('%&2.%') group by owner, segment_name, tablespace_name order by 5 desc, segment_name; host /bin/echo '\014\033\033' ttitle center '(s3BExtents for indexes on tables owned by &1 and beginning with &2' skip 2 - left 'Creator: &1.(s0B' right 'Date: ' date_var ' Page:' format 999 SQL.PNO skip 2 host /bin/echo '\033\014' select sysdate, substr(x.owner,1,7) "Owner", substr(x.segment_name,1,25) "Index Name", substr(x.tablespace_name,1,8) "Tsp.Name", count(*) "#ext.", sum(x.bytes) "Bytes", sum(x.blocks) "Blocks" from dba_indexes i, dba_extents x where i.table_owner = upper('&1') and i.table_name like upper('%&2.%') and x.owner = i.owner and x.segment_name = i.index_name group by x.owner, x.segment_name, x.tablespace_name order by 5 desc, x.segment_name; host /bin/echo '\033E' exit Note: "" represents the Escape character (ASCII code 27, hex 1B). To enter this character using the Enhanced Editor (EPM), hold down the Alt key, type "2" then "7" on the numeric keypad, then release the Alt key. Figure 10: SQL*Plus Script /util/dba/synonyms.sql set escape on set linesize 105 set newpage 0 set pagesize 40 host /bin/echo '\\033E\\033\&l1O\\033(s3BSynonyms for tables owned by &1 and beginning with &2\\033(s0B' host /bin/echo '\\033\\014' select synonym_name "Synonym Name", substr(owner,1,7) "Owner", substr(table_owner,1,7) "Tbl Own", table_name "Table Name", substr(db_link,1,13) "DB Link" from dba_synonyms where table_owner = upper('&1') and table_name like upper('&2.%') order by synonym_name, owner; host /bin/echo '\\033E' exit ----------------------------------------------------------------- ---------- Note: The pertinent command in the first host command is the HP LaserJet PCL5 command Escape ("\\033"), ampersand ("\&"), lower case letter "l", numeral one ("1"), capital letter "O". Figure 11: SQL*Plus Script /util/dba/counts.sql set escape off set pagesize 0 host /bin/echo '\033E\033(s3BCounts for tables owned by &1 beginning &2\033(s0B\033\014' set heading off spool tmpcount select 'select ''' || owner || '.' || table_name || ''' "Name", count(*) from ' || owner || '.' || table_name || ';' from all_tables where owner = upper('&1') and table_name like upper('&2.%') order by 1; spool off column "Name" format a30 host rm tmpcount.sql host grep -v "4: where" tmpcount.lst>tmpcount.sql set linesize 75 spool tmpcount @tmpcount spool off set heading on host /bin/echo '\014' host grep -v '^$' tmpcount.lst host /bin/echo '\033E' exit Figure 12: UNIX Shell Script /util/bin/allscripts /bin/echo "set escape off">tmpallscripts.sql /bin/echo "host /bin/echo '\\\\033\\\\033'">>tmpallscripts.sql /bin/echo "spool tmpallscripts">>tmpallscripts.sql ls $1/*.sql|awk '{print "@"$1}'>>tmpallscripts.sql /bin/echo "spool off">>tmpallscripts.sql /bin/echo "host /bin/echo '\\\\033\\\\033'">>tmpallscripts.sql /bin/echo "exit">>tmpallscripts.sql env ORACLE_HOME=/u/oracle ORACLE_SID=PROD /u/oracle/bin/sqlplus yourUsername/yourPassword @tmpallscripts Figure 13: SQL*Plus Script /util/dba/recrtpkindex.sql set escape off set linesize 80 set newpage 0 set pagesize 57 host /bin/echo '\033E\033(s3BColumns in primary key constraint for table &1..&2\033(s0B' host /bin/echo '\033\014' select c.constraint_name, cc.position, cc.column_name from dba_constraints c, dba_cons_columns cc where c.owner = upper('&1.') and c.table_name = upper('&2.') and c.constraint_type = 'P' and cc.owner = c.owner and cc.constraint_name = c.constraint_name order by c.constraint_name, cc.position, cc.column_name; host /bin/echo '\033\033' host /bin/echo '\033\033\033(s3BColumns in indexes for table &1..&2\033(s0B' host /bin/echo '\033\014' select ic.index_name, substr(ic.index_owner,1,7) "Ind Own", ic.column_position, ic.column_name from dba_ind_columns ic where ic.table_owner = upper('&1.') and ic.table_name = upper('&2.') order by ic.index_name, ic.index_owner, ic.column_position; host /bin/echo '\033\033' host /bin/echo '\033\033\033(s3BNon-primary key indexes for table &1..&2\033(s0B' host /bin/echo '\033\014' select ic.index_name, substr(ic.index_owner,1,7) "Ind Own", ic.column_position, ic.column_name, cc.position, cc.column_name from dba_ind_columns ic, dba_constraints c, dba_cons_columns cc where ic.table_owner = upper('&1.') and ic.table_name = upper('&2.') and c.owner = upper('&1.') and c.table_name = upper('&2.') and c.constraint_type = 'P' and cc.owner = c.owner and cc.constraint_name = c.constraint_name and cc.position = ic.column_position and cc.column_name <> ic.column_name order by ic.index_name, ic.index_owner, ic.column_position; host /bin/echo '\033\033' set heading off spool tmpdispkindex.sql select 'ALTER TABLE &1..&2 DISABLE PRIMARY KEY CASCADE;' from dba_tables where owner = upper('&1.') and table_name = upper('&2.'); spool off spool tmpenapkindex.sql select 'ALTER TABLE &1..&2 ENABLE PRIMARY KEY USING INDEX ' || 'storage (initial ' || trunc(sum(bytes + bytes/5) / 1000 + 1) || ' K ' || ' next ' || trunc(sum((bytes + bytes/5)/10) / 1000 + 1) || ' K ' || ' pctincrease 0);' from dba_indexes i, dba_extents x where i.table_owner = upper('&1.') and i.table_name = upper('&2.') and x.owner = i.owner and x.segment_name = i.index_name and not exists ( select ic.index_name, substr(ic.index_owner,1,7) "Ind Own", ic.column_position, ic.column_name, cc.position, cc.column_name from dba_ind_columns ic, dba_constraints c, dba_cons_columns cc where ic.index_owner = i.owner and ic.index_name = i.index_name and ic.table_owner = upper('&1.') and ic.table_name = upper('&2.') and c.owner = upper('&1.') and c.table_name = upper('&2.') and c.constraint_type = 'P' and cc.owner = c.owner and cc.constraint_name = c.constraint_name and cc.position = ic.column_position and cc.column_name <> ic.column_name ); spool off host /bin/echo '\033\033' host /bin/echo '\033(s3BDisabling Primary Key Index\033(s0B' host /bin/echo '\033\014' set echo on spool tmpdispkindex @tmpdispkindex set echo off host /bin/echo '\033\033' spool off host /bin/echo '\033\033' host /bin/echo '\033(s3BEnabling Primary Key Index\033(s0B' host /bin/echo '\033\014' set echo on spool tmpenapkindex @tmpenapkindex set echo off host /bin/echo '\033\033' spool off spool tmpenafkcons.sql select 'ALTER TABLE ' || b.owner || '.' || b.table_name || ' ENABLE CONSTRAINT ' || b.constraint_name || ';' from dba_constraints a, dba_constraints b where b.r_owner = a.owner and b.r_constraint_name = a.constraint_name and a.owner like upper('&1.%') and a.table_name like upper('&2.%') and a.constraint_type = 'P' and b.status <> 'ENABLED'; spool off host /bin/echo '\033\033' host /bin/echo '\033(s3BEnabling Foreign Key Constraints\033(s0B' host /bin/echo '\033\014' set echo on spool tmpenafkcons @tmpenafkcons set echo off host /bin/echo '\033\033' spool off set heading on exit Figure 14: Documentation to be Appended to Text File u:\doc\scripts.doc OS/2 REXX command procedures - d:\util\cmd These are the command procedures which contain a particular user's username and password and therefore are stored on the local hard disk. sqlp.cmd - Execute an SQL*Plus script against the production database sqlp