• 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.

• Oracle 1 :sql_elements001.htm

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.

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 " .

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 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.

• 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 (11g, release 1.1) manages all numeric data types with two Numeric data type families, NUMBER  and FLOATING-POINT-NUMBERS.

Comparing design and run

• 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 ³¹
  • ^{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

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.

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

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:

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.

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