Oracle in World: Data Type

Showing posts with label Data Type. Show all posts

Saturday, December 5, 2009

Temporary Tables and Data Types Exercises

I N D I V I D U A L E X E R C I S E S

HANDS-ON #1: Temporary tables

Your project is complete and now you are ready to start testing. Your application reads data from one table, processes portions of the data and makes some changes, then inserts the changed data into two additional tables before committing the work. Errors are captured in an external SPOOL file for evaluation and aid in debugging after each test of the application. The process of checking the errors, recording the data, and then deleting the records before the next test run of the application is somewhat time-consuming. The notion of creating a set of temporary tables for the output tables seems to be a good solution.
For this part of the exercise, write and execute the SQL commands to create two temporary tables.

The first table should be named CUSTORD_TEMP and should have columns for customer name and total order amount.
The second table should be named CUSTORDTOT_TEMP and should have columns for customer order number, total state tax, and total shipping charges.

In your own words, explain how you might use these tables for testing your application.
Place and save your answers in a Word document named week5_exercise.doc. You will also have a script and output file that you will include with your other files to turn in.

HANDS-ON #2: New data types

You are going to create a new table to be housed in the USERS01 tablespace. The table name will be PRECIOUS_METAL_PRICE and it will be used to store a history of the price of various precious metals. New rows are added to the table on a one-every-two-hours ratio. The table needs to have a relationship back to a parent table named METALS, which lists various precious metals like Gold, Silver, Zinc, etc. The table has a four-byte primary key ID number (METAL_ID) and a description of the metal. As you write the CREATE TABLE statement for this new table, you need to address the following points: The columns are named PRICE, PRICE_DATETIME, and TIME_BETWEEN. The following offers some additional direction on how the table should be created.

The PRICE can contain fractions of a penny down to thousandths of a penny, but the whole price will always be under 1000 dollars. A column named TIME_BETWEEN will store the number of days (up to 99 days), hours, minutes, and seconds (to the hundredth of a second) between the current record and the previous record. The table is never updated; only new data is inserted. Therefore, you want to minimize the storage space saved for updates.
Taking an average row length as 24 bytes, and an average of 12 inserted records per day, you want the table to be created with enough storage space for approximately 6 months; but at the same time, you do not want to waste unused space (try to be as exact as you can).

You do not need to run this part of the exercise in your database, unless you want to create the METALS table as well.

Related Documents

Basic Oracle Sql Exercise
Practice Exercise for Oracle PL/SQL
Oracle Procedure and Function Practice
Exercise Oracle package and package body
Database design practice for a Movie Company
Oracle Enterprise Manager Excercises
Oracle Database Installation Exercises
Oracle Database Creation Exercises
Database Administration Exercises - Week 3
Temporary Tables and Data Types Exercises
Exercises with Oracle Create Table Add Columns
Practice with User Profiles and User Roles in Oracle
Practice Oracle Joins Examples
Oracle Object type Exercises - VARRAY
Audit and Profile Management Exercises in Oracle

Thursday, August 27, 2009

Does oversize of datatype VARCHAR2 causes performance problem

From the beginning of learning Oracle SQL you have possibly heard that in case of VARCHAR2 datatype it allocates space exactly what it needs. So if you allocates 4000 bytes of VARCHAR2 data type and database needs 10 bytes only then exactly 10 bytes are allocated.

That is, in case of VARCHAR2(4000) and VARCHAR2(16) columns, if we store less then 16 bytes data in these two columns then same amount of space will be allocated, and performance should be the same. But, have you ever tested it? I got a funny example http://hrivera99.blogspot.com/2008/05/why-is-varchar2-oversizing-bad.html here. There it is said performance problem but in reality there is not. In the example it is shown problem in physical reads but I don't agree with the example. In fact in the first example it is cached data and hence physical reads is reduced.

In the following section I simulate same example and see no performance differences. However there may rise, http://arjudba.blogspot.com/2008/09/ora-01450-maximum-key-length-3215.html while creating index in case of bigger VARCHAR2 length.

The most misleading example can be created by omitting
"
ALTER TABLESPACE EXAMPLE OFFLINE;

ALTER TABLESPACE EXAMPLE ONLINE;"

If you omit this step you may get different result as data become cached. And you need to take tablespace offline in order to get most accurate result as offlining a tablespace uncache of corresponding tablespace data.

Step 1)Create varchar2_length_test table with VARCHAR2(4000) and insert data into it.

SQL> create table varchar2_length_test(
2  ID                  NUMBER,
3  COL2        VARCHAR2(4000),
4  COL3        VARCHAR2(4000),
5  COL4        VARCHAR2(4000),
6  COL5        VARCHAR2(4000),
7  COL6        VARCHAR2(4000),
8  COL7        VARCHAR2(4000),
9  COL8        VARCHAR2(4000),
10  COL9        VARCHAR2(4000),
11  COL10        VARCHAR2(4000),
12  COL11        VARCHAR2(4000),
13  COL12        VARCHAR2(4000),
14  COL13        VARCHAR2(4000)) TABLESPACE EXAMPLE;

