Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Using Oracle Scripts to Automate a Task Oracle Tips by Burleson

Other Uses

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 phone numbers.

UPDATE STUDENTS
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.