Using Oracle Scripts to
Automate a Task
Oracle Tips by Burleson
The potential use of scripts to automate a task inside an Oracle
database is limited only by the imagination of the developer. For
instance, a developer might need to write a simple script that changes
area codes to keep up with the rapid growth of the nation’s phone
system, as shown in Listing 1.8.
Listing 1.8 A simple script to update area codes inside
SET home_phone = to_number ('&&1') ||
substr (to_char (home_phone, 4, 7))
WHERE substr (to_char (home_phone), 4, 3) = '&&2'
AND substr (to_char (home_phone), 1, 3) = '&&3'
This script replaces the area code prefixes for all phone numbers
in the area code specified by &&3 and the local calling area &&2 with
the area code specified in &&1. The script concatenates the last seven
digits of the phone number (the local calling area and extension) with
the new area code
This is an excerpt from the book "High
Performance Oracle Database Automation" by
Jonathan Ingram and Donald K. Burleson, Series Editor.