Lab 4: Insert, Update, and Delete Data Using RESTful Services


Options



Before You Begin

Purpose

The REST Development in SQL Developer 4.1 provides users with the full capabilities of REST. REST endpoints can be defined along with basic operations including create, query, update and delete. More complex SQL and PL/SQL operations can be defined and mapped. The results can be displayed in JSON and other formats.

In this lab you will be going through the following:

  1. Retrieve information from tables based on a parameter using GET method.
  2. Insert data into Employees table using PUT method.
  3. Update data in Departments table using POST method.
  4. Delete data from Departments table using DELETE method.

Time to Complete

30 min

Background

This Hands-On-Lab series will show you how to use the RESTful Services feature in Oracle SQL Developer 4.1. You will be installing Oracle REST Data Services bundled in SQL Developer. Once installed, you will be creating, editing, validating, uploading and testing the RESTful Service.

The series include the following labs. These labs build on one another so it must be done in the following order.

  1. Installing and Administering Oracle REST Data Services (ORDS) with SQL Developer
  2. AutoREST Enable a Schema
  3. REST Development
  4. Insert, Update, and Delete Data Using RESTful Services

An Oracle VM Virtual Box virtual machine is set up with the required software and files to execute this Hands-On-Lab

Installed Software :

  • Oracle Database 12.1.0.2
  • Oracle SQL Developer 4.1.3
  • Browser Support :
    • Mozilla Firefox with JSONView extension (to view the JSON formatted in the browser)
  • RESTful Services Testing Tool :
    • WizTools.org REST Client release 3.5

What Do You Need?

Before starting this tutorial, you should have:

Setting up Environment

If you are continuing from a previous SQL Developer RESTful Services hands-on-lab, close any resource handler editors that are displayed by clicking on the x (close button) located on the resource handler editor tab.

Perform the following steps to set up the environment required before performing insert, update, or delete operations on hr schema:

  1. Go to HR worksheet and paste the following code to add a trigger to the Employees table. Then, click Run Script icon. If the HR worksheet tab is not displayed, go to Connections, right-click on hr and select Open SQL worksheet.
    create or replace trigger EMPLOYEES_EMPLOYEE_ID_TRG
       before insert on employees
       for each row
       begin
         if :new.employee_id is null then
            select employees_seq.nextval into :new.employee_id from sys.dual;
         end if;
       end;

    Description of this image
  2. EMPLOYEES_EMPLOYEE_ID_TRG is compiled successfully. In the next section, you will create RESTful service to retrieve data using parameters.

    Description of this image

Retrieving Data Using a Parameter

Perform the following steps to create a RESTful Service which retrieves the employee information based on the parameter id using the HTTP Method GET.

  1. In the RESTful Services navigator, right-click demo and select Add Template.

    Description of this image
  2. The RESTful Services Wizard displays Specify Template. Enter employees/:id for URI Pattern and click Next >.
    Note: The parameter :id is the employee number.

    Description of this image
  3. The RESTful Services Wizard displays Specify Handler. Select Query One Row for Source Type.

    Description of this image
  4. Click Next >.

    Description of this image
  5. The RESTful Services Wizard displays RESTful Summary. Click Finish.

    Description of this image
  6. In the RESTful Services navigator, the resource module demo contains the resource template employees/:id. Expand employees/:id by clicking + symbol beside it.

    Description of this image
  7. Click GET under employees/:id.

    Description of this image
  8. The resource handler editor GET employees/:id is displayed on the right side. Locate the connection drop-down menu on the right side of the SQL worksheet toolbar and select hr.

    Description of this image
  9. Enter the following query in the SQL worksheet and click Run statement icon:
    select * from employees where employee_id = :id

    Description of this image
  10. The Enter Binds dialog displays. Enter 200 for Value, and click Apply.

    Description of this image
  11. The Query Result tab displays the information for employee id, 200.

    Description of this image
  12. Click Details tab.

    Description of this image
  13. Click the red pushpin icon to pin this resource handler editor so that it does not get automatically removed when selecting a different resource handler. View the URI Pattern.

    Description of this image
  14. You want to add Locations/id: template. Right-click demo and select Add Template

    Description of this image
  15. The RESTful Services Wizard displays Specify Template. Enter locations/:id for URI Pattern and click Next >.
    Note: The parameter :id is the location id.

    Description of this image
  16. The RESTful Services Wizard displays Specify Handler. Select Query One Row for Source Type, and click Next >.

    Description of this image
  17. The RESTful Services Wizard displays RESTful Summary. Click Finish.

    Description of this image
  18. In the RESTful Services navigator, the resource module demo contains the resource template locations/:id. Expand locations/:id by clicking + symbol beside it. Then, click GET under locations/:id.

    Description of this image
  19. The resource handler editor GET locations/:id is displayed on the right side. Enter the following query in the SQL worksheet and click Run statement icon:
    select * from locations where location_id = :id

    Description of this image
  20. Select hr for Connection, and click OK.

    Description of this image
  21. The Enter Binds dialog displays. Enter 3000 for Value, and click Apply.

    Description of this image
  22. The Query Result tab displays the information for location id, 3000.

    Description of this image
  23. In the RESTful Services navigator, right-click demo and select Upload.

    Description of this image
  24. Click OK.

    Description of this image
  25. You are now ready to test the Restful Service. Open WizTools.org RESTClient (located on the desktop).

    Description of this image
  26. Enter http://localhost:9090/ords/hr/demo/employees/200 for URL, and select GET for HTTP Method. Then, click Go icon located next to the URL field on the top right side.

    Description of this image
  27. In the HTTP Response (lower section), select the Body tab. A single row formatted in JSON is displayed in the Body tab.

    Description of this image
  28. Now you want to test the RESTful service created for locations table. Enter http://localhost:9090/ords/hr/demo/locations/3000 for URL and click Go icon.

    Description of this image
  29. In the HTTP Response (lower section), select the Body tab. A single row formatted in JSON is displayed in the Body tab.

    Description of this image

