Just a quick post to point out that the Alexandria PL/SQL Library has been updated to v1.7, including updates to the Amazon S3 package and a new package for generating iCalendar objects – more details on Morten’s blog.
In Perth this morning, at a breakfast courtesy of the local AUSOUG, I spoke about using the Alexandria PL/SQL Library to automate various tasks with Amazon’s Simple Storage (S3) service. If you haven’t used Amazon Web Services before, or haven’t looked at Alexandria yet, and you enjoy discovering new capabilities with PL/SQL I think you’ll find this interesting.
The powerpoint slides and demo script are now available on my Presentations page.
In late October I’ll be speaking at the 20:20 Foresight Perth Conference – more details later.
Ever since I downloaded the Alexandria PL/SQL library, I haven’t been able to put it down. Just recently I decided I wanted to serve up a whole lot of media files directly from Amazon’s S3 simple storage service, instead of serving them from within my EC2 (elastic compute) instance. They were just wasting my linux server’s time responding to http requests.
So, I quickly wrote the following code to transfer them:
DECLARE l_blob BLOB; BEGIN /* initialise my AWS session */ ALEX.amazon_aws_auth_pkg.init ( 'yyy-my-aws-id-yyy' , 'xxx-not-telling-xxx' , p_gmt_offset => -8); FOR rec IN ( SELECT id, filename, mime_type, location FROM myfiles WHERE location = 'http://myserver/media/' ) LOOP /* read the file from its current location */ l_blob := ALEX.http_util_pkg.get_blob_from_url (rec.location || rec.filename); IF DBMS_LOB.getLength(l_blob) > 0 THEN /* upload the file to Amazon S3 */ ALEX.amazon_aws_s3_pkg.new_object ( 'mybucket' , rec.filename , l_blob , rec.mime_type , ALEX.amazon_aws_s3_pkg.g_acl_public_read); UPDATE myfiles SET location = 'https://mybucket.s3-ap-southeast-1.amazonaws.com/' WHERE id = rec.id; COMMIT; END IF; END LOOP; END;
After a short while, all the files had been copied across to my bucket on S3, and my table updated so that my web site now points people’s browsers to the new location for those files.
Of course, I could have used UTL_FILE to read the files from disk, but then I’d have to first create a directory, and write a loop to read the file in chunks into the BLOB. Why bother with all that when I can just call http_util_pkg.get_blog_from_url and get it all in one go?
That’s the trouble with powerful utilities like Alexandria: they’re too easy to use, make tasks like this trivial, and you start finding all sorts of uses for them. All of a sudden, Alexandria is your hammer, and the world is full of nails.
See also: this quick intro to using Alexandria’s API for Amazon S3.
I’ve just opened my new web site, built entirely in Oracle Application Express, and announced it on my other blog:
“www.myhomecontents.com.au has been built with just one simple purpose – to help you keep track of your home contents. It’s in “beta” status at the moment, so I’m keen to get as many people as possible to try it out to flesh out any bugs.”
Please check it out, and let me know what you think!
There is officially now no good excuse for not running your own instance of Oracle 11g and getting into Apex 4 development, or just playing with SQL and PL/SQL.
- Oracle 11g XE is free.
- You can rent the hardware to run it by the hour, at a fraction of the cost of most online hosting services.
- It’s easy to install – just use this step-by-step guide!
1. Gather your software.
- Oracle Database Express Edition 11g Release 2 for Linux x64.
The file name will be oracle-xe-11.2.0-1.0.x86_64.rpm.zip, and is about 308MB.
- WinSCP (or any SCP/SFTP client for your OS)
2. Get your Amazon EC2 account.
3. Launch Instance.
4. Set up your key pair.
5. Upload the Oracle installer to the instance.
6. Login to your instance.
7. Install Oracle.
If you just want to know what to type, here is basically all you need (type the commands after the “#” – don’t type the “#” itself though):
# unzip /root/oracle-xe-11.2.0-1.0.x86_64.rpm.zip # rpm -ivh /root/Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm # /etc/init.d/oracle-xe configure
This script will prompt you for the Apex port and the database port – just press Enter to accept the defaults (8080 and 1521, respectively). It will then ask for the password for SYS and SYSTEM. Finally, it will ask you if you want it to run on startup. Accept the default (yes).
8. Disable the firewall.
# chkconfig iptables off # service iptables stop
This disables the internal firewall running in your linux instance.
9. Log into Apex.
Open your browser and point it at your instance. Take the Public DNS and add :8080/apex/apex_admin, e.g.:
The login name is admin, and the password is (for now) the same password you entered for the SYS and SYSTEM accounts earlier.
It will prompt you to change the password. It has to be a fairly secure – you’ll need at least a letter, number and symbol, and you can’t include the login name. After changing your password, you’ll need to log in again but using the new password.
You now have a fully-fledged Oracle Application Express environment, version 4.0.
10. Create a Workspace.
The last thing you need to do before you can start creating applications and stuff is to create a workspace. Click “Create Workspace” and answer the prompts to create the workspace along with its own schema and admin user account.
Log out of Apex Administration, and log into the newly created workspace – and you will now be able to start creating applications, tables, and what-have-you. You can also use Apex to write code (such as procedures and packages), but I suggest you use Oracle SQL Developer for this kind of thing because it’s a bit more powerful.
From now on, to get back into your Apex development environment, you can use this url:
Oh, by the way: when you’ve had your fun, and you want to stop your instance (which at about 34c/h may cost around $60 per week if you leave it running 24×7), don’t forget to terminate it from AWS Management Console. Before you do, however, you can take a snapshot of it in its current state so you can restore from it later. (Before this, you might want to shut down the Oracle database within the instance first – although I’ve found it quite easy to recover an instance which was snapshotted while the database was up and running). To do that, right-click on the instance in AWS Management Console, and select “Create Image (EBS AMI)”. It may take some time for the image to be created. When the image creation is complete, you can then Terminate the instance. The AMI will take up a bit of storage that will cost a dollar or two per month; and you can now spin up as many copies of your machine whenever you want.
Lew posted an excellent introduction to PuTTY and SSH for AWS Newbies that I heartily recommend. It was posted in 2009 but still applies.