Recursive Pipelined function in PL/SQL

I've always thought pipelined functions were awesome, even more awesome though is a recursive pipelined function. For me, each function iteration calls a web service that returns xml describing a list of files and folders in a given directory. I then call the function on each folder.

The awesomeness:
Piping piped rows.

To use this you will need to replace <TABLE_SOURCE> with the function you use to get your table. For me, I make a web service call and throw the result into an XMLtable, and select from that.

 FUNCTION LIST_PIPE
(
    LIST_URL VARCHAR2
    ,RECURSIVE VARCHAR DEFAULT 'false'
) RETURN LIST_TABLE_TYPE
    PIPELINED IS
    
BEGIN
    
    FOR I IN (SELECT A.ITEM AS ITEM
       ,A.IS_FOLDER AS IS_FOLDER
      FROM <TABLE_SOURCE> A )
    LOOP
       PIPE ROW(LIST_ROW_TYPE(I.ITEM, I.IS_FOLDER));
       IF RECURSIVE = 'true' AND I.IS_FOLDER = 'true'
       THEN
          FOR J IN (SELECT * FROM TABLE(LIST_PIPE(I.ITEM, RECURSIVE)))
          LOOP
             PIPE ROW(LIST_ROW_TYPE(J.ITEM, J.IS_FOLDER));
          END LOOP;
       END IF;
    END LOOP;

END LIST_PIPE;

You will need the following two global types.

CREATE OR REPLACE TYPE LIST_ROW_TYPE AS OBJECT
(
              ITEM          VARCHAR2(4000),
              IS_FOLDER     VARCHAR2(10)
)

..and..

create or replace type LIST_TABLE_TYPE as TABLE OF LIST_ROW_TYPE

Deleting files from SharePoint (PL/SQL)

Developing with SharePoint web services is just painful sometimes. In order to delete a file, you must write a CAML (Collaborative Application Markup Language) batch with a delete command in it. The odd thing is you must include the absolute location and the ID of the file. Its odd because the ID just has to be any valid ID of a file, not actually the file you want to delete. Can you imagine the BS code logic behind this? This requires the developer to call the the web service once more to lookup the ID (unless you use a statically typed ID of a known persistent file). The call you must make to lookup this ID also retrieves the file contents, ugg, too much extra work when you obviously don't need it, MS.

In order to retrieve the file ID, I use the 'GetItem' call to the 'Copy' web service. If all goes well you well be returned a large set of attribute "Fields" and the file contents in base64, all we need is the "ID" so use something like the following, replace 'response' with the xml(in clob format) you are returned from the web service. ID can also be found on SharePoint if you enable the ID column for the view.

ID := XMLTYPE('response')
     .EXTRACT('//Fields/FieldInformation[@DisplayName="ID"]/@Value'
     ,'xmlns="http://schemas.microsoft.com/sharepoint/soap/')
     .GETSTRINGVAL();

The SOAP web service call to delete a file. The 'UpdateListItems' method in the 'Lists' web service.
Be sure to enter your 'list_name', 'file_id', and 'http://path_to_file'. Be sure to remove any extra whitespace between the batch tags "because CAML is strongly typed" (because MS cant handle squat).

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soap="http://schemas.microsoft.com/sharepoint/soap/">
   <soapenv:Header/>
   <soapenv:Body>
      <soap:UpdateListItems>
         <soap:listName>list_name</soap:listName>
         <soap:updates>
       <Batch OnError="Continue" 
          ListVersion="1" 
          ViewName="">
         <Method ID="1" Cmd="Delete">
           <Field Name="ID">file_id</Field>
           <Field Name="FileRef">http://path_to_file</Field>
         </Method>
       </Batch>
    </soap:updates>
      </soap:UpdateListItems>
   </soapenv:Body>
</soapenv:Envelope>

Selecting web service methods out of a WSDL using oracle SQL

I was tasked with listing all the methods for all web services that our Sharepoint instance provides. This is about 35 different web services, each with anywhere between 1 and forty methods, so looking up each WSDL and copy/pasting each method would take forever and us programmers are too lazy for repetitive tasks.

I use java to make the call because we have to deal with NTLM authentication, if you don't, there is almost too much information on the web about sending a "GET" using the UTL_HTTP package from oracle. You will be returned XML data describing the web service functionality. If you are cheap, direct your browser to the WSDL and just copy/paste the WSDL xml into the query.

The standard location of the WSDL is http(s)://host.com/pathToService/webservice.asmx?WSDL

To use this query, place your WSDL xml in the placeholder "< WSDL_XML >" below.
The result will be a table of methods.

SELECT SUBSTR(A.METHOD, REGEXP_INSTR(A.METHOD, ':') + 1)
FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://schemas.xmlsoap.org/wsdl/')
             ,'$d/*:definitions/*:message' PASSING XMLTYPE(< WSDL_XML >) 
             AS "d" COLUMNS METHOD VARCHAR2(4000) PATH 'part/@element') A
WHERE A.METHOD NOT LIKE '%Response'

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;

Building SOAP Envelopes in PL/SQL

There are multiple popular ways of communicating with a web service. While my co-worked uses REST (Representational State Transfer) for communicating with a Google API, Im using SOAP (Simple Object Access Protocol) to interact with a SQL Server Reporting Service (SSRS) web service.

