[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.

[TIBCO Spotfire] Filter only unique values in data to display

When configuring a data source, you have multiple options to limit the data in the display visualization. One of those is Limit data using expression, which allows you to write simple expressions that will always be evaluated, regardless of the filters the user may set.

One thing it's possible to accomplish this way, is the equivalent of the SQL statement:

SELECT DISTINCT(column1, ..., columnN) FROM mytable

to only display tuples with the same values in the specified columns once, regardless of any other different values they may have in other columns.

In Spotfire, you can do this with the Rank function:

Rank(Rowid(), "Asc", [column1], ..., [columnN]) = 1

[TIBCO Spotfire] Write data to database

TIBCO Spotfire primarily reads data from a DB to populate analysis and reports, but it is also possible to have it write data back on the database.

Doing so is fairly easy; the key point is to remember that the last operation in the Information Link MUST return data back. This means that your procedure that performs the desired operation (INSERT, UPDATE, DELETE) MUST return data back, even if usually you wouldn't do that in standard SQL.

[PL/SQL] Duplicate rows changing column values

Imagine you have a table user_accesses like this:

NAME--SURNAME--DEPARTMENT
John--Smith----Marketing;Sales
Mary--Ann------Accounting;Something;Something_else

And you want to split the department column so that each row only has one value instead of a semi-colon separated list.

The following script replicates the rows where the specified column has multiple values, replicating all the data in the other columns. The specified column will be populated with each value in the list (replicated values are not discarded).

[PL/SQL] INSERT row as ROWTYPE

You can perform an INSERT passing a ROWTYPE value in Oracle versions greater than 9 with:

INSERT INTO mytable VALUES myrowtypeval;