| converting oracle datatypes to and from RAW [message #6] |
Wed, 02 January 2002 15:57  |
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  |
Frank Naude
Messages: 4579
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 #26 is a reply to message #6] |
Fri, 04 January 2002 07:00 |
Frank Naude
Messages: 4579
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
