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:
INSERT INTO OPERATION_BINARY SELECT WORKORDER_TYPE, 'NEW_BASE_ID', 'NEW_LOT_ID', 'NEW_SPLIT_ID', 'NEW_SUB_ID', NEW_SEQUENCE_NO, TYPE, BITS, BITS_LENGTH FROM OPERATION_BINARY WHERE WORKORDER_TYPE='M' AND WORKORDER_BASE_ID='ABC123' AND WORKORDER_LOT_ID='123' AND WORKORDER_SPLIT_ID='0' AND WORKORDER_SUB_ID='0' AND SEQUENCE_NO=10;
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
Next, I check the current long descriptions:
SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BITS,32000,1)) BITS FROM PART_BINARY WHERE PART_ID='8X8202'; BITS ----------------------- FROM VENDOR DEL TO ASSY
SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BITS,32000,1)) BITS FROM PART_BINARY WHERE PART_ID='5P8245'; BITS ----------- (CASE ASSY)
Now an experiment to see what the result would look like:
SELECT UTL_RAW.CAST_TO_RAW(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB1.BITS,32000,1)) || ' '||UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB2.BITS,32000,1))||CHR(0)), LENGTH(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB1.BITS,32000,1)) || ' '||UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB2.BITS,32000,1))||CHR(0)) FROM PART_BINARY PB1, PART_BINARY PB2 WHERE PB1.PART_ID='8X8202' AND PB2.PART_ID='5P8245'; ---------------------------------------------------------- 46524F4D2056454E444F522044454C20544F204153535920284341534520415353592900 36
(If you remove the UTL_RAW.CAST_TO_RAW in the above, you will see the actual string).
Now, we can convert the SELECT statement into an UPDATE statement like this:
UPDATE PART_BINARY SET (BITS, BITS_LENGTH)= (SELECT UTL_RAW.CAST_TO_RAW(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB1.BITS,32000,1)) || ' '||UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB2.BITS,32000,1))||CHR(0)), LENGTH(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB1.BITS,32000,1)) || ' '||UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB2.BITS,32000,1))||CHR(0)) FROM PART_BINARY PB1, PART_BINARY PB2 WHERE PB1.PART_ID='8X8202' AND PB2.PART_ID='5P8245') WHERE PART_ID='8X8202';
Then we can test the result of the UPDATE:
SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BITS,32000,1)) BITS FROM PART_BINARY WHERE PART_ID='8X8202'; BITS ----------------------------------- FROM VENDOR DEL TO ASSY (CASE ASSY)
—
The OP further clarified that the above approach works, but there are possibly 3,000 pairs of rows that need to be combined. The original approach probably needs to be scrapped.
I then put together the following demonstration:
For experimentation, note that it is possible to do something like the following:
SELECT WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, SEQUENCE_NO, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB.BITS,32000,1)) VARCHAR_BITS, LENGTH(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB.BITS,32000,1))) VARCHAR_BITS_LENGTH, BITS_LENGTH FROM OPERATION_BINARY OB WHERE TYPE='D' AND ROWNUM<=10;
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:
CREATE OR REPLACE VIEW OPERATION_BINARY_VIEW AS SELECT WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, SEQUENCE_NO, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB.BITS,32000,1)) VARCHAR_BITS, LENGTH(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB.BITS,32000,1))) VARCHAR_BITS_LENGTH, BITS_LENGTH FROM OPERATION_BINARY OB WHERE TYPE='D';
Let’s assume that you have an engineering master that you want to use as the source that will be appended to various work order operations. You could use the view like this to see the source data:
SELECT VARCHAR_BITS, VARCHAR_BITS_LENGTH, BITS_LENGTH FROM OPERATION_BINARY_VIEW WHERE WORKORDER_TYPE='M' AND WORKORDER_BASE_ID='0011881110' AND WORKORDER_LOT_ID='00' AND WORKORDER_SPLIT_ID='0' AND WORKORDER_SUB_ID='0' AND SEQUENCE_NO=10; VARCHAR_BITS VARCHAR_BITS_LENGTH BITS_LENGTH ------------------------------- ------------------- ----------- TACK AND WELD COMPLETE PER B\P. 31 32
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:
UPDATE
OPERATION_BINARY OB2
SET
(BITS,BITS_LENGTH) =
(SELECT
UTL_RAW.CAST_TO_RAW(
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB2.BITS,32000,1))
|| ' ' || SOURCE_OB.VARCHAR_BITS || CHR(0)
) NEW_BITS,
OB2.BITS_LENGTH+SOURCE_OB.VARCHAR_BITS_LENGTH+2 NEW_BITS_LENGTH
FROM
OPERATION_BINARY_VIEW SOURCE_OB
WHERE
SOURCE_OB.WORKORDER_TYPE='M'
AND SOURCE_OB.WORKORDER_BASE_ID='0011881110'
AND SOURCE_OB.WORKORDER_LOT_ID='00'
AND SOURCE_OB.WORKORDER_SPLIT_ID='0'
AND SOURCE_OB.WORKORDER_SUB_ID='0'
AND SOURCE_OB.SEQUENCE_NO=10)
WHERE
OB2.TYPE='D'
AND OB2.WORKORDER_TYPE='W'
AND OB2.WORKORDER_BASE_ID='11111'
AND OB2.WORKORDER_SPLIT_ID='0'
AND OB2.WORKORDER_SUB_ID='0'
AND OB2.SEQUENCE_NO=10;
16 rows updated.
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:
CREATE OR REPLACE VIEW OPERATION_BINARY_VIEW AS SELECT WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, SEQUENCE_NO, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB.BITS,32000,1)) VARCHAR_BITS, LENGTH(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB.BITS,32000,1))) VARCHAR_BITS_LENGTH, BITS_LENGTH FROM OPERATION_BINARY OB WHERE TYPE='D';
Next, we need a temporary holding table for the work orders and sub IDs you would like to modify. Create the following table:
CREATE TABLE TEMP_WO_LIST AS SELECT WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID FROM OPERATION WHERE 1=2;
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:
INSERT INTO TEMP_WO_LIST VALUES ('W','89890','1','0','0');
INSERT INTO TEMP_WO_LIST VALUES ('W','89890','2','0','0');
INSERT INTO TEMP_WO_LIST VALUES ('W','89890','3','0','0');
INSERT INTO TEMP_WO_LIST VALUES ('W','89891','1','0','0');
COMMIT;
OK, there are now 4 work orders listed in the temporary holding table, let’s see the long descriptions for the operations:
SELECT OBV.WORKORDER_BASE_ID, OBV.WORKORDER_LOT_ID, OBV.WORKORDER_SUB_ID, OBV.SEQUENCE_NO, OBV.VARCHAR_BITS FROM OPERATION_BINARY_VIEW OBV, TEMP_WO_LIST TWL WHERE TWL.WORKORDER_TYPE=OBV.WORKORDER_TYPE AND TWL.WORKORDER_BASE_ID=OBV.WORKORDER_BASE_ID AND TWL.WORKORDER_LOT_ID=OBV.WORKORDER_LOT_ID AND TWL.WORKORDER_SPLIT_ID=OBV.WORKORDER_SPLIT_ID AND TWL.WORKORDER_SUB_ID=OBV.WORKORDER_SUB_ID AND OBV.SEQUENCE_NO IN (10,20,30); WORKORDER_ WOR WOR SEQUENCE_NO VARCHAR_BITS ---------- --- --- ----------- ------------ 89890 1 0 10 OP 10 DESC 89890 1 0 20 OP 20 DESC 89890 1 0 30 OP 30 DESC 89890 2 0 10 OP 10 DESC 89890 2 0 20 OP 20 DESC 89890 2 0 30 OP 30 DESC 89890 3 0 10 OP 10 DESC 89890 3 0 20 OP 20 DESC 89890 3 0 30 OP 30 DESC 89891 1 0 10 OP 10 DESC2 89891 1 0 20 OP 20 DESC2 89891 1 0 30 OP 30 DESC2
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):
SELECT OBV.WORKORDER_BASE_ID, OBV.WORKORDER_LOT_ID, OBV.WORKORDER_SUB_ID, MAX(DECODE(OBV.SEQUENCE_NO,10,OBV.VARCHAR_BITS||' ')) || MAX(DECODE(OBV.SEQUENCE_NO,20,OBV.VARCHAR_BITS||' ')) || MAX(DECODE(OBV.SEQUENCE_NO,30,OBV.VARCHAR_BITS)) VARCHAR_BITS, NVL(MAX(DECODE(OBV.SEQUENCE_NO,10,OBV.VARCHAR_BITS_LENGTH))+1,0) + NVL(MAX(DECODE(OBV.SEQUENCE_NO,20,OBV.VARCHAR_BITS_LENGTH))+1,0) + NVL(MAX(DECODE(OBV.SEQUENCE_NO,30,OBV.VARCHAR_BITS_LENGTH)),0) VARCHAR_LENGTH FROM OPERATION_BINARY_VIEW OBV, TEMP_WO_LIST TWL WHERE TWL.WORKORDER_TYPE=OBV.WORKORDER_TYPE AND TWL.WORKORDER_BASE_ID=OBV.WORKORDER_BASE_ID AND TWL.WORKORDER_LOT_ID=OBV.WORKORDER_LOT_ID AND TWL.WORKORDER_SPLIT_ID=OBV.WORKORDER_SPLIT_ID AND TWL.WORKORDER_SUB_ID=OBV.WORKORDER_SUB_ID AND OBV.SEQUENCE_NO IN (10,20,30) GROUP BY OBV.WORKORDER_BASE_ID, OBV.WORKORDER_LOT_ID, OBV.WORKORDER_SPLIT_ID, OBV.WORKORDER_SUB_ID; WORKORDER_ WOR WOR VARCHAR_BITS VARCHAR_LENGTH ---------- --- --- -------------------- -------------- 89890 1 0 OP 10 DESC OP 20 DES 32 C OP 30 DESC 89890 2 0 OP 10 DESC OP 20 DES 32 C OP 30 DESC 89890 3 0 OP 10 DESC OP 20 DES 32 C OP 30 DESC 89891 1 0 OP 10 DESC2 OP 20 DE 35 SC2 OP 30 DESC2
Now, like the final solution earlier, we convert the above into an UPDATE statement:
UPDATE
OPERATION_BINARY OB2
SET
(BITS,BITS_LENGTH)=
(SELECT
UTL_RAW.CAST_TO_RAW(
MAX(DECODE(OBV.SEQUENCE_NO,10,OBV.VARCHAR_BITS||' ')) ||
MAX(DECODE(OBV.SEQUENCE_NO,20,OBV.VARCHAR_BITS||' ')) ||
MAX(DECODE(OBV.SEQUENCE_NO,30,OBV.VARCHAR_BITS||' ')) ||
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB2.BITS,32000,1)) || CHR(0) ) VARCHAR_BITS,
NVL(MAX(DECODE(OBV.SEQUENCE_NO,10,OBV.VARCHAR_BITS_LENGTH))+1,0) +
NVL(MAX(DECODE(OBV.SEQUENCE_NO,20,OBV.VARCHAR_BITS_LENGTH))+1,0) +
NVL(MAX(DECODE(OBV.SEQUENCE_NO,30,OBV.VARCHAR_BITS_LENGTH))+1,0) +
OB2.BITS_LENGTH + 1 VARCHAR_LENGTH
FROM
OPERATION_BINARY_VIEW OBV,
TEMP_WO_LIST TWL
WHERE
TWL.WORKORDER_TYPE=OBV.WORKORDER_TYPE
AND TWL.WORKORDER_BASE_ID=OBV.WORKORDER_BASE_ID
AND TWL.WORKORDER_LOT_ID=OBV.WORKORDER_LOT_ID
AND TWL.WORKORDER_SPLIT_ID=OBV.WORKORDER_SPLIT_ID
AND TWL.WORKORDER_SUB_ID=OBV.WORKORDER_SUB_ID
AND OBV.SEQUENCE_NO IN (10,20,30)
AND OBV.WORKORDER_TYPE=OB2.WORKORDER_TYPE
AND OBV.WORKORDER_BASE_ID=OB2.WORKORDER_BASE_ID
AND OBV.WORKORDER_LOT_ID=OB2.WORKORDER_LOT_ID
AND OBV.WORKORDER_SUB_ID=OB2.WORKORDER_SUB_ID
GROUP BY
OBV.WORKORDER_BASE_ID,
OBV.WORKORDER_LOT_ID,
OBV.WORKORDER_SPLIT_ID,
OBV.WORKORDER_SUB_ID)
WHERE
(WORKORDER_TYPE,
WORKORDER_BASE_ID,
WORKORDER_LOT_ID,
WORKORDER_SPLIT_ID,
WORKORDER_SUB_ID) IN
(SELECT
WORKORDER_TYPE,
WORKORDER_BASE_ID,
WORKORDER_LOT_ID,
WORKORDER_SPLIT_ID,
WORKORDER_SUB_ID
FROM
TEMP_WO_LIST)
AND OB2.SEQUENCE_NO=40;
4 rows updated.
Then we verify that we obtained the desired results:
SELECT OBV.WORKORDER_BASE_ID, OBV.WORKORDER_LOT_ID, OBV.SEQUENCE_NO SEQ, OBV.VARCHAR_BITS, OBV.VARCHAR_BITS_LENGTH VB_LENGTH, OBV.BITS_LENGTH B_LENGTH FROM OPERATION_BINARY_VIEW OBV, TEMP_WO_LIST TWL WHERE TWL.WORKORDER_TYPE=OBV.WORKORDER_TYPE AND TWL.WORKORDER_BASE_ID=OBV.WORKORDER_BASE_ID AND TWL.WORKORDER_LOT_ID=OBV.WORKORDER_LOT_ID AND TWL.WORKORDER_SPLIT_ID=OBV.WORKORDER_SPLIT_ID AND TWL.WORKORDER_SUB_ID=OBV.WORKORDER_SUB_ID ORDER BY OBV.WORKORDER_BASE_ID, OBV.WORKORDER_LOT_ID, OBV.SEQUENCE_NO; WORKORDER_ WOR SEQ VARCHAR_BITS VB_LENGTH B_LENGTH ---------- --- --- -------------------- ---------- ---------- 89890 1 10 OP 10 DESC 10 11 89890 1 20 OP 20 DESC 10 11 89890 1 30 OP 30 DESC 10 11 89890 1 40 OP 10 DESC OP 20 DES 44 45 C OP 30 DESC OP 40 D ESC 89890 2 10 OP 10 DESC 10 11 89890 2 20 OP 20 DESC 10 11 89890 2 30 OP 30 DESC 10 11 89890 2 40 OP 10 DESC OP 20 DES 44 45 C OP 30 DESC OP 40 D ESC 89890 3 10 OP 10 DESC 10 11 89890 3 20 OP 20 DESC 10 11 89890 3 30 OP 30 DESC 10 11 89890 3 40 OP 10 DESC OP 20 DES 44 45 C OP 30 DESC OP 40 D ESC 89891 1 10 OP 10 DESC2 11 12 89891 1 20 OP 20 DESC2 11 12 89891 1 30 OP 30 DESC2 11 12 89891 1 40 OP 10 DESC2 OP 20 DE 48 49 SC2 OP 30 DESC2 OP 4 0 DESC2
In the above, B_LENGTH must be 1 greater than VB_LENGTH. If we are happy, we can issue a COMMIT.
Charles Hooper's Oracle Notes 
Recent Comments