¡Estamos orgullosos de anunciar que nuestro sistema de gestión de gastos de la concesión Tempest-GEMS ya está disponible en español!

El mismo software de administración de subvenciones de alta calidad en el que ha llegado a confiar ahora está disponible para sus usuarios hispanohablantes. No más traducciones confusas y lentas: los hispanohablantes pueden confiar en que podrán acceder a lo que necesitan, en español.

Además de poder acceder mejor a Tempest-GEMS, sus usuarios hispanohablantes también tienen acceso a nuestro servicio de atención al cliente en español. Nuestros agentes de atención al cliente son bilingües y les encanta servir a nuestros clientes en español.

Introduction

In 2015, I wrote an entry about doing multifactor authentication with PL/SQL (link: https://storm-petrel.com/orablog/2015/11/29/oracle-apex-multifactor-authentication/)

The SMS services that I based my work on has disappeared from the internet landscape, rendering my code and efforts futile.

So it was time to start again. The advantage was that I got to use better RESTful tools and JSON parsers.

The variance this time is that I put 100% of the code on Github

Why MFA?

Why use multifactor authentication (two-factor or 2FA) authentication? Well, frankly it is the right thing to do as I wrote in 2015…

The United States Office of Personnel Management is notifying 2.7 million people that their data were stolen. The blame lays at the feet of the people responsible for these data. While the ultimate responsibility rests with the executives who did not fund investment into data security, I shoulder that responsibility daily.

Github

https://github.com/cmoore-sp/pl-sql_nexmo_sms

Nexmo Multifactor Authentication

The last time I visited this topic, I used some random generators to create a 4-digit code that I then sent via text message to a phone. With the assistance of an Oracle global temporary table and precise timestamps, I did the heavy-ish lift on generating the code, storing the code, sending the code, and verifying the code and the timestamps. Not tough, but the Nexmo approach takes some of the effort.

The process initiates with MFA request to the phone. You get a message ID back. You do NOT get the code.

When the user keys in the code, you (the programmer) has to take the message ID generated from the request and the 4-digit code entered by the user. You send these off to Nexmo. They send back a status code. Zero is good. Not zero is bad.

Interesting Annoying Stuff

The tool apex_json.get_varchar2() does NOT like a dash in the “field name”. The vendor, Nexmo, put dashes in the field names for JSON, but did NOT for the XML interface. So a little replace() action is need to make Oracle happy.

To recap – this is acceptable but does not match Nexmo’s data definition

			r_response.client_ref :=
				apex_json.get_varchar2(
					p_values => l_values, 
					p_path => 'messages[' || trim(to_char(json_row)) ||'].clientRef'
				);

This is not acceptable to Oracle, but does match Nexmo’s data definition

			r_response.client_ref :=
				apex_json.get_varchar2(
					p_values => l_values, 
					p_path => 'messages[' || trim(to_char(json_row)) ||'].client-ref'
				);

Oh well, the solution is just as easy as changing the JSON data:

l_return := replace(l_return, 'client-ref', 'clientRef');

Oracle PL/SQL SMS and MFA

A few quick hints about the code…

Sending SMS text looks rather like this:

begin
	-- validate the parameters
	sms_ok(p_sms,'send_sms','Y');
	l_text := msg_ok(p_msg,'send_sms');

	-- structure the URL
	l_url := g_sms_uri 	||
			'api_key=' 	|| g_key || amp ||
			'api_secret=' 	|| g_secret || amp ||
			'to=' 		|| p_sms || amp ||
			'from=' 		|| g_sender || amp ||
			'text=' 		|| l_text ;
	if p_client_ref is not null then
		l_url := l_url		|| amp ||
			'client-ref='	|| p_client_ref;
	end if; -- client reference is not null
	
	l_return := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
			 p_url              => l_url
			,p_http_method      => 'GET'
			,p_wallet_path		=> g_wallet_path
			,p_wallet_pwd		=> g_wallet_pwd
			);
...

APEX Application

I whipped up a quick APEX 5.1 application to show the integration of the package and the Nexmo API. It is also posted at Github. It will send an SMS message, do two-factor authentication, and show you your balance.

Requirements

1. You MUST have an account with Nexmo.com. You can get a demo account for free.
2. You MUST put your own Oracle Wallet Path and Wallet password into the packages and compile them (G_WALLET_PATH and G_WALLET_PWD)
3. You MUST manually install the Root and Intermediate SSL certs for nexmo.com in your wallet
   • ROOT: Digicert Global Root CA, v3, valid through 09NOV2031
   • Intermediate: Digicert SHA2 Secure Server CA, valid thorugh 08MAR2013
