fil I fil 1 1.Qg 1 11gI 12c I 13c I 18c I 19c I lli I 23c I Misc I PL/SOL I $.

$.ill I RAC I WebLogic I Linux

Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : SQL Developer Web

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

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

Related articles.

• Orac e REST Data Services (OROS) : SQL Developer Web a

• Orac e REST Data Services (OROS) : REST Enab ed SQL
• Orac e REST Data Services (OROS) : 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 OROS version 19.3 on the cloud and 19.4 for on-prem inst allations .
• The paths for the OROS configuration match those from the OROS installation article listed above .
• You are using HTTPS in a browser to connect to OROS. 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=pdbl;

DROP USER testuserl CASCADE;

CREATE USER testuserl IDENTIFIED BY testuserl DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO testuserl;

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

 CONN testuserl/testuserl@pdbl

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('l-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

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

Normal web services from the schema can now be referenced using t he following base URLs. We can only use the HTTPS URL by default for
CONN testuserl/testuserl@pdbl
SQL Developer Web.

BEGIN
http://localhost:8080/ords/hr/ https://localhost:8443/odrs/hr/
ORDS.enable_schema(
p_enabled => TRUE,
p_schema =>
'TESTUSERl', p_url_mapping_type
=>
'BASE_pATH',
p_url_mapping_pattern => 'hr',
p_auto_rest_auth => FALSE
);

COMMI
We are now ready to start.

Enable SQL Developer Web (OROS 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 f eat ur e. 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/ or ds

export ORDS _CONFI G=/ u01/ conf i g/ or ds export PATH=${0 RDS _HOME}/ bi n: ${P ATH}

ords --config ${0RDS_CONFIG} config set r est Enabl edSql . act i ve true ords --config ${0RDS_CONFIG} config set f eat ur e . 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 TESTUSERl user with the following URL.

ht t ps : / / l ocal host :8443 / or ds / hr / _sdw

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

ht t ps : / / l ocal host :8443 / or ds/ sql -devel oper

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 (OROS 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.

<ent r y key="r est Enabl edSql . act i ve" >t r uec/ ent r y>
<ent r y key="f eat ur e. sdw" >t r ue</ ent r y>

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

cd / u01/ or ds
$JAVA_HOME/ bi n/ j ava -jar ords.war set-property restEnabledSql.active true
$JAVA_HOME/ bi n/ j ava -jar ords.war set - pr oper t y 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 To mcat, WebLogic or in
standalone mode.

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

ht t ps : / / l ocal host :8443 / or ds/ hr / _sdw

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

ht t ps: / / l ocal host : 8443 / or ds / sql -devel oper

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 proble m.

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 .

ht t ps : / / l ocal host :8443 / or ds / hr / _sdw

Enter the credentials of the sch ema.

O RA CLE '
SOL Developer Web

Username

tes tuse rl

Pas.sword

·········I

About Oracle Contact Us Legal Notice s Terms Of Use Your Privacy Rig hts
Copyright 2009, 2019 Oracle .a nd /o r +U. a ffiliate All ng'lts. res.e rved ,

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

O R A CL E ' SQL Developer E Worksheet Data Modeler I< TESTUSER1 v

Cl My Worksh eets I§ My Diagr ams

Name Created Updated N ame Updated

You have no Worksh eets You have no Dia g rams

@0 Re ce nt ly Mod if ie d Objects @x Invalid Objects

ObJect Type Statu s

No ite ms to display.

12/ 20 12/21 12/22

72/ 19 12/ 20 12/21 12/22

'" Table Stats Freshness

Tab le Rows Bloc ks La st Anal yzed

DEPT 12/ 21/201 9, 3:36:02PM

EMP 14 12/21/2019. 3:36:02 PM

@ 1 /Jj, □ (§1 o I 3:3.6:03 PM REST (all re l\'ed s.oc( es sfu l v

The "Worksheet" tab is where you create and edit objects, as well as running queries and scripts. Click the "Data modeler" tab.
 - ORACLE'
SQLDeveloper Home Worksheet Data Mode ler I< TESTUSER1 v

Navigator Worksheets [Wo rksheet ].. ,,.

00
0
1 SELECT * FRCJ-1 em p ;
TESTUSER1

Table s

Seilu:h. [ o [ ··· ]
► Im DEPT
--' im EM P
[II] EM PNO
[Il] Er-JAM E
[Il]JOB
[Il]MGR
[II] HIREDATE
[II] SAL
[Il]COMM
[II] DEPTN O

◄ Q uery Result Scrip1 Ou tput DBMS Output Explain Plan Autotrace SOL History Data Loading 0
liil 0 Dowr,load .,. E.xe<.ut1o n trme: 0.0 22 seconds

empno ename job mgr hired ate sal comm

7369 SM ITIJ CLERK 7902 12/17/ 80 12:00:00 .. 800

7499 ALLEN SALESMAN 7698 02/ 20/ 811 2:00:00 .. 1600

7521 WARD SALESMAN 7698 02/22 / 81 12:00:00 .. 1250

4 7566 JONES MAN AGER 7839 04/0 2/ 81 12:00:00 .. 2975

765-4 M ARTIN SALESMAN 7698 09/ 28/81 12:00:00 .. 1250

7698 BLAKE MAN AGER 7839 05/ 01 / 811 2:00:00 .. 2850

7782 CLARK M ANAGER 7839 06/ 09/ 81 12:00:00 ... 2450

7788 SCOTT ANALYST 7566 04/1 9/ 87 12:00:00 .. 3000

7839 KING PRESID EN (nu ll) 11/1 7/ 81 12:00:00 .. 5000

T
10 7844 TURNER SALESMAN 7698 09/ 08/ 81 12:00:00 .. 1500

11 7876 ADAMS CLERK 7788 05/ 23/ 87 12:00:00 ... 1100

12 7900 IAM F rlFRK 7/;9R 1 ) /0 1 /fl l 1/:{}0:00 .. qsn

@ 1 /!}. □ (§1 0 I 3:3.6: 39 PM - REST ( <111 re lved soc( essful y

The "Data Modeler" tab all ows you to create object s and diagram s.
DBA Access
Create a new test user, but this time grant it the DBA and PDB_DBA roles.

Connect to the new DBA user and enable OROS for it.
CONN / AS SYSDBA
ALTER
CONN SESSION
dumm SET CONTAINER=pdb
y_dba/dummy_dba l;
@ pdbl

DROP USER dummy_dba CASCADE;

BEGIN
CREATE USER dummy_dba IDENTIFIED BY dummy_dba;
ORDS.enable_schema(
p_enabled=> TRUE,
p_schema=>
GRANT DBA, 'DUMMY_DBA', p_url_mapping_type
PDB_DBA TO dummy_dba ; => 'BASE_PATH', p_url_mapping_pattern => 'dummy_dba', p_auto_rest_au
);

COMMIT; END;
I

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 credential s.
 https://localhost:844/3ords/sql-d eveloper

https://localhost:844/3ords/dumm y_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.
10 Dashboard
ORACLE' Dashborad

8 SQL Deve lop er

x Admin
Us e r Ma n age men
DA TA :IA '.
t

,,, Sessio ns Physical 10 Pane l

18

0.0 5

- Act r.l!"
- 0!"[ of R!"ad Tot al
lnactw!"
- D1"'1taofWrit!"Tolill
- 8tocked

0.0 2

0.00- - - - - - - - - - - - - - - - -
16:42:00 16:42:3 0 16:43:00 16.-43 :30 16:44:00 16:41:30 1 42:00 16:42:30 16:43:00 16:43:30

!:;cc Wait s

6000

- Us!"r VO
50.00
- System 1/0
- Schedule r
40.00 - QU:!"UI!"

Other

J 3 0. 00
-
-
-
Net wo rk
Id le
C;:, nfigurat ion
- ConaHr !"ncy
20.00
- Comm i t
- Cluste r
10.00 - Applicat io n
- Admmistr.,,LM''

0.00 - - - - - - - - - - - - - - - - -
16:42:00 16A2:30 16:43:00 16:43:30 16:44:00

0 0 A_ o @l o I .: 3:5g PM - RfiT (all reso ved sum;s s{ullx,

You are presented with the list of users, which can be filtered, and allow you to perform some basic user management.
 - O R A CLE " SOL Developer Home Activity (9 Worksheet
Data Modeler P, DUMMY_DBA v

Cur ren t User

•
DUMMY DBA RESTEMbled
OR DS Alias: dummy_dba
f Lilst logm : 2019-12-21T16:39:47Z 0 Password Expire. 1n. 179 days

https://JocalhostBdA3/ords/dummy_dba/_sdw/ ro

•
All Users P+ Crea te User

°' II J.:

APE X_LISTENER
• APEX _RE ST_PUB LI

•
C_USER
f Lilst logm : 2019-12-21T15:2Z:35Z 0 Password Expire. in. 179 days C9 Password Expires m 179 daY5

DUMMY _DBA REST Enahl,d

OR.DS Alias: dummy _dba
P Last login: 2.019-12-2\T16:39:47Z @ Password &pin."!i in 179 dc1ys

http';;.//localhostB443/ords/dummy_dba/ _sdw/
ro • OR DS_M ETADATA

• •
ORDS PUB LI C_USER PDBADM I N

.P Last login: 2019-l2 -2 \Tl6:59:07Z 0 Password Expires in 179 dc1ys 0 Password 6:pires 1n 179 day..

• ■
TESTUSER1 RESTEN hled
□R DS AHas: hr I/ Edit
/> Last Logm! 2019-12-21Tl5:3'.>:15Z © Password Expires in 179 days
f:!I D i sable REST
https:// loc:al hos t8443 /o rds /hr /_:.dw/
.fJ D rop REST
Services

@ D e le t e

htt ps://localhost B-443/o rds/ dumm _y_db a/ _sdw/?na v::c-ad min&admin=u ser-manage ment -"'

You will also notice an "Activity" tab is no w present on most screens. This gives you access to a number of useful feature s.
 - O R A CL E " SOL Developer Home Activity (2) Worksheet D ata Modeler P, DUMMY_DBA v

Overview
Cu rr ent

•
User
Alerts

DU M MY DBA mTE lo gins

OR DS Altas: dumm
Parameter
y_db, s s 1n. 179 days
f Lilst logm : 2019-12-21Tl Performance Hub
ro
https://JocalhOS tBdA3/ords/
Se,,sions
P+ Crea te User
All Us ers St orage

Top SQL

•
a_ - _.,

AP EX_LISTENER

f Lilst logm : 2019-12-21T15:2Z:35Z 0 Password Expire. in. 179 days

• AP EX_RE ST_PUB LI C_USER
C9 Password Expires m 179 daY5

•
DU M MY_DBA REST Enahl,d
OR.DS Alias: dumm y_dba
P Last login: 2.019-12-2\T16:39:47Z @ Password &pin."!i in 179 dc1ys

http';;.//localhostB443/ords/dummy_dba/ _sdw/
ro • OR DS_M ETADATA

• •
ORDS PUB LI C_USER PDBADM I N

.P Last login: 2019-l2 -2 \Tl6:59:07Z 0 Password Expin .•s in 179 da)IS 0 Possword 6:pires 1n 179 day..

•
TESTUSER1 RESTE N hl ,d
□R DS AHas: hr
/> Last Logm! 2019-12-21Tl5:3'.>:15Z © Password Expires in 179 days

https:// loc:al hos t 8443/o rds/hr /_:.dw/ ro

https ://l oca lho st B-443/o rds/ dumm y_dba/ _sdw /?nav ::c-ad min&ad min=user-management -"'

Here is the Performance Hub, which will look familiar.

 -
ORACLE" SOL Developer Home Activity@ Worksheet Data Modeler (2) P, DUMMY_OBA v

Activity / Perf orm ance Hub

Pe rfo rma nce H ub Refr esh

r
La t hour:
..ii Tim e Range Select Durat ion 21, 201 04.:Hi DM - 05:i Pr.t

Wait
Use r l/ O
C CPU
·0a - Max Thr .-.ds
<(

04: 20 PM 04:25? M 04:30 PM 04:15 PM 04:40PM 04:45 PM 04 50 PM 04:55P M 0 5:00 PM 05:05 PM 0 5:10 PM 05:1.SPM
D c 21, 2019 GMT-0000

ASH An alytics 5Q L Monitoring

T Appli ed Filt ers: Nc-ne View Options

a■
Average Active Sessio ns by Consumer Group ... Displ ay
2A ../ Max Threads
._ Tota l Act1vl

2.0

1.6

..
12

0.8

----------
OTHER~ GROUPS

0.0 - - Ma x Thr .!id s

OA 04:18:00 PM 04:18:40 PM 0:00 PM 04:20:40 PM 04:21:20 PM 04:22:00 PM 04:22:A.O PM

D c 21, 2019 GMT-0000

SQL ID ,.. by Consumer Group User Session ..,. b y Consumer Group

SQL ID Activity (Averag e Active Sessi ons) User Session Activrty (Average Active Sessions )

bg c0Skz0a6f0n 0.02 1:267.15424 0.01

24fgm9b6 pd7vg - < 0 .01 1:26A,1692 1 0 .0t

g n75tgh n7mm18 - < 0.01 1:282 ,34911 < 0.01

® 0 8 0 ® 0 I 5:t §: 25 PM REST (a ll re so ved 8,l((f :>:tfu !' 'l,

For more information see:

• Oracle REST Data Services (ORDS)...; 5QL Developer Web a

• Oracle REST Data Services (QRQ.S) : REST Enabled SOL
• 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 I Articles I ScriP-ts I Blog I Certification I Videos I Misc I About

About Tim Hall

CoJ2Y.right & Disclaimer