Table created.

SQL>
SQL> begin
2  for i in 1 .. 100000
3  LOOP
4  INSERT into varchar2_length_test VALUES(
5  i, i||'Col2',i||'Col3',i||'Col4',i||'Col5',i||'Col6',i||'Col7',i||'Col8',i||'Col9',i||'Col10',i||'Col11',i||'Col12',
6  i||'Col13');
7  END LOOP;
8  END;
9  /

PL/SQL procedure successfully completed.

Step 2)Create varchar2_length_test_short table with VARCHAR2(16) and insert data into it.

SQL> create table varchar2_length_test_short(
2  ID                  NUMBER,
3  COL2        VARCHAR2(16),
4  COL3        VARCHAR2(16),
5  COL4        VARCHAR2(16),
6  COL5        VARCHAR2(16),
7  COL6        VARCHAR2(16),
8  COL7        VARCHAR2(16),
9  COL8        VARCHAR2(16),
10  COL9        VARCHAR2(16),
11  COL10        VARCHAR2(16),
12  COL11        VARCHAR2(16),
13  COL12        VARCHAR2(16),
14  COL13        VARCHAR2(16)) TABLESPACE EXAMPLE;

Table created.

SQL> begin
2  for i in 1 .. 100000
3  LOOP
4  INSERT into varchar2_length_test_short VALUES(
5  i, i||'Col2',i||'Col3',i||'Col4',i||'Col5',i||'Col6',i||'Col7',i||'Col8',i||'Col9',i||'Col10',i||'Col11',i||'Col12',
6  i||'Col13');
7  END LOOP;
8  END;
9  /

PL/SQL procedure successfully completed.

Step 3)Clear caching in the tablespace.

SQL> ALTER TABLESPACE EXAMPLE OFFLINE;
Tablespace altered.

SQL> ALTER TABLESPACE EXAMPLE ONLINE;
Tablespace altered.

Step 4)Enable tracing and look at statistics

SQL> SET AUTOT TRACE
SQL> select count(*) from varchar2_length_test;

1 row selected.


Execution Plan
----------------------------------------------------------                                                                                 
Plan hash value: 1500664439                                                                                                                

-----------------------------------------------------------------------------------                                                        
| Id  | Operation          | Name                 | Rows  | Cost (%CPU)| Time     |                                                        
-----------------------------------------------------------------------------------                                                        
|   0 | SELECT STATEMENT   |                      |     1 |   418   (2)| 00:00:06 |                                                        
|   1 |  SORT AGGREGATE    |                      |     1 |            |          |                                                        
|   2 |   TABLE ACCESS FULL| VARCHAR2_LENGTH_TEST | 88364 |   418   (2)| 00:00:06 |                                                        
-----------------------------------------------------------------------------------                                                        

Note                                                                                                                                       
-----                                                                                                                                      
- dynamic sampling used for this statement                                                                                              


Statistics
----------------------------------------------------------                                                                                 
29  recursive calls                                                                                                               
1  db block gets                                                                                                                 
1980  consistent gets                                                                                                               
1912  physical reads                                                                                                                
176  redo size                                                                                                                     
411  bytes sent via SQL*Net to client                                                                                              
396  bytes received via SQL*Net from client                                                                                        
2  SQL*Net roundtrips to/from client                                                                                             
0  sorts (memory)                                                                                                                
0  sorts (disk)                                                                                                                  
1  rows processed                                                                                                                

SQL> ALTER TABLESPACE EXAMPLE OFFLINE;

Tablespace altered.

SQL>
SQL> ALTER TABLESPACE EXAMPLE ONLINE;

Tablespace altered.

SQL> select count(*) from varchar2_length_test_short;

1 row selected.


Execution Plan
----------------------------------------------------------                                                                                 
Plan hash value: 161270611                                                                                                                 

-----------------------------------------------------------------------------------------                                                  
| Id  | Operation          | Name                       | Rows  | Cost (%CPU)| Time     |                                                  
-----------------------------------------------------------------------------------------                                                  
|   0 | SELECT STATEMENT   |                            |     1 |   418   (2)| 00:00:06 |                                                  
|   1 |  SORT AGGREGATE    |                            |     1 |            |          |                                                  
|   2 |   TABLE ACCESS FULL| VARCHAR2_LENGTH_TEST_SHORT |   109K|   418   (2)| 00:00:06 |                                                  
-----------------------------------------------------------------------------------------                                                  

Note                                                                                                                                       
-----                                                                                                                                      
- dynamic sampling used for this statement                                                                                              


Statistics
----------------------------------------------------------                                                                                 
29  recursive calls                                                                                                               
1  db block gets                                                                                                                 
1993  consistent gets                                                                                                               
1912  physical reads                                                                                                                
176  redo size                                                                                                                     
411  bytes sent via SQL*Net to client                                                                                              
396  bytes received via SQL*Net from client                                                                                        
2  SQL*Net roundtrips to/from client                                                                                             
0  sorts (memory)                                                                                                                
0  sorts (disk)                                                                                                                  
1  rows processed

