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 thesql Workshoptab and selectRESTful Services.

   @H_502_91@

2. From the RESTful Services page,select theCreate a New RESTful Serviceoption.

   @H_502_91@

3. A page loads with entries grouped under three different categories named RESTful Services Module,Resource Template,and Resource Handler. Under RESTful Services Module,enteremployeesfor Name,and scroll down further.

   @H_502_91@

4. Under Resource Template,enteremployees/for URI Template to identify your Uniform Resource Identifier (URI),and scroll down further.

   @H_502_91@

5. Under Resource Handler,selectGETfor Method,Queryfor Source Type,CSVfor Format. This identifies the HTTP method to be used for the Resource Handler.
   Enter the following sql query for Source,and clickCreate Module.
   
   select * from employees

   @H_502_91@

6. The GET Handler is created under employees/. To edit its properties,clickGETunder employees/.

   @H_502_91@

7. SelectNofor Requires Secure Access,and clickApply Changes.

   @H_502_91@

8. To test the behavior of the RESTful Service Handler,clickTest.
   
   Note:If your screen does not show a Test button,please ensure that RESTful Services are configured in your Oracle Application Express installation properly.

   @H_502_91@

9. You are prompted to save the file which you can then view using a CSV editor.

   @H_502_91@

10. The CSV format result set is displayed.

    @H_502_91@

11. Let us now create a Handler for the POST method in the same Web Service. ClickCreate Handlerunder employees/.

    @H_502_91@

12. SelectPOSTfor Method andPL/sqlfor Source Type. Enterapplication/jsonfor MIME Types Allowed. SelectNofor Requires Secure Access. Enter the following PL/sql code for Source,and clickCreate.
    
    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;
    

    @H_502_91@

13. Scroll down the page,and clickCreate Parameterto add an OUT parameter to the handler that will return the newly created employee’s ID.

    @H_502_91@

14. Enteremployee_idfor Name and Bind Variable Name. SelectOUTfor Access Method,HTTP Headerfor Source Type,Stringfor Parameter Type,and clickCreate.

    @H_502_91@

15. The OUT parameter is created.

    @H_502_91@

16. ClickCreate Parameteragain to add the following IN parameters to the handler.

    @H_499_502@

    Name	Bind Variable Name	Access Method	Parameter Type
    first_name	first_name	IN	String
    email	email	IN	String
    last_name	last_name	IN	String
    hire_date	hire_date	IN	String
    job_id	job_id	IN	String

    @H_502_91@

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

1. ClickCreate Template.

2. Enteremployees/{id}for URI Template,and clickCreate.

   @H_502_91@

3. ClickCreate Handlerunder employees/{id}.

   @H_502_91@

4. SelectGETfor Method,Query One Rowfor Source Type,andNofor Requires Secure Access.
   Enter the following sql Query for Source,and clickCreate.
   
   select * from employees where employee_id = :id

   @H_502_91@

5. Scroll down and clickCreate Parameter.

   @H_502_91@

6. Enteridfor Name and Bind Variable Name. SelectINfor Access Method,and clickCreate.

   @H_502_91@

7. You want to change the Source Type. Under Parameters,click theidlink under Name.

   @H_502_91@

8. SelectURIfor Source Type,and clickApply Changes.

   @H_502_91@

9. Before testing this handler,you have to set a bind variable to pass a value for the input parameter(id). ClickSet Bind Variables >.

   @H_502_91@

10. Enter103for :ID,and clickTest.

    @H_502_91@

11. Complete details of the employee with employee_id = 103 is displayed.

    @H_502_91@

1. ClickCreate Template.

2. EnteremployeesFeed/for URI Template,and clickCreate.

   @H_502_91@

3. Under employeesFeed/,clickCreate Handler.

   @H_502_91@

4. SelectGETfor Method,Feedfor Source Type,andNofor Requires Secure Access. Under Source,enter the following sql query,and clickCreate.
   
   select employee_id,first_name
from employees
order by employee_id,first_name

   @H_502_91@

5. UnderemployeesFeed/,clickGETto open the Resource Handler Editor.

   @H_502_91@

6. 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 withemployee_id = 100ishttps://:<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:

   @H_499_502@

   On-Premises	http://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 port9090during Oracle APEX installation,hris the schema name,employeesFeedis the Resource Template name,and100is 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<user>.db.us2.oraclecloudapps.com/hr/is the Service URL for the cloud service subscription,and100is the employee_id.

   @H_502_91@

7. Select the URI for one of the employee_ids,and copy it to clipboard.

   @H_502_91@

8. 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.

   @H_502_91@

1. ClickCreate Template.

2. EnteremployeesFeed/{id},and clickCreate.

   @H_502_91@

3. Under employeesFeed/{id},clickCreate Handler.

   @H_502_91@

4. SelectGetfor Method,serif; padding: 3px; background-color: transparent; border-radius: 4px; line-height: 1.615; font-style: italic;">select employee_id,first_name
   from employees
   where department_id = :id

   @H_502_91@

5. Scroll down further,and clickCreate Parameter >to add an IN parameter to the handler that will receive the department_id.

   @H_502_91@

6. SelectURIfor Source Type,and clickApply Changes.

   @H_502_91@

7. Before testing this handler,you have to set bind variable to pass a value for the input parameter,id. ClickSet Bind Variables >.

   @H_502_91@

8. Enter60for :ID,and clickTest.

   @H_502_91@

9. 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_idis the first column and will turn into a hyperlink.
   
   For example,the URI for an employee with department_id = 60 ishttp://<hostname>:<port>/ords/hr/employeesFeed/60?page=1where 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,the URI formats are as follows:

   @H_499_502@

   On-Premises

   http://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 port9090during Oracle APEX installation,serif; padding: 3px; background-color: transparent; border-radius: 4px; line-height: 1.615;">hris the schema name,serif; padding: 3px; background-color: transparent; border-radius: 4px; line-height: 1.615;">employeesFeedis the Resource Template name and60is 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<user>.db.us2.oraclecloudapps.com/hr/is the Service URL for the cloud service subscription,serif; padding: 3px; background-color: transparent; border-radius: 4px; line-height: 1.615;">employeesFeedis the Resource Template name,and60is the department_id.

   @H_502_91@