4. If you are on Oracle 12c, DO NOT install the SSL cert for *.nexmo.com
5. You’ll need to create the table that is in the package body. It is in the commented out section.
6. You’ll need to put in your own G_KEY and G_SECRET at the top of the package body.

-\qed

Border lines! Feels like I’m going to lose my mind! (Madonna, anyone?) 

A challenge that I’ve been asked about and I’ve experienced myself using AOP to generate reports is: How do I make the borders behave?  

Although my AOP “style” is to use a Word template to a PDF output, I still like to utilize tables in my Word AOP report template to lay out and format my reports. It’s a matter of preference I tend to do this the same way every time, but the pointers below may also be used when laying out an Excel template, for example. 

Often in formatting a nice pretty report, we like to use a mixture of border lines and no border lines. We want to highlight key information, but at the same time, not make our report look too “busy”. 

Sometimes, MS Word seems to make design decisions on it’s own, that you would rather not have on your report output, such as repeating border lines throughout the report. Thanks, Word, but no thanks! 

I’ve picked up a few handy tricks for ensuring those annoying border lines appear where you want, and not appear where you don’t want them to appear. 

Here are a couple actual scenarios re: border lines you may or may not have run into already: 

Scenario 1 

“I need some assistance in removing some of the border lines. 

When I remove some it removes all the borders when I print.” 

The reason this happens is that the formatting in the table “thinks” that the bottom border for one row is also a top border for the next row.  

What I Wanted		What the template looks like		What shows on the report
Header Row		Header Row		Header Row
Detail Row 1		Detail Row 1		Detail Row 1
Detail Row 2		Detail Row 2		Detail Row 2

This becomes extremely frustrating, but there’s a simple answer! 

If there is a separation line that’s needed in the template, but not necessary between every detail section line, the best way to do this is using a “buffer” line (empty table row) that is a very narrow row height (to ensure that it’s inconspicuous on final report) to separate the line that needs a border with the line that doesn’t. So, between the close tag of the first row, and the open tag of the row that requires different formatting, that is where the “buffer line” goes. 

Scenario 2 

“How do I force spacing between lines?” 

Add a “buffer” row in the table  in the template between the rows that require spacing and select/highlight only that line. Right click and go to Table Properties. Go to the Row tab and set the row height to the size desired for spacing. 

Run the report and you can see that an empty row with the row height specified now is between the two rows you wanted to space apart. 

Compressing binary large objects (BLOB) or files using PL/SQL has been challenging. For at least a decade, Oracle includes UTL_COMPRESS as a means of compressing or zipping files. This technology faced a few limitations specifically, adding a BLOB with the UTL_COMPRESS.LZ_COMPRESS_ADD procedure. The source “file” is required to be raw. The utility spews errors when you asked it compress a BLOB directly. PLSQL Compress BLOB is easier with APEX_ZIP.

