Monthly Archives: September 2019


Make a REST call with JSON content to a HTTPS webservice from PL/SQL 4

All keywords are now in the title. 🙂

I’ve started developing this small PL/SQL procedure based on Lucas Jelema blog entry Invoke a REST service from PL/SQL – make an HTTP POST request using UTL_HTTP in Oracle Database 11g XE .

Unfortunately it took me a while to find out why I was getting the error:

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>400 Bad Request</title>
</head><body>
<h1>Bad Request</h1>
<p>Your browser sent a request that this server could not understand.<br />
</p>
</body></html>

There is a empty space at the beginning of a variable in the example on Lucas’ blog post – before the HTTP/1.1 on the line below:

req := utl_http.begin_request(url, 'POST',' HTTP/1.1');

My need is a bit more complex, so I leave here the complete code.

Certificate and Oracle wallet

First you need to download the certificate. A good tutorial do to this is at Shellhacks.com.

Then you need to create a wallet on the database server and add the certificate:

mkdir ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet
orapki wallet create -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet -pwd WalletPassword123 -auto_login
orapki wallet add -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet -trusted_cert -cert "/tmp/my_certificate.crt" -pwd WalletPassword123

Oracle ACLs

On the database (my case was Oracle 11.2) you need to have XDB installed and add the ACLs. I skip the instructions to install XDB. The user is called GREEN.

grant execute on utl_http to GREEN;
grant execute on dbms_lock to GREEN;

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'acl_file.xml', 
    description  => 'Allows sending alerts to REST service',
    principal    => 'GREEN',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);
end;
/

begin
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'acl_file.xml',
    host        => 'my_rest_service.com', 
    lower_port  => 443,
    upper_port  => NULL);    
end; 
/

Procedure to invoke REST service

The rest service requires a username password. I use the encoded version, so there are no visible passwords in the code. You can encode the pair username:password here.

CREATE OR REPLACE PROCEDURE green.publish_event
( p_robot in varchar2, p_business_service in varchar2, p_target_ci in varchar2, p_transaction in varchar2, p_status in number, p_message in varchar2) is
  req utl_http.req;
  res utl_http.resp;
  url varchar2(4000) := 'https://my_rest_service.com/api/event';
  buffer varchar2(32767); 
  content varchar2(8000) := '{ "robot":"'||p_robot||'", "business_service":"'||p_business_service||'", "target_ci":"'||p_target_ci||'", "transaction":"'||p_transaction||'", "status":"'||p_status||'", "message":"'||p_message||'", "target_support_group": "", "organization": "ANJO_EVENT"}';
  
BEGIN
  utl_http.set_wallet('file:${ORACLE_BASE}/admin/${ORACLE_SID}/wallet', null);
	req  :=UTL_HTTP.begin_request(url, 'POST');
	utl_http.set_header(req, 'Authorization', 'Basic f24iYsBpdGVzdDp3ZWJhcGl0ZXN0'); 
  utl_http.set_header(req, 'Content-Length', length(content));
  utl_http.write_text(req, content);

  res := utl_http.get_response(req);
  -- process the response from the HTTP call
  begin
    loop
      utl_http.read_text(res, buffer,32767);
      dbms_output.put_line(buffer);
    end loop;
    utl_http.end_response(res);
  exception
    when utl_http.end_of_body 
    then
      utl_http.end_response(res);
  end;
END publish_event;
/

Trigger to automatically send event

In my case, the idea is to send an event every time a line is inserted into a table. So I just create a small trigger that calls the procedure above.

CREATE OR REPLACE TRIGGER green.trg_table1_monitor
AFTER INSERT
   ON green.table1_monitor
   FOR EACH ROW
BEGIN
   publish_event (
	   p_robot => :new.host_name,
		 p_business_service => 'IT DB',
		 p_target_ci => '356224bs5b1f6f401d2262eb0b9619ad',
		 p_transaction => 'Table1',
		 p_status => 7,
		 p_message => 'Green-Table1 -Exception: '|| substr(:new.exception,0,100) 
	  );
END;
/

Et voilà!


Install Oracle Locator in Oracle 12.2

[Update: this is not anymore valid for Oracle 19c. To install Oracle Locator on Oracle 19c, check this post]

Oracle Locator is the free subset of Oracle Spatial functions. It is installed in MDSYS schemas. Some applications that use geographical data need Oracle Locator in order to compile.

There are two ways to install Oracle Locator, an easy and a complex.

When doing on single-tenant databases and Oracle Locator is only need for one pluggable database, please use the simple way.

Simple way to install Oracle Locator

In this case we install Locator directly on the pluggable database.

SQL> alter session set container=XPTO;
SQL> create user mdsys identified by "XXXXX" default tablespace sysaux;

@?/md/admin/mdprivs.sql
@?/md/admin/catmdloc.sql
SQL> alter user mdsys password expire;
SQL> alter user mdsys account lock;

SQL> col comp_name for a50
SQL> select comp_id,comp_name,status from dba_registry;

COMP_ID                        COMP_NAME                                          STATUS
------------------------------ -------------------------------------------------- -----------
CATALOG                        Oracle Database Catalog Views                      VALID
CATPROC                        Oracle Database Packages and Types                 VALID
XDB                            Oracle XML Database                                VALID
OWM                            Oracle Workspace Manager                           VALID

Complex way to install Oracle Locator

