使用PL/SQL来创建RESTful Web Services

Reference:

https://docs.oracle.com/cd/E56351_01/doc.30/e87809/installing-REST-data-services.htm#AELIG7217
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-create-basic-rest-web-services-using-plsql

Version:

ords.18.1.1.95.1251

jdk1.8.0_171

设置JDK

export JAVA_HOME=/u/dba/oracle/frank/jdk1.8.0_171
export PATH=$JAVA_HOME/bin:$PATH

设置ORDS

bash-4.1$ java -jar ords.war install advanced
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts

Enter the location to store configuration data:/lv01/apache-tomcat-8.5.31/ords/config
Enter the name of the database server [localhost]:<host name>
Enter the database listen port [1521]:152X
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2
Enter the database SID [xe]:<sid>
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Requires SYS AS SYSDBA to verify Oracle REST Data Services schema.

Enter the database password for SYS AS SYSDBA:
Confirm password:

Retrieving information.
Enter the default tablespace for ORDS_METADATA [SYSAUX]:
Enter the temporary tablespace for ORDS_METADATA [TEMP]:
Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Jul 04, 2018 7:07:23 PM  
INFO: Updated configurations: defaults, apex_pu
Installing Oracle REST Data Services version 18.1.1.95.1251
... Log file written to /web01/apache-tomcat-8.5.31/ords/logs/ords_install_core_2018-07-04_190723_00459.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /web01/apache-tomcat-8.5.31/ords/logs/ords_install_datamodel_2018-07-04_190806_00041.log
... Log file written to /web01/apache-tomcat-8.5.31/ords/logs/ords_install_apex_2018-07-04_190807_00872.log
Completed installation for Oracle REST Data Services version 18.1.1.95.1251. Elapsed time: 00:00:45.790

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2

在安装完成以后验证数据库对象

bash-4.1$ java -jar ords.war validate <sid>
Requires SYS AS SYSDBA to verify Oracle REST Data Services schema.

Enter the database password for SYS AS SYSDBA:
Confirm password:

Retrieving information.

Oracle REST Data Services will be validated.
Validating Oracle REST Data Services schema version 18.1.1.95.1251
... Log file written to /web01/apache-tomcat-8.5.31/ords/logs/ords_validate_core_2018-07-04_191324_00217.log
Completed validating Oracle REST Data Services version 18.1.1.95.1251.  Elapsed time: 00:00:12.509

view /web01/apache-tomcat-8.5.31/ords/logs/ords_validate_core_2018-07-04_191324_00217.log
[*** script: ords_validate_objects.sql]

Session altered.

INFO: 19:13:27 Validating objects for Oracle REST Data Services.
VALIDATION: 19:13:27 Starting validation for schema: ORDS_METADATA
VALIDATION: 19:13:27 Validating objects
VALIDATION: 19:13:35 Validating ORDS Public Synonyms
VALIDATION: 19:13:36 Total objects: 244, invalid objects: 0
VALIDATION: 19:13:36     72  INDEX
VALIDATION: 19:13:36      1  JOB
VALIDATION: 19:13:36     11  PACKAGE
VALIDATION: 19:13:36     11  PACKAGE BODY
VALIDATION: 19:13:36     43  PUBLIC SYNONYM
VALIDATION: 19:13:36      1  SEQUENCE
VALIDATION: 19:13:36     27  TABLE
VALIDATION: 19:13:36     26  TRIGGER
VALIDATION: 19:13:36     19  TYPE
VALIDATION: 19:13:36      6  TYPE BODY
VALIDATION: 19:13:36     27  VIEW
VALIDATION: 19:13:36 Validation completed.
INFO: 19:13:36 Completed validating objects for Oracle REST Data Services.


PL/SQL procedure successfully completed.

添加一个数据库map

export JAVA_HOME=/u/dba/oracle/frank/jdk1.8.0_171
export PATH=$JAVA_HOME/bin:$PATH

java -jar ords.war map-url --type base-path /<sid> <sid>
Jul 11, 2018 6:40:33 PM  
INFO: Creating new mapping from: [base-path,/<sid>] to map to: [<sid>,,]

在数据库里建立做一个简单测试

sqlplus testuser1/testuser1@<sid>


CREATE TABLE EMP (
  EMPNO NUMBER(4,0),
  ENAME VARCHAR2(10 BYTE),
  JOB VARCHAR2(9 BYTE),
  MGR NUMBER(4,0),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2,0),
  CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
  );
 
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

#enable schema
CONN testuser1/testuser1@<sid>

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


ase ORDS URL : http://<host>:8080/ords/<host>/
Schema (alias): http://<host>.bv.tek.com:8080/ords/<host>/testuser1/
Module        : http://<host>.bv.tek.com:8080/ords/<host>/testuser1/testmodule1/
Template      : http://<host>.bv.tek.com:8080/ords/<host>/testuser1/testmodule1/emp/





BEGIN
  ORDS.define_module(
    p_module_name    => 'testmodule2',
    p_base_path      => 'testmodule2/',
    p_items_per_page => 0);
 
  ORDS.define_template(
   p_module_name    => 'testmodule2',
   p_pattern        => 'emp/');

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



http://<host>:8080/ords/<host>/testuser1/testmodule2/emp/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31500759/viewspace-2168731/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31500759/viewspace-2168731/