So we see in both VARCHAR2(4000) and VARCHAR2(16) almost same consistent gets and physical reads. So oversize of varchar2 does not cause performance problem issue but lead to other problems.

Related Documents

ORA-01450: maximum key length (3215) exceeded

Numeric Datatype in Oracle with Examples

Oracle datatype internal code representation

ROWID and UROWID Datatype in Oracle

Char, Varchar2, Long etc Datatype Limits in Oracle

Thursday, September 25, 2008

ORA-01450: maximum key length (3215) exceeded

Error Description
Whenever I try to rebuild index online then it fails with message ORA-00604: error occurred at recursive SQL level 1 along with ORA-01450: maximum key length (3215) exceeded. Below is the scenario.
SQL> create table tab1(a varchar2(3000),b varchar2(2000));
Table created.

SQL> create index tab1_I on tab1(a,b);
Index created.

SQL> alter index tab1_I rebuild online;
alter index tab1_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

Let's now create one table with column length 3000+199=3199 bytes and see what happens.
SQL> create table tab3(a varchar2(3000),b varchar2(199));
Table created.

SQL> create index tab3_I on tab3(a,b);
Index created.

Try to rebuild it online and it works.
SQL> alter index tab3_I rebuild online;
Index altered.

Now just add extra 1 bytes on column b. And whenever we try to rebuild it online it will fail.

SQL> alter table tab3 modify b varchar2(200);
Table altered.

SQL> alter index tab3_I rebuild online;
alter index tab3_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

Cause of the Problem
When creating a index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.
If 2K block size then maximum index key length=758
If 4K block size then maximum index key length=1578
If 8K block size then maximum index key length=3218
If 16K block size then maximum index key length=6498
How the maximum index key length is measured by?
Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)

The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.

So we can say that the maximum key length for an index will be less than half of
the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.

Solution of the Problem
The causes already indicates what might be the solutions. Solution may be,
1)Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.

2)If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.

3)Rebuild the index without online clause. That is
ALTER INDEX index_name REBUILD;
Because The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.

Sunday, September 14, 2008

How to move LOB data to another tablespace

We know with the ALTER TABLE .. MOVE clause we can relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment.

If you want to make no other changes to the table other than rebuilding it then your statement is simply,
SQL>ALTER TABLE table_name MOVE;

Or if you want to move it to another tablespace then specify,
SQL>ALTER TABLE table_name MOVE TABLESPACE tablespace_name;

With this statement it does not affect any of the lob segments associated with the lob columns in the table.

If you want to move only lob segment to a new tablespace then your command will be,

ALTER TABLE table_name MOVE LOB(lob_columnname) STORE AS (TABLESPACE new_tablespace_name);

Along with the log segment you can also move the table as well as storage attribute of table and log by following query,

ALTER TABLE table_name MOVE
TABLESPACE new_tablespace STORAGE(new_storage)
LOB (lobcol) STORE AS
(TABLESPACE new_tablespace STORAGE (new_storage));

If you want to move all the lobs contained in a tablespace of a particular user then you can follow .

Let's have a look lob column_name and table_name of the specified tablespace of owner ARJU.

SQL> col COLUMN_NAME format a20
SQL> col TABLE_NAME format a20
SQL>  select owner, table_name, column_name from dba_lobs where segment_name in (select segment_name from dba_segments where tablespace_name='USERS' and segment_type='LOBSEGMENT' and owner='ARJU');

OWNER                          TABLE_NAME           COLUMN_NAME
------------------------------ -------------------- --------------------
ARJU                           TEST_LONG_LOB        B
ARJU                           LOB_TAB              COL2_LOB
ARJU                           LOB_TAB2             COL3
ARJU                           LOB_TAB2             COL2_LOB

set pagesize 0
set heading off
set lines 130
set feedback off
set verify off
set echo off
set termout off
spool move_table.scr
select 'alter table '||owner||'.'||table_name ||' move lob (' ||column_name||')' ||
'store as (tablespace DATA02);' from dba_lobs where segment_name in (select segment_name from dba_segments where tablespace_name='USERS' and owner='ARJU' and segment_type='LOBSEGMENT');
spool off

Now execute the script move_table.scr after modifying it.
SQL>@move_table.scr

Related Documents
Large Object (LOB) Datatypes with Example.
Advantages of LOB against LONG- Feature of LOB datatype
How to Insert Blob data(image, video) into oracle BLOB size
How to Convert Long data type to Lob

Tuesday, July 22, 2008

Char, Varchar2, Long etc Datatype Limits in Oracle

1)BFILE:
Maximum size: 4 GB-1 which is power(2,32)-1 bytes.
Maximum size of the file name: 255 characters
Maximum size of the directory name: 30 characters
Maximum number of open BFILEs: Limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which itself is limited by the maximum number of open files the OS will allow.

