=====Oracle ORDS 3.0 REST API nativ verwenden=====

**2017/03**

Ziel: Ein JSON Record soll als Webservice zur Verfügung gestellt werden.

Das ganze kann zwar auch direkt in der APEX Oberfläche über APEX erfolgen, hier soll aber eine saubere Trennung der Umgebung durchgeführt werden, es wird auf die native ORDS Methoden gesetzt.

Ein ORDS wurde bereits für die Verwendung mit APEX konfiguriert und eingerichtet => [[prog:first_steps_oracle_rest_data_service|Oracle ORDS 3.0 (Oracle REST Data Services) mit APEX 5.0]]


Ablauf:
  * URL Mapping auf den DB Connection Pool "apex_pu" (DB User "ORDS_PUBLIC_USER" !) für den REST Aufruf einrichten 
  * Datenbank Schema für REST aktivieren
  * Lesenden Zugriff auf das Schema einrichten
  * PL/SQL Code für den JSON erstellen und aufrufen
  * CRUD Operationen über REST aktivieren


Mit "GET" greifen wir später auf die folgenden URLS zu:

|Base ORDS URL  |<nowiki> http://localhost:9090/ords/restsrv/                  </nowiki>|
|Schema (alias) |<nowiki> http://localhost:9090/ords/restsrv/gpi/              </nowiki>|
|Module         |<nowiki> http://localhost:9090/ords/restsrv/gpi/readEMP/      </nowiki>|
|Template       |<nowiki> http://localhost:9090/ords/restsrv/gpi/readEMP/emp/  </nowiki>|


----

====URL Mapping für den REST Aufruf einrichten====

Um die APEX Funktionalität nicht zu beeinträchtigen und eine saubere Trennung zu den APEX DAD zu realisieren wird ein eigenes URL Mapping hinterlegt


Für den Connect zur DB wird dazu der Pool mit dem User "ORDS_PUBLIC_USER" verwendet , d.h. wir verwenden den Pool "apex_pu"


<code powershell>
C:\oracle\products\ords

java -jar ords.war map-url --type base-path /restsrv apex_pu

Mõr 22, 2017 10:00:01 AM
INFO: Creating new mapping from: [base-path,/restsrv ] to map to: [apex_pu,,]

</code>

Im "ords" Konfigurationsverzeichnis wird die Datei "url-mapping.xml" angelegt


D.h. über die **Base ORDS URL** <nowiki>http://localhost:9090/ords/restsrv/</nowiki> wird nun später diesen REST Service zugegriffen.

----

===Datenbank Schema für REST aktivieren - Schema Alias anlegen===


In der Datenbank wird das DB Schema "GPI" für die Daten des REST Service verwendet, das Schema muss natürlich bereits auch in der Datenbank existieren.

DB User aktiveren bzw. Schema Alias anlegen:
<code sql>

sqlplus gpi/gpi


BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'GPI',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'gpi',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

</code>




----


====Lesenden Zugriff auf das Schema einrichten====

===Modul/Handler/Template in einem Zug definieren===

ÜBer die ORDS.DEFINE_SERVICE Procedure läßt sich der ganze REST Service in einem Zug definieren.

Dazu wird das REST **Module** und das **Template** angelegt:

<code sql>
BEGIN
  ORDS.define_service(
    p_module_name    => 'readEMP',
    p_base_path      => 'readEMP/',
    p_pattern        => 'emp/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_query,
    p_source         => 'SELECT * FROM emp',
    p_items_per_page => 0);

  COMMIT;
END;
/

</code>


Überprüfen mit:
<code sql> 
set linesize 130 pagesize 300 

column source_type   format a15
column source        format a20
column uri_template  format a20
column name          format a20
column uri_prefix    format a20
column method        format a10
column id            format 9999999

ttitle left  "ORDS Modules" skip 2

select id
     , name
	 , uri_prefix
 from  user_ords_modules
order by name
/
 
ttitle left  "ORDS Templates" skip 2  
select id
     , module_id
	 , uri_template
from user_ords_templates
order by module_id
/
 

ttitle left  "ORDS Handlers" skip 2  
select id
     , template_id
	 , source_type
	 , method, source
from   user_ords_handlers
order by id
/

ttitle off

</code>


