8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

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.

Related articles.

Assumptions

This article assumes the following.

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.

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

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.

SQL Developer Web : Login

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

SQL Developer Web : Home

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

SQL Developer Web : Worksheet

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

SQL Developer Web : Data Modeler

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.

SQL Developer Web : DBA Dashboard

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

SQL Developer Web : DBA Hamburger Menu

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

SQL Developer Web : DBA 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.

SQL Developer Web : DBA Activity Menu

Here is the Performance Hub, which will look familiar.

SQL Developer Web : DBA Peformance Hub

For more information see:

Hope this helps. Regards Tim...

Back to the Top.