Monday, November 08, 2010

APEXBlogs v2 - Twitter sync for #apexblogs and #orclapex

Another new feature of APEXBlogs v2 will be the synchronisation of Tweets with the hashtags of #apexblogs and/or #orclapex.


Some of you might remember I enabled the synchronisation of Tweets with the hashtag of #apexblogs already in version 1, but I had to take it out as my procedure was sometimes hanging. My procedure/query would work in 95% of the times, but if Twitter was down, it would hang and the process would stay there forever. As Twitter was not that stable in the earlier days it caused to much of an issue, that was the reason it disappeared after a few weeks.

But now it's there again and better than before! Again I use the same new APEX 4 features as with the blogs; namely the detail view of the Interactive Report to show the tweets exactly like I want.

Here's a screenshot:


For the people interested behind the query to retrieve a Twitter stream, you can use something like this:

select *
from xmltable(xmlnamespaces('http://www.w3.org/2005/Atom' as "AE"), '//AE:entry'
passing httpuritype('http://search.twitter.com/search.atom?q=apexblogs').getxml()
columns title varchar2(250) path '/AE:entry/AE:title/text()'
);

That returns something like:


The above query would be easiest, but it won't always work. It depends the version of your database, your security settings and your environment.

Update:
Forgot to add that the twitter stream in APEXBlogs v2 is not realtime anymore. I'm going to sync every hour and will put a timer on the page till the next sync.
I choose to store the tweets in my own tables to have an archive, to increase performance and to enable easier search options through the Interactive Report.

2 comments:

Christian Rokitta ♠ said...

Hi Dimitri,

Unfortunately your Twitter Atom API query example doesn’t work on XE due to the lack of the Java based XQuery engine, throwing:

ORA-19114: error during parsing the XQuery expression: ORA-06550: line 1, column 13: PLS-00201: identifier 'SYS.DBMS_XQUERYINT' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

With a little rewrite the query will work fine on XE as well:

SELECT EXTRACTVALUE (COLUMN_VALUE, '/entry/title', 'xmlns="http://www.w3.org/2005/Atom"')
FROM TABLE (
XMLSEQUENCE (
EXTRACT (
httpuritype ('http://search.twitter.com/search.atom?q=%23orclapex').
getxml ()
, '/feed/entry'
, 'xmlns="http://www.w3.org/2005/Atom"')));


Regards,
Christian

Dimitri Gielis said...

Hi Christian,

Yes that is correct. Like I said in the post, it depends your database.

Dimitri