December 20, 2007 • 4 minute read
Using Flickr API in PL/SQL
Flickr Services API is nothing new, and if you're using PHP, ASP.NET or any of the other major languages, there is plenty of documentation available to get you started with putting Flickr functionality in your web application. If you're using PL/SQL, however, you're stuck to coming up with a solution yourself. I have come up with a simple package that should get you started with using the Flickr API in your PL/SQL programs.
NOTE This post was published in 2007. The Flickr API has likely changed significantly and as such the code below is almost guaranteed not to work. The technique for using the API (making a HTTP request, parsing the XML response) is still valid, so I've kept the post in case that is of any use to someone.
This package basically uses the Flickr API to search Flickr for the 25 most interesting photos related to the tag Ireland. It could easily be expanded to allow the user to search for images, display more than 25 photos, or to perform other API functions such as uploading images.
To use this example, you will need to get a Flickr API key and replace the your_api_key_goes_here string in the code below with the key that Flickr supply you. Getting a key is simple, free and only takes a minute. Go to http://flickr.com/services/api/keys/apply/ to get started.
pkg_flickr.pak
SET ESCAPE '\';
CREATE OR REPLACE PACKAGE pkg_flickr AS
PROCEDURE display_page;
PROCEDURE print_images(doc xmldom.DOMDocument);
PROCEDURE handle_xml(xmlData CLOB);
END pkg_flickr;pkg_flickr.bod
CREATE OR REPLACE PACKAGE BODY pkg_flickr AS
PROCEDURE display_page IS
v_api_key VARCHAR2(100);
v_method VARCHAR2(100);
v_tags VARCHAR2(100);
v_format VARCHAR2(100);
v_per_page VARCHAR2(3);
v_sort_by VARCHAR2(100);
v_url VARCHAR2(255);
v_pieces utl_http.html_pieces;
v_response CLOB;
BEGIN
/* Set our Flickr Services API related Variables */
v_api_key := htf.escape_url('your_api_key_goes_here');
v_method := htf.escape_url('flickr.photos.search');
v_tags := htf.escape_url('Ireland');
v_format := htf.escape_url('rest');
v_per_page := htf.escape_url('25');
v_sort_by := htf.escape_url('interestingness-desc');
/* Create URL based on Flickr Variables */
v_url := 'http://api.flickr.com/services/rest/?api_key='
||v_api_key||'\&method='||v_method||'\&tags='||v_tags
||'\&format='||v_format||'\&per_page='||v_per_page
||'\&sort='||v_sort_by;
/* Retrieve the data in pieces */
v_pieces := utl_http.request_pieces(v_url, 100);
/* Piece back together the data */
FOR x IN 1..v_pieces.count LOOP
v_response := v_response||v_pieces(x);
END LOOP;
htp.p('<html><head><title>Flickr Photos</title></head><body>');
htp.p('<body><h1>Flickr Photos</h1><p>');
/* Parse the XML Response */
handle_xml(v_response);
htp.p('</p></body></html>');
END display_page;
PROCEDURE print_images(doc xmldom.DOMDocument) IS
nl xmldom.DOMNodeList;
len1 NUMBER;
len2 NUMBER;
n xmldom.DOMNode;
e xmldom.DOMElement;
nnm xmldom.DOMNamedNodeMap;
attrname VARCHAR2(100);
attrval VARCHAR2100);
id VARCHAR2(100);
owner VARCHAR(100);
secret VARCHAR2(100);
server VARCHAR2(255);
farm VARCHAR2(255);
title VARCHAR2(255);
BEGIN
/* Get all <photo> tags */
nl := xmldom.getElementsByTagName(doc, 'photo');
len1 := xmldom.getLength(nl);
/* Loop through elements */
FOR j IN 0..len1-1 LOOP
n := xmldom.item(nl, j);
e := xmldom.makeElement(n);
/* Get all attributes */
nnm := xmldom.getAttributes(n);
IF (xmldom.isNull(nnm) = FALSE) THEN
len2 := xmldom.getLength(nnm);
/* Loop through attributes */
FOR i IN 0..len2-1 LOOP
n := xmldom.item(nnm, i);
attrname := xmldom.getNodeName(n);
attrval := xmldom.getNodeValue(n);
/* The following if block gets the attributes we need */
IF attrname = 'id' THEN
id := attrval;
ELSIF attrname = 'owner' THEN
owner := attrval;
ELSIF attrname = 'secret' THEN
secret := attrval;
ELSIF attrname = 'server' THEN
server := attrval;
ELSIF attrname = 'farm' THEN
farm := attrval;
ELSIF attrname = 'title' THEN
title := attrval;
END IF;
END LOOP;
/* Build the image and the link */
htp.p('<a href="http://www.flickr.com/photos/'||owner||'/'||id||'/">');
htp.p('<img src="http://farm'||farm||'.static.flickr.com/'||server
||'/'||id||'_'||secret||'_s.jpg" border="0" width="75" height="75" alt="'
||title||'" title="'||title||'" /></a>');
END IF;
END LOOP;
END print_images;
PROCEDURE handle_xml(xmlData CLOB) IS
p xmlparser.parser;
doc xmldom.DOMDocument;
BEGIN
/* New parser */
p := xmlparser.newParser;
/* Parse input file */
xmlparser.parseclob(p, xmlData);
/* Get document */
doc := xmlparser.getDocument(p);
/* Print the Images from Flickr */
print_images(doc);
/* Deal with exceptions */
EXCEPTION
WHEN xmldom.INDEX_SIZE_ERR THEN
raise_application_error(-20120, 'Index Size error');
WHEN xmldom.DOMSTRING_SIZE_ERR THEN
raise_application_error(-20120, 'String Size error');
WHEN xmldom.HIERARCHY_REQUEST_ERR THEN
raise_application_error(-20120, 'Hierarchy request error');
WHEN xmldom.WRONG_DOCUMENT_ERR THEN
raise_application_error(-20120, 'Wrong doc error');
WHEN xmldom.INVALID_CHARACTER_ERR THEN
raise_application_error(-20120, 'Invalid Char error');
WHEN xmldom.NO_DATA_ALLOWED_ERR THEN
raise_application_error(-20120, 'No data allowed error');
WHEN xmldom.NO_MODIFICATION_ALLOWED_ERR THEN
raise_application_error(-20120, 'No mod allowed error');
WHEN xmldom.NOT_FOUND_ERR THEN
raise_application_error(-20120, 'Not found error');
WHEN xmldom.NOT_SUPPORTED_ERR THEN
raise_application_error(-20120, 'Not supported error');
WHEN xmldom.INUSE_ATTRIBUTE_ERR THEN
raise_application_error(-20120, 'In use attr error');
END handle_xml;
END pkg_flickr;If you find this helpful or would like some assistance with getting this code to work, please leave a comment. Code is provided as is, free of charge, with no support offered, although I will try my best to answer any questions you have.