Re: LONG RAW fields

spelegan_at_csc.com [Stew Pelegan] wrote:

>I have some *.GIF and *.JPEG files I'd like to store in some LONG RAW fields.
>I saw some mention of HEXTORAW and RAWTOHEX but seems to be character
>functions. What's the best way to store and retrieve LONG RAW data?

I'm finding Oracle's support of RAW and LONG RAW to be spotty, at best. Tables can be constructed to include them, but (1) you can't manipulate or even reference them in a PL/SQL trigger, other than through conversion to VARCHAR, and (2) export/import operations on tables including RAW data often fail.

For (1), you run into the got'ya that VARCHAR2 columns in tables are of limited length. Approximately 2K, I believe. So to store a RAW into VARCHAR2 actually takes a fairly large set of columns or tuples to accomodate a single reasonably sized image.

For (2), you really get yourself into a bind. You may export data that you later can't (re)import. And even worse, if the import fails due to buffer overrun, you can't get at _any_ of the remaining data in the database. Oracle gives you a buffer size parameter that you can play with on import, but it screws up other aspects of the import process. You're caught in a catch-22 situation.

So far, the only "reasonable" alternative that I've been able to arrive at the is supportable in a production environment is to proceed as follows:

1. split the data column that you would normally store as a LONG RAW column value out into its own table with a sequence number generated key. Since that table would be in 1:1 corrospondence with the now-parent, store the id-field that was generated in the parent.
2. define the child table as a key, an internal sequence (1 to n), and a VARCHAR2 column of 2000 bytes.
3. transform the LONG RAW applicable data into char form using rawtohex in a Pro*C application, splitting it into chucks of 2k.
4. number each chunk as you store it into the child table.

Obviously, you simply retrieve the chunks with an Order By clause to reassemble the image in character form, then run it through hextoraw to transform it back to its original image. Again, probably a problem for a Pro*C application.

You can now use referential integrity and cascading deletes to assist in the maintenance of the parent-child relationship. You should have no problems with import/export of data, and the tables/columns can be manipulated from triggers. Unfortunately, you can't embed the RAW<->VARCHAR transform in trigger so that the application developers don't have to deal with it.

Not pretty, but IMHO Oracle's support of binary/image information is _really_ weak to date. I'm hoping for improvement as they begin shipping the OLAP ready extensions in 7.3 (February, I believe).

Hope this helps.

==> Has anyone out there found a better solution to this problem??? Mark A. Scarton, ABD
CompUtah!, Park City, Utah USA
Home: 801.565.9835
Office: 801.265-4612 Received on Tue Jan 23 1996 - 00:00:00 CET