使用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/