2)BLOB:
Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter/ LOB Chunk size.

So, if db_block_size=8024K then maximum size=32T
SQL> select 4*1024*1024*1024*8*1024/1024/1024/1024/1024 from dual;
4*1024*1024*1024*8*1024/1024/1024/1024/1024
-------------------------------------------
32

As database block size vary from 2K to 32K so BLOB size can vary from 8TB to 128TB.
(8 TB to 128 TB)

Number of LOB columns per table: Limited by the maximum number of columns per table where maximum can be 1000.

3)CHAR:
Maximum size: 2000 bytes
Minimum and Default Size: 1 byte

4)CHAR VARYING
Maximum size: 4000 bytes

5)CLOB
Same as BLOB in the range of 4T to 128T.

6)Literals (characters or numbers in SQL or PL/SQL)
Maximum size: 4000 characters

7)LONG
Maximum size: 2 GB - 1

8)NCHAR
Maximum size: 2000 bytes

9)NCHAR VARYING
Maximum size: 4000 bytes

10)NCLOB
Same as BLOB in the range of 4T to 128T

11)NUMBER
Maximum size: 999...(in this way 38 9s) * power(10,125)
Minimum size: -999...(in this way 38 9s) *power(10,125)

12)RAW
Maximum size: 2000 bytes

13)VARCHAR
Maximum size: 4000 bytes
Minimum size: 1 byte.

14)VARCHAR2
Maximum size: 4000 bytes
Minimum size: 1 byte.

Related Documents

Large Object (LOB) Datatypes with Example.
Advantages of LOB against LONG- Feature of LOB datatype
How to move LOB data to another tablespace
How to Insert Blob data(image, video) into oracle BLOB size
How to Convert Long data type to Lob

Tuesday, July 1, 2008

Advantages of LOB against LONG- Feature of LOB datatype

•LOB datatypes can be attributes of user defined datatypes.
•LOB locator is stored in the table column, either with or without the actual LOB value.
•Actually whenever LOB data is accessed the LOB locator is returned.
•LOB datatype supports transactional query, commit, rollback, update.
•In a table more than one column can have LOB datatype.
•Declare of LOB bind variable is possible.
•You can insert a LOB value with an existing LOB row datatype.
•You can update and delete a LOB row based on another LOB data type.

Monday, June 30, 2008

Datetime and Interval Datatypes Description in Oracle

DateTime DataTypes
--------------------------------------
1)DATE Datatype
----------------------------------
•To store date and time in a table you can use DATE datatype in oracle.
•To insert DATE datatype in a table you have to use either date value as a literal or convert by TO_DATE funcation.
An example,

SQL> create table a_t (a date);
Table created.
As a literal,
SQL> insert into a_t values ( DATE '11-02-07');
1 row created.
Using TO_DATE function,
SQL> insert into a_t values (to_date('10-02-07','DD-MM-yy'));
1 row created.

SQL> select * from a_t;
A
---------
07-FEB-11
10-FEB-07

2)TIMESTAMP Datatype
---------------------------------------
•It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values.
The fields are discussed in http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes-in.html
•It is an extension of DATE datatype.
•To convert character data to timestamp values use TO_TIMESTAMP function.
3)TIMESTAMP WITH TIME ZONE Datatype
------------------------------------------------
•TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone offset in its value.
•This datatype is really useful for collecting and evaluating date information across geographic regions.

4)TIMESTAMP WITH LOCAL TIME ZONE Datatype
----------------------------------------------------------
•TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that includes a time zone offset in its value.
•This datatype differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone.

Interval DataTypes
-----------------------------------------
1)INTERVAL YEAR TO MONTH Datatype
---------------------------------------------
•This datatype stores a period of time using the YEAR and MONTH datetime fields.

•When we want to store the difference between two datetime values in terms of year and months then we can use this datatype.

2)INTERVAL DAY TO SECOND Datatype
---------------------------------------------------
•This datatype stores a period of time in terms of days, hours, minutes, and seconds.

•This datatype is useful for representing the actual difference between two datetime values.

Examples:
------------------------
SQL>CREATE TABLE with_date_interval (date_dt DATE, timest_dt TIMESTAMP, timest_wtz TIMESTAMP WITH TIME ZONE, timest_wltz TIMESTAMP WITH LOCAL TIME ZONE,
int_1 INTERVAL YEAR TO MONTH, int_2 INTERVAL DAY TO SECOND);
Table created.

SQL> desc with_date_interval;
Name Null? Type
----------------------------------------- -------- ----------------------------
DATE_DT DATE
TIMEST_DT TIMESTAMP(6)
TIMEST_WTZ TIMESTAMP(6) WITH TIME ZONE
TIMEST_WLTZ TIMESTAMP(6) WITH LOCAL TIME
ZONE
INT_1 INTERVAL YEAR(2) TO MONTH
INT_2 INTERVAL DAY(2) TO SECOND(6)

