Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



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.

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.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter