Varchar vs Varchar2 2

[MadhanaGopal]

Hi,

I am new to oracle and i am having the following basic doubt.

1.Differnce between Varchar and Varchar2 data types.
( As of my knowledge varchar is deprecated in oracle 9i
and memory management is different btw varchar and varchar2.i am not sure about it)

2.Locking Mechanism in Oracle

Thanks in advance.

-mAdhan

 

[DotNetGnat]

1. The difference between Varchar and Varchar2 is both are variable length but only 2000 bytes of character of data can be store in varchar where as 4000 bytes of character of data can be store in varchar2.

2.

Locking Mechanisms 

Oracle also uses locks to control concurrent access to data. Locks are mechanisms intended to prevent destructive interaction between users accessing Oracle data. 


Locks are used to achieve two important database goals: 

consistency  
 Ensures that the data a user is viewing or changing is not changed (by other users) until the user is finished with the data.  
 
integrity  
 Ensures that the database's data and structures reflect all changes made to them in the correct sequence.  
 
 


Locks guarantee data integrity while allowing maximum concurrent access to the data by unlimited users. 


Automatic Locking

Oracle locking is performed automatically and requires no user action. Implicit locking occurs for SQL statements as necessary, depending on the action requested. 


Oracle's sophisticated lock manager automatically locks table data at the row level. By locking table data at the row level, contention for the same data is minimized. 


Oracle's lock manager maintains several different types of row locks, depending on what type of operation established the lock. In general, there are two types of locks: exclusive locks and share locks. Only one exclusive lock can be obtained on a resource (such as a row or a table); however, many share locks can be obtained on a single resource. Both exclusive and share locks always allow queries on the locked resource, but prohibit other activity on the resource (such as updates and deletes). 


Manual Locking

Under some circumstances, a user may want to override default locking. Oracle allows manual override of automatic locking features at both the row level (by first querying for the rows that will be updated in a subsequent statement) and the table level.

info found online...

-DNG

 

[Beilstwh]

Directly from the Oracle 9 manual.

VARCHAR2 and VARCHAR Datatypes

The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. For each row, Oracle stores each value in the column as a variable-length field unless a value exceeds the column's maximum length, in which case Oracle returns an error. Using VARCHAR2 and VARCHAR saves on space used by the table.

For example, assume you declare a column VARCHAR2 with a maximum size of 50 characters. In a single-byte character set, if only 10 characters are given for the VARCHAR2 column value in a particular row, the column in the row's row piece stores only the 10 characters (10 bytes), not 50.

Oracle compares VARCHAR2 values using nonpadded comparison semantics.
See Also:

Oracle9i SQL Reference for details about nonpadded comparison semantics

VARCHAR Datatype

The VARCHAR datatype is synonymous with the VARCHAR2 datatype. To avoid possible changes in behavior, always use the VARCHAR2 datatype to store variable-length character strings.

That being said, there has been talk about changing how VARCHAR works in the future to make it support the ANSI VARCHAR type, which is closer to the CLOB in oracle.

Bill
Oracle DBA/Developer
New York State, USA