Previous Table of Contents Next


Chapter 3
SQL And PL/SQL Scripting

The use of scripts and batch processes is the oldest aspect of computing and still plays a very active role in most systems. Most major operating systems support at least one scripting language. Table 3.1 highlights the predominent scripting language for each major operating system.

Table 3.1 The scripting languages supported by major operating systems.

Operating System Scripting Language
MS-DOS MS-DOS batch language
OS/2 REXX
VMS and OpenVMS DCL
Windows 95 Visual Basic, Perl, MS-DOS batch language
Windows NT Visual Basic, Perl, MS-DOS batch language
Unix C shell, Bourne shell, Korn shell, Perl, awk, sed

As you can see, Unix is particularly rich in scripting tools. Unix is also the predominant platform for Oracle databases (Windows NT is gaining strength, while VMS and OpenVMS are gradually disappearing). Consequently, the scripts included in this chapter (where appropriate) contain examples of scripts on a Unix system, but keep in mind that SQL*Plus and PL/SQL can be used with the scripting language of any operating system.

This chapter will introduce you to the basic uses of scripts in an Oracle database and discuss the tools you’ll need to script using SQL*Plus and PL/SQL.

The Many Faces Of Scripting

Scripts are used to perform many types of tasks in an Oracle database. The database administrator’s duties often involve complex tasks that are automated with scripts, but this isn’t the entire world of scripting. Scripts perform many different tasks within a system, including:

  Performing system backups and helping with restoration.
  Administering system security and user creation.
  Dynamically generating code.
  Testing stored PL/SQL objects.
  Any other task that has to be performed regularly.

Backup And Restore

The most well-known scripts in an Oracle database are those that the DBA uses to back up the database. Listing 3.1 is a simple generic script that a DBA could use to perform a cold backup of an Oracle database (you may remember this script from Chapter 1).

Listing 3.1 A generic cold backup script for an Oracle database.

#
# Set up the environment variables.
#
ORACLE_SID=registrar_db; export ORACLE_SID
ORACLE_HOME=/dbhost/database/oracle/v722; export ORACLE_HOME

#
# Shut down the database.
#
svrmgrl
connect internal
shutdown immediate

#
# Backup the database control files, redo logs, and dbf files.
#
tar -cvf /dev/backup/tdr $ORACLE_HOME

#
# Restart the database.
#
svrmgrl
connect internal
startup

Dynamic Code Generation

One common use of SQL scripts is the generation of other SQL scripts. Consider the script shown in Listing 3.2, which dynamically builds and executes an SQL script containing commands to drop all the objects in the specified user’s schema.

Listing 3.2 The DROP_ALL.SQL script.

set head off
set pages 0
set verify off
set lines 80
set feedback off
set termout off

spool &&1.sql

SELECT 'DROP ' || object_type || ' ' || &&1 || '.' ||
       object_name || ';'
FROM   ALL_OBJECTS
WHERE  owner = upper ('&&1')
ORDER BY object_type desc;

spool off

@&&1.sql

host rm &&1.sql

exit

When run for the jschmoe schema (assuming that the person running the script has access to the schema), this script would generate and execute an SQL script containing the following SQL commands:

DROP TABLE JSCHMOE.STUDENTS;
DROP TABLE JSCHMOE.ENROLLED_COURSES;
DROP TABLE JSCHMOE.STUDENT_FINANCIAL_AID;
DROP PROCEDURE JSCHMOE.CALCULATE_GPA;
DROP PACKAGE BODY JSCHMOE.STUDENT_UPDATES;
DROP PACKAGE JSCHMOE.STUDENT_UPDATES;
DROP FUNCTION JSCHMOE.GRANT_FINANCIAL_AID;

Security And User Administration

Even with the advent of roles in Oracle7, the DBA still has to maintain a record of the rights a role has. If a role is a template user, the template must still be configured. The configuration of the roles for a system is often done with a script, like the one shown in Listing 3.3.

Listing 3.3 A script that grants privileges to roles.

-- *****************************************************************
-- This role will be granted to accounts that process financial
-- aid applications.
--
CREATE ROLE Financial_Aid_Processor;

--
-- This role must be able to read the student's contact information.
--
GRANT SELECT ON STUDENTS TO Financial_Aid_Processor;

--
-- This role assigns all financial aid application data.
--
GRANT SELECT ON STUDENT_FINANCIAL_AID TO Financial_Aid_Processor;
GRANT INSERT ON STUDENT_FINANCIAL_AID TO Financial_Aid_Processor;

--
-- This role needs read access to students' grades.
--
GRANT SELECT ON ENROLLED_COURSES TO Financial_Aid_Processor;

--
-- This role is granted to management accounts in the financial aid
-- office.
--
CREATE ROLE Financial_Aid_Manager;

-- ******************************************************************
-- The manager will have all the privileges of a processor.
--
GRANT Financial_Aid_Processor TO Financial_Aid_Manager;
--
-- The manager must also be able to update a student's financial
-- aid records.
--
GRANT UPDATE ON STUDENT_FINANCIAL_AID TO Financial_Aid_Processor;

Although the configuration of individual users can be done through roles, a security feature in Oracle requires granted rights to individual tables (including views and snapshots) before a developer can compile objects that reference the table. The DBA must maintain a script to create developer accounts, like the one shown in Listing 3.4.

Listing 3.4 A script to create an application developer’s account.

GRANT CONNECT, RESOURCE TO &&1 IDENTIFIED BY &&2;

--
-- Allow the developer to create stored procedures, functions,
-- packages, and triggers.
--
GRANT CREATE ANY OBJECT TO &&1;

--
-- The developer must have full access to these tables.
--
GRANT ALL ON STUDENTS TO &&1;
GRANT ALL ON STUDENT_FINANCIAL_AID TO &&1;
GRANT ALL ON ENROLLED_CLASSES TO &&1;
EXIT

The scope of the rights and the fact that the rights granted directly to the account makes a developer’s account very powerful (as it should be). Most systems find it prudent to not create development accounts on production systems, for reasons that should be obvious.


Previous Table of Contents Next