Nun kann die Tabelle mit **<nowiki>http://localhost:9090/ords/restsrv/gpi/readEMP/emp/</nowiki>** komplett ausgelesen werden.



----

===Modul/Handler/Template im Detail anlegen und konfigurieren ===

Modul anlegen:
<code sql>
BEGIN
  
  ORDS.define_module(
    p_module_name    => 'readDEPT',
    p_base_path      => 'readDEPT/',
    p_items_per_page => 0);
    
  commit;
END;
/    
</code>

Template anlegen um alles auszulesen
<code sql>
begin
  ORDS.define_template(
   p_module_name    => 'readDEPT',
   p_pattern        => 'dept/');
   
  commit;
end;
/      
</code>



Template anlegen um nur ein Department auszulesen
<code sql>
begin
  ORDS.define_template(
   p_module_name    => 'readDEPT',
   p_pattern        => 'dept/deptno/:deptno');
  commit;
end;
/      
</code>

Händler anlegen für das Template "dept/"
<code sql>
begin
  ORDS.define_handler(
    p_module_name    => 'readDEPT',
    p_pattern        => 'dept/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_query,
    p_source         => 'SELECT * FROM dept',
    p_items_per_page => 0);
  commit;
end;
/          
</code>
Aufruf mit <nowiki>http://localhost:9090/ords/restsrv/gpi/readDEPT/dept</nowiki> zeigt nun all Rekords aus der Tabelle


Händler anlegen für das Template "dept/deptno/:deptno/"
<code sql>
begin
  ORDS.define_handler(
    p_module_name    => 'readDEPT',
    p_pattern        => 'dept/deptno/:deptno',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_query,
    p_source         => 'SELECT * FROM dept WHERE deptno = :deptno',
    p_items_per_page => 0);
  commit;
end;
/          
</code>

Aufruf mit <nowiki>http://localhost:9090/ords/restsrv/gpi/readDEPT/dept/deptno/20</nowiki> ruft nur die Daten vom Departement 20 ab.


----

=== PL/SQL Routine aufrufen ===

Soll etwas komplexer abgefragt werden, bietet es sich an den Aufruf über eine PL/SQL Routine zu kapseln.


In diesem Beispiel wird der Übergabe Wert einfach wieder in Großbuchstaben zurückgeben

PL/SQL Beispiel:

<code sql>
create or replace procedure getRestData(p_input_value varchar2)
is
begin
 htp.p(upper(p_input_value));
end;
/
</code>


REST Service:
<code sql>
BEGIN
 -- Module
  ORDS.define_module(
    p_module_name    => 'transVAL',
    p_base_path      => 'transVAL/',
    p_items_per_page => 0);
 
 -- Template
  ORDS.define_template(
   p_module_name    => 'transVAL',
   p_pattern        => 'trans/:value');
   
 -- Händler
 ORDS.define_handler(
    p_module_name    => 'transVAL',
    p_pattern        => 'trans/:value',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN getRestData(:value); END;',
    p_items_per_page => 0);
    
  COMMIT;
  
END;
/
</code>


Aufruf mit <nowiki>http://localhost:9090/ords/restsrv/gpi/transVAL/trans/dies_ist_klein_geschreiben</nowiki>

== apex_json Package verwenden ==

Beispiel PL/SQL für das anlegen eines eigenen JSON Records, gebe das Datum, die Anzahl der Mitarbeiter und den Eingabewert zurück:
<code sql>
create or replace procedure getRestData(p_input_value varchar2 default null)
 as
  l_cursor sys_refcursor;
begin
  
  open l_cursor for
     select sysdate as mydate
           , (select count(*) from emp) as empcount 
           , upper(p_input_value) as input_value from dual;

  apex_json.open_object;
  apex_json.write('emp_count', l_cursor);
  apex_json.close_object;
  
end;
/
</code>

----

==== CRUD - Daten mit REST Aufruf verarbeiten ====


^REST AUFRUF^Aktion^
|POST| Create or Update|
|PUT | Update|
|DELETE| Delete|
|GET | Read|

Neben dem Lesen soll auch Daten in die DB geschrieben werden.


Tabelle:

<code sql>

create table WAGO_LOG_MESSAGES( ID number(11), log_date date, message varchar2(2000));

