Archive for the ‘Databases/Embedded Sql’ Category

Oracle External Tables are great

September 7, 2010 Leave a comment

Tasked to scrape data from a fix width text file on a website and store this data in a table for further massaging and joining with other data. The UTL_HTTP package enabled retrieval of the file by going out to the website, navigating to the address where the file was located and read the file into a clob.

After obtaining the contents of the file, it was time to parse the clob line by line in order to extract data into appropriate columns in a table. The steps entailed:

  1. Loop through the clob breaking on each End of line
  2. Write a series of expressions that would substring out each column from the line

Although this process works it ends up embedding data in the code and if the flat file changes would then result in cracking open the PL/SQL package to make the mod.

A better way to do this would be to use an external table. An external table defines a file as a data source and allows querying of the file as if it is a table.

Read more…


Using Oracle to Place a Google Voice Call

December 31, 2009 Leave a comment

Google Voice PL/SQL Package:

Project done in partnership with Steve Bealer.

Purpose: We needed a way to make a phone call from our Oracle database whenever there was a failure. We did not have access to a PBX to place the call. However, Google voice offers a service to create free phone calls through VOIP. After some investigation to figure out the process used to make a Google voice call we built a PL/SQL package to utilize this feature.

Programming Language: PL/SQL
Database: Oracle

Read more…