Create Basic RESTful Web Services Using PL/SQL

-
Use SQLcl to Display ORDS Web Service Definitions

useful link
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-create-basic-rest-web-services-using-plsql

https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-use-sqlcl-to-display-ords-web-service-definitions

=============================================================================================================

Basic Information (schemas, modules, privileges)

('Module --> Template --> Handler')
=============================================================================================================

=> Schema

CONN sys/OraPassword1@pdb1 AS SYSDBA
Connected.
SQL> REST schemas
PARSING_SCHEMA PATTERN STATUS
-------------- ------- -------
TESTUSER1      hr      ENABLED

=> Module

SQL> CONN testuser1/testuser1@pdb1
Connected.
SQL> REST modules
NAME    PREFIX    STATUS    ITEMS_PER_PAGE
------- --------- --------- --------------
rest-v1 /rest-v1/ PUBLISHED 0
rest-v2 /rest-v2/ PUBLISHED 0

=> privileges

SQL> CONN testuser1/testuser1@pdb1
Connected.
SQL> REST privileges
NAME                            LABEL DESCRIPTION COMMENTS
------------------------------- ----- ----------- --------
oracle.soda.privilege.developer


=============================================================================================================
A) Quick Build
=============================================================================================================

1) Enable Schema

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'BZLIVE',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'hr',
    p_auto_rest_auth      => FALSE
  );

    COMMIT;
END;

2) Create RESTful Web Services:- The DEFINE_SERVICE procedure allows you to create a new module,
   template and handler in a single step. If the module already exists, it replaced by the new definition.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_service(
    p_module_name    => 'rest-v1',
    p_base_path      => 'rest-v1/',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp',
    p_items_per_page => 0);

  ORDS.define_service(
    p_module_name    => 'rest-v2',
    p_base_path      => 'rest-v2/',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp',
    p_items_per_page => 0);

  COMMIT;
END;

3)  View from SQL

=> Module

COLUMN name FORMAT A20
COLUMN uri_prefix FORMAT A20


SELECT id, name, uri_prefix FROM   user_ords_modules ORDER BY name;

=> Templates

COLUMN uri_template FORMAT A20

SELECT id, module_id, uri_template FROM   user_ords_templates ORDER BY module_id;

=> Handler

SELECT id, template_id, source_type, method, source FROM   user_ords_handlers ORDER BY id;

=============================================================================================================

B) Manual Build

Rather than using the DEFINE_SERVICE procedure, we can build the same web service manually using
the DEFINE_MODULE, DEFINE_TEMPLATE and DEFINE_HANDLER procedures.
The following code creates a similar web service to that defined previously,
but this time defining all the pieces manually.
=============================================================================================================

1)

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v2',
    p_base_path      => 'rest-v2/',
    p_items_per_page => 0);
 
  ORDS.define_template(
   p_module_name    => 'rest-v2',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v2',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp',
    p_items_per_page => 0);
   
  COMMIT;
END;
/

The web service is available using the following URL

http://localhost:8080/ords/hr/rest-v2/employees/

2) Multiple Templates

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v3',
    p_base_path      => 'rest-v3/',
    p_items_per_page => 0);
 
  ORDS.define_template(
   p_module_name    => 'rest-v3',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v3',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp',
    p_items_per_page => 0);
   
  ORDS.define_template(
   p_module_name    => 'rest-v3',
   p_pattern        => 'employees/:empno');

  ORDS.define_handler(
    p_module_name    => 'rest-v3',
    p_pattern        => 'employees/:empno',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp WHERE empno = :empno',
    p_items_per_page => 0);
   
  COMMIT;
END;


The web service is available using the following URLs.

http://localhost:8080/ords/hr/rest-v3/employees/
http://localhost:8080/ords/hr/rest-v3/employees/7499


3) Multiple Parameters


CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v3b',
    p_base_path      => 'rest-v3b/',
    p_items_per_page => 0);

  ORDS.define_template(
   p_module_name    => 'rest-v3b',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v3b',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp WHERE empno BETWEEN :empno_start AND :empno_end',
    p_items_per_page => 0);
  COMMIT;
END;
/

http://localhost:8080/ords/hr/rest-v3b/employees/?empno_start=7876&empno_end=7934

=============================================================================================================

C) The following code recreates the previous web service to return a result set,
 but the parameters are specified in the template, so they are mandatory and the position is fixed.

=============================================================================================================

 CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v3b',
    p_base_path      => 'rest-v3b/',
    p_items_per_page => 0);

  ORDS.define_template(
   p_module_name    => 'rest-v3b',
   p_pattern        => 'employees/:empno_start/:empno_end');

  ORDS.define_handler(
    p_module_name    => 'rest-v3b',
    p_pattern        => 'employees/:empno_start/:empno_end',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp WHERE empno BETWEEN :empno_start AND :empno_end',
    p_items_per_page => 0);
  COMMIT;
