Home » SQL & PL/SQL » SQL & PL/SQL » converting oracle datatypes to and from RAW
converting oracle datatypes to and from RAW [message #6] Wed, 02 January 2002 15:57 Go to next message
mike arnold
Messages: 3
Registered: January 2002
Junior Member
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.

Any suggestions????

----------------------------------------------------------------------
Re: converting oracle datatypes to and from RAW [message #9 is a reply to message #6] Thu, 03 January 2002 03:29 Go to previous messageGo to next message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
Hi Mike,

The CAST function is available in Oracle from Oracle9i (9.0.1). See the SQL Reference guide (chapter 6 - functions) for details.

Example: SELECT CAST('02-JAN-2002' AS DATE) FROM dual;

Best regards

Frank Naude

----------------------------------------------------------------------
Re: converting oracle datatypes to and from RAW [message #14 is a reply to message #6] Thu, 03 January 2002 13:55 Go to previous messageGo to next message
mike arnold
Messages: 3
Registered: January 2002
Junior Member
Hi Frank ... I know about the CAST function, but the crux of the problem is that I can't cast RAW to DATE. Any ideas????

----------------------------------------------------------------------
Re: converting oracle datatypes to and from RAW [message #26 is a reply to message #6] Fri, 04 January 2002 07:00 Go to previous messageGo to next message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
Hi,

Look at this example:

create table rawtab (rawtyp varchar2(30), rawdata RAW(30));

insert into rawtab values ('NUMBER', '123');
insert into rawtab values ('DATE', '02012002');

select to_number(cast(rawdata AS varchar2(10))) from rawtab where rawtyp = 'NUMBER';

select to_date(cast(rawdata AS varchar2(10)), 'DDMMYYYY')from rawtab where rawtyp = 'DATE';

Best regards.

Frank Naude

----------------------------------------------------------------------
Re: converting oracle datatypes to and from RAW [message #38 is a reply to message #26] Sun, 06 January 2002 14:17 Go to previous message
mike arnold
Messages: 3
Registered: January 2002
Junior Member
Thanks very much for your reply, Frank. It certainly gets me a lot further than I was before. However, if I understand your example correctly, what you are really storing in the RAW column are the character representations of the values rather than their true binary values. While this is fine for numbers, I can foresee potential problems with dates, in that my application requires comparisons with the data held in the raw column, in particular 'BETWEEN' operations. Unless I can massage all date values into a consistent format, date comparisons are a minefield, which is one of the reasons why I wanted all my data stored in binary in the first place. The application I am working on will be distributed internationally, so my dates are going to be all over the place.

To my way of thinking, all data is stored in binary deep down in the guts of all databases anyway, so why does Oracle make it so hard to get back?

Cheers
Mike
Previous Topic: Subscript Beyond Count
Next Topic: create user
Goto Forum:
  


Current Time: Tue Aug 11 11:47:59 CDT 2020