I am writing an application which stores data comprising a mixture of strings, dates and numerics in a single column. The table looks like this:
'CREATE TABLE FRED(MY_ROW_ID NUMBER(4) NOT NULL PRIMARY KEY,COL_ID NUMBER(4) NOT NULL, BLURB RAW(255) NOT NULL)'
Where COL_ID can tell me the underlying data type of the RAW data stored in the BLURB column of a given row. Naturally I also need to convert the RAW data back to the original data type.
This works beautifully in SQL Server, but as usual, Oracle makes life difficult. While I can get data into the BLURB column by using RAWTOHEX and HEXTORAW, I can't convert the RAW data back to the native data type. What I would like to do, and SQL Server does this very nicely, is, for eg, 'CAST(BLURB AS DATE)' or 'CAST(BLURB AS NUMBER(4))' etc.
Obviously I know how to read the BLURB back as a hex string, but that seems to be as far as I can go.