create sequence wago_log_messages_seq;

</code>


Wrapper um in die Tabelle zu schreiben mit POST und zu löschen mit DELETE REST Aufruf

<code sql>
create or replace procedure logMessage(p_task varchar2, p_date varchar2 , p_message varchar2)
is
begin
  case p_task
    when  'INSERT' then
          insert into wago_log_messages ( id, log_date,message) values
          (wago_log_messages_seq.nextval,to_date(p_date,'dd.mm.yyyy-hh24:mi'), p_message);  
           htp.p('Insert ' ||SQL%ROWCOUNT||' Records');
    when  'DELETE' then
           delete from wago_log_messages where log_date=to_date(p_date,'dd.mm.yyyy-hh24:mi');
            htp.p('Delete ' ||SQL%ROWCOUNT||' Records');
    when  'UPDATE' then  
            update wago_log_messages set message=p_message where log_date=to_date(p_date,'dd.mm.yyyy-hh24:mi');
            htp.p('Update ' ||SQL%ROWCOUNT||' Records');
    else
          htp.p('Parameter p_task ::' ||p_task||' no action defined');
 
    end case      
    commit;
EXCEPTION
  WHEN OTHERS THEN
    HTP.print(SQLERRM);     
    rollback; 
end logMessage;
/ 
</code>

===POST Handler für INSERT===


Anlegen mit:
<code sql>
BEGIN
 -- Module
  ORDS.define_module(
    p_module_name    => 'logMESS',
    p_base_path      => 'logMESS/',
    p_items_per_page => 0);
 
 -- Template
  ORDS.define_template(
   p_module_name    => 'logMESS',
   p_pattern        => 'log/');
 
 -- Händler
 ORDS.define_handler(
    p_module_name    => 'logMESS',
    p_pattern        => 'log/',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN logMessage(p_task => ''INSERT'', p_date => :log_date , p_message => :message); END;',
    p_items_per_page => 0);
     
  COMMIT;
 
END;
/
</code>

<code>
URL:            http://localhost:9090/ords/restsrv/gpi/logMESS/log
Content-Type:  application/json
Daten:         { "log_date":"22.03.2017-12:22" , "message":"Sensor 2 - READ ERROR" }
</code>

===PUT Handler für UPDATE===


<code sql>
BEGIN
 -- Template
  ORDS.define_template(
   p_module_name    => 'logMESS',
   p_pattern        => 'log/update');
 -- Händler
 ORDS.define_handler(
    p_module_name    => 'logMESS',
    p_pattern        => 'log/update',
    p_method         => 'PUT',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN logMessage(p_task => ''UPDATE'', p_date => :log_date , p_message => :message); END;',
    p_items_per_page => 0);
 
  COMMIT;
 
END;
/
</code>

<code>
URL:            http://localhost:9090/ords/restsrv/gpi/logMESS/log/update
Content-Type:  application/json
Daten:         { "log_date":"22.03.2017-12:22" , "message":"Sensor 3 - READ SUCESS" }
</code>

===DELETE Handler für Delete===


<code sql>
BEGIN
 -- Template
  ORDS.define_template(
   p_module_name    => 'logMESS',
   p_pattern        => 'log/delete');
 
 -- Händler
 ORDS.define_handler(
    p_module_name    => 'logMESS',
    p_pattern        => 'log/delete',
    p_method         => 'DELETE',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN logMessage(p_task => ''DELETE'', p_date => :log_date , p_message => :message); END;',
    p_items_per_page => 0);
     
  COMMIT;
 
END;
/

</code>

<code>
URL:            http://localhost:9090/ords/restsrv/gpi/logMESS/log/delete
Content-Type:  application/json
Daten:         { "log_date":"22.03.2017-12:22" , "message":"" }
</code>

----


==== Quellen ====


Doku:
  * Implicit Parameters => https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/18.3/aelig/implicit-parameters.html


Oracle

  * Developing Oracle REST Data Services Applications => http://docs.oracle.com/cd/E56351_01/doc.30/e56293/developing-REST-applications.htm#AELIG3000


Web

  * https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-create-basic-rest-web-services-using-plsql
  * http://json-rest-oracledb.blogspot.de/2016/01/ords-und-3-legged-oauth-so-gehts.html