REST 服务安装过程中的 Oracle 数据库错误：PLS-00201：标识符“ORDS”。必须声明ENABLE_SCHEMA'

Question

安装在CentOS 8上。

已尝试两者

• Oracle Database Express Edition （XE）
• 企业版

从这里 https://www.oracle.com/database/technologies/oracle-database-software-downloads.html

ords.warhttps://www.oracle.com/database/technologies/appdev/rest-data-services-downloads.html

安装进行得很顺利

java -jar ords.war install

https://docs.oracle.com/cd/E56351_01/doc.30/e58123/rest.htm#GUID-4F666968-86CF-44EC-9784-1DFCE88F908F

在我必须执行PL/SQL过程的步骤中，我得到了

[oracle@oracle-ee-8gb ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 11:08:59 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> EXEC ords.enable_schema;
BEGIN ords.enable_schema; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'ORDS.ENABLE_SCHEMA' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

当然，首先我尝试了一个新创建的用户 - 完全是为了SODA目的

CREATE USER JSONDB IDENTIFIED BY MyNewPassword;
GRANT CONNECT, RESOURCE TO JSONDB;
GRANT UNLIMITED TABLESPACE TO JSONDB;
GRANT CREATE SESSION TO JSONDB;

并记录为

sqlplus JSONDB/MyNewPassword@localhost/ORCLCDB

也试图在官方Docker镜像上做同样的事情https://github.com/oracle/docker-images/tree/main/OracleDatabase-相同的问题。

使用docker激活REST时的输出

# docker run --name ords --network dbnet -p 8888:8888 -e ORACLE_HOST=oracledb -e ORACLE_PORT=1521 -e ORACLE_SERVICE=ORCLPDB1 -e ORACLE_PWD=*** -e ORDS_PWD=*** -e CONTEXT_ROOT=ords -v /root/ords-config-data:/opt/oracle/ords/config/ords oracle/restdataservices:21.2.0
Requires to login with administrator privileges to verify Oracle REST Data Services schema.

Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//oracledb:1521/ORCLPDB1

Retrieving information.
Enter a number to select a feature to enable:
   [1] SQL Developer Web  (Enables all features)
   [2] REST Enabled SQL
   [3] Database API
   [4] REST Enabled SQL and Database API
   [5] None
Choose [1]:

2021-07-28T14:17:01.072Z INFO        HTTP and HTTP/2 cleartext listening on host: localhost port: 8888
2021-07-28T14:17:01.173Z INFO        The document root is serving static resources located in: /opt/oracle/ords/doc_root
2021-07-28T14:17:02.540Z INFO        No pools configured yet
2021-07-28T14:17:04.115Z INFO        Oracle REST Data Services initialized
Oracle REST Data Services version : 21.2.0.r1741826
Oracle REST Data Services server info: jetty/9.4.42.v20210604

当然，它给出404，因为我不能让它为模式启用REST

# curl -i -X PUT http://localhost:8888/ords/jsondb/soda/latest/mycoll
HTTP/1.1 404 Not Found
Content-Type: application/problem+json
Content-Length: 336


{
    "code": "NotFound",
    "title": "Not Found",
    "message": "The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured",
    "type": "tag:oracle.com,2020:error/NotFound",
    "instance": "tag:oracle.com,2020:ecid/au4ymHSYbdm27PDCJ9KjsA"
}

Docker设置:

# docker ps
CONTAINER ID   IMAGE                            COMMAND                  CREATED          STATUS                    PORTS                                                                                  NAMES
f26a03a15512   oracle/restdataservices:21.2.0   "/bin/sh -c $ORDS_HO…"   4 minutes ago    Up 4 minutes              0.0.0.0:8888->8888/tcp, :::8888->8888/tcp                                              ords
6928780180b4   oracle-ee:latest                 "/bin/sh -c 'exec $O…"   51 minutes ago   Up 51 minutes (healthy)   0.0.0.0:1521->1521/tcp, :::1521->1521/tcp, 0.0.0.0:5500->5500/tcp, :::5500->5500/tcp   oracledb

当然，我尝试了在本地安装JDK和手动安装，而不仅仅是使用https://github . com/Oracle/docker-images/tree/main/OracleRestDataServices

Answer 1

1. 这是一个很好的例子

# java -jar ords.war install advanced
Specify the database connection type to use.
Enter number for [1] Basic  [2] TNS  [3] Custom URL [1]:1
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:ORCLPDB1
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 to login with administrator privileges to verify Oracle REST Data Services schema.

Enter the administrator username:SYS AS SYSDBA
Enter the database password for SYS AS SYSDBA:
Confirm password:
Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//localhost:1521/ORCLPDB1

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 [SYSAUX]:
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
Enter a number to select a feature to enable:
   [1] SQL Developer Web  (Enables all features)
   [2] REST Enabled SQL
   [3] Database API
   [4] REST Enabled SQL and Database API
   [5] None
Choose [1]:
2021-07-28T20:16:57.913Z INFO        reloaded pools: []
Installing Oracle REST Data Services version 21.2.0.r1741826
... Log file written to /root/ords_install_core_2021-07-28_221658_00061.log
Warning: Nashorn engine is planned to be removed from a future JDK release
... Verified database prerequisites
... Created Oracle REST Data Services proxy user
... Created Oracle REST Data Services schema
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /root/ords_install_datamodel_2021-07-28_221715_00056.log
... Log file written to /root/ords_install_apex_2021-07-28_221716_00329.log
Completed installation for Oracle REST Data Services version 21.2.0.r1741826. Elapsed time: 00:00:19.493

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

这里的< code>advanced是强制性的-您必须选择< code>Service name并设置可插拔数据库(PDB)，而不是选择< code > SID如果是Oracle DB XE，则是< code>XEPDB1，对于企业版- ORCLPDB1

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 20:06:06 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> ALTER SESSION SET CONTAINER=ORCLPDB1;

Session altered.

SQL> CREATE USER testuser1 IDENTIFIED BY MyNewPassword DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO testuser1;

Grant succeeded.

SQL> GRANT CONNECT, RESOURCE TO testuser1;

Grant succeeded.

SQL> GRANT SODA_APP TO testuser1;

Grant succeeded.

SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 20:21:19 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> CONN testuser1/MyNewPassword@ORCLPDB1
Connected.
SQL> EXEC ords.enable_schema;

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.