071-074

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


High Performance Oracle Database Automation
High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL
ISBN: 1576101525
EAN: 2147483647
Year: 2005
Pages: 92

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