Saturday, July 5, 2008

PLS-00428: an INTO clause is expected in this SELECT statement

Error Description:
----------------------------------
You tried to write a Select statement inside PL/SQL and it returns with the error like,

LINE/COL ERROR
-------- -----------------------------------------------------------------
60/1 PLS-00428: an INTO clause is expected in this SELECT statement

Cause of The Error:
---------------------------------------
Inside PL/SQL you just ran the select statement as you did in SQL. The INTO clause of a SELECT INTO statement was omitted. For example, inside PL/SQL you wrote SELECT * FROM dept WHERE ... instead of
SELECT * INTO dept_rec FROM dept WHERE ...

In PL/SQL, only a subquery is written without an INTO clause.

Solution of The Error:
--------------------------------
Merely to execute the statement just add execute keyword. Like instead of SELECT * FROM dept WHERE .. use EXECUTE IMMEDIATE 'SELECT * FROM dept WHERE ...';

Or add the required INTO clause which is used to store the data returned from select statement.
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html
Posted by Arju at 8:36 PM 0 comments Labels: ,
Reactions: 

Wednesday, July 2, 2008

Schema Object Naming Rules

In order to know the schema objects please have a look at http://arjudba.blogspot.com/2008/06/database-objects-in-oracle.html
There are some rules to name a schema objects which are defined below.

•Schema name as well as database objects name can be specified within double quotes and without quotes.

•DB_NAME, GLOBAL_NAME, and database link names are always case insensitive and are stored as uppercase. So if you specify these names as quoted identifiers, then the quotation marks are silently ignored.

•Database name can have maximum 8 bytes.

•Database Link name can have maximum 128 bytes.

•Schema name other than DB_NAME and database link name can be 1 to 30 bytes long.

•If an identifier includes multiple parts then each attribute can be up to 30 bytes long.

•Before going into this section have an idea about oracle database reserved keyqords which are discussed on
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
We can use reserved words to name schema objects within quotes but can't use it without quotes. An example below will make you clear.

SQL> create table is ( a number);
create table is ( a number)
*
ERROR at line 1:
ORA-00903: invalid table name

SQL> create table "IS" ( a number);
Table created.

SQL> create table "is" ( a number);
Table created.
The only exception is ROWID. Uppercase ROWID can't be used to name a column. However uppercase ROWID can be used in another quoted identifier other than column name and you can use the word with one or more lowercase letters (for example, "Rowid" or "rowid" or "ROwid") as any quoted identifier, including a column name.

SQL> create table "ROWID"( "ROWID" number);
create table "ROWID"( "ROWID" number)
*
ERROR at line 1:
ORA-00904: "ROWID": invalid identifier

SQL> create table "ROWID"( "rowid" number);
Table created.

•Though Non ASCII characters can be used oracle recommends to use ASCII characters in database names, global database names, and database link names.

•Non quoted identifiers must begin with an alphabetic character from your database character set.
SQL> create table 1a ( a number);
create table 1a ( a number)
*
ERROR at line 1:
ORA-00903: invalid table name

SQL> create table "1a" (a number);
Table created.

•Non quoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@).

Quoted identifiers can contain any characters and punctuations marks as well as spaces.

So this one containing "(" failed with ORA-00902: invalid datatype.
SQL> create table epr_employee(Mail_(per) varchar2(20), Mail_(off) varchar2(20));
create table epr_employee(Mail_(per) varchar2(20), Mail_(off) varchar2(20))
*
ERROR at line 1:
ORA-00902: invalid datatype

But note that within quotes it is ok.
SQL> create table epr_employee5("Mail_\0 (per)" varchar2(20), "Mail_(off)" varchar2(20));
Table created.

Also see quote within quotes are not valid.
SQL> create table epr_employee5("Mail_\" (per)" varchar2(20), "Mail_(off)" varchar2(20));
create table epr_employee5("Mail_\" (per)" varchar2(20), "Mail_(off)" varchar2(20))
*
ERROR at line 1:
ORA-00902: invalid datatype

It is good to know that neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).

•Within a namespace, no two objects can have the same name. To know more about namespace and objects name please have a look at,

Object Namespace in oracle

•Quoted strings are case sensitive and non quoted string are not case sensitive. Oracle interpret non-quoted strings as uppercase.

•In a table no two columns can have the same name.

•Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. If procedures or functions contained in the same package can have the same name and their arguments are not of the same number and datatypes then it is called overloading functions or overloading procedures.
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html
Posted by Arju at 11:49 PM 0 comments Labels: , ,
Reactions: 

Install Oracle Database 10g Software on Red Hat Linux

