Search Oracle Blogs

List of Blogs maintained by Paweł Barut.

Sunday, November 19, 2006

Blogger Beta Backup tool

Finally i found time to prepare solution to backup my blog. I've decided to do that as I had problems to backup my blog using available tools. To archive that I use Blogger Beta Atom feeds, few tables in Oracle, and PL/SQL package to read blog posts, parse them using XML DB features and store in DB tables.


  • Oracle 10g (tested on Oracle XE on Windows and Oracle Enterprise on Windows). Oracle9i should work also, but it's not tested.
  • Blogs maintained by Beta Blogger and hosted on You must allow to publish full content of your posts and comments.
  • Direct connection to Internet


  1. Download installation package
  2. Create new Oracle schema and grant to it:
    1. roles CONNECT and RESOURCE
  3. run script install.sql


First You have to register blog you want to backup. Use BLOGSPOT_BACKUP.register_blog function:
('Software Engineer Thoughts', ''));
To download all your posts with comments andl labels use this PL/SQL block:
BLOGSPOT_BACKUP.synchronize_blog(null, false);
BLOGSPOT_BACKUP.synchronize_labels(null, null);
BLOGSPOT_BACKUP.synchronize_comments(null, null, false);
For more information take a look into BLOGSPOT_BACKUP specification.

Data storage

Table name Desription
BLOGS Blogs registered for backup.
BLOG_POSTS Blog posts downloaded.
BLOG_LABELS Labels associated to blog posts.
BLOG_COMMENTS Comments related to blog posts.
FEED_CACHE Used speed up tests. In tests phase feeds are read from this table instead of downloading every time.

End notes

I'm publishing this tool, as some of you might find it useful. It should work on all beta blogger blogs, but I do not give ant warranty. In case of any problems fill free to contact me.

Regards, Paweł


Dimitri Gielis said...

Hi Pawel,

I'm impressed by your package.

I created a GUI on top of your application in APEX here.

Your blog is backuped correctly, but when I try mine I get an XML error:
ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00200: could not convert from encoding UTF-8 to UCS2 Error at line 1


Paweł Barut said...

Hi Dimiti,

Thanks for reporting problem. And congratulation for nice front end. It's what I wanted to do next.
I could not reproduce your problem. On my machine I get ORA-00600: internal error code, arguments: [kole_t2u], [34] :)
I did some changes (now only 25 entries are download in one step) and I was able to successfully backup your blog. New version is ready to download.
I've tested it on Oracle XE with NLS_CHARACTERSET = AL32UTF8 on Windows.
If there still is a problem, please contact me.


Karl L. Gechlik said...

This is less intrusive doesnt effect your current blogger blog, gives you a test bed and a different platform all with a few clicks. After that Google Mess yesterday I will be keeping both up until we migrate to our own box!

Free and Easy Backup Method For Blogger


Copyright © Paweł Barut
Printing from DOS to USB Printer