Inserting Data Using RESTful Services

In this section, you will create a RESTful Service to insert data using the HTTP Method POST. You will use the RESTClient tool to test your RESTful Service and to verify the results.

  1. In the RESTful Services navigator, right-click employees/, select Add Handler  and then select POST.

    Description of this image
  2. In the Mime Types, click the + icon to add a row to the Mime Types.  

    Description of this image
  3. Enter application/json and click Apply.

    Description of this image
  4. In the RESTful Services navigator, the resource template employees/ contains the resource handler POST.
    The resource handler editor POST employees/ is displayed on the right-side. Select hr from Connections drop-down menu.

    Description of this image
  5. Enter the following PL/SQL code in the SQL Worksheet tab:
    DECLARE
       id EMPLOYEES.EMPLOYEE_ID%type;
    BEGIN
      INSERT INTO EMPLOYEES (LAST_NAME, FIRST_NAME, EMAIL, HIRE_DATE, JOB_ID,
                                       SALARY, MANAGER_ID, DEPARTMENT_ID)
         VALUES (:LAST_NAME, :FIRST_NAME, :EMAIL, TO_DATE(:HIRE_DATE,'DD-MON-RR'), :JOB_ID,
                         :SALARY, :MANAGER_ID, :DEPARTMENT_ID)
      RETURNING EMPLOYEE_ID INTO id;
      :location := id;
      :status := 201;
    END;

    Note: The RETURNING EMPLOYEE_ID INTO id returns the value assigned to the EMPLOYEE_ID (by the trigger) into the variable id.  The id variable is defined in the DECLARE section of the code, and the EMPLOYEES.EMPLOYEE_ID%type syntax simply assigns the datatype for the EMPLOYEE_ID column in the EMPLOYEES table to the id variable.
    Note: The bind variable location is assigned the id which contains the value of the EMPLOYEE_ID.  The status is assigned 201 to indicate that the resource is created.  You will have to define a specific parameter for the location bind variable and status bind variable.

    Description of this image
  6. Select Parameters tab, and click + icon to add new parameter.

    Description of this image
  7. Enter the following values for the new parameter and click + icon again to add another parameter:

    Name X-APEX-FORWARD
    Bind Parameter location
    Access Method OUT
    Source Type HTTP HEADER
    Data Type STRING
    Description of this image
  8. Enter the following values for the second parameter:

    Name X-APEX-STATUS-CODE
    Bind Parameter status
    Access Method OUT
    Source Type HTTP HEADER
    Data Type INTEGER
    Note:
    • The X-APEX-FORWARD is a special parameter which is used to return a particular resource as part of the HTTP response to your RESTful Service.
    • The X-APEX-STATUS-CODE is used to return the status of the service.  The OUT indicates that the value will be returned in the HTTP response.

    Description of this image
  9. In the RESTful Services navigator, right-click demo and select Upload.

    Description of this image
  10. Click OK.

    Description of this image
  11. You are now ready to test the Restful Service. Provide following values in the WizTools.org RESTClient.

    URL http://localhost:9090/ords/hr/demo/employees/
    HTTP Method POST
    Description of this image
  12. You want to specify that the body of this request is a JSON format using the character set UTF-8. Click Body tab and select String body from the drop-down list.

    Description of this image
  13. Click Edit Content-type & Charset icon.

    Description of this image
  14. Select application/json for Content-type and UTF-8 for Charset. Click Ok.

    Description of this image
  15. Type the body in JSON fomat using key/value pair as given below, and click Go:

    {"LAST_NAME":"TESTINSERT", "FIRST_NAME":"TESTFIRST", "EMAIL":"TESTMAIL", "HIRE_DATE":"25-JUN-15", "JOB_ID":"IT_PROG", "SALARY":6000, "MANAGER_ID":103, "DEPARTMENT_ID":60}

    Note:

    • The data is typed in the body using JSON format key/value pair, the key and value is separated by a colon. The key is enclosed in double quotes. The value for string is enclosed in double quotes and the numeric value is not enclosed in double quotes.
    • LAST_NAME, FIRST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID and DEPARTMENT_ID will be mapped to the bind variables with the same names that you have specified in the POST handler PL/SQL code.
    Description of this image
  16. In the HTTP Response (lower section), click the body tab to view the results as:
    {"employee_id":207,"first_name":"TESTFIRST","last_name":"TESTINSERT","email":"TESTMAIL","phone_number":null, "hire_date":"2015-06-25T07:00:00Z","job_id":"IT_PROG","salary":6000,"commission_pct":null,
    "manager_id":103,"department_id":60}

    Note

    • Your employee_id may have a different value if you run it multiple times.
    • By defining special parameter X-APEX-FORWARD, the results are returned in a JSON format.  The X-APEX-FORWARD parameter’s bind variable location contained the employee_id value which will list the columns values for that row.
    • Since you have defined a special parameter X-APEX-STATUS-CODE, the value is used in the HTTP Response for status.

    Description of this image
  17. You can verify the row has been inserted using your browser. Enter http://localhost:9090/ords/hr/demo/employees/207 in the address bar and press enter. The record for employee_id, 207 is displayed.

    Description of this image
  18. Similarly, you can verify the row has been inserted using your SQL Developer also. In the GET employees/:id editor, click the Run Statement icon on SQL Worksheet tab. 

    Description of this image
  19. The Enter Binds dialog displays. Enter 207 for Value, and click Apply.

    Description of this image
  20. The Query Result tab displays the information for employee id, 207.

    Description of this image

Updating Data Using RESTful Services

In this section, you will create a RESTful Service to update data using the HTTP Method PUT. You will use the RESTClient tool to test your RESTful Service and to verify the results.

  1. In the RESTful Services navigator, right-click employees/:id, select Add Handler and then select PUT.  

    Description of this image
  2. In the Mime Types, click the + icon to add a row to the Mime Types. 

    Description of this image
  3. Enter application/json and click Apply.

    Description of this image
  4. In the RESTful Services navigator, the resource template employees/:id contains the resource handler PUT.
    The resource handler editor PUT employees/:id is displayed on the right-side. Select hr from Connections drop-down menu.

    Description of this image
  5. Enter the following PL/SQL code in the SQL Worksheet tab:
    BEGIN
       UPDATE EMPLOYEES SET LAST_NAME = :LAST_NAME, SALARY = :SALARY, JOB_ID = :JOB_ID,
                            DEPARTMENT_ID = :DEPARTMENT_ID
          WHERE EMPLOYEE_ID = :id;
      :location := :id;
      :status := 200;
    END;

    Description of this image
  6. Select Parameters tab, and click + icon to add new parameter.

    Description of this image
  7. Enter the following values for the new parameter and click + icon again to add another parameter:

    Name X-APEX-FORWARD
    Bind Parameter location
    Access Method OUT
    Source Type HTTP HEADER
    Data Type STRING
    Description of this image
  8. Enter the following values for the second parameter:

    Name X-APEX-STATUS-CODE
    Bind Parameter status
    Access Method OUT
    Source Type HTTP HEADER
    Data Type INTEGER
    Description of this image
  9. In the RESTful Services navigator, right-click demo and select Upload.

    Description of this image
  10. Click OK.

    Description of this image
  11. You are now ready to test the Restful Service. Provide following values in the WizTools.org RESTClient.

    URL http://localhost:9090/ords/hr/demo/employees/207
    HTTP Method PUT
  12. Description of this image
  13. You want to specify that the body of this request is a JSON format using the character set UTF-8. Click Body tab and select String body from the drop-down list.

    Description of this image
  14. Click Edit Content-type & Charset icon.

    Description of this image
  15. Select application/json for Content-type, and click Ok.

    Description of this image
  16. Type the body in JSON fomat using key/value pair as given below, and click Go:

    {"LAST_NAME":"TESTUPDATE", "JOB_ID":"SA_REP", "SALARY":8000, "DEPARTMENT_ID":80}

    Note: LAST_NAME, JOB_ID, SALARY and DEPARTMENT_ID will be mapped to the bind variable with the same name that you have specified in the PUT Handler PL/SQL code.

    Description of this image
  17. In the HTTP Response (lower section), click the body tab to view the results as:

    {"employee_id":207,"first_name":"TESTFIRST","last_name":"TESTINSERT","email":"TESTMAIL","phone_number":null, "hire_date":"2015-06-25T07:00:00Z","job_id":"SA_REP","salary":8000,"commission_pct":null,
    "manager_id":103,"department_id":80}

    Note:
    • The Status returned is 200 since you have specified the X-APEX-STATUS-CODE which returned the status value.  The Body displays the results of the updated row.
    • Using X-APEX-FORWARD parameter which contains the employee_id, returns the results for that row in JSON, consisting of its columns and values. 
    • The columns’ values for LAST_NAME, JOB_ID, SALARY and DEPARTMENT_ID are updated.
    Description of this image
  18. You can verify the row has been updated using your browser. Enter http://localhost:9090/ords/hr/demo/employees/207 in the address bar and press enter. The updated record for employee_id, 207 is displayed.

    Description of this image
  19. Similarly, you can verify the row has been inserted using your SQL Developer also. In the GET employees/:id editor, click the Run Statement icon on SQL Worksheet tab.

    Description of this image
  20. The Enter Binds dialog displays. Enter 207 for Value, and click Apply.

    Description of this image
  21. The Query Result tab displays the updated information for employee id, 207.

    Description of this image

