Oracle APEX 5.1发布 RESTful Web Service教程

版权声明:http://www.sunansheng.com/ https://blog.csdn.net/sunansheng/article/details/74025246


Before You Begin

Purpose

This tutorial covers creating a RESTful Web Service and accessing the Web Service through an application in Application Express 5.1. It also covers consuming the Web Service using a REST client.

Time to Complete

Approximately 40 minutes.

Overview

Web Services enable applications to interact with one another over the web in a platform-neutral, language independent environment. In a typical Web Services scenario, a business application sends a request to a service at a given URL by using the protocol over HTTP. The service receives the request, processes it, and returns a response. Web Services are typically based on Simple Object Access Protocol (SOAP) or Representational State Transfer (REST) architectures. RESTful Web Services are result oriented. The scope of the Web Service is found in the URI and the method of the service is described by the HTTP method that is used such as GET, POST, PUT, and DELETE.

The RESTful Web Service Wizard is a set of pages in the SQL Workshop area of Oracle Application Express that help you to create a RESTful Web Service declaratively. Once you have defined a RESTful Web Service, you can call it with a unique Uniform Resource Identifier (URI). RESTful Web Services are organized within Oracle APEX through a hierarchy of a module, a resource template and handlers within a template. The resource template includes a prefix for the URI, which is completed with a final portion of the URI defined by the handler. 

This tutorial covers creating a RESTful Web Service declaratively using Oracle Application Express's SQL Workshop tool, and then consuming the same service by creating an application and adding a Web Service Reference to the RESTful Web Service. The RESTful Web Service is also consumed using a REST client.

Please keep in mind the following while running this tutorial:

  • Logging into your Oracle Application Express workspace: Your Oracle Application Express workspace may reside in an on-premises Oracle Database or in Oracle Database Cloud Services. The login credentials differ depending on where your workspace is located:
    • Logging into Oracle Application Express in a Oracle Database Cloud Service: Go to the Oracle Help Center for Cloud, and select Platform and Infrastructure. From here, select your Database Cloud Service and the Get Started page will appear.
    • Logging in to Oracle Application Express on-premises: From your browser, go to the location of your on-premises installation of your Oracle Application Express workspace provided by your Workspace Administrator.
  • Application ID: Screenshots in this tutorial show a blurred Application ID. Your Application ID can be any value assigned automatically while creating the application.
  • Schema: If you are accessing an Oracle Application Express workspace in Database Schema Service,  you have one schema assigned to you with a schema name that you cannot change. If you are accessing the workspace in an on-premises Oracle database, you may have more than one schema assigned to your workspace by the Oracle Application Express Instance Administrator.

What Do You Need?

Before starting this tutorial, you should:

  • Have access to an Oracle Database 11.2.x.x or later release, including Enterprise Edition and Express Edition (Oracle Database XE), either on-premises or in a Database Cloud Service.
  • Install Oracle Application Express Release 5.1 into your Oracle Database with RESTful Services configured in Oracle Application Express (for on-premises only).
  • Download and unzip the files.zip into your working directory.
  • Execute Create_Employees.sql from the extracted files, to create required database objects.
 

Creating a RESTful Web Service

In this topic, you create a RESTful Web Service using RESTful Services tool in SQL Workshop. The RESTful Web Service Wizard is a set of pages in SQL Workshop that help you to create a new RESTful Web Service declaratively. The RESTful Web Service calls a specific SQL statement in your database.

 

Creating a RESTful Web Service with GET and PUT Resource Handlers

To create a RESTful Web Service on the Employees table with sample GET and PUT service handlers, perform the below steps:

  1.  From the Oracle Application Express Home page, select the SQL Workshop tab and select RESTful Services.

    Oracle Application Express Home page
    Description of this image
  2. From the RESTful Services page, select the Create a New RESTful Service option.

    Creating a new RESTful service
    Description of this image
  3. A page loads with entries grouped under three different categories named RESTful Services Module, Resource Template, and Resource Handler. Under RESTful Services Module, enter employees for Name, and scroll down further.

    RESTful services module section
    Description of this image
  4. Under Resource Template, enter employees/ for URI Template to identify your Uniform Resource Identifier (URI), and scroll down further.

    Resource Template section
    Description of this image
  5. Under Resource Handler, select GET for Method, Query for Source Type, CSV for Format. This identifies the HTTP method to be used for the Resource Handler.
    Enter the following SQL query for Source, and click Create Module.

    select * from employees

    Resource Handler section
    Description of this image
  6. The GET Handler is created under employees/. To edit its properties, click GET under employees/.

    The GET Handler created under employees/
    Description of this image
  7. Select No for Requires Secure Access, and click Apply Changes.

    Selecting No for Requires Secure Access
    Description of this image
  8. To test the behavior of the RESTful Service Handler, click Test.

    Note: If your screen does not show a Test button, please ensure that RESTful Services are configured in your Oracle Application Express installation properly.

    Testing the behavior of the RESTful Service Handler
    Description of this image
  9. You are prompted to save the file which you can then view using a CSV editor.

    Saving the file
    Description of this image
  10. The CSV format result set is displayed.

    The saved file in CSV format
    Description of this image
  11. Let us now create a Handler for the POST method in the same Web Service. Click Create Handler under employees/.

     creating a Handler for the POST method
    Description of this image
  12. Select POST for Method and PL/SQL for Source Type. Enter application/json for MIME Types Allowed. Select No for Requires Secure Access. Enter the following PL/SQL code for Source, and click Create.

    declare
      id employees.employee_id%TYPE; 
    begin 
      id := employees_seq.nextval; 

      insert into employees 
      (employee_id,first_name, last_name, email, hire_date, job_id) 
      values 
      (id, :first_name, :last_name, :email, to_date(:hire_date, 'DD-MM-YYYY'), 
      :job_id); 

      :employee_id := id; 
    end;

    Entering parameters to the POST method
    Description of this image
  13. Scroll down the page, and click Create Parameter to add an OUT parameter to the handler that will return the newly created employee’s ID.

    Adding an OUT parameter
    Description of this image
  14. Enter employee_id for Name and Bind Variable Name. Select OUT for Access Method, HTTP Header for Source Type, String for Parameter Type, and click Create.

    Entering parametes for the OUT parameter
    Description of this image
  15. The OUT parameter is created.

    The OUT parameter is created
    Description of this image
  16. Click Create Parameter again to add the following IN parameters to the handler.

    NameBind Variable NameAccess MethodParameter Type
    first_name
    first_nameIN
    String
    email
    email
    IN
    String
    last_namelast_nameINString
    hire_date
    hire_dateIN
    String
    job_id
    job_idIN
    String
    Adding IN parameters
    Description of this image

    In the next section, you create a new template to retrieve JSON result set based on Query One Row with a bind variable.

 