There are alternatives. Anton Scheffer wrote and maintains a fine package called AS_ZIP (the “AS” likely to reference Anton’s initials.

I learned this week after searching and searching that Oracle has picked up Anton’s utility and included it as an APEX Package. It is called APEX_ZIP. According to Christian Neumueller, it was Patrick Wolff that adopted Anton’s work.

How did I miss this? Well, I searched compress BLOB. I ought to have searched ZIP file. As of early May 2017, the phrases on the APEX_ZIP utility documentation are zip and unzip. The phrases on the UTL_COMPRESS utility are compress and uncompress. I avoided searching “ZIP” because of its association with a brand. Dooh! Let’s hope that this blog helps others find the compress blob utility APEX_ZIP.

PLSQL BLOB Compression

The purpose of this blog is to assist the next Oracle PLSQL developer who wants to compress blobs or zip blobs to find the APEX_ZIP utility. Have I used the phrases compress blob and zip blob enough to be picked up by search engines? I hope so.

APEX_ZIP

Please search for the APEX_ZIP utility. There are a few nuggets to know. First, you really can add a file to an empty set. Unlike Alice’s experience at a tea party, when offered “a little more tea?”. She states: “Well, I haven’t had any yet, so I can’t very well take more.” And the readers of all things Oracle PL/SQL know that one can’t add to a null. But, in this case you may add to an empty set.

ORA-06502

Protect the procedure call and screen for nulls. A null filename gives a ORA-06502 “numeric or value error” from sys.utl_raw.

What’s Next

Having a big ole BLOB with a set of zipped or compressed BLOBs doesn’t yet help the end user. We tend to use global temporary tables (“GTT”) as a means of hosting BLOBs for users to download via Oracle APEX. While GTT are self-cleaning and offer protection against nosy neighbors from seeing data, when using Global Temporary Tables in APEX, they can be seen by another user. So we always post the session ID with a record and we filter on that. That keeps nosy neighbors out and boundaries clean.

Function ZIP_BLOBS

The function below is a sample. It spins through a table with a bunch of blobs that need compressing. It puts the compressed BLOB into a global temporary table that is easily used in Oracle APEX.

Contributors

Anton Scheffer wrote a fine blog and utility in 2010. He has updated this utility as recently as April 2016. His code gives insights into the workings. Dimitri Gielis unceremoniously pointed out that had I searched ZIP instead of compress, I would have save days of misery. We really do need friends like that! Christian Neumueller shared the story of APEX_ZIP package and confirmed its roots. Patrick Wolf adopted Anton’s work.

Sample Code/Function

— sql create or replace function zip_blobs return number

as l_compress_blob blob; l_session number; l_zip_filename varchar2(60); l_sb_pk number; begin

l_zip_filename := ‘compressed_blobs.zip’; dbms_lob.createtemporary(l_compress_blob, false); l_session := apex_application.g_instance;

delete from tg_store_blob where sb_session = l_session;

– fetch blobs, build zip file for i in ( select doc_pk, doc_blob, doc_filename from tg_doc ) loop – Compress BLOB / ZIP BLOB if i.doc_filename is not null and i.doc_blob is not null then apex_zip.add_file ( p_zipped_blob => l_compress_blob, p_file_name => i.doc_filename, p_content => i.doc_blob); end if; – parameters not null end loop;

– Let the utility close up the zip file and add necessary footers apex_zip.finish(l_compress_blob);

insert into tg_store_blob ( sb_session, sess_date, blob_file,

created_date,
blob_mimetype,
blob_filename

) values (

l_session,
localtimestamp,
l_compress_blob,

localtimestamp,
null,
l_zip_filename

) returning sb_pk into l_sb_pk; commit; return l_sb_pk; end zip_blobs;

Oracle PL/SQL AWS S3

Amazon Web Services (AWS) Simple Storage Solution (S3) has upgraded its application programming interface (API) to support HTTPS. This upgrade involved a redesign of the authentication process at AWS. The authentication process now involves authenticating the user and verifying the canonical request made to AWS S3.

Morten Braten and Jason Straub have published and supported tools related to AWS S3 and Oracle PL/SQL. These tools, located within the Alexandria Library, do not support the new AWS4 signature and do not support HTTPS.

During the recent months, I have researched the AWS interface and written an updated package. I have hosted these tools at my github . I think with solid collaboration and greater testing from our Oracle and APEX community we can get this package solid enough to be included in the library.

Why?

Answer #1

At Storm Petrel, we use AWS S3 to augment the database for the storage of documents in our various document management tools. We keep an original in a BLOB and a modified copy at S3. We have wanted to make sure that ALL communication between the users and our infrastructure is 100% encrypted. With the link to AWS S3 travelling via HTTPS, we can stand behind this statement.

Answer #2

Initially, AWS S3 was all hosted from the Northern Virginia region. S3 is now hosted in 15 regions. Some of these regions require HTTPS and some require the AWS4 signature. It seems that AWS will want to depreciate the HTTP interface in time. Best to stay ahead of the demand!

Answer #3

This was well out of my comfort zone. In 2015, we wrote an interface to MailChimp. In 2016, interfaces to FEMA and to Nexmo for SMS and multifactor authentication. The AWS S3 stuff was tough!!!!

What is next?

I focused on getting the feature we use in place. AWS S3 has a huge number of features. This package does not even have all of the features that Morten put in to the original effort. So, testing and continued development is what is next.

I can’t do it alone. The foundation is here. The authentication stuff and HTTPS stuff works. Now to add the other bits that S3 has to offer, or that we need.

GitHub!

Please direct yourself to the Github site for more information about the package, and to get the packages. At that site you will find:

• Documentation
• The package (specification and body)Oracle PLSQL AWS S3

-/qed