Appendix AO

Overview

UTL_RAW is a package supplied with Oracle since version 7.1.6. It is a utility package developed by the Procedural Gateway development team initially for accessing and converting mainframe data into ASCII, and then later by the replication development team. It contains four functions I use frequently, and you've seen these scattered throughout the book. I am going to cover these four functions only, as they are the ones I find to be of most use. There are other functions contained in the package (thirteen more to be exact), but I won't be covering them here. Check the Supplied PL/SQL Packages Reference for more details on these.

The four functions I will cover are:

• CAST_TO_VARCHAR2 - Converts a RAW to a VARCHAR2

• CAST_TO_RAW - Converts a VARCHAR2 to a RAW

• LENGTH - Returns the length of a RAW variable

• SUBSTR - Returns a substring of a RAW variable

We use these functions heavily when dealing with binary data. This can be seen in the CRYPT_PKG we use in the sections on DBMS_OBFUSCATION_TOOLKIT, DBMS_LOB, and UTL_TCP .

We'll start with the CAST_ functions. These simply change the type field of a RAW variable to be VARCHAR2, and vice-versa. They do this without any translation of the data contained in the variable whatsoever. Normally, if I assign a RAW to a VARCHAR2, the VARCHAR2 would be twice as long as the RAW was, and would contain hexadecimal digits. Each byte of the RAW would be converted to hexadecimal (we took advantage of this translation in the DBMS_OBFUSCATION_TOOLKIT routine for example, to display encrypted data in hexadecimal on screen). In the cases where we do not desire this translation to take place, the CAST_TO_VARCHAR2 function comes in handy. To see what it does, we can use the DUMP SQL function as follows:

tkyte@TKYTE816> create table t ( r raw(10) ); Table created.      tkyte@TKYTE816> insert into t values ( utl_raw.cast_to_raw('helloWorld' ) ); 1 row created.      tkyte@TKYTE816> select dump(r) r1, dump(utl_raw.cast_to_varchar2(r)) r1    2              from t;      R1                             R1 ------------------------------ ------------------------------ Typ=23 Len=10:                 Typ=1 Len=10: 104,101,108,108,111,87,111,114 104,101,108,108,111,87,111,114 ,108,100                       ,108,100 

As you can see from the DUMP, the only thing that changed about the data was the TYP of it. It changed from 23 to 1. If you go to the Oracle Call Interface Programmer's Guide and look at the Internal Datatypes chart, you will find that type 23 is a RAW up to 2000 bytes in length, and type 1 is a VARCHAR2 up to 4000 bytes. The only thing CAST_TO_VARCHAR2 does is to change the data type flag in the variable - it does not touch the data at all. This is exactly what we need in the case where we use DBMS_LOB.SUBSTR on a BFILE, and this BFILE happens to contain 'clear text'. We need to convert this RAW into a VARCHAR2 without it being converted into hexadecimal - we only need change the data type.

UTL_RAW.CAST_TO_RAW goes the other way. If you have a VARCHAR2 that you need treated as a RAW, this will convert it by changing the type, and nothing else. We use this in our SIMPLE_TCP_CLIENT implementation, in the section on the UTL_SMTP supplied package. Externally, the PL/SQL client is sending us VARCHAR2 data, but the Java layer needs byte arrays (RAWs). PL/SQL does this conversion easily for us.

The last two functions of note are UTL_RAW.LENGTH and UTL_RAW.SUBSTR. When we have a RAW and send it to the built-in routines LENGTH and SUBSTR, the RAW will be implicitly converted to a VARCHAR2 (into hexadecimal) first. Unfortunately, the built-in functions are not overloaded to accept and receive RAW types, but rather they convert them into VARCHAR2. This means the return from LENGTH would always be twice the size, SUBSTR would always return a hexadecimal string, and we would have to fix the offset and length parameters as well. The UTL_RAW functions supply this missing functionality for us. They are equivalent to the following SQL:

tkyte@TKYTE816> select utl_raw.length(r), length(r)/2 from t;      UTL_RAW.LENGTH(R) LENGTH(R)/2 ----------------- -----------                10          10      tkyte@TKYTE816> select utl_raw.substr(r,2,3) r1,   2         hextoraw(substr(r,3,6)) r2   3    from t   4  /      R1         R2 ---------- ---------- 656C6C     656C6C