DataType1.htm
Please preview SQL_Standards_2.htm , compare_integer_number1.htm
Objective :
  • SQL To PL/SQL or PL/PGSQL Brief Generalized Description
  • Oracle DBMS provides extensive and in-depth information on DATA-TYPE, and  could be overwhelming due to huge volumes, and it may demand your patients. But it is doable for all freelancers,  and it becomes easier as you start creating table or follow the guide lines how to use SQL. I found the online documents of PostgreSQL DBMS, are very user's friendly for Windows, Mac, and UNIX users, except some introductory chapters, in which mostly UNIX commands were mentioned.
     Some knowledge of C/C++ or Java programming could be helpful to get the essences of data-types used by leading DBMS vendors.
    It is my personal view that you can teach yourself how to query a database using SQL, as a freshman having little or no programming background. 
    • Oracle Reference Websites: 1, 2, 3
    • PostgreSQL documents : 1, 2, 3
  • PostgreSQL manages collection of databases managed by a single PostgreSQL server instance constitutes a database cluster. Oracle
  • There are many citations which described scalar, composite or reference type of data, in a DBMS, and the extensions of SQL in the procedural languages like PL/SQL or PL/PGSQL, which may pose a considerable amount of task to the developers.
  • In this document, I tried to put a synopsis of data-types used in a DBMS, before plunging into Oracle or MySQL or PostgreSQL DBMS.
List of References :
 
SQL Data Type

In general, internal data would be stored as variable, constants, records or tables, in a procedural language. Therefore the variables  may be scalar or composite type.

ANSI SUPPORTED DATA TYPES IN ORACLE

Distinction between scalar and composite data type

SCALAR : stores no internal components, can have sub types.

COMPOSITE: stores values that have internal components. Both Oracle and PostgreSQL DBMS , recognize RECORD as a composite data types. Oracle DBMS considers COLLECTIONS  as a composite data type; where as PostgreSQL DBMS did not use the name "collection " .

DBMS: SQL and PL/SQL or PL/PGSQL

Each DBMS is supported with predefined data types, and each vendor documented these data-types autonomously.

This document would introduce an overview  of data types used by Oracle and PostgreSQL DBMS.

SQL is considered to be a strongly typed data base query language, and it does not support any procedural or logical actions  like programming languages.

The overcome the above shortfalls of SQL, the procedural languages like ,PL/SQL and PL/PGSQL, were introduced by lead DBMS vendors.

These procedural languages, PL/SQL and PL/PGSQL, are structured with the insertions or extensions of SQL data-type. Here  I chose to review ANSI- SQL and SQL extensions in the procedural languages,  PL/SQL and PL/PSQL. FYI, storage capacity of PL/SQL differs from SQL ( Data_Type2.htm)

 
 The PL/SQL scalar data types are:
  • The SQL data type : NUMBER, CHARACTER, BOOLEAN, DATE
  • PLS_INTEGER
  • BINARY INTEGER
  • REF CURSOR
  • USER DEFINED SUB TYPES

http://docstore.mik.ua/orelly/oracle/prog2/ch04_02.htm

Both PL/SQL and PL/PGSQL offer predefined scalar and composite data type. A scalar type is a singleton or atomic type, and a storage for single data item/type.

 