Context:
Say I want to pull a list of all objects that exist in the home directory of my SSRS instance. I would look at the server's WSDL (http://location_of_web_service.asmx?WSDL) and find choose the method ListChildren (Side Note: soapUI is an awesome open-source tool for this kind of thing, goolge it).

The WSDL tells us that the 'ListChildren' method takes the parameters 'Item' and 'Recursive.' Item is the folder that you list and Recursive (true/false) is whether or not you want to recursively print the sub-folders. The only way you can figure this out is trial and error, or if you dare, Microsoft documentation.

Parameters:
METHOD: Your SOAP action, example: ListChildren.
PARAMS: Tad trickier. I concatonate the params together then break them apart later due to the fact there may by zero or many. Seperate the parameter name and value with a '$' and multiple name-value pairs with a '^'.

PARAMS example: "parameter1$value1^parameter2$value2"
will yield:
"<namespace:parameter1>value1</namespace:parameter1>
<namespace:parameter2>value2</namespace:parameter2>"

How to use this code:
To use this you will need to set your own envelope_tag and namespace as variables, mine are "rep" and "soap," respectively.

FUNCTION BUILD_ENVELOPE
(
     METHOD VARCHAR2
     ,PARAMS CLOB
          
) RETURN CLOB
     PARALLEL_ENABLE is
     ENVELOPE  CLOB;
     PARAM_SECTION CLOB;
     PARAM_REG VARCHAR2(10) := '[^^]+';
     VALUE_REG VARCHAR2(10) := '[^$]+';

BEGIN
     --Add namespaces to envelope. Add an empty header.
     ENVELOPE := '<' || ENVELOPE_TAG || ':Envelope xmlns:' || ENVELOPE_TAG || '="http://schemas.xmlsoap.org/soap/envelope/" ' 
         || 'xmlns:' || NAMESPACE || '="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices">' 
         || '<' || ENVELOPE_TAG || ':Header>' 
         || '</' || ENVELOPE_TAG || ':Header>' 
         || '<' || ENVELOPE_TAG || ':Body>' 
         || '<' || NAMESPACE || ':' || METHOD || '>';
      
     --each method can take zero or many parameters, this function will take a list as "<param1>$<value1>^<param2>$<value2>^..."
     IF TRIM(PARAMS) IS NOT NULL
     THEN
          FOR I IN 1 .. REGEXP_COUNT(PARAMS, PARAM_REG)
          LOOP
              --Select out the parameter value pair.
              PARAM_SECTION := TRIM(REGEXP_SUBSTR(PARAMS, PARAM_REG, 1, I));
              IF (PARAM_SECTION) IS NOT NULL
              THEN
                   BEGIN  
                        -- Concatonate the parameter-value pair as <namespace:param>value<namespace:param>
                        ENVELOPE := ENVELOPE 
                        || '<' || NAMESPACE || ':' || TRIM(REGEXP_SUBSTR(PARAM_SECTION, VALUE_REG, 1, 1)) || '>' 
                        || TRIM(REGEXP_SUBSTR(PARAM_SECTION, VALUE_REG, 1, 2)) 
                        || '</' || NAMESPACE || ':' || TRIM(REGEXP_SUBSTR(PARAM_SECTION, VALUE_REG, 1, 1)) || '>';
                   END;
              END IF;
          END LOOP;
     END IF;
          
     -- Close the envelope
     ENVELOPE := ENVELOPE 
         || '</' || NAMESPACE || ':' || METHOD || '>' 
         || '</' || ENVELOPE_TAG || ':Body>' 
         || '</' || ENVELOPE_TAG || ':Envelope>';
     RETURN(ENVELOPE);
EXCEPTION
     WHEN OTHERS THEN
          LOG_TO_TABLE('BUILD_ENVELOPE'
              ,'Exception thrown on action: ' || METHOD || ', params: ' || PARAMS || ' error: ' || SQLERRM , 'E');
          RETURN NULL;
          
END BUILD_ENVELOPE;

Tokenizing in Java

So Im playing with the idea of making a compiler built on Java. All reasons why that is a horrible idea aside, I want to.

Problem:
Tokenize a dirty source file.
Ive worked with bison and flex for C++ and know the basics of how they work, I plan on mimicking that functionality.

Situation:
We have a 'Source string' which is the code we are tokenizing(analyzing and breaking down into data thats easier to work with) and we have regex-token pairs. The regex determines what part of the source the token represents.

Boring:
I have a file of regex and the tokens they produce. Just to make some progress I looped through the regex-token pairs, replaced all the regex matches with their respective token's and was left with with a string of tokens. My tokens however where just a few characters surrounded by sideways-carrots (yes thats what I call them) with the literals added something like "<string_constant value="test">".

Fun:
All I can say is that previous part depressed me by how boring it was. So lets have some fun. Instead of repeatedly having to parse text, lets throw each token's data into an object as they're matched by the regex. If we have a collection of these token objects they need to be sorted by their occurrence in the source string, not by the order by which the regex evaluates them. Instead of replacing the token with a string, lets remove the regex and replace it with a CHAR, say, #01 (not null but also not an actual character). Then we take that token object and and put in a map where the key is the memory location of the dummy char. As the source string gets evaluated is should break down into a string of #01 characters. After that, all you have to do is iterate through the string, for each character, pull out the corresponding token object from the map and add it to you're favorite sorted list (mine's ArrayList). BAM!

Although, this is Java, and Java doesn't like to share addresses. I think its time to bust out the java.misc.Unsafe.

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;