SQL>insert into with_date_interval values(DATE '11-01-08', SYSTIMESTAMP, SYSTIMESTAMP, SYSDATE,INTERVAL '10-2' YEAR(3) TO MONTH, INTERVAL '7 8:10:10.100' DAY TO SECOND(3)) ;
1 row created.

SQL> select * from with_date_interval;

DATE_DT TIMEST_DT TIMEST_WTZ TIMEST_WLTZ INT_1 INT_2
-------------------- --------------------
08-JAN-11 01-JUL-08 02.19.20.238715 AM 01-JUL-08 02.19.20.238715 AM -04:00 01-JUL-08 02.19.20.000000 AM +10-02 +07 08:10:10.100000

Related Documents:
------------------------
Datetime and Interval Datatypes Fields and Values in Oracle

ROWID and UROWID Datatype in Oracle

ROWID Datatype
-------------------------
•Each row stored in a table has an address. You can see a row address by querying ROWID pseudo column. Like,
SQL> select rowid from with_lob;
ROWID
------------------
AAANp9AALAAADDPAAA

ROWIDs can be restricted Rowids which forms the format as block.row.file and can be Extended Rowids which forms the format as the data in the restricted rowid plus a data object number. The data object number can be found by querying from USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS like
SQL> select DATA_OBJECT_ID from dba_objects;

UROWID Datatype
------------------------
The rows of some tables have addresses that are not physical or permanent or were not generated by Oracle Database. Like, the row addresses of index-organized tables are stored in index leaves, which can move.

Oracle uses universal rowids (urowids) to store the addresses of index-organized and foreign tables. Index-organized tables have logical urowids and foreign tables have foreign urowids.

Large Object (LOB) Datatypes with Example.

•The LOB datatypes are used to store large and unstructured data such as text, image, video, and spatial data.

•Oracle can store large objects in both internally and externally.

•Oracle built-in LOB datatypes BLOB, CLOB, and NCLOB store data internally and built-in BFILE datatype store data externally.

•The size of BLOB, CLOB, and NCLOB data can be up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage). If LOBs are stored in 8K block sized tablespace and if you have used the default value of the CHUNK parameter of LOB storage when creating a LOB column then maximum size of LOB can be =(4GB-1)*8KB

•BFILE data can be up to power(2,31)-1 bytes.

BFILE Datatype
-----------------------
•Suppose a LOB file is exist in OS file system that is outside of oracle database. Then to access of that file you will use BFILE datatype.

•A BFILE column or attribute stores a BFILE locator, which serves as a pointer to the LOB file on the OS file system. The locator maintains the directory name and the filename.

•The BFILE datatype enables read-only support of large binary files. The column defined as BFILE can't be modified or can't be replicated.

An example is here about how we can access LOB data externally by BFILE datatype.
How to Insert Blob data(image, video) into oracle and determine LOB size

BLOB Datatype
--------------------------
•To store binary file internally into a database we can use BLOB datatype.
•The column defined as BLOB datatype have fully transactional support that is they can modified, committed, rolled back and can be replicated.

An example is here about how we can store LOB data in to oracle database using BLOB.
How to Insert Blob data(image, video) into oracle and determine LOB size

CLOB Datatype
----------------------------
•To store a large character of strings we can use CLOB datatype.
•The column defined as CLOB datatype have fully transactional support that is they can modified, committed, rolled back and can be replicated.

NCLOB Datatype
-----------------------------------
•The NCLOB datatype stores Unicode data. Both fixed-width and variable-width character sets are supported, and both use the national character set.

•The column defined as NCLOB datatype have fully transactional support that is they can modified, committed, rolled back and can be replicated.

Example of CLOB and NCLOB
-----------------------------------
SQL> CREATE TABLE WITH_LOB(clob_dt CLOB, nclob_dt NCLOB);
Table created.

SQL> insert into with_lob values('This is Clob','This is Nclob');
1 row created.

SQL> select * from with_lob;
CLOB_DT
--------------------------------------------------------------------------------
NCLOB_DT
--------------------------------------------------------------------------------
This is Clob
This is Nclob

Related Documents:
--------------------------------
How to Insert Blob data(image, video) into oracle and determine LOB size

Advantages of LOB against LONG- Feature of LOB datatype

How to move LOB data to another tablespace
How to Insert Blob data(image, video) into oracle BLOB size
Char, Varchar2, Long etc Datatype Limits in Oracle

Datetime and Interval Datatypes Fields and Values in Oracle

•In oracle, the datetime datatypes are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. The interval datatypes are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.

•Both Datetime and Interval datatypes in oracle are made up of fields. These fields determines the value of these datatypes.

•Database and sesion time zone can be verified by querying the built-in SQL functions DBTIMEZONE and SESSIONTIMEZONE.

•If the time zones have not been set manually, Oracle Database uses the OS time zone by default. If the OS time zone is not a valid Oracle time zone, then Oracle uses UTC as the default value.

•From now on datetime datatypes will be referred as datatimes and interval datatypes will be referred as intervals.

