#8 Export ALL REST Definitions

Exporting REST Definitions as bulk is helpful when you want to backup REST Definitions.

Oracle provides very good , simple and lightweight tool  , it's SQLcl (SQL command line) to  interact with DataBase.

Download SQLcl from here

Here are the steps to EXPORT REST:

1. Extract downloaded file  to  D:\Oracle\sqlcl
2. Go to : D:\Oracle\sqlcl\bin
3. Run the sql.exe 
4. Enter user name and password :
• Username? (''?) TREST/trest@ORCL
• now, you must be connected to Oracle Database
5. Type in : SPOOL D:\REST_Export.SQL
6. Type in : REST EXPORT
7. Type in : SPOOL OFF;

Explain:

• Step#5  : locate the file path where to save the output
• Step#6  : Identify  which Module to export , you can choose one module or all modules
• Step#7  : End writing data to output file, and saving data

Full copy of REST definitions are saved in the path :  D:\REST_Export.SQL you 

#7 Your first DELETE RESTful Service

Here's the case we'll work on using the following example:
The case is : How to Delete specific data from EMP_TABLE  using API ?

The Fix: Using DELETE method
Before You Begin: In contrast to GET methods,  all of POST,PUT and DELETE methods   require third party editor to run .
There are many applications for this purpose, you can install any of them using Google Chrome web store  .
YARC may be the simplest one to use , you can download it from here .

1. Using PLSQL copy the following code:

BEGIN
  -----------------
  ORDS.DEFINE_MODULE(P_Module_Name    => 'emp',
                     P_Base_Path      => 'emp_data',
                     P_Items_Per_Page => 0);
  -----------------
  ORDS.DEFINE_TEMPLATE(P_Module_Name => 'emp', 
                       P_Pattern     => 'dml/:id');
  -----------------
  ORDS.DEFINE_HANDLER(P_Module_Name    => 'emp',
                      P_Pattern        => 'dml/:id',
                      P_Method         => 'DELETE',
                      P_Source_Type    => 'plsql/block',
                      P_Source         => 'BEGIN 
                                           DELETE FROM EMP_TABLE
                                                 WHERE Id   = :id;
                                           COMMIT;
                                           :P_Status := SQLERRM;
                                           END;',
          p_mimes_allowed  => '',
          P_Items_Per_Page => 0);
  -----------------
  ORDS.DEFINE_PARAMETER(P_Module_Name        => 'emp',
                        P_Pattern            => 'dml/:id',
                        P_Method             => 'DELETE',
                        p_name               => 'id',
                        p_bind_variable_name => 'id',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'IN');   
  -----------------
  ORDS.DEFINE_PARAMETER(P_Module_Name        => 'emp',
                        P_Pattern            => 'dml/:id',
                        P_Method             => 'DELETE',
                        p_name               => 'P_Status',
                        p_bind_variable_name => 'P_Status',
                        p_source_type        => 'RESPONSE',
                        p_param_type         => 'STRING',
                        p_access_method      => 'OUT');
  -----------------
  COMMIT;
  -----------------
END;

In This POST Request:

• Employee Id = 1  was DELETED from  EMP_TABLE
• P_Status Parameter defined to return message after inserting data

To run POST request :

1. Click YARC extension from google chrome
2. In the URL add: http://localhost:8080/ords/trest/emp_data/dml/1
3. Choose from the list : DELETE

Once you have finished, click Send Request.

you should get reply of  200 and the following message: 

 {
  "P_Status": "ORA-0000: normal, successful completion"
}

In the next post, we'll export all REST Defined Services

#6 Your first PUT RESTful Service

Here's the case we'll work on using the following example:
The case is : How to Update specific data from EMP_TABLE  using API ?

The Fix: Using PUT method
Before You Begin: In contrast to GET methods,  all of POST,PUT and DELETE methods   require third party editor to run .
There are many applications for this purpose, you can install any of them using Google Chrome web store  .
YARC may be the simplest one to use , you can download it from here .

1. Using PLSQL copy the following code:

BEGIN
  -----------------
  ORDS.DEFINE_MODULE(P_Module_Name    => 'emp',
                     P_Base_Path      => 'emp_data',
                     P_Items_Per_Page => 0);
  -----------------
  ORDS.DEFINE_TEMPLATE(P_Module_Name => 'emp', 
                       P_Pattern     => 'dml/:id');
  -----------------
  ORDS.DEFINE_HANDLER(P_Module_Name    => 'emp',
                      P_Pattern        => 'dml/:id',
                      P_Method         => 'PUT',
                      P_Source_Type    => 'plsql/block',
                      P_Source         => 'BEGIN 
                                           UPDATE EMP_TABLE
                                                  SET  Name = :Name
                                                 WHERE Id   = :id;
                                           COMMIT;
                                           :P_Status := SQLERRM;
                                           END;',
                      p_mimes_allowed  => '',
                      P_Items_Per_Page => 0);
  -----------------
  ORDS.DEFINE_PARAMETER(P_Module_Name        => 'emp',
                        P_Pattern            => 'dml/:id',
                        P_Method             => 'PUT',
                        p_name               => 'id',
                        p_bind_variable_name => 'id',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'IN');   
  -----------------
  ORDS.DEFINE_PARAMETER(P_Module_Name        => 'emp',
                        P_Pattern            => 'dml/:id',
                        P_Method             => 'PUT',
                        p_name               => 'P_Status',
                        p_bind_variable_name => 'P_Status',
                        p_source_type        => 'RESPONSE',
                        p_param_type         => 'STRING',
                        p_access_method      => 'OUT');
  -----------------
  COMMIT;
  -----------------
END;

In This POST Request:

• Employee Id = 1  was UPDATED from  EMP_TABLE
• P_Status Parameter defined to return message after inserting data

To run POST request :

1. Click YARC extension from google chrome
2. In the URL add: http://localhost:8080/ords/trest/emp_data/dml/1
3. Choose from the list : PUT
4. In Payload,add the new data: {"Name":"Ismael"}

Once you have finished, click Send Request.

you should get reply of  200 and the following message: 

 {
  "P_Status": "ORA-0000: normal, successful completion"
}

In the next post, we'll create DELETErequest.

#5 Your first POST RESTful Service

Here's the case we'll work on using the following example:
The case is : How to Fetch specific data from EMP_TABLE  using API ?

The Fix: Using GET method
Before You Begin: In contrast to GET methods,  all of POST,PUT and DELETE methods   require third party editor to run .
There are many applications for this purpose, you can install any of them using Google Chrome web store  .
YARC may be the simplest one to use , you can download it from here .

1. Using PLSQL copy the following code:

BEGIN
  -----------------
  ORDS.DEFINE_MODULE(P_Module_Name    => 'emp',
                     P_Base_Path      => 'emp_data',
                     P_Items_Per_Page => 0);
  -----------------
  ORDS.DEFINE_TEMPLATE(P_Module_Name => 'emp', 
                       P_Pattern     => 'dml');
  -----------------
  ORDS.DEFINE_HANDLER(P_Module_Name    => 'emp',
                      P_Pattern        => 'dml',
                      P_Method         => 'POST',
                      P_Source_Type    => 'plsql/block',
                      P_Source         => 'BEGIN 
                                           INSERT INTO EMP_TABLE(Id, Name) 
                                                         VALUES (:Id, :Name); 
                                           COMMIT;
                                           -----------
                                           :P_Status := SQLERRM;
                                           -----------
                                           END;',
          p_mimes_allowed  => '',
          P_Items_Per_Page => 0);
  -----------------
  ORDS.DEFINE_PARAMETER(P_Module_Name        => 'emp',
                        P_Pattern            => 'dml',
                        P_Method             => 'POST',
                        p_name               => 'P_Status',
                        p_bind_variable_name => 'P_Status',
                        p_source_type        => 'RESPONSE',
                        p_param_type         => 'STRING',
                        p_access_method      => 'OUT');   
  COMMIT;
  -----------------
END;

In This POST Request:

• Data INSERTED into EMP_TABLE
• P_Status Parameter defined to return message after inserting data

To run POST request :

1. Click YARC extension from google chrome
2. In the URL add: http://localhost:8080/ords/trest/emp_data/dml
3. Choose from the list : POST
4. In Payload,add the new data: {"Id":1,  "Name":"Omar"}

Once you have finished, click Send Request.

you should get reply of  200 and the following message: 

 {
  "P_Status": "ORA-0000: normal, successful completion"
}

In the next post, we'll create PUT request.

#4 Your first GET RESTful Service

Here's the case we'll work on using the following example:
The case is : How to Fetch specific data from EMP_TABLE  using API ?
The Fix: Using GET method

1. Using PLSQL copy the following code:

BEGIN
  -----------------
  ORDS.DEFINE_MODULE(P_Module_Name    => 'emp',
                     P_Base_Path      => 'emp_data',
                P_Items_Per_Page => 0);
  -----------------
  ORDS.DEFINE_TEMPLATE(P_Module_Name => 'emp', 
                       P_Pattern     => 'dml/:P_Emp_Id');
  -----------------
  ORDS.DEFINE_HANDLER(P_Module_Name    => 'emp',
                      P_Pattern        => 'dml/:P_Emp_Id',
        P_Method         => 'GET',
        P_Source_Type    => Ords.Source_Type_Collection_Feed,
        P_Source         => 'SELECT * FROM EMP_TABLE WHERE Id = :P_Emp_Id',
        P_Items_Per_Page => 0);
  -----------------
  ORDS.DEFINE_PARAMETER(P_Module_Name        => 'emp',
                        P_Pattern            => 'dml/:P_Emp_Id',
                        P_Method             => 'GET',
                        p_name               => 'P_Emp_Id',
                        p_bind_variable_name => 'P_Emp_Id',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'IN');   
  COMMIT;
  -----------------
END;
/

You now have created your first REST API , the URL IS: http://localhost:8080/ords/trest/emp_data/dml/1

To get Employee Number 1 , add 1 at the end of URI

#3 ORDS.DEFINE_SERVICE Format

To create REST service , you do have two choices using SQL developer, PLSQL .

We'll create REST services using PLSQL (ORACLE SQL Developer later).

REST Service has four major methods :

1. GET:  Fetch data  [SELECT * FROM EMP_TABLE]
2. POST: Insert data [INSERT INTO EMP_TABLE (Id, Name) VALUES (1,'Omar')]
3. PUT: Updates data [UPDATE EMP_TABLE SET Name='Samar' WHERE Id=1)]
4. DELETE: Deletes data [DELETE FROM EMP_TABLE WHERE Id=1]

ORDS.DEFINE_SERVICE Format :

ORDS.DEFINE_SERVICE(
   p_module_name        IN ords_modules.name%type, 
   p_base_path          IN ords_modules.uri_prefix%type,
   p_pattern            IN ords_templates.uri_template%type,
   p_method             IN ords_handlers.method%type DEFAULT 'GET',
   p_source_type        IN ords_handlers.source_type%type 
                             DEFAULT ords.source_type_collection_feed,
   p_source             IN ords_handlers.source%type,
   p_items_per_page     IN ords_modules.items_per_page%type DEFAULT 25,
   p_status             IN ords_modules.status%type DEFAULT 'PUBLISHED',
   p_etag_type          IN ords_templates.etag_type%type DEFAULT 'HASH',
   p_etag_query         IN ords_templates.etag_query%type DEFAULT NULL,
   p_mimes_allowed      IN ords_handlers.mimes_allowed%type DEFAULT NULL,
   p_module_comments    IN ords_modules.comments%type DEFAULT NULL,
   p_template_comments  IN ords_modules.comments%type DEFAULT NULL,
   p_handler_comments   IN ords_modules.comments%type DEFAULT NULL);

NOTE: The first seven Parameters (colored in blue) are the most used when creating REST service.

• P_Module_Name: RESTful service name, CASE sensitive and Unique
• P_Base_Path : RESTful api  URI 
• P_Pattern:  RESTful Parameters, SELECT * FROM HR_TABLE WHERE Id=101, To send Parameter using REST API, you send :id (:id is P_Pattern )
• P_Method: REST Method (GET,POST,PUT,DELETE)
• P_Source_Type: Define RESTful service Data Type OUTPUT , how many rows are fetched
• P_Source: Select statement 
• P_Items_Per_Page: How many records are fetched, default is NULL
• P_Status: Define whether REST is PUBLISHED or NOT_PUBLISHED

#2 Enable Oracle REST Data Service (ORDS)

Get ORDS version and make sure  ORDS is Enabled:

Run the following command to idnetify ORDS version if installed :

SELECT 'ORDS is ' || Version "Version of ORDS"
FROM   Ords_Metadata.Ords_Schema_Version;       

Creating URL Mapping to Database Connection

1. Create USER: CREATE USER TREST IDENTIFIED BY trest;
2. Grant DBA:   GRANT DBA TO TREST;
3. Create Table EMP (Id NUMBER, Name VARCHAR2(15)); 
4. Map URL: C:\Program Files\Java\jdk1.8.0_152\bin>java -jar d:\oracle\ords\ords.war map-url --type base-path /api api
5. Enable REST for TREST Schema

ENABLING REST For user TREST:

BEGIN
  ORDS.ENABLE_SCHEMA(
    p_enabled             => TRUE,
    p_schema              => 'TREST',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'api',
    p_auto_rest_auth      => FALSE
  );
    
COMMIT;
END;       

NOW your REST services is ready and  the URL is : http://localhost:8080/ords/trest/