Creating a Resource Handler with Query One Row

In this section, you will create a RESTful Service that provides detailed information of an employee, given the employee id. The result is returned in JSON format. Perform the following steps:

  1. Click Create Template.

    Clicking Create Template
    Description of this image
  2. Enter employees/{id} for URI Template, and click Create.

    Entering employees/{id} for URI Template
    Description of this image
  3. Click Create Handler under employees/{id}.

    Selecting Create Handler
    Description of this image
  4. Select GET for Method, Query One Row for Source Type, and No for Requires Secure Access.
    Enter the following SQL Query for Source, and click Create.

    select * from employees where employee_id = :id

    Entering attributes of the GET handler
    Description of this image
  5. Scroll down and click Create Parameter.

    Clicking Create Parameter
    Description of this image
  6. Enter id for Name and Bind Variable Name. Select IN for Access Method, HTTP Header for Source Type, String for Parameter Type, and click Create.

    Creating the GET handler
    Description of this image
  7. You want to change the Source Type. Under Parameters, click the id link under Name.

    Changing source type
    Description of this image
  8. Select URI for Source Type, and click Apply Changes.

    Selecting URI for Source type
    Description of this image
  9. Before testing this handler, you have to set a bind variable to pass a value for the input parameter(id). Click Set Bind Variables >.

    Setting a bind variable
    Description of this image
  10. Enter 103 for :ID, and click Test.

    ENtering 103 for ID
    Description of this image
  11. Complete details of the employee with employee_id = 103 is displayed.

    Viweing details of employee 103
    Description of this image
 

Creating a Resource Handler with Employees Feed

In this section, you will be creating a RESTful service of a feed source type. The feed results are rendered in JSON format. Each item in the feed contains a summary of a resource and a hyperlink to a full representation of the resource. Perform the below steps:

  1. Click Create Template.

    Clicking Create Template
    Description of this image
  2. Enter employeesfeed/ for URI Template, and click Create.

    Entering employeesfeed/ for URI Template
    Description of this image
  3. Under employeesfeed/, click Create Handler.

    Clicking Create Handler
    Description of this image
  4. Select GET for Method, Feed for Source Type, and No for Requires Secure Access. Under Source, enter the following SQL query, and click Create.

    select employee_id, first_name 
    from employees 
    order by employee_id, first_name

    Entering attributes of the handler
    Description of this image
  5. Under employeesfeed/, click GET to open the Resource Handler Editor.

    Opening resource handler editor
    Description of this image
  6. Scroll down the page, and click Test.

    Clicking Test
    Description of this image
  7. The results are rendered in JSON format. Each item consists of a URI which contains the base URI from this RESTful Service, and the value of employee_id used as the parameter. For the Feed source type, the first column must be a unique identifier. It will be transformed into a hyperlink when this RESTful Service is called. 
    In this example, employee_id is the first column and will turn into a hyperlink. 

    For example, in the screenshot shown below, the URI for an employee with employee_id = 100 is https://:<hostname>:<port>/ords/hr/employeesfeed/100

    Note: The URI shown in this example is specific to the database On-Premise subscription used for executing this tutorial, and it might be different for you. The value of the URI also depends on whether you are performing this tutorial On-Premises or on a Cloud Service. In general, the URI formats are as follows:

    On-Premiseshttp://localhost:<Port where ords is set up>/ords/<schema name>/<Resource Template Name>/<employee_id>

    For example: http://localhost:9090/ords/hr/employeesfeed/100 
    where ords is set up on port 9090 during Oracle APEX installation, hr is the schema name, employeesfeed is the Resource Template name, and 100 is the employee_id.
    On a Cloud Service<Service URL as received in the Welcome email from Oracle Cloud>/<Resource Template Name>/<employee_id>

    For example: https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/employeesfeed/100 
    where https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/ is the Service URL for the cloud service subscription, employeesfeed is the Resource Template name, and 100 is the employee_id.
    Results rendered in JSON Format
    Description of this image
  8. Select the URI for one of the employee_ids, and copy it to clipboard.

    Copying URI for one of the employee_ids
    Description of this image
  9. Open a browser, paste the copied URI, and press Enter. Notice that the details of that particular employee are returned as a JSON result set.

    Pasting the copied URI
    Description of this image
 

Creating a Resource Handler with Employees Feed for a Given Department