Datetime Fields and Values
---------------------------------------
YEAR: The valid value of YEAR field are from -4712 to 9999 (excluding year 0) in case of datetimes and in case of intervals any integer values are valid.

MONTH:The valid value of MONTH field are from 01 to 12 in case of datetimes and from 0 to 11 in case of intervals.

DAY:The valid value of DAY field are from 0 to 31 in case of datetimes and in case of intervals any integer values are valid.

HOUR:Valid value ranges from 00 to 23.

MINUTE:Valid value ranges from 00 to 59.

SECOND:Valid value ranges from 00 to 59.9(n). It is not applicable for DATE datatype.

TIMEZONE_HOUR:Only applicable for datetimes (except DATE and TIMESTAMP) and valid value ranges from -12 to 14.

TIMEZONE_MINUTE:Only applicable for datetimes (except DATE and TIMESTAMP) and valid value ranges from 0 to 9.

TIMEZONE_REGION:Only applicable for datetimes except DATE and TIMESTAMP) and to know the valid values query from V$TIMEZONE_NAME,
SQL> select distinct TZNAME from V$TIMEZONE_NAMES;

TIMEZONE_ABBR:Only applicable for datetimes except DATE and TIMESTAMP) and to know the valid values query from V$TIMEZONE_NAME,
SQL> select distinct TZABBREV from V$TIMEZONE_NAMES;
DateTime DataTypes
--------------------------------------
1)DATE Datatype
2)TIMESTAMP Datatype
3)TIMESTAMP WITH TIME ZONE Datatype
4)TIMESTAMP WITH LOCAL TIME ZONE Datatype

Interval DataTypes
-----------------------------------------
1)INTERVAL YEAR TO MONTH Datatype
2)INTERVAL DAY TO SECOND Datatype

LONG Datatype and its restriction in Oracle

•Before going into detail oracle strongly recommend not to use LONG datatype in oracle. LONG datatype is remained for backward compatibility. If you have LONG datatype in your database then convert it to LOB data type using TO_LOB function which is discussed on How to Convert LOB .

•LONG datatype store variable-length character strings containing up to 2 gigabytes -1, or power(2,31)-1 bytes.

The use of LONG datatype is subject to the following restriction.

•A table can contain only one LONG column.

•You cannot create an object type with a LONG attribute.

•LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).

•Index can't be created on LONG columns.

•In regular expressions LONG datatype can't be specified.

•Stored function can't return a LONG value.

•You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL.

•LONG and LONG RAW columns can't be replicated.

•All LONG columns, updated tables, and locked tables must be located on the same database within an SQL statement.

•LONG column can't appear in GROUP BY, ORDER BY clause, UNIQUE / DISTINCT operator or CONNECT BY clause in SELECT statements.

•LONG columns cannot appear in these parts of SQL statements
ALTER TABLE ... MOVE statement.
SELECT lists in subqueries in INSERT statements
SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
SQL built-in functions, expressions, or conditions

Example:
----------------------
Create table with_long (long_dt LONG);
Table created.

SQL> insert into with_long values('This is a long datatype');
1 row created.

SQL> select * from with_long;
LONG_DT
--------------------------------------------------------------------------------
This is a long datatype

Sunday, June 29, 2008

Numeric Datatype in Oracle with Examples

1)NUMBER Datatype
-----------------------------------------
•The NUMBER datatype can store numeric values ranges from 1.0 xpower(130,-10) to (but not including) 1.0 x power(10,126).

•NUMBER value requires from 1 to 22 bytes.

•To store a fixed-point number use following form NUMBER (p,s) where p is the precision which specifies the total number of significant decimal digits and it can be 39 or 40. s is the scale which specifies the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127.

•The precision p is counted as total number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit.

•If actual data is 123.67 and you declare as NUMBER(10,1) then it is stored as 123.7

•To store an integer use NUMBER(p) where scale is considered as 0.

•If you use only NUMBER without any precision and scaling value then oracle uses the maximum range and precision of NUMBER.

2)BINARY_FLOAT
----------------------------
•BINARY_FLOAT differ from NUMBER datatype in the way the values are stored internally by oracle database.

•BINARY_FLOAT is a 32-bit, single-precision floating-point number datatype.

•BINARY_FLOAT value requires 5 bytes, including a length byte.

•Maximum positive value for BINARY_FLOAT datatype is 3.40282E+38F and Minimum is 1.17549E-38F.

3)BINARY_DOUBLE
----------------------------------
•BINARY_DOUBLE is a 64-bit, double-precision floating-point number datatype.

•Each BINARY_DOUBLE value requires 9 bytes, including a length byte.

•Maximum positive value for BINARY_DOUBLE datatype is 1.79769313486231E+308 and Minimum is 2.22507485850720E-308.

SQL>CREATE TABLE WITH_NUMBER(number_dt NUMBER, num_dt_1 NUMBER(3), num_dt_2 NUMBER(6,7), num_dt_4 NUMBER(3,-2),num_dt_5 NUMBER(4,5), bd_dt BINARY_DOUBLE, bf_dt BINARY_FLOAT);
Table created.