Deleting Data Using RESTful Services

In this section, you will create a RESTful Service to delete data using the HTTP Method DELETE. You will use the RESTClient tool to test your RESTful Service and to verify the results.

  1. In the RESTful Services navigator, right-click locations/:id, select Add Handler and then select DELETE.

    Description of this image
  2. Click Apply.

    Description of this image
  3. In the RESTful Services navigator, the resource template locations/ contains the resource handler DELETE.
    The resource handler editor DELETE locations/:id is displayed on the right-side. Select hr from Connections drop-down menu.

    Description of this image
  4. Enter the following PL/SQL code in the SQL Worksheet tab:
    BEGIN
       DELETE FROM LOCATIONS WHERE location_id = :id;
       :status := 204;
    END;

    Description of this image
  5. Select Parameters tab, and click + icon to add new parameter.

    Description of this image
  6. Enter the following values for the new parameter:

    Name X-APEX-STATUS-CODE
    Bind Parameter status
    Access Method OUT
    Source Type HTTP HEADER
    Data Type INTEGER
    Description of this image
  7. In the RESTful Services navigator, right-click demo and select Upload...

    Description of this image
  8. Click OK.

    Description of this image
  9. You are now ready to test the Restful Service. Provide following values in the WizTools.org RESTClient.

    URL http://localhost:9090/ords/hr/demo/locations/3000
    Method DELETE
    Description of this image
  10. Click Body tab and select None from the drop-down list since nothing will be returned in the body.

    Description of this image
  11. Click Go icon.

    Description of this image
  12. In the HTTP Response (lower section), the Status returned is 204 No Content since you have specified the
    X-APEX-STATUS-CODE which returned the status value indicating that the service fulfilled the delete request.

    Description of this image
  13. Using your browser, you can verify the row has been deleted.. Enter http://localhost:9090/ords/hr/demo/locations/3000 in the address bar and press enter. A 404 Not Found is displayed.

    Description of this image
  14. Using SQL Developer, you can verify the row has been deleted.In the GET locations/:id editor, click the Run Statement icon on SQL Worksheet tab.

    Description of this image
  15. The Enter Binds dialog displays. Enter 3000 for Value, and click Apply.

    Description of this image
  16. The Query Result tab displays empty record.

    Description of this image

Summary

Congratulations! You have completed Developing RESTful Services with Oracle SQL Developer series successfully.

Want to Learn More?

Credits

  • Curriculum Developer: Anjani Pothula

  • Other Contributors:Ashley Chen