In this section, you will be creating a RESTful service of a feed source type given the Department ID. The feed results are rendered as JSON. Perform the below steps:

  1. Click Create Template.

    Clicking Create Template
    Description of this image
  2. Enter employeesfeed/{id}, and click Create.

    Entering employeesfeed/{id for URI Template
    Description of this image
  3. Under employeesfeed/{id}, click Create Handler.

    Clicking Create Handler
    Description of this image
  4. Select Get for Method, Feed for Source Type, and No for Requires Secure Access. Under Source, enter the following SQL query, and click Create.

    select employee_id, first_name 
    from employees 
    where department_id = :id

    Selecting GET method
    Description of this image
  5. Scroll down further, and click Create Parameter > to add an IN parameter to the handler that will receive the department_id.

    Creating a parameter
    Description of this image
  6. Enter id for Name and Bind Variable Name. Select IN for Access Method, HTTP Header for Source Type, String for Parameter Type, and click Create.

    Entering attributes of the parameter
    Description of this image
  7. You want to change the Source Type. Under Parameters, click the id link under Name.

    Changing source type
    Description of this image
  8. Select URI for Source Type, and click Apply Changes.

    Selecting URI for source type
    Description of this image
  9. Before testing this handler, you have to set bind variable to pass a value for the input parameter, id. Click Set Bind Variables >.

    Setting bind variable
    Description of this image
  10. Enter 60 for :ID, and click Test.

    ENtering 60 for ID
    Description of this image
  11. The results are rendered in JSON format. Each item consists of a URI which contains the base URI from this RESTful Service, and the value of department_id used as the parameter. For the Feed source type, the first column must be unique identifier and will be transformed into a hyperlink when this RESTful Service is called. 
    In this example, department_id is the first column and will turn into a hyperlink. 

    For example, in the screenshot shown below, the URI for an employee with department_id = 60 is http://<hostname>:<port>/ords/hr/employeesfeed/60?page=1 where page=1 indicates that these results are part of page 1. If there are many records in the result set, the results can span across page 2 and so on.

    Note: The URI shown in this example is specific to the database On-Premise service subscription used for executing this tutorial, and it might be different for you. The value of the URI also depends on whether you are performing this tutorial On-Premises or on a Cloud Service. In general, the URI formats are as follows:

    On-Premiseshttp://localhost:<Port where ords is set up>/ords/<schema name>/<Resource Template Name>/<department_id>?page=1

    For example: http://localhost:9090/ords/hr/employeesfeed/60?page=1
    where ords is set up on port 9090 during Oracle APEX installation, hr is the schema name, employeesfeed is the Resource Template name and 60 is the department_id.
    On a Cloud Service<Service URL as received in the Welcome email from Oracle Cloud>/<Resource Template Name>/<department_id>?page=1

    For example: https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/employeesfeed/60?page=1
    where https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/
     is the Service URL for the cloud service subscription, employeesfeed is the Resource Template name, and 60 is the department_id.
    The result in JSON format
    Description of this image
 

Creating a RESTful Web Service Reference in Oracle Application Express

In this topic, you consume the RESTful Web Service in Oracle Application Express by creating a database application and by creating a Web Service Reference in the application. You create a form and report page that uses the web service.

Note: If you are executing this tutorial On-Premises, make sure you have granted the connect privileges by executing the APEX_ACL.sql script from the files.zip folder that you had downloaded and unzipped in the Prerequisites section of this tutorial.

  1. From the Oracle Application Express home page, click the down arrow next to App Builder, and select Database Applications.

    Selecting Databse Applications
    Description of this image
  2. Click the Create icon.

    Clicking the Create icon
    Description of this image
  3. Accept the default, and click Next >.

    Navigating to the next page
    Description of this image
  4. Enter RESTful Web Services Demo for Name, and click Next >.

    Entering RESTful Web Services Demo for Name
    Description of this image
  5. Click Next >.

    Clicking Next to navigate to the next screen
    Description of this image
  6. Accept the default, and click Next >.

    Description of this image
  7. Select Application Express Accounts for Authentication Scheme, and click Next >.

    Selecting Application Express Accounts for Authentication Scheme
    Description of this image
  8. Click Create Application.

    Clicking Create Application
    Description of this image
  9. The application is created. Click Shared Components.

    Clicking Shared Components
    Description of this image
  10. Under Data References, click Web Service References.

    Clicking Web Service References.
    Description of this image
  11. Click Create >.

    Clicking Create >
    Description of this image
  12. Select REST for What type of Web reference would you like to create, and click Next >.

    Selecting REST for the type of web reference
    Description of this image
  13. Enter employees for Name. Select GET for HTTP Method and No for Basic Authentication. For the URL, enter the Web Reference URI for the GET Service Handler which was created in the first section of this tutorial.

    As explained in the below table, your URI depends on the location of your Oracle Application Express instance, whether On-Premises or on a Database Cloud Service.

    On-Premiseshttp://localhost:<Port where ords is set up>/ords/<schema name>/<Resource Template Name>/

    For example: http://localhost:9090/ords/hr/employees/
    where ords is set up on port 9090 during Oracle APEX installation, hr is the schema name, employees is the Resource Template name.
    On a Cloud Service<Service URL as received in the Welcome email from Oracle Cloud>/<Resource Template Name>/

    For example: https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/employees/
    where https: databasetrial:<user>.db.us2.oraclecloudapps.com/hr/ is the Service URL for the cloud service subscription and employees is the Resource Template name.
    This RESTful Web Service does not require an HTTP Header parameter. So, scroll down and click the Delete Header icon. 

    Clicking delete header
    Description of this image
  14. Click Next >.

    navigating to the next screen
    Description of this image
  15. There are no parameters defined for the GET Service Handier. So, click the Delete Parameter icon for Input Parameters.

    Clicking the Delete Parameter icon for Input Parameters.
    Description of this image
  16. Click Next >.

    Navigating to hte next screen
    Description of this image
  17. Now, you have to define the REST Outputs. Select Text for Output FormatEnter comma for Parameter Delimiter, and \n for New Record Delimiter. 
    Under Output Parameters, enter Employee ID for Name and 1 for Path. Select String for Type, and click Add Parameter.

    Clicking Add Parameter
    Description of this image
  18. Similarly, add the following Output Parameters, and click Create.

    NamePathType
    Name
    2
    String
    Hire Date
    6
    String
    Job Id
    7
    String
    Adding output parameters
    Description of this image
  19. Click the View Report icon.

    Clicking the View Report icon
    Description of this image
  20. Click the Test icon for employees.

    Clicking Test
    Description of this image
  21. Employees' details is displayed in the Response section. Click Cancel.

    Employee details is displayed
    Description of this image
  22. Click Application<n> in the breadcrumb.

    Clicking Application<n> in the breadcrumb.
    Description of this image
  23. Click Create Page >.

    Clicking Create Page >.
    Description of this image
  24. Click the Form icon.

    Clicking the Form icon
    Description of this image
  25. Click the Report and Form on Web Service icon.

    Clicking the Report and Form on Web Service icon
    Description of this image
  26. Select employees for Web Service Reference, doREST for Operation, and click Next >.

    Note: Employees appears in the select list for Web Service Reference because you added it under Web Service References in Shared Components.

    Selecting employees for Web Service Reference
    Description of this image
  27. Click Next >.

    Navigating to the next screen
    Description of this image
  28. Click Next >.

    Navigating to the next screen
    Description of this image
  29. Select all four parameters, that is, employee IdNameHire Date, and Job Id. Click Next >.

    Selecting  all four parameters and avigating to the next screen
    Description of this image
  30. Accept the default an click Next >.

    Navigating to the next screen
    Description of this image
  31. Click Create.

    Clicking Create
    Description of this image
  32. The new page is created. Click Save and Run Page.

    Clicking Save and Run Page.
    Description of this image
  33. Enter your Oracle Application Express credentials, and click Log In.

    Entering your Oracle Application Express credentials
    Description of this image
  34. Click Submit.

    Cliking Submit
    Description of this image
  35. The Web Service is executed, and the results displayed.

    Results displayed on screen
    Description of this image
 

Consuming the RESTful Web Service Created in Oracle Application Express Using a REST Client

In this section, you consume the Web Service that you have created in Oracle Application Express, using a REST Client.

Note: For the purpose of this tutorial, we will be using the RESTClient add-on in Firefox to demonstrate how to consume the RESTful Web Service created in APEX. You can also use other REST Clients such as, REST Easy and RESTer to perform these steps in Firefox. If you are using Google Chrome, you can install add-ons such as Postman to perform the steps.

  1. Open Firefox and install the RESTClient, a debugger for RESTful web services add-on to your browser.

    Installing add-on
    Description of this image
  2. Open the installed add-on in your browser.

    Opening the installed add-on
    Description of this image
  3. To fetch the details of an employee, select GET as the Request Method. For the URL, enter the Web Reference URI for the GET Service Handler which was created in the first section of this tutorial.

    As explained in the below table, your URI depends on the location of your Oracle Application Express instance, whether On-Premises or on a Database Cloud Service. In this example, https://:<hostname>:<port>/ords/hr/employees/employees is the URL used for the On-Premise machine.

    On-Premiseshttp://localhost:<Port where ords is set up>/ords/<schema name>/<Resource Template Name>/<Resource Handler>

    For example: http://localhost:9090/ords/hr/employees/employees
    where ords is set up on port 9090 during Oracle APEX installation, hr is the schema name, employees is the Resource Template name and employees is the Resource Handler.
    On a Cloud Service<Service URL as received in the Welcome email from Oracle Cloud>/<Resource Template Name>/<Resource Handler>

    For example: https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/employees/
    employeeswhere https: databasetrial:<user>.db.us2.oraclecloudapps.com/hr/ is the Service URL for the cloud service subscription and employees is the Resource Template name and employees is the Resource Handler.

    Click SEND.

    Fetching employee details
    Description of this image
  4. Select the Response Body (Preview) tab to view all the employee records.

    Viewing the employee records
    Description of this image
  5. To insert new values into the employees table, under Headers, select Custom Header.

    Inserting a new record
    Description of this image
  6. Enter Content-Type as Name, application/json as Value, and click OK.

    Entering Request Header values
    Description of this image
  7. In the RESTClient page, enter POST as the request method, enter the same URL as that of the GET method, enter the following code in the Request Body, and click SEND.

    {    
      "first_name":"Supriya",
      "last_name":"Ananth",
      "email":"SUPANANT",
      "hire_date":"13-05-2001",
      "job_id":"AD_PRES"
       }
    entering parameters of POST request
    Description of this image
  8. Under Response, select Responses Header. You see that the new employee's information is added into the Employees table. The newly created employee’s ID is returned back to the application.

    Newly created employee's ID displayed on screen
    Description of this image
 

Summary

In this tutorial, you have learned how to:

  • Create a RESTful Web Service  with various Resource Handlers using Oracle Application Express.
  • Create a RESTful Web Service Reference in Application Express.
  • Consume the Web Service created in Application Express using a REST client.

Before You Begin

Purpose

This tutorial covers creating a RESTful Web Service and accessing the Web Service through an application in Application Express 5.1. It also covers consuming the Web Service using a REST client.

Time to Complete

Approximately 40 minutes.

Overview

Web Services enable applications to interact with one another over the web in a platform-neutral, language independent environment. In a typical Web Services scenario, a business application sends a request to a service at a given URL by using the protocol over HTTP. The service receives the request, processes it, and returns a response. Web Services are typically based on Simple Object Access Protocol (SOAP) or Representational State Transfer (REST) architectures. RESTful Web Services are result oriented. The scope of the Web Service is found in the URI and the method of the service is described by the HTTP method that is used such as GET, POST, PUT, and DELETE.

The RESTful Web Service Wizard is a set of pages in the SQL Workshop area of Oracle Application Express that help you to create a RESTful Web Service declaratively. Once you have defined a RESTful Web Service, you can call it with a unique Uniform Resource Identifier (URI). RESTful Web Services are organized within Oracle APEX through a hierarchy of a module, a resource template and handlers within a template. The resource template includes a prefix for the URI, which is completed with a final portion of the URI defined by the handler. 

This tutorial covers creating a RESTful Web Service declaratively using Oracle Application Express's SQL Workshop tool, and then consuming the same service by creating an application and adding a Web Service Reference to the RESTful Web Service. The RESTful Web Service is also consumed using a REST client.

Please keep in mind the following while running this tutorial:

  • Logging into your Oracle Application Express workspace: Your Oracle Application Express workspace may reside in an on-premises Oracle Database or in Oracle Database Cloud Services. The login credentials differ depending on where your workspace is located:
    • Logging into Oracle Application Express in a Oracle Database Cloud Service: Go to the Oracle Help Center for Cloud, and select Platform and Infrastructure. From here, select your Database Cloud Service and the Get Started page will appear.
    • Logging in to Oracle Application Express on-premises: From your browser, go to the location of your on-premises installation of your Oracle Application Express workspace provided by your Workspace Administrator.
  • Application ID: Screenshots in this tutorial show a blurred Application ID. Your Application ID can be any value assigned automatically while creating the application.
  • Schema: If you are accessing an Oracle Application Express workspace in Database Schema Service,  you have one schema assigned to you with a schema name that you cannot change. If you are accessing the workspace in an on-premises Oracle database, you may have more than one schema assigned to your workspace by the Oracle Application Express Instance Administrator.

What Do You Need?

Before starting this tutorial, you should:

  • Have access to an Oracle Database 11.2.x.x or later release, including Enterprise Edition and Express Edition (Oracle Database XE), either on-premises or in a Database Cloud Service.
  • Install Oracle Application Express Release 5.1 into your Oracle Database with RESTful Services configured in Oracle Application Express (for on-premises only).
  • Download and unzip the files.zip into your working directory.
  • Execute Create_Employees.sql from the extracted files, to create required database objects.
 

Creating a RESTful Web Service

In this topic, you create a RESTful Web Service using RESTful Services tool in SQL Workshop. The RESTful Web Service Wizard is a set of pages in SQL Workshop that help you to create a new RESTful Web Service declaratively. The RESTful Web Service calls a specific SQL statement in your database.

 

Creating a RESTful Web Service with GET and PUT Resource Handlers

To create a RESTful Web Service on the Employees table with sample GET and PUT service handlers, perform the below steps:

  1.  From the Oracle Application Express Home page, select the SQL Workshop tab and select RESTful Services.

    Oracle Application Express Home page
    Description of this image
  2. From the RESTful Services page, select the Create a New RESTful Service option.

    Creating a new RESTful service
    Description of this image
  3. A page loads with entries grouped under three different categories named RESTful Services Module, Resource Template, and Resource Handler. Under RESTful Services Module, enter employees for Name, and scroll down further.

    RESTful services module section
    Description of this image
  4. Under Resource Template, enter employees/ for URI Template to identify your Uniform Resource Identifier (URI), and scroll down further.

    Resource Template section
    Description of this image
  5. Under Resource Handler, select GET for Method, Query for Source Type, CSV for Format. This identifies the HTTP method to be used for the Resource Handler.
    Enter the following SQL query for Source, and click Create Module.

    select * from employees

    Resource Handler section
    Description of this image
  6. The GET Handler is created under employees/. To edit its properties, click GET under employees/.

    The GET Handler created under employees/
    Description of this image
  7. Select No for Requires Secure Access, and click Apply Changes.

    Selecting No for Requires Secure Access
    Description of this image
  8. To test the behavior of the RESTful Service Handler, click Test.

    Note: If your screen does not show a Test button, please ensure that RESTful Services are configured in your Oracle Application Express installation properly.

    Testing the behavior of the RESTful Service Handler
    Description of this image
  9. You are prompted to save the file which you can then view using a CSV editor.

    Saving the file
    Description of this image
  10. The CSV format result set is displayed.

    The saved file in CSV format
    Description of this image
  11. Let us now create a Handler for the POST method in the same Web Service. Click Create Handler under employees/.

     creating a Handler for the POST method
    Description of this image
  12. Select POST for Method and PL/SQL for Source Type. Enter application/json for MIME Types Allowed. Select No for Requires Secure Access. Enter the following PL/SQL code for Source, and click Create.

    declare
      id employees.employee_id%TYPE; 
    begin 
      id := employees_seq.nextval; 

      insert into employees 
      (employee_id,first_name, last_name, email, hire_date, job_id) 
      values 
      (id, :first_name, :last_name, :email, to_date(:hire_date, 'DD-MM-YYYY'), 
      :job_id); 

      :employee_id := id; 
    end;

    Entering parameters to the POST method
    Description of this image
  13. Scroll down the page, and click Create Parameter to add an OUT parameter to the handler that will return the newly created employee’s ID.

    Adding an OUT parameter
    Description of this image
  14. Enter employee_id for Name and Bind Variable Name. Select OUT for Access Method, HTTP Header for Source Type, String for Parameter Type, and click Create.

    Entering parametes for the OUT parameter
    Description of this image
  15. The OUT parameter is created.

    The OUT parameter is created
    Description of this image
  16. Click Create Parameter again to add the following IN parameters to the handler.

    NameBind Variable NameAccess MethodParameter Type
    first_name
    first_nameIN
    String
    email
    email
    IN
    String
    last_namelast_nameINString
    hire_date
    hire_dateIN
    String
    job_id
    job_idIN
    String
    Adding IN parameters
    Description of this image

    In the next section, you create a new template to retrieve JSON result set based on Query One Row with a bind variable.

 

Creating a Resource Handler with Query One Row

In this section, you will create a RESTful Service that provides detailed information of an employee, given the employee id. The result is returned in JSON format. Perform the following steps:

  1. Click Create Template.

    Clicking Create Template
    Description of this image
  2. Enter employees/{id} for URI Template, and click Create.

    Entering employees/{id} for URI Template
    Description of this image
  3. Click Create Handler under employees/{id}.

    Selecting Create Handler
    Description of this image
  4. Select GET for Method, Query One Row for Source Type, and No for Requires Secure Access.
    Enter the following SQL Query for Source, and click Create.

    select * from employees where employee_id = :id

    Entering attributes of the GET handler
    Description of this image
  5. Scroll down and click Create Parameter.

    Clicking Create Parameter
    Description of this image
  6. Enter id for Name and Bind Variable Name. Select IN for Access Method, HTTP Header for Source Type, String for Parameter Type, and click Create.

    Creating the GET handler
    Description of this image
  7. You want to change the Source Type. Under Parameters, click the id link under Name.

    Changing source type
    Description of this image
  8. Select URI for Source Type, and click Apply Changes.

    Selecting URI for Source type
    Description of this image
  9. Before testing this handler, you have to set a bind variable to pass a value for the input parameter(id). Click Set Bind Variables >.

    Setting a bind variable
    Description of this image
  10. Enter 103 for :ID, and click Test.

    ENtering 103 for ID
    Description of this image
  11. Complete details of the employee with employee_id = 103 is displayed.

    Viweing details of employee 103
    Description of this image
 

Creating a Resource Handler with Employees Feed

In this section, you will be creating a RESTful service of a feed source type. The feed results are rendered in JSON format. Each item in the feed contains a summary of a resource and a hyperlink to a full representation of the resource. Perform the below steps:

  1. Click Create Template.

    Clicking Create Template
    Description of this image
  2. Enter employeesfeed/ for URI Template, and click Create.

    Entering employeesfeed/ for URI Template
    Description of this image
  3. Under employeesfeed/, click Create Handler.

    Clicking Create Handler
    Description of this image
  4. Select GET for Method, Feed for Source Type, and No for Requires Secure Access. Under Source, enter the following SQL query, and click Create.

    select employee_id, first_name 
    from employees 
    order by employee_id, first_name

    Entering attributes of the handler
    Description of this image
  5. Under employeesfeed/, click GET to open the Resource Handler Editor.

    Opening resource handler editor
    Description of this image
  6. Scroll down the page, and click Test.

    Clicking Test
    Description of this image
  7. The results are rendered in JSON format. Each item consists of a URI which contains the base URI from this RESTful Service, and the value of employee_id used as the parameter. For the Feed source type, the first column must be a unique identifier. It will be transformed into a hyperlink when this RESTful Service is called. 
    In this example, employee_id is the first column and will turn into a hyperlink. 

    For example, in the screenshot shown below, the URI for an employee with employee_id = 100 is https://:<hostname>:<port>/ords/hr/employeesfeed/100

    Note: The URI shown in this example is specific to the database On-Premise subscription used for executing this tutorial, and it might be different for you. The value of the URI also depends on whether you are performing this tutorial On-Premises or on a Cloud Service. In general, the URI formats are as follows:

    On-Premiseshttp://localhost:<Port where ords is set up>/ords/<schema name>/<Resource Template Name>/<employee_id>

    For example: http://localhost:9090/ords/hr/employeesfeed/100 
    where ords is set up on port 9090 during Oracle APEX installation, hr is the schema name, employeesfeed is the Resource Template name, and 100 is the employee_id.
    On a Cloud Service<Service URL as received in the Welcome email from Oracle Cloud>/<Resource Template Name>/<employee_id>

    For example: https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/employeesfeed/100 
    where https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/ is the Service URL for the cloud service subscription, employeesfeed is the Resource Template name, and 100 is the employee_id.
    Results rendered in JSON Format
    Description of this image
  8. Select the URI for one of the employee_ids, and copy it to clipboard.

    Copying URI for one of the employee_ids
    Description of this image
  9. Open a browser, paste the copied URI, and press Enter. Notice that the details of that particular employee are returned as a JSON result set.

    Pasting the copied URI
    Description of this image
 

Creating a Resource Handler with Employees Feed for a Given Department

In this section, you will be creating a RESTful service of a feed source type given the Department ID. The feed results are rendered as JSON. Perform the below steps:

  1. Click Create Template.

    Clicking Create Template
    Description of this image
  2. Enter employeesfeed/{id}, and click Create.

    Entering employeesfeed/{id for URI Template
    Description of this image
  3. Under employeesfeed/{id}, click Create Handler.

    Clicking Create Handler
    Description of this image
  4. Select Get for Method, Feed for Source Type, and No for Requires Secure Access. Under Source, enter the following SQL query, and click Create.

    select employee_id, first_name 
    from employees 
    where department_id = :id

    Selecting GET method
    Description of this image
  5. Scroll down further, and click Create Parameter > to add an IN parameter to the handler that will receive the department_id.

    Creating a parameter
    Description of this image
  6. Enter id for Name and Bind Variable Name. Select IN for Access Method, HTTP Header for Source Type, String for Parameter Type, and click Create.

    Entering attributes of the parameter
    Description of this image
  7. You want to change the Source Type. Under Parameters, click the id link under Name.

    Changing source type
    Description of this image
  8. Select URI for Source Type, and click Apply Changes.

    Selecting URI for source type
    Description of this image
  9. Before testing this handler, you have to set bind variable to pass a value for the input parameter, id. Click Set Bind Variables >.

    Setting bind variable
    Description of this image
  10. Enter 60 for :ID, and click Test.

    ENtering 60 for ID
    Description of this image
  11. The results are rendered in JSON format. Each item consists of a URI which contains the base URI from this RESTful Service, and the value of department_id used as the parameter. For the Feed source type, the first column must be unique identifier and will be transformed into a hyperlink when this RESTful Service is called. 
    In this example, department_id is the first column and will turn into a hyperlink. 

    For example, in the screenshot shown below, the URI for an employee with department_id = 60 is http://<hostname>:<port>/ords/hr/employeesfeed/60?page=1 where page=1 indicates that these results are part of page 1. If there are many records in the result set, the results can span across page 2 and so on.

    Note: The URI shown in this example is specific to the database On-Premise service subscription used for executing this tutorial, and it might be different for you. The value of the URI also depends on whether you are performing this tutorial On-Premises or on a Cloud Service. In general, the URI formats are as follows:

    On-Premiseshttp://localhost:<Port where ords is set up>/ords/<schema name>/<Resource Template Name>/<department_id>?page=1

    For example: http://localhost:9090/ords/hr/employeesfeed/60?page=1
    where ords is set up on port 9090 during Oracle APEX installation, hr is the schema name, employeesfeed is the Resource Template name and 60 is the department_id.
    On a Cloud Service<Service URL as received in the Welcome email from Oracle Cloud>/<Resource Template Name>/<department_id>?page=1

    For example: https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/employeesfeed/60?page=1
    where https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/
     is the Service URL for the cloud service subscription, employeesfeed is the Resource Template name, and 60 is the department_id.
    The result in JSON format
    Description of this image
 

Creating a RESTful Web Service Reference in Oracle Application Express

In this topic, you consume the RESTful Web Service in Oracle Application Express by creating a database application and by creating a Web Service Reference in the application. You create a form and report page that uses the web service.

Note: If you are executing this tutorial On-Premises, make sure you have granted the connect privileges by executing the APEX_ACL.sql script from the files.zip folder that you had downloaded and unzipped in the Prerequisites section of this tutorial.

  1. From the Oracle Application Express home page, click the down arrow next to App Builder, and select Database Applications.

    Selecting Databse Applications
    Description of this image
  2. Click the Create icon.

    Clicking the Create icon
    Description of this image
  3. Accept the default, and click Next >.

    Navigating to the next page
    Description of this image
  4. Enter RESTful Web Services Demo for Name, and click Next >.

    Entering RESTful Web Services Demo for Name
    Description of this image
  5. Click Next >.

    Clicking Next to navigate to the next screen
    Description of this image
  6. Accept the default, and click Next >.

    Description of this image
  7. Select Application Express Accounts for Authentication Scheme, and click Next >.

    Selecting Application Express Accounts for Authentication Scheme
    Description of this image
  8. Click Create Application.

    Clicking Create Application
    Description of this image
  9. The application is created. Click Shared Components.

    Clicking Shared Components
    Description of this image
  10. Under Data References, click Web Service References.

    Clicking Web Service References.
    Description of this image
  11. Click Create >.

    Clicking Create >
    Description of this image
  12. Select REST for What type of Web reference would you like to create, and click Next >.

    Selecting REST for the type of web reference
    Description of this image
  13. Enter employees for Name. Select GET for HTTP Method and No for Basic Authentication. For the URL, enter the Web Reference URI for the GET Service Handler which was created in the first section of this tutorial.

    As explained in the below table, your URI depends on the location of your Oracle Application Express instance, whether On-Premises or on a Database Cloud Service.

    On-Premiseshttp://localhost:<Port where ords is set up>/ords/<schema name>/<Resource Template Name>/

    For example: http://localhost:9090/ords/hr/employees/
    where ords is set up on port 9090 during Oracle APEX installation, hr is the schema name, employees is the Resource Template name.
    On a Cloud Service<Service URL as received in the Welcome email from Oracle Cloud>/<Resource Template Name>/

    For example: https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/employees/
    where https: databasetrial:<user>.db.us2.oraclecloudapps.com/hr/ is the Service URL for the cloud service subscription and employees is the Resource Template name.
    This RESTful Web Service does not require an HTTP Header parameter. So, scroll down and click the Delete Header icon. 

    Clicking delete header
    Description of this image
  14. Click Next >.

    navigating to the next screen
    Description of this image
  15. There are no parameters defined for the GET Service Handier. So, click the Delete Parameter icon for Input Parameters.

    Clicking the Delete Parameter icon for Input Parameters.
    Description of this image
  16. Click Next >.

    Navigating to hte next screen
    Description of this image
  17. Now, you have to define the REST Outputs. Select Text for Output FormatEnter comma for Parameter Delimiter, and \n for New Record Delimiter. 
    Under Output Parameters, enter Employee ID for Name and 1 for Path. Select String for Type, and click Add Parameter.

    Clicking Add Parameter
    Description of this image
  18. Similarly, add the following Output Parameters, and click Create.

    NamePathType
    Name
    2
    String
    Hire Date
    6
    String
    Job Id
    7
    String
    Adding output parameters
    Description of this image
  19. Click the View Report icon.

    Clicking the View Report icon
    Description of this image
  20. Click the Test icon for employees.

    Clicking Test
    Description of this image
  21. Employees' details is displayed in the Response section. Click Cancel.

    Employee details is displayed
    Description of this image
  22. Click Application<n> in the breadcrumb.

    Clicking Application<n> in the breadcrumb.
    Description of this image
  23. Click Create Page >.

    Clicking Create Page >.
    Description of this image
  24. Click the Form icon.

    Clicking the Form icon
    Description of this image
  25. Click the Report and Form on Web Service icon.

    Clicking the Report and Form on Web Service icon
    Description of this image
  26. Select employees for Web Service Reference, doREST for Operation, and click Next >.

    Note: Employees appears in the select list for Web Service Reference because you added it under Web Service References in Shared Components.

    Selecting employees for Web Service Reference
    Description of this image
  27. Click Next >.

    Navigating to the next screen
    Description of this image
  28. Click Next >.

    Navigating to the next screen
    Description of this image
  29. Select all four parameters, that is, employee IdNameHire Date, and Job Id. Click Next >.

    Selecting  all four parameters and avigating to the next screen
    Description of this image
  30. Accept the default an click Next >.

    Navigating to the next screen
    Description of this image
  31. Click Create.

    Clicking Create
    Description of this image
  32. The new page is created. Click Save and Run Page.

    Clicking Save and Run Page.
    Description of this image
  33. Enter your Oracle Application Express credentials, and click Log In.

    Entering your Oracle Application Express credentials
    Description of this image
  34. Click Submit.

    Cliking Submit
    Description of this image
  35. The Web Service is executed, and the results displayed.

    Results displayed on screen
    Description of this image
 

Consuming the RESTful Web Service Created in Oracle Application Express Using a REST Client

In this section, you consume the Web Service that you have created in Oracle Application Express, using a REST Client.

Note: For the purpose of this tutorial, we will be using the RESTClient add-on in Firefox to demonstrate how to consume the RESTful Web Service created in APEX. You can also use other REST Clients such as, REST Easy and RESTer to perform these steps in Firefox. If you are using Google Chrome, you can install add-ons such as Postman to perform the steps.

  1. Open Firefox and install the RESTClient, a debugger for RESTful web services add-on to your browser.

    Installing add-on
    Description of this image
  2. Open the installed add-on in your browser.

    Opening the installed add-on
    Description of this image
  3. To fetch the details of an employee, select GET as the Request Method. For the URL, enter the Web Reference URI for the GET Service Handler which was created in the first section of this tutorial.

    As explained in the below table, your URI depends on the location of your Oracle Application Express instance, whether On-Premises or on a Database Cloud Service. In this example, https://:<hostname>:<port>/ords/hr/employees/employees is the URL used for the On-Premise machine.

    On-Premiseshttp://localhost:<Port where ords is set up>/ords/<schema name>/<Resource Template Name>/<Resource Handler>

    For example: http://localhost:9090/ords/hr/employees/employees
    where ords is set up on port 9090 during Oracle APEX installation, hr is the schema name, employees is the Resource Template name and employees is the Resource Handler.
    On a Cloud Service<Service URL as received in the Welcome email from Oracle Cloud>/<Resource Template Name>/<Resource Handler>

    For example: https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/employees/
    employeeswhere https: databasetrial:<user>.db.us2.oraclecloudapps.com/hr/ is the Service URL for the cloud service subscription and employees is the Resource Template name and employees is the Resource Handler.

    Click SEND.

    Fetching employee details
    Description of this image
  4. Select the Response Body (Preview) tab to view all the employee records.

    Viewing the employee records
    Description of this image
  5. To insert new values into the employees table, under Headers, select Custom Header.

    Inserting a new record
    Description of this image
  6. Enter Content-Type as Name, application/json as Value, and click OK.

    Entering Request Header values
    Description of this image
  7. In the RESTClient page, enter POST as the request method, enter the same URL as that of the GET method, enter the following code in the Request Body, and click SEND.

    {    
      "first_name":"Supriya",
      "last_name":"Ananth",
      "email":"SUPANANT",
      "hire_date":"13-05-2001",
      "job_id":"AD_PRES"
       }
    entering parameters of POST request
    Description of this image
  8. Under Response, select Responses Header. You see that the new employee's information is added into the Employees table. The newly created employee’s ID is returned back to the application.

    Newly created employee's ID displayed on screen
    Description of this image
 

Summary

In this tutorial, you have learned how to:

  • Create a RESTful Web Service  with various Resource Handlers using Oracle Application Express.
  • Create a RESTful Web Service Reference in Application Express.
  • Consume the Web Service created in Application Express using a REST client.
阅读更多
换一批

没有更多推荐了,返回首页