I already assume that you have installed Linux on your machine and now you want to install oracle software in linux box. Before installing oracle you have to perform several checkings. In the following section I have discussed one by one which may help you to install oracle on linux.

1)Check the OS bit:
---------------------------
Before installing oracle it is necessary which type of oracle software you will install. It is actually depend on OS. You have to check whether OS is 32 or 64 bit. Then you have to decide which version of oracle you will install. I have a fine post related to checking OS
Check OS 64 or 32 bit

2)Verify System Requirements:
---------------------------------------------
To install oracle on your system you should be at least 1GB RAM, 1GB swap space on your system. To find the available RAM and swap space on your system issue,

grep MemTotal /proc/meminfo
grep SwapTotal /proc/meminfo

Besides RMAN and SWAP space you need to have space in your hard disk in a partition where oracle software will reside. Approximately you need 2.5GB space for oracle software, 1.2GB space for oracle database and 400MB space in /tmp directory.

You can check your space available on the hard disk by,

df -h

3)Configure the Linux Kernel Parameters
------------------------------------------------------------------
In order to effect changed linux kernel parameters no need to reboot. Linux support modification of most kernel parameters while the system is up and running.
To install oracle on linux several kernel parameters need to be set. You can see the current settings of these parameters by,

/sbin/sysctl -a | grep shm
/sbin/sysctl -a | grep sem
/sbin/sysctl -a | grep file-max
/sbin/sysctl -a | grep ip_local_port_range
/sbin/sysctl -a | grep rmem_default
/sbin/sysctl -a | grep rmem_max
/sbin/sysctl -a | grep wmem_default
/sbin/sysctl -a | grep wmem_max

You can see the kernel parameter permanently by Forcing the /etc/sysctl.conf settings to the Kernel at run-time.
Here is an example. This will immediately reflect the settings in /etc/sysctl.conf to the Kernel (/proc/sys).

cat >> /etc/sysctl.conf <<
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=262144
EOF

Copy and paste abouve code and then run,
debian:/home/Arju# /sbin/sysctl -p
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_max = 262144

This procedure sets kernel parameter permanently, however you can set temporary by

debian:/home/Arju# cd /proc/sys/net/core
echo 1048576 > /proc/sys/net/core/rmem_default
echo 1048576> /proc/sys/net/core/rmem_max
echo 262144 > /proc/sys/net/core/wmem_default
echo 262144 > /proc/sys/net/core/wmem_max

4)Create the Oracle Groups and User Account
----------------------------------------------------------------
Log in as a root user and then create two groups oinstall and dba, create oracle user who owns the oracle software making initial group oinstall and secondary group dba.


$su
Password:
# /usr/sbin/groupadd oinstall
# /usr/sbin/groupadd dba
# /usr/sbin/useradd -m -g oinstall -G dba oracle
# id oracle
uid=501(oracle) gid=502(oinstall) groups=502(oinstall),503(dba)

Set the password on the oracle user,

# passwd oracle
Changing password for user oracle.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

5)Create Directory for Oracle software
----------------------------------------------------
Create directory as root and then change permission 755 and make owner to oracle user.
mkdir /u01/oracle
chown -R oracle:oinstall /u01/oracle
chmod -R 775 /u01/oracle

6)Set Shell Limits for the oracle User
-------------------------------------------------------------
It is sometime necessary to set limit of how much memory oracle user permit to use, how many files it can open, how many processes it can run. This is done by,
cat >> /etc/security/limits.conf <<> /etc/pam.d/login <<

7)Install Oracle
-------------------------------
Go to the location of the oracle cd or software.Change the directory install and run the runInstaller file as a oracle user. Like
$cd install
$./runInstaller

Go according to OUI as prompted and click next.

In the end run root.sh as root as OUI prompted.
Posted by Arju at 2:20 AM 0 comments Labels: , , ,
Reactions: 

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.
Posted by Arju at 8:17 PM 0 comments Labels:
Reactions: 

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
Posted by Arju at 10:37 PM 0 comments Labels: ,
Reactions: 

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.
Posted by Arju at 4:45 AM 0 comments Labels:
Reactions: 

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
Posted by Arju at 4:44 AM 0 comments Labels: ,
Reactions: 

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
Posted by Arju at 4:23 AM 0 comments Labels:
Reactions: 

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
Posted by Arju at 3:33 AM 1 comments Labels: ,
Reactions: 

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
Posted by Arju at 9:48 PM 0 comments Labels:
Reactions: 

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
Posted by Arju at 8:03 PM 0 comments Labels:
Reactions: 

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
Posted by Arju at 3:49 AM 0 comments Labels:
Reactions: 
Subscribe to: Posts (Atom)