The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

  • May 4, 2018

Quick and Easy Twitter API with APEX 18.1

Carsten Czarski
Consulting Member of technical Staff

This is the next part of the blog post series on the new REST features in Application Express 18.1. In this blog posting, we will show how to access a protected REST service, which requires authentication. Since APEX has a great Twitter community, we'll be accessing the Twitter API using the new REST capabilities of Application Express. 

So, you need to have a Twitter account in order to follow this example. Once you have the Twitter Account, log into apps.twitter.com using your Twitter account and click on the Create new App button in order to register a Twitter Application. Then provide a name and a description for your new Twitter application. You can use http://apex.oracle.com as the Web site. Make sure to check the Yes, I have read and agree to the Twitter Developer Agreement (after you read it) and click the Create your Twitter Application button.

Once your application has been created, you will be directed to an overview page.

Note the Token URL for the App-only authentication (https://api.twitter.com/oauth2/token), you will need this later on. Then click on the manage keys and access tokens link.

Note down the Consumer Key and Consumer Secret; these are the authentication credentials we will be using in Application Express in order to access the Twitter API. It also might be a good idea to change the applications' permissions to Read Only.

Next, head over to Application Express. Create an empty application or use an existing one. If your database is behind a Firewall, set the Proxy Server first (Shared Components > Application Definition Attributes). Then navigate to Shared Components > Web Sources. Create a new Web Source Module from Scratch

Use Simple HTTP as the Web Source Type and https://api.twitter.com/1.1/search/tweets.json as the URL Endpoint. Then click Next.

In the Remote Server dialog, make a change to the proposal of APEX. Use https://api.twitter.com/1.1/ as Base URL and search/tweets.json as Service URL Path. Then click Next to advance to the Authentication dialog.

The Twitter API does require authentication, so set the switch to Yes. Choose OAuth2 Client Credentials flow as the Authentication Type and use the Twitter Token URL noted down above above. Put in the Consumer Key and Secret (which you also noted down earlier) and the Client ID and Client Secret attributes. Then finished, do not click on Discover, click on Advanced instead, in order to configure required parameters.

The parameter is used to pass the actual query to the Twitter Search API endpoint. Declare it as a Query String Variable and use #orclapex as the default value (we need a default value in order to discover the API response in the next step). Add a count parameter with a value of 100. When the Is Static attribute is set to Yes, individual APEX components cannot change that parameter value. When done, click on the Discover button. Application Express will invoke the REST endpoint and then investigate the response.


You will see a sample of the data being returned from the REST Endpoint. Click Create Web Source to save the Web Source Module in Application Express.

Before building components based on this API, we'll do two things:

  • To minimize HTTP requests performed, the Caching feature will be used
  • Within the data profile, a "real" TIMESTAMP column will be created, based on the CREATED_AT attribute from the Twitter API.
  • The Twitter API returns many attributes. We'll hide all unnecessary ones.

So click the name of the Web Source Module (here: Twitter Search API) and open the Operations tab.

Click the pencil to edit this operation and choose the Caching tab. Enable Caching for All Users and choose 15 as the Invalidate When attribute. That means, that an API response is being cached for all APEX sessions and users and that the cached content will remain valid for 15 minutes. Of course, the cache is parameter-aware. So a different query parameter will lead to a new request being made.

Click the Apply Changes to save your changes. Then click the Data Profile tab.

As you can see, the Twitter API returns a lot of attributes - and we will only need a subset of these. So click the Edit Data Profile button to see the details.

In the Columns section first scroll down until the end, to have all the columns being loaded into the page. Then scroll up to the heading again, click the Select All check box and open the context menu. Click Hide selected Columns. All columns are now "invisible" to APEX components. Next, we'll unhide the columns of interest.

Look up the following attributes and set the Visible column to Yes

  • ID
  • TEXT
  • NAME

Then click the Add Column button to add a new column. We will create the column CREATED_AT_TSTZ to derive a TIMESTAMP WITH TIME ZONE value from the CREATED_AT attribute (which is of VARCHAR2). Use the following column attributes:

  • Column Type: Derived
  • Data Type (should be populated automatically): Timestamp with time zone

Click Create to save the new Derived Column. Click Apply Changes to save the data profile. Now all preparations are done and application components can be built. Navigate to the pages overview of your application and click Create Page. Create a report page and pick Classic Report

Provide a name for your page and follow the wizard until the last Report Source step.

In the Report Source wizard step, choose Web Source Module as the Data Source for your report. Then choose the Twitter Search API module. Accept the list of columns and click Create in order to create your page.

Since the Web Source Module has a parameter defined, you can now change its value for this report (you can change it to use a page item later on, in Page Designer). Click Create when done.

When the page has been created, you will be redirected to Page Designer. Change some titles and layout options if you wish, then run your page. It should look as follows.


Next, we'll improve the layout. There are many different layouts available out-of-the-box for Classic Report - the That's a Classic Report? Really? blog posting introduces these and shows how to use them. 

In Page Designer, navigate to the report attributes and, in the property pane on the right, change the report to use the Search Results template.

However, the Search Results template expects specific result column names like SEARCH_TITLE, SEARCH_DESC and so on.

The Web Source Module does not return these columns. We could go back to the data profile and change column names there, but since we will need such customized columns only in this Classic Report, we'll use the Post Processing feature. In Page Designer, navigate to the region attributes, look up the Local Post Processing area (below Source) and choose SQL Query as the Post Processing Type.

The SQL Query will be pre-populated with the data profile columns. Now change the SQL query as follows:

select NAME || ' tweeted at ' 
            || to_char(CREATED_AT_TSTZ, 'Mon DD, HH24:MI TZR') as search_title,
       TEXT                                                    as search_desc,
       'https://twitter.com/' || SCREEN_NAME 
                              || '/status/' || id              as search_link,
       'Retweets'                                              as label_01,
       RETWEET_COUNT_2                                         as value_01,
       'Likes'                                                 as label_02,
       FAVORITE_COUNT_2                                        as value_02,
       SCREEN_NAME || ' followers'                             as label_03,
       FOLLOWERS_COUNT                                         as value_03

In Page Designer, the column list will also change.

Now run the page again.

Finally, we'll amend this page with a page item, allowing to enter a search term. The report is then supposed to display tweets matching the search term. Luckily, all integration is already done in the Web Source Module. Here, we just need to add the required components to the page.

  • Add a region above the Classic Report and add a Text Field Item named PX_SEARCH to that region 
  • Use #orclapex as the default value for the PX_SEARCH item
  • Add a dynamic action which refreshes the Classic Report when the item is changed
  • Also add a button right to the text field. Add another dynamic action, which also refreshes the classic report on button click.

The page should then look as follows (we're not done yet).

Now go back to Page Designer and navigate to the Classic Report region in the tree on the left. Open the parameters tree node and look up the q parameter.

Then, head over to the property pane on the right. In the Value section, change from Static Value to Item and pick the item PX_SEARCH, you just have created. 

Finally, add PX_SEARCH to the Page Items to Submit attribute below the region source to make sure that changed values are being sent with every AJAX request.

Save your changes and run the page. Change the query and click the button - you'll see that the result list changes as well. You have built your own Twitter Search interface - with Application Express!

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.