Hands-on IOTemp

The goal of this project is to develop an Oracle Application Express (APEX) application to control and report on a wi-fi enabled temperature sensor. It will utilize an Oracle REST Data Services (ORDS) JSON service to communicate with the device.

Setting Up Your Own Electric Imp

    If you've acquired your own parts or purchased a kit from us, there is information at the bottom of the next page, titled Build Your IOTemp, that will guide you in connecting your Electric Imp to your WiFi network and to your own schema.

Oracle APEX Workspace Setup

    You have been pre-assigned a workspace. You will need to log in to the workspace and create the necessary database objects. Follow the steps below to import an application and install the supporting objects.

  1. Log in to the APEX Workspace
    Use the workspace, username and password provided to log in to your APEX workspace at the following URL: http://apex.oracle.com
    If you are doing this on your own and not as part of one of our classes, you should use your own schema.

  2. Download the Seeded Application

    Click here to download the APEX application export with supporting objects.

    Importing this application along with its supporting objects will create two tables, C2IOT_CONTROL and C2IOT_TEMP_LOG. It will seed one row of data in C2IOT_CONTROL. This table should only have one row of data. It will also create an application that allows you to change the data in this table (but not to insert or delete).

  3. Import the seeded APEX application with Supporting Objects
    Show Details

  4. Run the Application

    Run the application and review the help associated with the items on the page.

Create JSON Service

    Your Electric Imp has been programmed to call a specific JSON service which you need to build. Follow the instructions below, using the exact names below, in order to allow your device to communicate correctly.

    Note: This lesson utilizes the APEX interface to create the ORDS components. Beginning with ORDS 3.0 and SQL*Developer 4.1 you have the option of creating the ORDS components via SQL*Developer instead.

  1. Navigate to RESTful Services
    Show Details

  2. Create the JSON service

    Click on Modules at the left. Then click the Create Module button on the right.

    Enter the fields exactly as shown below.
    Module Name: c2.IOTemp
    Base Path: iotemp
    Click the Create Module button.
    Scroll down and click the Create Template button. Enter
    URI Template: temp
    Click the Create Template button and then the Create Handler button. Enter
    Method: Post
    Source Type: Pl/SQL

    Copy and paste the code from the text area below in to Source.


    Click the "Create Handler" button.

  3. Add Parameters

    In this step you will map the bind variables in the code above to outbound parameters. The ORDS engine will create a properly formatted JSON response based upon these parameters, adding them to the response header and body as specified. The Electric Imp can easily read the JSON response and act upon it.

    At the bottom of the right panel, under Parameters, click add row to create each the parameters shown below.
    NameBind Variable NameAccess MethodSource TypeParameter Typecomments
    X-ORDS-STATUS-CODEx_statusOUTHTTP HeaderIntegerThis is a special ORDS parameter that will send the HTTP Header response code. A post should get a 201 if it was successful or a 500 if there was an error.

    Be sure to indicate String or Integer correctly in the Parameter Type field.
    Note: The name and bind variable name can be the same. The example above was created to make it explicit how each value is being used.

  4. Test your service

    Select the link below to test your service.

    test service

    Your response should resemble the following:


    This is the JSON response that your web service provided. If your response does not resemble the string above you likely have made a typo while creating the service.

    If your response is similar to that above, your web service received the data, wrote it to the log table and responded with a JSON message. After you build the IOTemp, it will receive the JSON and act accordingly, lighting the appropriate light and sleeping for the indicated number of seconds.

    You can use SQL Workshop to confirm that the data was stored in the table C2IOT_TEMP_LOG.

    Note: if the test service above does not work for any reason (most likely that Oracle has changed the TLS certificate of apex.oracle.com), you can perform the same test using the command line utility curl.

    curl -H "Content-Type:application/json" \
         -X POST \
         -d '{"impid":"234324","temp":65}' \