Concatenate Text Stored in Byte Arrays within BLOB Type Columns

2012010

January 2, 2010

A couple of days ago I received a question on a private message board regarding how to duplicate the variable length text stored by an ERP system in a BLOB type column. The ERP system stores the text as a byte array (one byte per character) that is terminated by an ASCII character 0 (a string termination character). Previous versions of this ERP system actually stored the variable length text in a LONG RAW type column. BLOB type columns are easier to deal with than LONG RAW columns, so I gave the person a simple INSERT… SELECT to essentially copy the BLOB value from one row to a new row:

The above is nice and simple, except that it does not accomplish what the person wanted to have happen.

—

The OP then clarified that he would like to concatenate the text stored in one BLOB type column with the text stored in a BLOB type column found in a different row in the same table:
Row 1 has BLOB bits that says “I AM A NOTE“.
Row 2 has BLOB bits that says “I AM AN ADDITIONAL NOTE.”

The end result should be Row 1 having a BLOB column with “I AM A NOTE. I AM AN ADDITIONAL NOTE.“, terminated with an ASCII 0 and with the total length of the byte array (including the ASCII 0) recorded in the BITS_LENGTH column. Easy, right?

To process will be something like this:

Convert the current BLOB value to a VARCHAR2 with UTL_RAW.CAST_TO_VARCHAR2

Append a space to the above result

Convert the BLOB value to be appended to a VARCHAR2 with UTL_RAW.CAST_TO_VARCHAR2 and append to the above result

Append a CHR(0) character to the end of the above result.

Find the length of the above and enter it into the BITS_LENGTH column.

Use UTL_RAW.CAST_TO_RAW to convert the above VARCHAR2 result back into a BLOB and enter it into the BITS column.

Here is how I showed the OP to find a solution (note that I switched to a different table to simplify the explanation):
First, I find the first two rows in the PART_BINARY table with a non-null long description:

SELECT
PART_ID
FROM
PART_BINARY
WHERE
BITS IS NOT NULL
AND ROWNUM<3;
PART_ID
-------
5P8245
8X8202

The above code sample converts the first 10 long descriptions from the operation binary table, ready to be used as VARCHAR2 data types. If you remove the AND ROWNUM<10 restriction, you could easily create a view using the above:

In the above, note that the VARCHAR_BITS column value is one less than the value in the BITS_LENGTH column – the BITS_LENGTH column value includes the CHR(0) at the end of the column data, while the other length does not. Now, let’s find a victim series of work order lots:

SELECT
VARCHAR_BITS,
VARCHAR_BITS_LENGTH,
BITS_LENGTH,
WORKORDER_LOT_ID
FROM
OPERATION_BINARY_VIEW
WHERE
WORKORDER_TYPE='W'
AND WORKORDER_BASE_ID='11111'
AND WORKORDER_SPLIT_ID='0'
AND WORKORDER_SUB_ID='0'
AND SEQUENCE_NO=10
ORDER BY
WORKORDER_LOT_ID;
VARCHAR_BITS VARCHAR_BITS_LENGTH BITS_LENGTH WO
----------------------------------------------- ------------------- ----------- --
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 1
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 10
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 11
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 12
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 13
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 14
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 15
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 16
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 2
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 3
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 4
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 5
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 6
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 7
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 8
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. 48 49 9

There are 16 lots in this work order base ID, so let’s create an UPDATE statement to append our source engineering master’s operation long description to the long description for these lots:

The inline view in the above combines the source OPERATION_BINARY long description with the long description (aliased as OB2) from the row that will be updated in the OPERATION_BINARY table. The inline view also calculates the new value for the BITS_LENGTH column – I initially thought that it should be +1, not +2, but the following step shows that it should be +2 so that BITS_LENGTH is one greater than VARCHAR_BITS_LENGTH. Now, we can check the outcome of the append:

SELECT
VARCHAR_BITS,
VARCHAR_BITS_LENGTH,
BITS_LENGTH,
WORKORDER_LOT_ID
FROM
OPERATION_BINARY_VIEW
WHERE
WORKORDER_TYPE='W'
AND WORKORDER_BASE_ID='11111'
AND WORKORDER_SPLIT_ID='0'
AND WORKORDER_SUB_ID='0'
AND SEQUENCE_NO=10
ORDER BY
WORKORDER_LOT_ID;
VARCHAR_BITS VARCHAR_BITS_LENGTH BITS_LENGTH WO
------------------------------------------------------------------------------- ------------------- ----------- --
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 1
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 10
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 11
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 12
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 13
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 14
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 15
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 16
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 2
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 3
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 4
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 5
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 6
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 7
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 8
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P. 81 82 9

If we are satisfied with the results, we can issue a COMMIT and check the result in the ERP system. TEST, TEST, TEST before using the above in production.

—

Great, but not quite there yet – the OP further refined the question. He would like to combine the text stored in the BLOBs for SEQUENCE_NO 10, 20, and 30 with the text stored in the BLOB for SEQUENCE_NO 40, and store those results in the row for SEQUENCE_NO 40, for any given combination of WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_SPLIT_ID, and WORKORDER_SUB_ID. Additionally, the OP would like to be able to supply a list of WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_SPLIT_ID, and WORKORDER_SUB_ID to which this combining operation will be performed.

We have now progressed from difficult to impossible (well, almost impossible). Here is what I suggested:

You want to automatically fold the long descriptions stored in BLOB columns for the first three operations into the fourth operation, and you want to be able to do it without repeatedly executing update statements. WARNING – do not try this unless you understand what is happening and how to verify that the correct change is made before issuing the final COMMIT. First, the view from the previous solution will be used:

I created a dummy engineering master and then created 3 work orders from the master: 89890/1 through 89890/3. I then modified the engineering master and created work order 89891/1. These are the work orders I want to fix, like you are wanting to fix (actually, just Sub 0). I insert the rows into the temporary holding table:

Notice in work order 89891/1 I added the number 2 to the end of each long description when I modified the engineering master before creating that work order.

Next, we need to use a trick to collapse 3 rows into a single row so that the long descriptions may be combined. This may be done by using MAX and DECODE with a GROUP BY clause like this (note that I told SQL*Plus to line-wrap the column – just ignore that):

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: