Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: field types possible with Oracle

Re: field types possible with Oracle

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 18 Nov 1998 01:34:41 GMT
Message-ID: <365220c2.1834207@192.86.155.100>


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'))



01

SQL> select utl_raw.bit_or( hextoraw('01'), hextoraw( '03' ) ) from dual;

UTL_RAW.BIT_OR(HEXTORAW('01'),HEXTORAW('03'))



03

SQL> select utl_raw.bit_xor( hextoraw('01'), hextoraw( '03' ) ) from dual;

UTL_RAW.BIT_XOR(HEXTORAW('01'),HEXTORAW('03'))



02

>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  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US