SQL> insert into WITH_NUMBER values(12.7,12.7,12.7,12.7,12.7,12.7,12.7);
insert into WITH_NUMBER values(12.7,12.7,12.7,12.7,12.7,12.7,12.7)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

As end column specification is NUMBER(6,7) and we tried to insert 12.7 so error comes.

SQL> desc WITH_NUMBER
Name Null? Type
----------------------------------------- -------- ----------------------------
NUMBER_DT NUMBER
NUM_DT_1 NUMBER(3)
NUM_DT_2 NUMBER(6,7)
NUM_DT_4 NUMBER(3,-2)
NUM_DT_5 NUMBER(4,5)
BD_DT BINARY_DOUBLE
BF_DT BINARY_FLOAT

SQL> insert into WITH_NUMBER values(12.7,12.7,12.7e-8,121.7,1211e-9,12.7,12.7);
1 row created.


SQL> select * from with_number;
NUMBER_DT   NUM_DT_1   NUM_DT_2   NUM_DT_4   NUM_DT_5      BD_DT      BF_DT
---------- ---------- ---------- ---------- ---------- ---------- ----------
     12.7         13   .0000001        100          0  1.27E+001  1.27E+001

Related Documents
Types of SQL function in Oracle
Numeric EXP FLOOR LN LOG MOD NANVL POWER functions
List of Analytic functions in oracle
List of Aggregate functions in Oracle
Numeric ABS ACOS ASIN ATAN ATAN2 BITAND CEIL COS COSH functions
List of Single row functions in Oracle

Character Datatypes with example in Oracle

1)CHAR Datatype
---------------------------------------
•The CHAR datatype in oracle specifies fixed length character string. That is if you specify datatype as COL1 CHAR(10) then regardless of value entered in column COL1 the length of the value will be 10 bytes.

•In fact if you insert a value that is shorter than the column length, then Oracle blank pads (add spaces after the text) the value to column length. If you try to insert a value that is larger than the column length, then Oracle returns an error.

•The default length for a CHAR datatype column is 1 byte and the maximum allowed is 2000 bytes.

•The column length for CHAR datatype can be specified both in bytes and characters. By default if you just CHAR(10) then 10 bytes of column size is specified. If you want to specify the size of CHAR datatype in characters then declare as CHAR(10 CHAR). Then the size of the CHAR datatypes column varies between 1 to 4 bytes based on the database character sets.

•The BYTE and CHAR qualifiers override the semantics specified by the NLS_LENGTH_SEMANTICS parameter, which has a default of byte semantics.

2)NCHAR Datatype
--------------------------------------------
•When a column is defined with NCHAR datatype then column length is defined with characters.

•It is a Unicode-only datatype.

•The maximum column size allowed is 2000 bytes.

•If you insert a value that is shorter than the column length, then Oracle blank pads (add spaces after the text) the value to column length.

•CHAR value can't be inserted into an NCHAR column,and also NCHAR value can't be inserted into a CHAR column.

3)NVARCHAR2 Datatype
-----------------------------------------------
•The NVARCHAR2 datatype is a Unicode-only datatype.

•When you create a table with an NVARCHAR2 column, you specify the maximum number of characters it can hold.

•The maximum column size allowed is 4000 bytes.

4)VARCHAR2 Datatype
----------------------------------------------------
•When you create a column with VARCHAR2 datatype, you specify the maximum number of bytes or characters of data that it can hold.

•This minimum length of VARCHAR2 datatype must be at least 1 byte, although the actual string stored is permitted to be a zero-length string ('').

•The maximum length of VARCHAR2 data is 4000 bytes.

5)VARCHAR Datatype
------------------------
•Oracle recommends not to use VARCHAR datatype. Though currently there is no difference between VARCHAR and VARCHAR2 datatype. The VARCHAR datatype is currently synonymous with VARCHAR2.

•Oracle schedule VARCHAR datatype to use separate datatype.

To know the differenece between CHAR, VARCHAR2 and VARCHAR please visit What is the difference between VARCHAR, VARCHAR2 and CHAR data types

Example:
-----------------
In the following example I used all character datatypes to create a table.

SQL> SHOW PARAMETER nls_length_semantics

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string BYTE

SQL> CREATE TABLE WITH_ALL_CHAR(char_dt CHAR, char_dt_in_char CHAR(5 CHAR), nchar_dt NCHAR(4), varchar_dt VARCHAR2(10), nvarchar2_dt NVARCHAR2(10));
Table created.

SQL> desc WITH_ALL_CHAR
Name Null? Type
----------------------------------------- -------- ----------------------------
CHAR_DT CHAR(1)
CHAR_DT_IN_CHAR CHAR(5 CHAR)
NCHAR_DT NCHAR(4)
VARCHAR_DT VARCHAR2(10)
NVARCHAR2_DT NVARCHAR2(10)

