Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: field types possible with Oracle
A copy of this was sent to RPrell_at_PacBell.Net (Robert Prell)
(if that email address didn't require changing)
On Tue, 17 Nov 1998 23:38:49 GMT, you wrote:
>ok, sorry, let me expand. I want to store a true / false into a field
>that I called a bit or binary. I want to take up only one bit of
>space is this possible? Char (1) is not an answer to this question.
>I only want to allow one bit, no larger possible.
>
If you look at how a bit type in other databases is actually physically stored -- it will be stored physically in at least a byte (you cannot store just a bit and have the rest of the bits in the block shift over by 7 bits -- storing a bit type on disk will take a byte, although you might be able to pack >1 bit type into the same byte)... So, "I want to take up only one bit of space" is not possible for anyone -- once you put the bit type into a program variable -- its going to be a BYTE as well (unless you put it into something like a bit field in C in which case you could put 8 bits into 1 byte but in general, selecting a bit type into a program variable is going to take a byte...)
so given that, there exists a series of bit twiddling functions you can apply on RAWS. RAW in Oracle7 can be 1-255 bytes when stored in a table and 1-32760 bytes in a stored procedure. RAW in Oracle8 can be 1-2000 bytes when stored in a table and 1-32760 bytes in a stored procedure. Here is an example of using these funtions in sql (note the use of hextoraw('xx') to produce some 'raw' binary data:
SQL> select utl_raw.bit_and( hextoraw('01'), hextoraw( '03' ) ) from dual;
UTL_RAW.BIT_AND(HEXTORAW('01'),HEXTORAW('03'))
SQL> select utl_raw.bit_or( hextoraw('01'), hextoraw( '03' ) ) from dual;
UTL_RAW.BIT_OR(HEXTORAW('01'),HEXTORAW('03'))
SQL> select utl_raw.bit_xor( hextoraw('01'), hextoraw( '03' ) ) from dual;
UTL_RAW.BIT_XOR(HEXTORAW('01'),HEXTORAW('03'))
>Memo/text, I want to have a field that contains text that does not
>have a size limit. One time there are 200 characters, another there
>are 20000.
>
Oracle7 Oracle8 char 255 2000 varchar 2000 4000 long 2gig 2gig clob NA 4gig (excellent piecewise access from any language, including stored procedures -- you can read and write the entire Character Large OBject in stored procs or any language you want)
>Intiger, again I want the SQL server to only accept intigers, no other
>numbers are allowed! Is this possible?
>
You can enforce this via a check constraint or let the default conversion from 'float' to 'integer' take place on inserts. If you declare a type in a table of INTEGER, and you attempt to put 1.1 into it, it will have the same effect as if you coded in C:
float f = 1.1;
int i;
i = f;
i will have the value 1 ( the value you get converting the float 1.1 into an int). If you do not like this behaviour, you can use a check constraint to catch and raise an error. for example, showing the effects of an INTEGER type with 1.1 and a number with a check constraint. both examples only allow integers into the database:
SQL> create table t ( x integer );
Table created.
SQL> insert into t values ( 1.1 );
1 row created.
SQL> select * from t;
X
1
SQL> drop table t;
Table dropped.
SQL> create table t ( x number check ( trunc(x) = x ) ); Table created.
SQL> insert into t values ( 1.1 );
insert into t values ( 1.1 )
*
ERROR at line 1:
ORA-02290: check constraint (TKYTE.SYS_C0030636) violated
SQL> insert into t values (1);
1 row created.
SQL> select * from t;
X
1
>On Tue, 17 Nov 1998 22:50:35 GMT, RPrell_at_PacBell.Net (Robert Prell)
>wrote:
>
>>Does oracle support field types of;
>>
>>Binary/bit?
>>
>>Memo/text?
>>
>>Intiger?
>>
>>If not what field types do you suggest I use. Also if anyone has a
>>list of allowed field types for Oracle 8.05 or better It would be
>>greatly appreciated!
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Nov 17 1998 - 19:34:41 CST