Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : SQL Developer Web

SQL Developer Web allows Oracle REST Data Services (ORDS) to present a web-based version of SQL Developer and SQL Developer Data Modeler. This can be a convenient alternative to opening firewall access to database servers.

• Assumptions
• Create a Test Database User
• Enable ORDS
• Enable SQL Developer Web (ORDS Version 22.1 Onward)
• Enable SQL Developer Web (ORDS Versions 19.4 to 21.4)
• Using SQL Developer Web
• DBA Access

Related articles.

• Oracle REST Data Services (ORDS) : SQL Developer Web
• Oracle REST Data Services (ORDS) : REST Enabled SQL
• Oracle REST Data Services (ORDS) : All Articles

Assumptions

This article assumes the following.

• You already have a functioning installation of ORDS 19.4 or higher, using an application server or standalone mode. The SQL Developer Web functionality was introduced in ORDS version 19.3 on the cloud and 19.4 for on-prem installations.
• The paths for the ORDS configuration match those from the ORDS installation article listed above.
• You are using HTTPS in a browser to connect to ORDS. SQL Developer Web will only work with HTTP if you enable HTTP access to REST Enabled SQL, which is a bad idea, as you will be transferring passwords across the network. If you want to do it, it is explained here, but please don't!

Create a Test Database User

We need a new database user for our testing.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

DROP USER testuser1 CASCADE;
CREATE USER testuser1 IDENTIFIED BY testuser1
  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
  
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO testuser1;

We connect to the test user and create some test tables.

CONN testuser1/testuser1@pdb1

-- DROP TABLE EMP PURGE;
-- DROP TABLE DEPT PURGE;

CREATE TABLE DEPT (
  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13)
) ;

CREATE TABLE EMP (
  EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

Enable ORDS

Enable ORDS for the test schema itself. We could use any unique and legal URL mapping pattern for the schema, so it is not necessary to expose the schema name. In this example we've used a schema alias of "hr".

CONN testuser1/testuser1@pdb1

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

Normal web services from the schema can now be referenced using the following base URLs. We can only use the HTTPS URL by default for SQL Developer Web.

http://localhost:8080/ords/hr/
https://localhost:8443/ords/hr/

We are now ready to start.

Enable SQL Developer Web (ORDS Version 22.1 Onward)

So far we have enabled the conventional functionality available in previous versions. To enable the SQL Developer Web functionality we must set the feature.sdw attribute. This is the on/off switch for this functionality. As mentioned previously, we also need to enable REST Enabled SQL using the restEnabledSql.active atrribute.

export ORDS_HOME=/u01/ords
export ORDS_CONFIG=/u01/config/ords
export PATH=${ORDS_HOME}/bin:${PATH}

ords --config ${ORDS_CONFIG} config set restEnabledSql.active true
ords --config ${ORDS_CONFIG} config set feature.sdw true

We will need to restart ORDS for this to take effect. The method will vary depending on if you are running ORDS under Tomcat, WebLogic or in standalone mode.

With the above configuration in place, we can access SQL Developer Web and connect to the TESTUSER1 user with the following URL.

https://localhost:8443/ords/hr/_sdw

There is also a top-level URL that guides you through the login URL.

https://localhost:8443/ords/sql-developer

It assumes your base path and username match, which is unlikely to be the case for a schema owner or API user, so I'm going to avoid it in this example, but if you are setting up a regular user for a developer, having a matching schema name and base path are not a problem.

Enable SQL Developer Web (ORDS Versions 19.4 to 21.4)

So far we have enabled the conventional functionality available in previous versions. To enable the SQL Developer Web functionality we must amend the "defaults.xml" file, which in this case is located in the "/u01/ords/conf/ords" directory. The following entries are on/off switches for REST Enabled SQL and SQL Developer Web. Both must be enabled.

<entry key="restEnabledSql.active">true</entry>
<entry key="feature.sdw">true</entry>

This can be done manually, or with the following commands.

cd /u01/ords
$JAVA_HOME/bin/java -jar ords.war set-property restEnabledSql.active true
$JAVA_HOME/bin/java -jar ords.war set-property feature.sdw true

You will need to restart ORDS for this to take effect. The method will vary depending on if you are running ORDS under Tomcat, WebLogic or in standalone mode.

With the above configuration in place, we can access SQL Developer Web and connect to the TESTUSER1 user with the following URL.

https://localhost:8443/ords/hr/_sdw

There is also a top-level URL that guides you through the login URL.

https://localhost:8443/ords/sql-developer

It assumes your base path and username match, which is unlikely to be the case for a schema owner or API user, so I'm going to avoid it in this example, but if you are setting up a regular user for a developer, having a matching schema name and base path are not a problem.

Using SQL Developer Web

Open a browser and go to the following URL, adjusted to suit your installation. The URL must be to an ORDS enabled schema, via the relevant schema alias.

https://localhost:8443/ords/hr/_sdw

Enter the credentials of the schema.

Once logged in you are presented with the "Home" tab. Click the "Worksheet" tab.

The "Worksheet" tab is where you create and edit objects, as well as running queries and scripts. Click the "Data modeler" tab.

The "Data Modeler" tab allows you to create objects and diagrams.

DBA Access

Create a new test user, but this time grant it the DBA and PDB_DBA roles.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

DROP USER dummy_dba CASCADE;
CREATE USER dummy_dba IDENTIFIED BY dummy_dba;
  
GRANT DBA, PDB_DBA TO dummy_dba;

Connect to the new DBA user and enable ORDS for it.

CONN dummy_dba/dummy_dba@pdb1

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

We've made the schema name and base paths match this time, so we could access this schema via either of the following URLs. Login with the DBA credentials.

https://localhost:8443/ords/sql-developer

https://localhost:8443/ords/dummy_dba/_sdw

You are presented with a dashboard. You can use the "Quick Links" on the right of the screen, or expand the hamburger menu on the top-left of the screen, show the available options. Click the hamburger menu on the top-left of the screen.

Expand the "Admin" section and click the "User Management" option.

You are presented with the list of users, which can be filtered, and allow you to perform some basic user management.

You will also notice an "Activity" tab is now present on most screens. This gives you access to a number of useful features.

Here is the Performance Hub, which will look familiar.

For more information see:

• Oracle REST Data Services (ORDS) : SQL Developer Web
• Oracle REST Data Services (ORDS) : REST Enabled SQL
• Oracle REST Data Services (ORDS) : All Articles

Hope this helps. Regards Tim...

Back to the Top.

Created: 2019-12-21  Updated: 2022-04-28

Contact Us

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall
Copyright & Disclaimer