SQL> select length(CHAR_DT) CHAR_DT, length(CHAR_DT_IN_CHAR) CHAR_DT_IN_CHAR , length(NCHAR_DT) NCHAR_DT ,length(VARCHAR_DT) VARCHAR_DT, length(NVARCHAR2_DT) NVARCHAR2_DT from WITH_ALL_CHAR;

CHAR_DT CHAR_DT_IN_CHAR NCHAR_DT VARCHAR_DT NVARCHAR2_DT
---------- --------------- ---------- ---------- ------------
1 5 4 4 7

Oracle Built in Datatypes

Each column in a table/ index or each argument in a function/ procedure is associated with a datatype what represents how data will be.

Oracle has built-in datatypes.

The datatype code of a column or object attribute is returned by the DUMP function. To know more visit How can one dump or examine the exact content of a database column?

We can categories the list of oracle built in datatypes as following.

A)Character datatypes.
B)Numeric datatypes.
C)Long and Raw datatypes.
D)Date time datatypes.
E)Large Object datatypes.
F)RowID datatypes.

A)Character Datatypes.
----------------------------------------------
CHAR Datatype
NCHAR Datatype
NVARCHAR2 Datatype
VARCHAR2 Datatype
To know about these datatypes and example of these please visit
Character Datatypes with example in Oracle

B)Numeric datatypes.
---------------------------------------------
NUMBER Datatype
BINARY_FLOAT
BINARY_DOUBLE
To know about these datatypes and example of these please visit
Numeric Datatype in Oracle with Examples
C)Long and Raw datatypes.
-----------------------------------------------------------
LONG Datatype
RAW Datatype
LONG RAW Datatype

To know about these datatypes and example of these please visit
LONG Datatype in Oracle
D)Date time datatypes.
------------------------------------------------
DATE Datatype
TIMESTAMP Datatype
TIMESTAMP WITH TIME ZONE Datatype
TIMESTAMP WITH LOCAL TIME ZONE Datatype
INTERVAL YEAR TO MONTH Datatype
INTERVAL DAY TO SECOND Datatype
To know about these datatypes and example of these please visit
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes-in.html
E)Large Object datatypes.
------------------------------------------------
BFILE Datatype
BLOB Datatype
CLOB Datatype
NCLOB Datatype

To know about these datatypes and example of these please visit
Large Object (LOB) Datatypes with Example.
F)RowID datatypes.
------------------------------------------------
ROWID Datatype
UROWID Datatype

Tuesday, June 10, 2008

ORA-01843: not a valid month

Problem Description:
---------------------------

SQL> select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts;
A TO_TIMESTAMP(B,'DD-MON-RRHH.MI.SSXFFAM')
----------- ----------------------------------------------------------------
1 10-JUN-08 03.21.33.106197 AM

SQL> alter session set NLS_TIMESTAMP_FORMAT='DD-MM-YY HH';
Session altered.

SQL> select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts;
select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts
*
ERROR at line 1:
ORA-01843: not a valid month

Cause of The Problem:
-----------------------------
The settings of the date or timestamp does not match with the settings or current date or timestamp format.

To know current session settings issue,
SQL> select * from NLS_SESSION_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MM-YY HH

TO know current instance settings query,

SQL> select * from NLS_INSTANCE_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT

To know database settings,
SQL> select * from NLS_DATABASE_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

To know currently affected parameter issue,
SQL> select * from V$NLS_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
---------------- -----------------------------------
NLS_TIMESTAMP_FORMAT DD-MM-YY HH

As this settings does not match with 'DD-MON-RR HH.MI.SSXFF AM' format so error comes.

Solution of The Problem:
---------------------------------
1)Use a format that match current settings. like,
SQL> select a, to_timestamp(b,'DD-MM-RR HH.MI.SSXFF AM') from test_ts;
A TO_TIMESTAMP(B,'DD-MM-RRHH.MI.SSXFFAM')
---------------------------------------------------------------------------
1 10-06-08 03

2)Or left it to default as ,
SQL> select a, to_timestamp(b) from test_ts;
A TO_TIMESTAMP(B)
---------- ---------------------------------------------------------------------------
1 10-06-08 03

3)Or exit session if it is set session wise and issue same query.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
-bash-3.00$ sqlplus arju/a

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 10 04:51:05 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from V$NLS_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';

PARAMETER VALUE
-------------------- --------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

SQL> select a, to_timestamp(b,'DD-MM-RR HH.MI.SSXFF AM') from test_ts;
A
----------
TO_TIMESTAMP(B,'DD-MM-RRHH.MI.SSXFFAM')
---------------------------------------------------------------------------
1
10-JUN-08 03.21.33.106197 AM

SQL> select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts;
A
----------
TO_TIMESTAMP(B,'DD-MON-RRHH.MI.SSXFFAM')
---------------------------------------------------------------------------
1
10-JUN-08 03.21.33.106197 AM

Both version worked and returned the same formatted result.

SQL> select * from NLS_SESSION_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';

PARAMETER VALUE
-------------------- --------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

ORA-01830: date format picture ends before converting