SQL*Plus Additional Functionality

The remainder of this chapter will discuss a variety of methods of formatting SQL*Plus output to create database-driven types of output (SQL code, operating system-dependent command language, script files for other Oracle products, and so on).

SQL Creating SQL

The classic example of using SQL*Plus formatting to create other SQL statements (hence the term “SQL creating SQL”) is that of table cleanup after an employee leaves a company. The Oracle data dictionary table TAB is used in this example. The steps in Listing 22.16 can easily be entered at the SQL_Plus prompt (shown here as SQL>), or adapted to a SQL_Plus command file using features already learned.

Listing 22.16. Dropping all of a user’s tables example.

      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 scenerio assumes that the login ID and the owner of the table objects to be dropped are one and the same. The first three commands are used to set up the SQL*Plus environment. The spool file drop_tbl.sql will capture the concatenated text and table names (tname) from the SQL query. The spool off closes the file and the start executes the drop table commands now inside the drop_tbl.sql file.

---

Tip:  
It is common practice to create versions of this and other SQL scripts that take command line parameters to do a variety of cleanup and monitoring tasks.

---

The creating database triggers code example in Listing 22.17 will add four auditing fields to the end of each table owned by the userid that runs this particular SQL*Plus command file. This script will also create a database trigger that will automatically maintain these four added fields.

Lines 1 through 7 (Listing 22.17) set up the SQL*Plus environment so that no extra messages appear in the cre_dbtrg.sql file (see line 8). Lines 9 through 14 create the SQL alter table statement that will add the audit fields to each table, and lines 15 through 33 will create 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 (see Listing 22.18) that was just created.

Listing 22.17. 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(‘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 cre_dbtrg.sql

41:    spool off

42:    exit

Listing 22.18. Creating database triggers output.

    alter table EXAMPLE_TABLE

             add (inserted_by      varchar2(10),

                  inserted_date        date    ,

                  updated_by       varchar2(10),

                  updated_date         date    );



     create trigger trg_EXAMPLE_TABLE

      before insert or update

         on

         for each row

     begin

      if :old.inserted_by is null then

          :new.inserted_by   := USER;

         :new.inserted_date := SYSDATE;

         :new.updated_by    := null;

          :new.updated_date  := null;

        else

        :new.inserted_by   := :old.inserted_by;

         :new.inserted_date := :old.inserted_date;

         :new.updated_by    := USER;

         :new.updated_date  := SYSDATE;

         end if;

      end;

    /

Third-Party Products

There are quite a number of tools available for the Oracle environment from companies other than Oracle Corporation. There are tools that assist the DBA with such tasks as tablespace reorganization, multiple types of database administration from one tool, schema management and movement, tuning, and backup and recovery. The Data Administrator uses modeling tools, SQL generators, and so on. Developers need code version control, coding environments, symbolic debuggers, and tuning aids. End users need OLAP tools.