El Blogcito de Ricardo Dufoo

Cliente de twitter para la base de datos de Oracle.

por en Feb.15, 2011, archivado en Oracle

Sin entrar en detalles de como llegué a esto, es posible tener un cliente de twitter en la base de datos de Oracle. Podemos consultar el timeline y hacer publicaciones (útil para un sistema de notificaciones).

El código que se presenta a continuación está basado en lo que me topé en las siguientes ligas:

http://database-geek.com/2009/03/15/ora_tweet-tweet-from-oracle-a-plsql-twitter-api/

http://apextoday.blogspot.com/2008/06/getting-friends-timeline-from-twitter.html

 

CREATE OR REPLACE PACKAGE otn_sample_ttr_pkg IS

FUNCTION get_twitter_timeline(t_user IN VARCHAR2,   t_pass IN VARCHAR2)
RETURN XMLTYPE;
/*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(otn_sample_ttr_pkg.get_twitter_timeline('twitter_user','twitter_password'), '/statuses/*'))) twitter

*/

FUNCTION tweet
(
p_user IN VARCHAR2,
p_pwd IN VARCHAR2,
p_string IN VARCHAR2 )
RETURN BOOLEAN;
/*
USAGE:

SET SERVEROUTPUT ON

BEGIN

IF otn_sample_ttr_pkg.tweet
(
p_user => 'twitter_username',
p_pwd => 'twitter_password',
p_string => 'ora_tweet v1.0 is complete!' )
THEN
dbms_output.put_line('Success!');
ELSE
dbms_output.put_line('Failure!');
END IF;

END;
/

*/

END otn_sample_ttr_pkg;

CREATE OR REPLACE PACKAGE BODY otn_sample_ttr_pkg AS

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://128.1.56.56:80');

–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',   'GET',   utl_http.http_version_1_1);
utl_http.set_authentication(http_req, 'cm\pryaurora', 'pryaurora','Basic',true);
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;

FUNCTION tweet
(
p_user IN VARCHAR2,
p_pwd IN VARCHAR2,
p_string IN VARCHAR2 )
RETURN BOOLEAN
AS
v_req   UTL_HTTP.REQ;
v_resp  UTL_HTTP.RESP;
v_value VARCHAR2(1024);
v_status VARCHAR2(160);
v_call VARCHAR2(2000);
BEGIN

— Twitter update url
v_call := 'http://twitter.com/statuses/update.xml';

— encoded status tring
v_status := utl_url.escape(
url => 'status=' || SUBSTR(p_string,1,140));

— Has to be a POST for status update
Utl_Http.Set_Proxy ('http://128.1.56.56:80');

v_req := UTL_HTTP.BEGIN_REQUEST(
url => v_call,
method =>'POST');
utl_http.set_authentication(v_req, 'cm\pryaurora', 'pryaurora','Basic',true);

— Pretend we're a moz browser
UTL_HTTP.SET_HEADER(
r => v_req,
name => 'User-Agent',
value => 'Mozilla/4.0');

— Pretend we're coming from an html form
UTL_HTTP.SET_HEADER(
r => v_req,
name => 'Content-Type',
value => 'application/x-www-form-urlencoded');

— Set the length of the input
UTL_HTTP.SET_HEADER(
r => v_req,
name => 'Content-Length',
value => length(v_status));

— authenticate with twitter user/pass
UTL_HTTP.SET_AUTHENTICATION(
r => v_req,
username => p_user,
password => p_pwd );

— Send the update
UTL_HTTP.WRITE_TEXT(
r => v_req,
data => v_status );

— Get twitter's update
v_resp := UTL_HTTP.GET_RESPONSE(
r => v_req);

— Get the update and display it,
— only useful for debugging really
LOOP
UTL_HTTP.READ_LINE(
r => v_resp,
data => v_value,
remove_crlf => TRUE);

DBMS_OUTPUT.PUT_LINE(v_value);
END LOOP;

— Close out the http call
UTL_HTTP.END_RESPONSE(
r => v_resp);

RETURN TRUE;

EXCEPTION
— normal exception when reading the response
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(
r => v_resp);
RETURN TRUE;

— Anything else and send false
WHEN OTHERS THEN
UTL_HTTP.END_RESPONSE(
r => v_resp);
Dbms_Output.Put_Line ( 'Request_Failed: ' ||
Utl_Http.Get_Detailed_Sqlerrm );
Dbms_Output.Put_Line ( 'Ora: ' || Sqlerrm );
RETURN FALSE;

END;

END otn_sample_ttr_pkg;

No hay comentarios para esta entrada...

Escribir un Comentario

Busca algo?

Escriba una palabra para buscar en el sitio:

Todavia no encontrando lo que usted esta buscando? Deje un comentario y entraremos en contacto con ud, para poderlo ayudar!

Visita a nuestros amigos!

Amigos recomendados...

Archivos

Entradas, ordenadas por mes...