END;
/

http://localhost:8080/ords/hr/rest-v3b/employees/7876/7934
=============================================================================================================

D) Stored Procedure (JSON)

=============================================================================================================

create the following procedure, which uses the APEX_JSON package to generate the JSON results.

CONN / AS SYSDBA

ALTER SESSION SET CONTAINER=pdb1;

GRANT CREATE PROCEDURE TO testuser1;

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE get_emp_json (p_empno IN emp.empno%TYPE DEFAULT NULL) AS
  l_cursor SYS_REFCURSOR;
BEGIN
 
  OPEN l_cursor FOR
    SELECT e.empno AS "empno",
           e.ename AS "employee_name",
           e.job AS "job",
           e.mgr AS "mgr",
           TO_CHAR(e.hiredate,'YYYY-MM-DD') AS "hiredate",
           e.sal AS "sal",
           e.comm  AS "comm",
           e.deptno AS "deptno"
    FROM   emp e
    WHERE  e.empno = DECODE(p_empno, NULL, e.empno, p_empno);

  APEX_JSON.open_object;
  APEX_JSON.write('employees', l_cursor);
  APEX_JSON.close_object;
END;
/


The following code creates a web service which calls this procedure. Notice the source type of SOURCE_TYPE_PLSQL
and a PL/SQL block as the source.


CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v4',
    p_base_path      => 'rest-v4/',
    p_items_per_page => 0);
 
  ORDS.define_template(
   p_module_name    => 'rest-v4',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v4',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN get_emp_json; END;',
    p_items_per_page => 0);
   
  ORDS.define_template(
   p_module_name    => 'rest-v4',
   p_pattern        => 'employees/:empno');

  ORDS.define_handler(
    p_module_name    => 'rest-v4',
    p_pattern        => 'employees/:empno',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN get_emp_json(:empno); END;',
    p_items_per_page => 0);
   
  COMMIT;
END;
/
=============================================================================================================
E) Stored Procedure (XML)
Using a stored procedure is a simple way to produce XML REST web services. To show this, create the following
procedure, which uses SQL/XML to generate the XML results.
=============================================================================================================



ONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE get_emp_xml (p_empno IN emp.empno%TYPE DEFAULT NULL) AS
  l_clob CLOB;
BEGIN

  SELECT XMLELEMENT("employees",
           XMLAGG(
             XMLELEMENT("emp",
               XMLFOREST(e.empno AS "empno",
                         e.ename AS "employee_name",
                         e.job AS "job",
                         e.mgr AS "mgr",
                         TO_CHAR(e.hiredate,'YYYY-MM-DD') AS "hiredate",
                         e.sal AS "sal",
                         e.comm  AS "comm",
                         e.deptno AS "deptno"
               )
             )
           )
         ).getClobVal()
  INTO   l_clob
  FROM   emp e
  WHERE  e.empno = DECODE(p_empno, NULL, e.empno, p_empno);

  OWA_UTIL.mime_header('text/xml'); 
  HTP.print(l_clob);

END;
/


The following code creates a web service that calls this procedure.


CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v5',
    p_base_path      => 'rest-v5/',
    p_items_per_page => 0);
 
  ORDS.define_template(
   p_module_name    => 'rest-v5',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v5',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN get_emp_xml; END;',
    p_items_per_page => 0);
   
  ORDS.define_template(
   p_module_name    => 'rest-v5',
   p_pattern        => 'employees/:empno');

  ORDS.define_handler(
    p_module_name    => 'rest-v5',
    p_pattern        => 'employees/:empno',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN get_emp_xml(:empno); END;',
    p_items_per_page => 0);
   
  COMMIT;
END;
/


http://localhost:8080/ords/hr/rest-v5/employees/
http://localhost:8080/ords/hr/rest-v5/employees/7499

=============================================================================================================

Create POST Web Services (Create/INSERT)

=============================================================================================================


The POST method is typically used for a create operation, like inserting some data. Remember, from a database
perspective, a create operation, like creating an employee,
may involve multiple operations, not just inserts. The POST method expects the parameter values to be passed
as a payload. I used the "Advanced REST client" extension for Chrome to send the requests here.

The source of the POST handler can be a regular PL/SQL block containing an insert statement, but it makes sense
to enclose this in a stored procedure or packaged procedure. Create a stored procedure to insert rows.



CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE create_employee (
  p_empno     IN  emp.empno%TYPE,
  p_ename     IN  emp.ename%TYPE,
  p_job       IN  emp.job%TYPE,
  p_mgr       IN  emp.mgr%TYPE,
  p_hiredate  IN  VARCHAR2,
  p_sal       IN  emp.sal%TYPE,
  p_comm      IN  emp.comm%TYPE,
  p_deptno    IN  emp.deptno%TYPE
)
AS
BEGIN
  INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  VALUES (p_empno, p_ename, p_job, p_mgr, TO_DATE(p_hiredate, 'YYYY-MM-DD'), p_sal, p_comm, p_deptno);
