[PL/SQL] Extract data from XML BLOB

What if you need to query a stored XML, but it was unfortunately stored as a BLOB instead of a CLOB?

You can easily convert it to a CLOB first, then query it with:

 DECLARE  
   
 l_XML XMLTYPE;   
 l_blob BLOB;  
 l_clob CLOB;  
 l_dest_offsset PLS_INTEGER := 1;  
 l_src_offsset PLS_INTEGER := 1;  
 l_lang_context PLS_INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;  
 l_warning PLS_INTEGER;  
   
 l_myData MYTYPE;  
        
 BEGIN  
   
 --get the blob  
 SELECT myBlob  
 INTO l_blob  
 FROM myTable  
 WHERE something;  
   
 --create empty CLOB  
 DBMS_LOB.CREATETEMPORARY(l_clob, true);  
    
 DBMS_LOB.CONVERTTOCLOB(  
      dest_lob => l_clob,  
      src_blob => l_clob,  
      amount => DBMS_LOB.LOBMAXSIZE,  
      dest_offset => l_dest_offsset,  
      src_offset => l_src_offsset,  
      blob_csid => DBMS_LOB.DEFAULT_CSID,  
      lang_context => l_lang_context,  
      warning => l_warning  
 );  
   
 --crea XML from CLOB  
 l_XML := XMLTYPE.CREATEXML(l_clob);  
   
 --extract data from the XML  
 BEGIN  
 SELECT EXTRACTVALUE(  
   l_xml,  
   '//root/node/text()',  --be sure to match your XML tree naming! - /text() is optional, use it if the node content is CDATA
   'xmlns="OPTIONAL_XMLNS_HERE"' --optional, if you don't need it, do not even pass it to the function   
 ) node_value  
 INTO l_myData  
 FROM DUAL;  
 EXCEPTION WHEN NO_DATA_FOUND THEN --optional exception block  
      --something  
 WHEN OTHERS THEN  
      --something else  
 END;  
   
 END;  
   

[PL/SQL] Convert BLOB to CLOB

So you decided for some reasons that a BLOB would be a good type choice to store something for which the CLOB was invented, but now you'd like to take full advantage of all the CLOB functions or you need to query that XML you stored.

After blaming the gods for that poor choice, you may want to ease your pain by converting it to a CLOB:

 DECLARE  
   
 l_blob BLOB;  
 l_clob CLOB;  
 l_dest_offsset PLS_INTEGER := 1;  
 l_src_offsset PLS_INTEGER := 1;  
 l_lang_context PLS_INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;  
 l_warning PLS_INTEGER;  
   
 BEGIN  
   
 SELECT myBlob  
 INTO l_blob  
 FROM myTable  
 WHERE myConditions;  
   
 DBMS_LOB.CONVERTTOCLOB(  
      dest_lob => l_clob,  
      src_blob => l_blob,  
      amount => DBMS_LOB.LOBMAXSIZE,  
      dest_offset => l_dest_offsset,  
      src_offset => l_src_offsset,  
      blob_csid => NLS_CHARSET_ID('YOUR_CHARSET'), --optional, you may also use DBMS_LOB.DEFAULT_CSID if you don't have specific requirements  
      lang_context => l_lang_context,  
      warning => l_warning  
 );  
   
 END;  

You'll then have your converted BLOB in the l_clob variable

[Oracle SQL] SELECT FROM list of string values

In Oracle SQL, you can perform a SELECT from a list of string values using the DBMS_DEBUG package as:

SELECT *
FROM TABLE(SYS.DBMS_DEBUG_VC2COLL('value1', '...'. 'valueN'))

Which will create a table on the fly populating it with the values passed, one for each new row in a single column. Of course, you'll need to be able to execute that package's procedures/functions.

[Microsoft Access] UPDATE table SELECT FROM nested query

For those of you forced to use Microsoft Access, thus needing to write bad SQL, here's how you would write an UPDATE table SELECT FROM subquery statement:

UPDATE A, B
SET A.column = B.column
WHERE A.key = B.key

which is equal to SQL's:

UPDATE A
SET column = (
        SELECT B.column
        FROM B
        WHERE A.key = B.key)

[TIBCO BusinessEvents] Send message to specific receiver on a multi-event queue

In an usual TIBCO BusinessEvents setting, all messages received on a channel are automatically mapped to the specified default event, but this process only works if ALL the messages received represent that particular event.

Since it's also possible to register multiple events on a queue binded to the same channel, for all the non-default events, we have to explicitly specify which event to fire, so that the incoming message is mapped to the correct event.

This can be done by adding two parameters to the message in the header section:

• _ns_ which specifies the namespace. The namespace points to the event type, eg: www.tibco.com/be/ontology/Events/MyEvent
• _nm_ which specifies the name of the event, eg: MyEvent

[TIBCO Spotfire] Export only marked rows from Web Player

Users accessing Spotfire analysis from the Web Player have the ability to export data in multiple formats (assuming they were given the necessary licenses).

It is also possible to only export marked rows by selecting the data of interest, enabling the Details-On-Demand view, and exporting the data from there, simple as that.

[TIBCO Spotfire] Count grouping by columns

With custom expressions in Spotfire, you have more control on how the data is to be handled and displayed. These expressions are also useful when managing calculated values and calculated columns.

One common thing you may want to do, is the equivalent of the SQL statement:

SELECT column1, ..., columnN, COUNT(columnX) FROM table GROUP BY column1, ..., columnN

To count how many rows have a particular value in columnX, grouping the results by the unique values in column1, ..., columnN

In Spotfire, this can be accomplished with the OVER expression:

Count([columnX]) OVER ([column1], ..., [columnN])

If you need a COUNT(DISTINCT(columnX)), you can use UniqueCount([columnX]) instead.