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
Happy Posting :)