Oracle Data Type -\/----\/ :
Oracle Platform (10 and 11.2g): OracleŽ Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) B14261-01
  • Scalar Type  : has no Internal component and a scalar data-type will hold single value. It has seven members, numeric, character , raw, boolean, date, rowid and trusted. The member of this data type and some subtypes are listed below.
    • Numeric types: binary_double, binary_float, binary_integer, dec, decimal, double precision, float, int, integer, natural, naturaln, number, numeric, pls_integer, positive, positiven, real, signtype, smallint
    • Character (String) Type: char, character, long, long raw, nchar, nvarchar2, raw, rowid, string, urowid, varchar, varchar2
    • RAW Type: RAW  and LONG RAW . The LONG RAW  data type has been archived for backward compatibility, and use BLOB and LOB. RAW and be indexed and LONG RAW can't be indexed.
    • Boolean
      (True/False/null) logical operations,
    • Date -Time: DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND.
    • LOB: BFILE, BLOB, CLOB,NCLOB
    • ROWID  and UROWID:
      • ROWID: This data type is primarily for values returned by the ROWID pseudo-column, points to the unique address of a row in a table.

        SQL> select ename, empno from emp
        2 where rowid = 'AAAR3sAAEAAAACXAAA';

         
      • UROWID[size]: The Universal ROWID (UROWID is a datatype that can store both logical and physical rowids of Oracle tables.
        The following host variables can be bound to Universal ROWIDs:
        SQLT_CHR (VARCHAR2)
        SQLT_VCS (VARCHAR)
        SQLT_STR (NULL-terminated string)
        SQLT_LVC (LONG VARCHAR)
        SLQT_AFC (CHAR)
        SQLT_AVC (CHARZ)
        SQLT_VST (OCI String)
        SQLT_RDD (ROWID descriptor)
         
  • Composite Type:
    RECORD, TABLE , VARRAY
    • Composite types are user manipulated data-types. To use a composite data types, one has to define the type.
      • RECORD ( different data type : syntax
        declare

         

  • Reference Type:
    REFCURSOR, REF
/\----Oracle Data Type--- /\
 
ORACLE'S NUMBER / NUMERIC DATA TYPE

Oracle (11g, release 1.1) manages all numeric data types with two Numeric data type families, NUMBER  and FLOATING-POINT-NUMBERS.

Comparing design and run

 
SQL-ANSI predefined Numeric data-sub-types
Subtype Compatibility Corresponding Oracle Data type
DEC (prec, scale) ANSI NUMBER (prec, scale)
DECIMAL (prec, scale) IBM NUMBER (prec, scale)
DOUBLE PRECISION ANSI NUMBER
FLOAT (binary) ANSI, IBM NUMBER
INT ANSI NUMBER (38)
INTEGER ANSI, IBM NUMBER (38)
NUMERIC (prec, scale) ANSI NUMBER (prec, scale)
REAL ANSI NUMBER
SMALLINT ANSI, IBM NUMBER (38)
 
Scalar Data Type (Recap) :
 NUMBER / numeric DATA TYPE :
In general, Numeric data variables are described by the number of bytes they use and whether they are signed (values could be either positive or negative) or unsigned (always positive numbers). Oracle (11g, release 1.1) manages all numeric data types with two Numeric data type families, NUMBER  and FLOATING-POINT-NUMBERS. The description below is to provide a flash-back of commonly-known data-types used in the recent years.
  • INTEGER : is an ANSI SQL data type,  has 38 digits precision Number (38), and store only whole number, can't store 1234.56. For standard input and output of numbers, oracle uses decimal character as "stop" or period. The decimal separates the integer integer from a number, in 1234.56, 1234 would make integer part of the number.
  • BINARY_INTEGER (signed integers), don't need conversion. Variables of  NUMBER need to be converted before PL/SQL performs calculation, therefore use of binary_integer may improve performance. When a number is stored in a decimal format, the arithmetic operations can't be performed directly, should be converted to binary types, and PL/SQL engine does this operation for decimal numbers. NATURAL and POSITIVE  are both sub-types of BINARY_INTEGER.
    • NATURAL : 0 TO 231
    • POSITIVE : 1 TO 2 31
    • NATURAL OR POSITIVE should be used when values are NOT-NEGATIVE (0 and greater).
  • DECIMAL types are number, float, decimal, double precision, number, numeric ,real. NUMBER( PRECISION, SCALE) UP TO 38 , default scale is 0.
  •  PLS_INTEGER: needs less storage than NUMBER, when pls-integer overflows , pl/sql will raise an exception
 
CHARACTER DATA TYPE:

CHAR, CHARACTER, LONG, LONG RAW, NCHAR, NVARCHAR2,RAW, ROWID , STRING, VARCHAR, VARCHAR2

CHAR: Expects a fixed number of length of character (as a string  ) data type, ranging between 1 -2000 bytes . The default is 1 byte.

  • Not Valid : string1 CHAR
  • Valid : string1 CHAR(40); // FIXED 40 CHAR
  • If you provide a shorter value during insert or update , the value will blank padded to the fixed length.
  • IF VALUE EXCEEDS  the defined length  db will throw an error.

VARCHAR2 TYPE  ( SYNONYMOUS TO VARCHAR)

  • uses:  ename varchar2(20);
  • Conversion during Fetch or Select:
    • CHAR ->VARCHAR : During a data conversion from CHAR to Varchar2, with Select and Fetch operation , the trailing spaces are retained.
    • VARCHAR ->CHAR : PL/SQL pads with spaces the value to max, during a Fetch or Select Operation.
  • Conversion during INSERT  or UPDATE
    • CHAR (5)-> VARCHAR2(12):
      char flower-> varchar (flower......).
       PL/SQL padded six paces after flower.
    • VARCHAR2(20)->CHAR(12):

LONG: stores  2gb of data , PL/SQL LONG  is less than database LONG, can store variable-string up to 32760 bytes. Therefore, you cannot select a LONG database value larger than 32760 bytes into a PL/SQL. SQL restrictions on LONG database column. " Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) instead. LONG columns are supported only for backward compatibility--Oracle 1"

  • A table may contain only one LONG  column.
  • You can't use GROUP BY, ORDER BY, WHERE or CONNECT BY  clause in LONG COLUMN.
  • You can't use   substr, instr or length functions with LONG column

PL/SQL LONG variables hove no such restrictions, supports WHERE , UPDATE clauses. In fact, Varchar2 can hold more than PL/SQL  LONG variable type.

RAW TYPE:
The RAW data-type is used to store binary data or other kinds of raw data, such as a digitized picture or image, used for backward compatibilities, like RAW(L). A RAW variable resembles CHAR  varaiable, but unlike CHAR , RAW is not converted between character set, PL/SQL will not try to interpret raw data. Within the Oracle RDBMS it appears that  that Oracle will not perform character set conversions on RAW data when it is moved from one system (based, for example, on 7-bit ASCII) to another system.

MLSLABEL--For use with Trusted Oracle, this data type stores the binary format of the system label.The maximum width of a column declared as MLSLABEL is 255 bytes.

 
 
 
Some non-Oracle data types are  converted to this Oracle data type
 

DECIMAL -TO -- NUMBER
INTEGER, -TO- INT NUMBER(38)
SMALLINT -TO- NUMBER(38)
FLOAT -TO- NUMBER
REAL -T0- NUMBER
DOUBLE -TO-NUMBER

 
Composite Data Type
VARRAYs (http://www.smart-soft.co.uk/Oracle/oracle-plsql-tutorial-part-11.htm )

The Varrays are one-dimensional, variable length arrays, with the maximum length defined in the declaration. These are used when it is known in advance that you will only need a few of the items to be stored and you know in advance the maximum number that you will need to hold.


The VARRAY data type consists of a set of built-in types or objects referred to as elements of the array.
Each element has an index that corresponds to the position in the array. The number of elements in this data type can vary, like an dynamic array, therefore it is referred to as a VARRAY, or variable array.

To create an array, you must declare its maximum size because it does vary. To create an array type, use this syntax:

 

REFs

The REF data type acts as a pointer to an object. A REF can also be used in a manner similar to a foreign key in an RDBMS. A REF is used primarily to store an object identifier, and to allow you to select that object.

SELECT e.name FROM emp e WHERE e.empno = 7902;

In this example, e acts as a reference (alias) to emp table allowing easier easier access to that table.

LOBs : LOB refers to large schema objects. Oracle recognizes several different types of LOBs, including the following:
* BLOB--
An unstructured binary data field. A BLOB might be something like video or picture information.
*CLOB--
A large field that consists of standard, single-byte characters. This might be something like a document.
* NCLOB--
Similar to the CLOB type, but consists of single or multibyte characters from the National Character Set.
* BFILE--
Essentially a pointer to an externally stored file. All these types reference large pieces of data, such as video or text. Because they are large by definition, Oracle does not store the data inline with the other columns of the table. Instead, a LOB pointer is stored there, which points to the location
A number of schema objects can be managed here, including
*Clusters
*Database links
*Functions
*Indexes
*Package bodies
*Packages
*Procedures
*Refresh groups
*Sequences
*Snapshot logs
*Snapshots
*Synonyms
*Tables
*Triggers
*Views
PostgreSQL data-types