0310-0313
Page 310
| NOTE |
Listing 13.5 uses lines 28 through 31 to skip to the top of a new page. Listing 13.6 uses a break command to skip to a new page after each row of data from the SQL query. The entire SELECT command of each example formats one row of information from the database. |
SQL*Plus Additional Functionality
The remainder of this chapter discusses a variety of ways to format SQL*Plus output to create database-driven types of output (that is, SQL code, operating-system_dependent command language, and script files for other Oracle products).
SQL Creating SQL
The classic example of using SQL*Plus formatting to create other SQL statements (hence the phrase SQL creating SQL) is cleaning up a table after an employee leaves a company. Listing 13.8 is an example of SQL creating SQL. This example uses the Oracle data dictionary view TAB. You easily can enter at the SQL*Plus prompt (shown here as SQL>) the steps in Listing 13.8 or adapt them to a SQL*Plus command file using features you already learned.
Listing 13.8. Dropping all tables owned by a particular user .
SQL>set headings off SQL>set pagesize 0 SQL>set termout off SQL>spool drop_tbl.sql SQL>select `DROP TABLE ` tname `;' from tab; SQL>spool off SQL>set termout on SQL>start drop_tbl
This scenario assumes that the logon ID and the owner of the table objects to be dropped are the same. The first three commands are used to set up the SQL*Plus environment. The spool file drop_tbl.sql captures the concatenated text and table names (tname) from the SQL query. The spool off command closes the file, and the start command executes the drop table commands now inside the drop_tbl.sql file.
| TIP |
It is common practice to use this SQL-creating-SQL example to perform a variety of cleanup and monitoring tasks . |
Page 311
Listing 13.9 is an extension of Listing 13.8 as another example of creating useful database-driven programs. This example adds four auditing fields to the end of each table owned by the user ID that runs this particular SQL*Plus command file. This script also creates a database trigger that automatically maintains these four added fields. I used the fixed-position formatting discussed in Listing 13.5.
Listing 13.9. SQL creating database triggers.
1: set ECHO OFF 2: set TERMOUT OFF 3: set FEEDBACK OFF 4: set VERIFY OFF 5: set PAGESIZE 0 6: set LINESIZE 80 7: set HEADING OFF 8: spool cre_dbtrg.sql 9: select RPAD(`select alter table ` TNAME null,80), 10: RPAD( ` add (inserted_by varchar2(10), ` null,80), 11: RPAD( ` inserted_date date , ` null,80), 12: RPAD( ` updated_by varchar2(10), ` null,80), 13: RPAD( ` updated_date date ); ` null,80) 14: from TAB; 15: select RPAD(` create trigger trg_' TNAME null,80), 16: RPAD(` before insert or update ` null,80), 17: RPAD(` on ` TNAME null,80), 18: RPAD(` for each row ` null,80), 19: RPAD(` begin ` null,80), 20: RPAD(` if :old.inserted_by is null then ` null,80), 21: RPAD(` :new.inserted_by := USER; ` null,80), 22: RPAD(` :new.inserted_date := SYSDATE; ` null,80), 23: RPAD(` :new.updated_by := null; ` null,80), 24: RPAD(` :new.updated_date := null; ` null,80), 25: RPAD(` else ` null,80), 26: RPAD(` :new.inserted_by := :old.inserted_by; ` null,80), 27: RPAD(` :new.inserted_date := :old.inserted_date; ` null,80), 28: RPAD(` :new.updated_by := USER; ` null,80), 29: RPAD(` :new.updated_date := SYSDATE; ` null,80), 30: RPAD(` end if; ` null,80), 31: RPAD(` end; ` null,80), 32: RPAD( `/' null,80) 33: from TAB; 34: spool off 35: set FEEDBACK ON 36: set TERMOUT ON 37: set VERIFY ON 38: set ECHO ON 39: spool dbtrg.log 40: start dbtrg.sql 41: spool off 42: exit
Lines 1 through 7 set up the SQL*Plus environment so that no extra messages appear in the cre_dbtrg.sql file (line 8). Lines 9 through 13 create the SQL alter table statement that adds the audit fields to each table, and lines 15 through 33 create the SQL create trigger
Page 312
statement that adds the database triggers necessary to maintain these audit fields. Lines 35 through 38 reset the SQL*Plus environment so that all SQL commands and messages display. Line 40 then runs the SQL*Plus command file cre_dbtrg.sql that was just created.
| TIP |
In Listing 13.9, line 39 opens the file DBTRG.LOG. This file contains the output (an audit trail) when the DBTRG.SQL statement is executed with the START command on line 40. I like to create SQL audit trails for various DBA commands, particularly ones such as this example where the process is rather automated. The audit trails enable me to review the additions and any errors that might have occurred simply by editing the log file. |
SQL Creating Command-Language Scripts
SQL*Plus formatting commands are quite versatile. In addition to their uses discussed previously, they can be used to create operating-system_dependent command language scripts. The examples in this section apply to an MS-DOS environment; however, the scripts easily can be adapted to any operating-system_dependent command language.
The example in Listing 13.10 applies the SQL-creating-SQL method shown in Listing 13.8 to create a DOS BAT file.
Page 313
Listing 13.10. SQL creating command-language scripts.
1: column HOME_DIR new_value HDIR noprint 2: column PROGRAM_DIR new_value PDIR noprint 3: column PROGRAM_SUFFIX new_value PSUF noprint 4: select HOME_DIR,PROGRAM_DIR,PROGRAM_SUFFIX 5: from APPLICATION_DEFAULTS 6: / 7: spool LIST1414.BAT 8: select `CD &PDIR' 9: from dual 10: / 11: select `DIR *.&PSUF' 12: from dual 13: / 14: select `CD &HDIR' 15: from dual 16: / 17: spool off 18: exit
The following code is the output created by Listing 13.10:
CD \COBOL\PROGRAMS DIR *.COB CD \
Listing 13.10 is a simple example of creating an MS-DOS batch file with SQL*Plus formatting commands. The important concept of this example is in lines 1 through 3. These lines contain three column commands that contain the NEW_VALUE clause. The importance of this concept is that these variables can be loaded from the Oracle database, and their values can be referenced again in other SQL queries. Lines 4 and 5 populate these variables as named in the column statement. Note that when the variables are referenced in other SQL queries (lines 8, 11, and 14), the reference is to the NEW_VALUE variable name .
| TIP |
Use the column command with the NEW_VALUE option to load variables from Oracle tables to use in other SQL queries. |
SQL*Plus Creating Database-Driven Command-Language Scripts
The final example, Listing 13.11, incorporates a variety of concepts discussed in this chapter. The goal of this example is to load all program names and program sizes found in a particular directory structure, along with some database information, into an Oracle database table: APPLICATION_PROGRAMS. This directory structure is stored in a different Oracle database table: APPLICATION_DEFAULTS.
Listing 13.11. An MS-DOS batch command file.
1: PLUS80 -S KELLY/LEIGH @LIST14_16.SQL 2: CALL LIST14_16.BAT 3: SED -F LIST14_19.SED LIST14_15A.DAT > LIST14_15B.DAT 4: SQLLDR80 USERID=KELLY/LEIGH CONTROL=LIST14_16.CTL
Listing 13.11 is the actual MS-DOS BAT command file that runs the four computer tasks to accomplish the goal. The PLUS80 command on line 1 connects to the database and runs the SQL*Plus command file LIST14_16.SQL (see Listing 13.12). LIST14_16.SQL creates two files, LIST14_16.BAT (see Listing 13.12) and LIST14_16.CTL (see Listing 13.13). Line 2 executes the newly created LIST14_16.BAT file. This command creates the file, LIST14_15A.DAT, that is an MS-DOS DIR (directory) list of directory C:\COBOL. Line 3 is a stream editor (SED) that deletes the first few lines and the last few lines (as directed by LIST14_19.SED; see Listing 13.15) of file LIST14_15A.DAT, creating LIST14_15B.DAT. This file is the MS-DOS DIR output without the heading and trailing text information. Line 4 then runs Oracle's SQL*Loader program, using the LIST14_16.CTL SQL*Loader control file created by line 1 and reading the data file LIST14_15B.DAT file created by line 3.
Listing 13.12 is the LIST14_16.SQL file referenced in Line 1 of Listing 13.11; it creates the LIST14_16.BAT file referenced in Line 2 of Listing 13.11.
EAN: 2147483647
Pages: 391