0314-0316

Previous Table of Contents Next

Page 314

Listing 13.12. The SQL*Plus command file LIST14_16.SQL.

 1:    set PAGESIZE 0      2:    column HOME_DIR new_value HDIR noprint      3:    column PROGRAM_DIR new_value PDIR noprint      4:    column PROGRAM_SUFFIX new_value PSUF noprint      5:    select HOME_DIR,PROGRAM_DIR,PROGRAM_SUFFIX      6:       from APPLICATION_DEFAULTS      7:    /      8:    spool  LIST14_16.BAT      9:    select `DIR &PDIR\*.&PSUF > &HDIR\LIST14_15A.DAT'     10:        from dual     11:    /     12:    spool off     13:    spool LIST14_16.ctl     14:    select `load data'     15:         from dual     16:    /     17:    select `infile ` `'''  `LIST14_15B.DAT'  `'''     18:            from dual     19:    /     20:    select `append'     21:          from dual     22:    /     23:    select `into table APPLICATION_PROGRAMS'     24:         from dual     25:    /     26:    select `(PROGRAM_NAME position(1:8) char,'     27:         from dual     28:    /     29:    select `PROGRAM_SUFFIX constant  `  `'''  `&PSUF'  `'''  `,'     30:         from dual     31:    /     32:    select `PROGRAM_SIZE position(15:22) integer external,'     33:           from dual     34:    /     35:    select `PROGRAM_PATH constant `  `'''  `&PDIR'  `'''  `,'     36:          from dual     37:    /     38:    select `ASSIGNED_ANALYST constant `  `'''  `&USER'  `'''  `)'     39:          from dual     40:    /     41:    spool off     42:    exit 

The file in Listing 13.13, LIST14_16.BAT, was created by Listing 13.12, lines 8 and 9.

Listing 13.13. SQL creating MS-DOS batch file output.

 DIR C:\COBOL\*.COB > C:\FILES\LIST14_15A.DAT 

The Oracle SQL*Loader control file in Listing 13.14, LIST14_16.CTL, was created by the remainder of Listing 13.12, beginning at line 13.

Page 315

Listing 13.14. SQL creating Oracle SQL*Loader control file output.

 load data     infile `LIST14_15B.DAT'     append     into table APPLICATION_PROGRAMS      (PROGRAM_NAME position(1:8) char,     PROGRAM_SUFFIX constant `COB',     PROGRAM_SIZE position(15:22) integer external,     PROGRAM_PATH constant `C:\COBOL',     ASSIGNED_ANALYST constant `HOTKA') 

The file in Listing 13.15 is needed to modify the LIST14_15A.DAT file ”the file created from an MS-DOS DIR command (see Listing 13.13). Remember that Listing 13.13 was created by Listing 13.12 on Line 9.

Listing 13.15. The LIST14_17.SED file used in Listing 13.15, line 3.

 1,4d     /bytes/,$d 

Listing 13.12 expands on the Listing 13.10 example. This SQL*Plus command file reads the Oracle database, loading three user variables with default information from database table APPLICATION_DEFAULTS (lines 2 through 7). Line 8 opens the first file, LIST14_16.BAT. Simple text, in the form of MS-DOS commands, is joined with information stored in the earlier-
mentioned variables with default information (see line 9 for syntax and Listing 13.13 to view the results of this SQL query). This file is closed at line 12, and the second output file, LIST14_16.CTL, is opened. This file is the control file that tells Oracle's SQL*Loader what to do. Lines 14 through 40 are a series of select ... from dual SQL queries. Each of these SQL statements outputs one line. The table DUAL (its real name is SYSTEM.DUAL) contains one column and one row and is convenient in the example when only one row of output is desired from each of these SQL queries (lines 14 through 40). Lines 29 and 35 incorporate the default information stored in the user variables. Listing 13.14 displays the results of this series of SQL queries.

The goal here was to use information stored in the Oracle database to retrieve information from an operating-system file system directory and to load this information into the Oracle database. Listing 13.11 drives this whole example, running Listing 13.12 to create the necessary files with information from the Oracle database, preparing the output file created for loading, and running the Oracle SQL*Loader process with the SQL*Loader control file created by Listing 13.14. This example has a simple goal and uses several of the concepts in this chapter.

Page 316

NOTE
The table SYSTEM.DUAL, or DUAL, is a one-column, one-row table that played a major role in the programming of Oracle tools before the introduction of Oracle's PL/SQL software.
NOTE
Line 9 of Listing 13.12 easily can be adapted for more complex command syntax by using the SQL UNION operator discussed previously in this chapter.

Summary

In this chapter, you learned the history and functional uses of SQL*Plus and saw an in-depth list of SQL*Plus commands with examples. You used these commands in a variety of ways to produce report and program output examples. Some of the features discussed in this chapter are not directly referenced in Oracle documentation. For a complete listing of SQL commands and further examples of their use, refer to Oracle's SQL Command Reference Guide.

Applying and modifying the techniques discussed in this chapter can greatly increase the productivity, versatility, and usability of your SQL scripts. Be sure to check out Oracle's SQL scripts included in the ADMIN and RDBMS.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

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