Variable Data Types

Contact Us or call 1-877-932-8228
Variable Data Types

Variable Data Types

PL/SQL Variables can be classified in a number of broad categories. These categories are not part of the language itself, but are helpful for organizing the numerous data types into meaningful groupings. Scalar data types include numeric, character and date/time types. Composite data types include collections and records. These complex data types contain other values that can be individually referenced. Large Objects or LOBs reference large objects such as binary files or documents. Reference data types (e.g. cursors) are essentially pointers.

Many variable data types includes several variations. Numeric variable types can be created in a variety of sizes and can have a defined scale and precision. Character data types can be of a set size (CHAR) or vary in size (VARCHAR2). Time data types can store date and time information at various levels of precision, and data types exist not only to define specific moments of time but also to describe intervals of time. Some data types (most notably VARCHAR) have been deprecated and should not be used.

One way that PL/SQL is closely integrated with SQL is by the fact that all SQL variable types are available in PL/SQL. There is also a syntax that allows the declaration of PL/SQL variables to correspond to the data types that exist in Oracle tables. The %TYPE keyword is used to declare a variable whose type matches a specific database table's column. The %ROWTYPE keyword is used to declare a complex variable (a record) which has individual fields that correspond to every column in a given database table. However, not all PL/SQL data types are available in SQL. An important consideration is that the Boolean data type available in PL/SQL is not a valid datatype in SQL.

Next