Twitter Public timeline for Oracle

Provides a view for Oracle database that fetches and returns tweets from Twitter public timeline. You can SELECT Twitter public timeline as below:

SELECT created_at, screen_name, text FROM TWITTER_PUBLIC_TIMELINE WHERE LOWER(text) LIKE '%http%';

Note that this library will not work on Twitter API v1.1 because of dropping XML support and requiring authentication with OAuth.
This library gets timeline with XML API and is not authenticated.

Requirement

Oracle Database 11g Release 2
- Tested on 11g R2 Express Edition for Linux x64

Install

1. Setup UTL_HTTP package

Execute $ORACLE_HOME/rdbms/admin/utlhttp.sql to enable UTL_HTTP package.
Run the following commands in the shell on your DB server.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus SYS/YOUR-PASSWORD@localhost:1521/XE AS SYSDBA @utlhttp.sql

2. Setup the network Access Control List (ACL)

On Oracle 11g, the network access configuration is required to use UTL_HTTP package.

The following code is an example to grant the connect and resolve privileges for host api.twitter.com to user SCOTT.
Replace 'SCOTT' to your user name and run the following codes in SQL*Plus.

DECLARE
  user VARCHAR2(30) := 'SCOTT'; -- *Enter your user name*
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('www.xml', 'WWW ACL', user, TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('www.xml', user, TRUE, 'resolve');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('www.xml', 'api.twitter.com');
END;

For more information, see this document.

3. Install Twitter Public timeline for Oracle

Execute install.sql.
Run the following commands in the shell on your DB server.

$ sqlplus YOUR-USER-NAME/YOUR-PASSWORD@localhost:1521/XE @install.sql

Usage

Query to TWITTER_PUBLIC_TIMELINE view.

SELECT created_at, screen_name, text FROM TWITTER_PUBLIC_TIMELINE;

Columns of TWITTER_PUBLIC_TIMELINE is the following.

ID	NUMBER
CREATED_AT	TIMESTAMP(6)
TEXT	VARCHAR2(1000)
SOURCE	VARCHAR2(1000)
TRUNCATED	VARCHAR2(5)
FAVORITED	VARCHAR2(5)
IN_REPLY_TO_STATUS_ID	NUMBER
IN_REPLY_TO_USER_ID	NUMBER
IN_REPLY_TO_SCREEN_NAME	VARCHAR2(100)
RETWEET_COUNT	NUMBER
RETWEETED	VARCHAR2(5)
USER_ID	NUMBER
USER_NAME	VARCHAR2(100)
SCREEN_NAME	VARCHAR2(100)

Uninstall

Run uninstall.sql.

$ sqlplus YOUR-USER-NAME/YOUR-PASSWORD@localhost:1521/XE @uninstall.sql

License

This project is released under the MIT license:
http://opensource.org/licenses/MIT

ser1zw/twitter-public-timeline-for-oracle