Converting to base-64 CLOB in PL/SQL

Recently I have been working on a project to send and retrieve files from a Microsoft SharePoint site using it's web service. The file contents are base-64 encoded and transmitted in a SOAP envelope.

Problem:
Encode a clob to base-64.

Trouble:
Oracle provides a utility package (UTL_ENCODE) that will encode VARCHAR2s to base-64, so we must chunk up the clob and convert it piece at a time. What size chunk turns out to be the problem.

Confusing:
The data density between ascii and base64 is different, meaning it takes more bits to store base64 as ascii then ascii as ascii. In PL/SQL, all base64 transformations take place in the RAW datatype which can only be an even number of characters (two characters represent a byte).

What this means:
If we take a chunk of data and convert it to base64 and the result doesn't fit perfectly into the RAW, the data will be padded with zeros. Not a problem if there is only one chunk, but if you concatenate several of these base64 chunks, you will have extra zeros thrown in seemingly randomly.

How I fixed this:
I tried a handful of chunk-sizes before I made the computer figure it out for me. I took a clob that could be converted in one chunk (less than the size of a VARCHAR2(4000)) and saved the result so that I had initial clob and my desired output. Then I wrote my loop the would chunk and convert the initial clob given a chuck-size. I stuck that in another loop that would test every chunk-size between 1 and half the clob plus 1. The procedure would print out whether the chunk size worked. Turns out 48, 96, 144, 192, ... showed up as 'Success'. Multiples of 48, I would not have guessed.

So here is the function, pass it a clob and you shall be returned a clob in base64.

FUNCTION ENCODE64_CLOB(IN_CLOB CLOB) RETURN CLOB IS
       --The chunk size must be a multiple of 48
       CHUNKSIZE INTEGER := 576;
       PLACE   INTEGER := 1;
       FILE_SIZE INTEGER;
       TEMP_CHUNK VARCHAR(4000);
       OUT_CLOB  CLOB;
BEGIN
       FILE_SIZE := LENGTH(IN_CLOB);
       WHILE (PLACE <= FILE_SIZE)
       LOOP
              TEMP_CHUNK := SUBSTR(IN_CLOB, PLACE, CHUNKSIZE);
              OUT_CLOB := OUT_CLOB 
              || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(TEMP_CHUNK)));
              PLACE := PLACE + CHUNKSIZE;
       END LOOP;
       RETURN OUT_CLOB;
END;

Type Matching between PL/SQL and Java

PL/SQL is great for some things, but built for a somewhat specific environment. Lately, I've needed to get some extra functionality and have turned to Java for things.

Problem:
Call a Java method from PL/SQL that will in turn make a call to a web service and return large amount of data that can then be used in PL/SQL.

Initial Problem Analysis:
-The amount of data that will be returned from Java is greater than a PL/SQL VARCAHR2, so we must use a clob.
-The java.sql.clob constructor is protected in Java because it was meant to be used in conjunction with a java.sql.ResultSet. This stumped me for a while, but I ended up passing an initialized clob from PL/SQL that I could then write to and pass back. In the end however, I ended up needing to pass so much data to the web service that I used the clob is input as well as output.

Getting started.
First off, however PL/SQL is an object-oriented programming language, you will not be initializing a Java class in your PL/SQL code, this was odd to me at first due to my experience being mostly with Java and C++. Without access to the constructor, variables you want to use in multiple methods must be passed in the single method you call from PL/SQL and assigned to static variables that are in the class scope. This is a small inconvenience here but could be huge in other cases.

Basic Structure:

create or replace and compile java source named CLOB_TEST as

import java.sql.Clob;

public class clobTest {

  public static Clob getResponse(Clob p_clob) throws Exception {
    // pull the string from the clob
    String stringAsClob = p_clob.getSubString(1, (int) p_clob.length());

    // make the string clean so you can write back to it.
    p_clob.truncate(0);
    
    // ...
    //mess with the string
    // ...
    
    //stuff the string back into the clob
    p_clob.setString(1, stringAsClob);
    return p_clob;
  }
}

You will need a function like this in Oracle that you will be calling in you PL/SQl code.

CREATE OR REPLACE FUNCTION JAVA_CLOB_TEST(P_CLOB IN CLOB) RETURN CLOB AS
              LANGUAGE JAVA NAME 'clobTest.getResponse( java.sql.Clob ) 
              return java.sql.Clob';

And finally, your PL/SQL code will look something like this.

DECLARE
              TEMP_CLOB CLOB;
BEGIN
              --Even if you dont need to pass data in the clob, you must initialize it.
              TEMP_CLOB := 'input data';
              TEMP_CLOB := JAVA_CLOB_TEST(P_CLOB => TEMP_CLOB);
END;

NTLM Authentication in PL/SQL -- Part 1 Project Overview

Goal:
Be able to call a SQL Server Web Service (SSRS) that used NTLM authentication from a PL/SQL package. End goal is to communicate from an Oracle 11g Database to SQL Server Reporting Server using SOAP (Simple Object Access Protocol) to return data that we cant currently get due to the expensive driver required to create a DB link between the Oracle and SQL Server.

Problem:
NTLM is a Microsoft proprietary authentication system and doesn't play nice with PL/SQL developers. Currently (11g r2) Oracle does not offer support for NTLM authentication, in Java however, that is a different story.

Steps I've taken:
-Use SoapUI to send requests to the web service. SoapUI is a handy program that provides an XML template for creating your SOAP requests and then displays the response in cleanly formatted XML. SoapUI also supports various authentication methods including Basic Authentication (plain text password exchange) and NTLM v1, which is what the web service I'm working with requires.

-Use Wireshark (a great open-source packet analyzer) to observe the NTLM authentication process and get an understanding of what needs to be done. Wireshark can decode the NTLM auth tokens pretty well but I ran into some quirks, mostly incorrectly showing the location in the packet where certain information was derived from. Despite some initial frustration due to skewed data, Wireshark was absolutely instrumental in this project.

-Start the long tedious process of replicating the NTLM auth tokens. There are three important tokens in this process that make up the authentication 'handshake.' The client sends the first token with basic information about itself excluding the password, the servers responds with a token with a challenge (a seemingly random 8-byte hex string), and then the client takes obfuscates the password with this challenge and send the third token back with it. This process requires lots of low-level programming, and we will be attempting in a very high-level language, PL/SQL.

-Fail. Yep, after a week of programming and testing, I can replicate the NTLM auth tokens down to the BIT. Yet I still get an 'Unauthorized' response. Bummer, I fully believe that this can work but I am getting held up by either a network firewall that is protecting the Oracle DB and not protecting my desktop, or the Microsoft web service simply refuses to accept requests from a Linux box (where the DB resides) but will from my Windows 7 desktop. I think this needs to revisited but in order to keep this project moving forward I will try switching to Java to for the authentication.

-Success! From PL/SQL I call a stored Java source and pass it my credentials along with the soap message I want to sent the web service. Java handles the authentication by creating an SSL connection and java.net.Authenticator does the rest of the NTLM magic for you. It's too bad really how simple it is in Java.

Note: Some responses from web services are larger then a PL/SQL VARCHAR2 (32k) for this I used a clob. The constructor for a clob is protected in Java because they want you to use it in conjunction with a java.sql.ResultSet. In order to overcome this problem I passed an initialized clob from PL/SQL to Java, wrote in it, then passed it back.