EXCEPTION
  WHEN OTHERS THEN
    HTP.print(SQLERRM);
END;
/


CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v6',
    p_base_path      => 'rest-v6/',
    p_items_per_page => 0);
 
  ORDS.define_template(
   p_module_name    => 'rest-v6',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v6',
    p_pattern        => 'employees/',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           create_employee(p_empno    => :empno,
                                           p_ename    => :ename,
                                           p_job      => :job,
                                           p_mgr      => :mgr,
                                           p_hiredate => :hiredate,
                                           p_sal      => :sal,
                                           p_comm     => :comm,
                                           p_deptno   => :deptno);
                         END;',
    p_items_per_page => 0);

  COMMIT;
END;
/
=============================================================================================================
Create PUT Web Services (Amend/UPDATE)

The PUT method is typically used to amend data and expects the parameter values to be passed in the payload.
The PUT operation is often expected to perform a create if the data doesn't already exist, but I personally don't
like this approach.
=============================================================================================================

The source of the PUT handler can be as simple as a regular PL/SQL block containing an update statement,
but it makes sense to enclose this in a stored procedure or packaged procedure. Create a stored procedure to
update rows.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE amend_employee (
  p_empno     IN  emp.empno%TYPE,
  p_ename     IN  emp.ename%TYPE,
  p_job       IN  emp.job%TYPE,
  p_mgr       IN  emp.mgr%TYPE,
  p_hiredate  IN  VARCHAR2,
  p_sal       IN  emp.sal%TYPE,
  p_comm      IN  emp.comm%TYPE,
  p_deptno    IN  emp.deptno%TYPE
)
AS
BEGIN
  UPDATE emp
  SET ename    = p_ename,
      job      = p_job,
      mgr      = p_mgr,
      hiredate = TO_DATE(p_hiredate, 'YYYY-MM-DD'),
      sal      = p_sal,
      comm     = p_comm,
      deptno   = p_deptno
  WHERE empno  = p_empno;
EXCEPTION
  WHEN OTHERS THEN
    HTP.print(SQLERRM);
END;
/

The following code creates a web service with a PUT handler that calls the stored procedure,
passing the parameters from the payload.


CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v7',
    p_base_path      => 'rest-v7/',
    p_items_per_page => 0);
 
  ORDS.define_template(
   p_module_name    => 'rest-v7',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v7',
    p_pattern        => 'employees/',
    p_method         => 'PUT',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           amend_employee(p_empno    => :empno,
                                          p_ename    => :ename,
                                          p_job      => :job,
                                          p_mgr      => :mgr,
                                          p_hiredate => :hiredate,
                                          p_sal      => :sal,
                                          p_comm     => :comm,
                                          p_deptno   => :deptno);
                         END;',
    p_items_per_page => 0);

  COMMIT;
END;
/

=============================================================================================================

Create DELETE Web Services (Remove/DELETE)

=============================================================================================================
Like the POST and PUT methods, the source of the DELETE handler can be a as simple as a PL/SQL block containing a
delete statement, but it makes sense to enclose this in a stored procedure or packaged procedure.
Create a stored procedure to delete rows.


CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE remove_employee (
  p_empno  IN  emp.empno%TYPE
)
AS
BEGIN
  DELETE FROM emp WHERE empno = p_empno;
EXCEPTION
  WHEN OTHERS THEN
    HTP.print(SQLERRM);
END;
/

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v8',
    p_base_path      => 'rest-v8/',
    p_items_per_page => 0);
 
  ORDS.define_template(
   p_module_name    => 'rest-v8',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v8',
    p_pattern        => 'employees/',
    p_method         => 'DELETE',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           remove_employee(p_empno => :empno);
                         END;',
    p_items_per_page => 0);

  COMMIT;
END;
/


=============================================================================================================

Insert , Update , Delete , Read  All In One Example

=============================================================================================================



CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v9',
    p_base_path      => 'rest-v9/',
    p_items_per_page => 0);
 
  ORDS.define_template(
   p_module_name    => 'rest-v9',
   p_pattern        => 'employees/');

  -- READ : All records.
  ORDS.define_handler(
    p_module_name    => 'rest-v9',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp',
    p_items_per_page => 0);

  -- INSERT
  ORDS.define_handler(
    p_module_name    => 'rest-v9',
    p_pattern        => 'employees/',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           create_employee (p_empno    => :empno,
                                            p_ename    => :ename,
                                            p_job      => :job,
                                            p_mgr      => :mgr,
                                            p_hiredate => :hiredate,
                                            p_sal      => :sal,
                                            p_comm     => :comm,
                                            p_deptno   => :deptno);
                         END;',
    p_items_per_page => 0);

  -- UPDATE
  ORDS.define_handler(
    p_module_name    => 'rest-v9',
    p_pattern        => 'employees/',
    p_method         => 'PUT',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           amend_employee(p_empno    => :empno,
                                          p_ename    => :ename,
                                          p_job      => :job,
                                          p_mgr      => :mgr,
                                          p_hiredate => :hiredate,
                                          p_sal      => :sal,
                                          p_comm     => :comm,
                                          p_deptno   => :deptno);
                         END;',
    p_items_per_page => 0);

  -- DELETE
  ORDS.define_handler(
    p_module_name    => 'rest-v9',
    p_pattern        => 'employees/',
    p_method         => 'DELETE',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           remove_employee(p_empno => :empno);
                         END;',
    p_items_per_page => 0);

  -- READ : One Record
  ORDS.define_template(
   p_module_name    => 'rest-v9',
   p_pattern        => 'employees/:empno');

  ORDS.define_handler(
    p_module_name    => 'rest-v9',
    p_pattern        => 'employees/:empno',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp WHERE empno = :empno',
    p_items_per_page => 0);

  COMMIT;
END;
/
=============================================================================================================
Web Service Definitions (export)

=============================================================================================================

SQL> REST export

SQL> REST export rest-v1

SQL> REST export /rest-v2/

Comments

Post a Comment

Popular posts from this blog

Telegram Integration with Oracle

-
 How to Integrate Telegram with Oracle Apex 1) create Telegram Account in your Mobile like whatsapp 2) Ones you register in telegram apps serach for BotFather 3) To Create New Bot u need to enter below command  /newbot  Alright, a new bot. How are we going to call it? Please choose a name for your bot. Macsbot Good. Now let's choose a username for your bot. It must end in `bot`. Like this, for example: TetrisBot or tetris_bot. MacsOmanBot Done! Congratulations on your new bot. You will find it at t.me/MacsOmanBot. You can now add a description, about section and profile picture for your bot, see /help for a list of commands. By the way, when you've finished creating your cool bot, ping our Bot Support if you want a better username for it. Just make sure the bot is fully operational before you do this. Use this token to access the HTTP API:<Here you will prompt with your api> 4) /mybots (this command will return the list of bots which you have created) ...
Read more

The username or password for the connection pool named |apex||, are invalid, expired, or the account is locked

-
  1. Execute the following command from SQL Plus as the SYS user:  Conn sys/Pass@xe as sysdba  (connect as container if 12c then  ALTER SESSION SET container = CDB$ROOT;) Alter user ords_public_user identified by xxxx; ALTER USER ORDS_PUBLIC_USER ACCOUNT UNLOCK; Connect as PDB Conn sys/Pass@xepdb1 as sysdba (if 12c  ALTER SESSION SET container = PDB1;) Alter user APEX_PUBLIC_USER identified by xxxx; Alter user APEX_REST_PUBLIC_USER identified by xxxx; Alter user APEX_LISTENER identified by xxxx; 2. Unlock the users: ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK; ALTER USER APEX_REST_PUBLIC_USER ACCOUNT UNLOCK; ALTER USER APEX_LISTENER ACCOUNT UNLOCK; ALTER USER ORDS_PUBLIC_USER ACCOUNT UNLOCK; 3. Now create a profile for the APEX related users: Create profile apex_public_profile limit password_life_time unlimited; 4. Assign the profile to the APEX users: alter user apex_public_user profile apex_public_profile; alter user apex_rest_public_user profile apex_public_pr...
Read more

Enabled SQL Developer Web (SDW) & ORDS Database API ORDS 19.4

-
SQL Developer Web (SDW)  ORDS 19.2 to 19.4 Migration /  Basic Setup to Enable ORDS Database API Bsic Information  Installing Oracle REST Data Services https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.4/aelig/installing-REST-data-services.html#GUID-673D35DD-9811-42F8-A500-4F494F79C470 https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.4/aelig/installing-REST-data-services.html#GUID-2C42D982-BF08-48E3-A4C6-3FE1A78287E3 Using Oracle® SQL Developer Web https://docs.oracle.com/en/database/oracle/sql-developer-web/19.2.1/sdweb/about-sdw.html#GUID-AF7601F9-7713-4ECC-8EC9-FB0296002C69 How to Configure / Run ORDS for SQL Developer Web and APEX in Standalone Mode: ORDS 19.4 + (Doc ID 2628004.1) GOAL This document is an overview on how to configure and run ORDS standalone for SQL Developer Web using the command line interface. It also includes the steps related to setting up APEX, since the ability to run APEX as well as SQL Develo...
Read more