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.
- Oracle Database 11g Release 2
- Tested on 11g R2 Express Edition for Linux x64
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
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.
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
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) |
Run uninstall.sql.
$ sqlplus YOUR-USER-NAME/YOUR-PASSWORD@localhost:1521/XE @uninstall.sql
This project is released under the MIT license:
http://opensource.org/licenses/MIT