Oracle Database error during REST services installation: PLS-00201: identifier 'ORDS.ENABLE_SCHEMA' must be declared

Question

Installed on CentOS 8.

Tried both

• Oracle Database Express Edition (XE)
• Enterprise Edition

from here https://www.oracle.com/database/technologies/oracle-database-software-downloads.html

ords.war taken from https://www.oracle.com/database/technologies/appdev/rest-data-services-downloads.html

Installation went well

java -jar ords.war install

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

On the step where I must execute PL/SQL procedure I am getting

[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

Of course, first I tried with a newly created user - exactly for SODA purposes

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

and logged as

sqlplus JSONDB/MyNewPassword@localhost/ORCLCDB

Also tried to do the same on official Docker images https://github.com/oracle/docker-images/tree/main/OracleDatabase - same issue.

Output when I activate REST with docker

# 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

Of course, it gives 404 because I can't make it to enable REST for the schema

# 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 setup:

# 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

Of course, I tried to install JDK locally and installing manually, not only using https://github.com/oracle/docker-images/tree/main/OracleRestDataServices

There's no option to even activate REST using SQL Developer

Answer 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

advanced here is mandatory - then instead of selecting SID you must choose Service name and set pluggable database (PDB) - in case of Oracle DB XE it's XEPDB1, for Enterprise Edition - ORCLPDB1

2. that's how user should be created

$ 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

3. that's how rest should be enabled for the schema

$ 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.

alternatively, you can setup SQL Developer in the way of setting Service name instead of SID - Rest Services menu item will become available.