Getting friends timeline from Twitter to Oracle APEX (PL/SQL)

Now that you have posted your Twitter updates from APEX, you might want to read your friends time line. The procedure is very similar to posting of updates, except this time you want to receive the XML and process it for display inside a SQL query report. The returning XML will show the 20 most recent statuses posted in the last 24 hours from the authenticating user and that user's friends.

create or replace
FUNCTION get_twitter_timeline(t_user IN VARCHAR2,   t_pass IN VARCHAR2)
RETURN XMLTYPE
IS
http_req utl_http.req;
http_resp utl_http.resp;
t_update_send VARCHAR2(200);
res_value VARCHAR2(4000);
XML_RETURN CLOB;
BEGIN
t_update_send := '--head';
--utl_http.set_proxy('http://www-yourproxy.com:80'); --If you need to specify a proxy use this.
http_req := utl_http.begin_request('http://twitter.com/statuses/friends_timeline.xml',   'POST',   utl_http.http_version_1_1);
utl_http.set_response_error_check(TRUE);
utl_http.set_detailed_excp_support(TRUE);
utl_http.set_body_charset(http_req,   'UTF-8');
utl_http.set_header(http_req,   'User-Agent',   'Mozilla/4.0');
utl_http.set_header(http_req,   'Content-Type',   'application/x-www-form-urlencoded');
utl_http.set_header(http_req,   'Content-Length',   to_char(LENGTH(t_update_send)));
utl_http.set_transfer_timeout(to_char('60'));
utl_http.set_authentication(http_req,   t_user,   t_pass,   'Basic');
utl_http.write_text(http_req,   t_update_send);
http_resp := utl_http.get_response(http_req);
BEGIN
WHILE 1 = 1
LOOP
 utl_http.read_line(http_resp,   res_value,   TRUE);
 XML_RETURN := XML_RETURN || res_value;
END LOOP;
EXCEPTION
WHEN utl_http.end_of_body THEN
NULL;
END;
utl_http.end_response(http_resp);
return XMLTYPE(XML_RETURN);
EXCEPTION
WHEN others THEN
XML_RETURN := sqlerrm;
RAISE;
END get_twitter_timeline;

Usage:

SELECT EXTRACTVALUE(VALUE(twitter), '/status/user/name')  AS NAME
, EXTRACTVALUE(VALUE(twitter), '/status/text') AS TEXT
, EXTRACTVALUE(VALUE(twitter), '/status/created_at') AS CREATED_AT
, EXTRACTVALUE(VALUE(twitter), '/status/source') AS SOURCE
, EXTRACTVALUE(VALUE(twitter), '/status/user/screen_name') AS SCREEN_NAME
, EXTRACTVALUE(VALUE(twitter), '/status/user/profile_image_url') AS PROFILE_IMAGE_URL
FROM
TABLE(XMLSEQUENCE(EXTRACT(get_twitter_timeline('twitter_user','twitter_password'), '/statuses/*'))) twitter

Note that according to Twitter API the public timeline is cached for 60 seconds so requesting it more often than that is a waste of resources

Happy Posting :)

Post Updates to Twitter from Oracle APEX (PL/SQL)

I started a pet project called OraTweet. It is an Oracle internal "Twitter" with some key advantages such as the ability to follow groups and IM (XMPP/Jabber) integration. Aside from having our own internal "tweets" a user can direct a message @oratweet and the application will post it to the generic OraTweet Twitter account. Also the user will be able to store its own credentials and post to its own account (ie @noelportugal). I started playing with the Twitter API and first start sending updates with a combination of a Java Class to execute a shell Curl command. I soon realized that was not the best way to do it, so I went to work and got the following Procedure. (note that this is the quick and dirty version, feel free to extend it and make it more foolproof)

CREATE OR REPLACE PROCEDURE update_twitter(t_user IN VARCHAR2,   t_pass IN VARCHAR2,   t_update IN VARCHAR2) AS
http_req utl_http.req;
http_resp utl_http.resp;
h_name VARCHAR2(255);
h_value VARCHAR2(1023);
t_update_send VARCHAR2(4000);
res_value VARCHAR2(32767);
show_header NUMBER := 0;--0 False, 1 True
show_xml NUMBER := 1;--0 False, 1 True
BEGIN
t_update_send := 'status=' || SUBSTR(t_update,   1,   140) || '';
--utl_http.set_proxy('http://www,yourpoxy.com:80'); --If you need to specify a proxy un comment this line.
http_req := utl_http.begin_request('http://twitter.com/statuses/update.xml',   'POST',   utl_http.http_version_1_1);
utl_http.set_response_error_check(TRUE);
utl_http.set_detailed_excp_support(TRUE);
utl_http.set_body_charset(http_req,   'UTF-8');
utl_http.set_header(http_req,   'User-Agent',   'Mozilla/4.0');
utl_http.set_header(http_req,   'Content-Type',   'application/x-www-form-urlencoded');
utl_http.set_header(http_req,   'Content-Length',   to_char(LENGTH(t_update_send)));
utl_http.set_transfer_timeout(to_char('60'));
utl_http.set_authentication(http_req,   t_user,   t_pass,   'Basic');
utl_http.write_text(http_req,   t_update_send);
http_resp := utl_http.get_response(http_req);

DBMS_OUTPUT.PUT_LINE('status code: ' || http_resp.status_code);
DBMS_OUTPUT.PUT_LINE('reason phrase: ' || http_resp.reason_phrase);

IF show_header = 1 THEN
FOR i IN 1 .. utl_http.get_header_count(http_resp)
LOOP
utl_http.get_header(http_resp,   i,   h_name,   h_value);
DBMS_OUTPUT.PUT_LINE(h_name || ': ' || h_value);
END LOOP;
END IF;

IF show_xml = 1 THEN
BEGIN
WHILE 1 = 1
LOOP
utl_http.read_line(http_resp,   res_value,   TRUE);
DBMS_OUTPUT.PUT_LINE(res_value);
END LOOP;

EXCEPTION
WHEN utl_http.end_of_body THEN
NULL;
END;
END IF;

utl_http.end_response(http_resp);

EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
RAISE;

END update_twitter;

Usage:

begin
UPDATE_TWITTER('my_twitter_acct','my_twitter_pass','Hello World from Oracle PL/SQL and APEX http://apextoday.blogspot.com');
end;

My following post will include on how to create an XMPP/Jabber listener with Java and how I hooked it up to my application.

***Update I just blogged about Getting friends timeline from Twitter to APEX (PL/SQL)