When you have a multitnenant environment and you want that Locator is available on all future PDBs that you create, the following method is to use. Unfortunately there are a few bugs (on 12.2 at least) when I tried this.

We need to install Oracle Spatial first and then remove Oracle Spatial and leaving Locator only objects.

I start with a fairly empty CDB, without JAVAVM. This must first be installed. I’ve already downloaded the scripts from the note described below, so I don’t go much in detail.

set lines 200
select comp_id,version, status from dba_registry;

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ --------------------------------------------
CATALOG                        12.2.0.1.0                     VALID
CATPROC                        12.2.0.1.0                     VALID
XDB                            12.2.0.1.0                     VALID
OWM                            12.2.0.1.0                     VALID


-- Install JAVAVM, XDB, XML - Repairing/Reinstalling JVM To A Multitenant Database (CDB and all PDBs) (Doc ID 2262919.1):
==========================================================================================================================

INSTALL_DIR=/temp/rdms122_java
cp $INSTALL_DIR/reinitjvm.sql $ORACLE_HOME/javavm/install
sqlplus / as sysdba << EOF
alter profile default limit password_verify_function null;
@$INSTALL_DIR/fulljvminstall.sql
shutdown immediate;
EOF

-- Oracle restart starts back the database automatically

sqlplus / as sysdba << EOF
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp_ -d $ORACLE_HOME/rdbms/admin utlrp.sql
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l $INSTALL_DIR -n 1 -b post_jvm_installation_info_ $INSTALL_DIR/jvm_info.sql;
set lines 200
select comp_id,version, status from dba_registry;
EOF

...

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ --------------------------------------------
CATALOG                        12.2.0.1.0                     VALID
CATPROC                        12.2.0.1.0                     VALID
XML                            12.2.0.1.0                     VALID
JAVAVM                         12.2.0.1.0                     VALID
CATJAVA                        12.2.0.1.0                     VALID
XDB                            12.2.0.1.0                     VALID
OWM                            12.2.0.1.0                     VALID

Next step is to install Oracle Multimedia, which is also required by Spatial:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l ${ORACLE_BASE}/admin/${ORACLE_SID}/create/logs -d $ORACLE_HOME/md/admin -b mdinst mdinst.sql

SQL> select comp_id,version, status from dba_registry;

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ --------------------------------------------
CATALOG                        12.2.0.1.0                     VALID
CATPROC                        12.2.0.1.0                     VALID
XML                            12.2.0.1.0                     VALID
JAVAVM                         12.2.0.1.0                     VALID
CATJAVA                        12.2.0.1.0                     VALID
XDB                            12.2.0.1.0                     VALID
OWM                            12.2.0.1.0                     VALID
ORDIM                          12.2.0.1.0                     VALID
SDO                            12.2.0.1.0                     VALID

And finally we install Spatial and remove it again, leaving Locator

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l ${ORACLE_BASE}/admin/${ORACLE_SID}/create/logs -d $ORACLE_HOME/md/admin -b mddins mddins.sql

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp_ -d $ORACLE_HOME/rdbms/admin utlrp.sql


SQL> select comp_id,version, status from dba_registry;

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ --------------------------------------------
CATALOG                        12.2.0.1.0                     VALID
CATPROC                        12.2.0.1.0                     VALID
XML                            12.2.0.1.0                     VALID
JAVAVM                         12.2.0.1.0                     VALID
CATJAVA                        12.2.0.1.0                     VALID
XDB                            12.2.0.1.0                     VALID
OWM                            12.2.0.1.0                     VALID
ORDIM                          12.2.0.1.0                     VALID
SDO                            12.2.0.1.0                     REMOVED

There were some objects invalid, which match the Oracle note: “Invalid MDSYS objects after running mddins.sql to keep Locator option on 12.2.0.1 (Doc ID 2307454.1)”

vi /tmp/bug2307454.sql
drop function mdsys.NDM_RESET_ID_GENERATOR;
@$ORACLE_HOME/md/admin/semremov.sql

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l ${ORACLE_BASE}/admin/${ORACLE_SID}/create/logs -d /tmp -b bug2307454 bug2307454.sql

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp_ -d $ORACLE_HOME/rdbms/admin utlrp.sql

There were still some invalid public synonyms left. I opened an SR, and Oracle said they can be removed:

SQL> set lines 200
SQL> set pages 40
SQL> col owner for a20
SQL> col object_name for a50
SQL> col object_type for a30
SQL> select owner,object_name,object_type,con_id,sharing,status from cdb_objects where status!='VALID' and owner!='MLOG' order by 1,2;

OWNER                OBJECT_NAME                                        OBJECT_TYPE                        CON_ID SHARING            STATUS
-------------------- -------------------------------------------------- ------------------------------ ---------- ------------------ -------
PUBLIC               ALL_SDO_GEOR_SYSDATA                               SYNONYM                                 3 METADATA LINK      INVALID
...
21 rows selected.

alter session set "_ORACLE_SCRIPT" = true;
BEGIN
FOR cmdline IN (select 'drop public synonym '|| object_name cmd from dba_objects where status!='VALID' and owner='PUBLIC') LOOP
dbms_output.put_line(cmdline.cmd);
execute immediate(cmdline.cmd); 
END LOOP;
END;
/
alter session set "_ORACLE_SCRIPT" = true;

And that’s it. A complex way to install Locator…