What is the type in PL SQL

PLSQL data type

PLSQL data types are used to specify PL / SQL variables and constants in the declaration section. The data types are essentially the same as in SQL with the exception of BOOLEAN, RECORD ?,% ROWTYPE,% TYPE and REF CURSOR, among others.

With value assignments, parameter transfers, comparison operators? and other statements in which several expressions are related to each other, you must ensure that the expressions involved have the same or at least compatible data types. Oracle offers a very convenient automatic type conversion for many "related" data types. If this is not the case, the type conversion functions are still available.

Here is just a small selection of possible data types:

Scalar data typesdescription
BOOLEANcan have values ​​TRUE, FALSE or UNKONWN
BINARY_INTEGERinteger numerical values ​​for faster processing
BLOBbinary large object
BFILEPointer to a large object file outside the DBS
CHARSQL CHAR type, text data type of fixed length
CLOBcharacter large object
DATEDate with time
INTERVALPeriod between two timestamps
NCLOBcharacter large object with unicode characters
NUMBERnumeric data type with decimal places
PLS_INTEGERinteger numerical values ​​for faster processing
RECORD?Compound data type as a data record
REF Pointer to a self-defined data type
REF CURSORPointer to a user-defined cursor
ROWID?Data type for the memory address of a data record
TYPEuser-defined data types
TYPE ... IS VARRAYan ARRAY of fixed length
TYPE ... IS RECORD?Compound data type as a data record
TYPE ... IS TABLEOne-dimensional array of variable length, in which tables can also be entered as a data type in brackets
VARCHARSQL VARCHAR type, text data type of variable length
VARCHAR2like VARCHAR, only Oracle-specific
% ROWTYPEComplete table structure for the data set variable
% TYPEColumn definitions for the variable / constant

This selection can be expanded as required using user-defined data types.

see also: CREATE-TYPE


  • Faeskorn-Woyke, Heide; Bertelsmeier, Birgit; Riemer, Petra; Bauer, Elena: "Database systems - theory and practice with SQL2003, Oracle and MySQL", Pearson Education, Munich, 2007, ISBN 978-3-8273-7266-6
  • Flint, Steven, Pribyl. Bill: "Oracle PL / SQL Programming", O'Reilly, 2009, ISBN-13: 978-0596514464
  • Oracle� Database SQL Language Reference 11g Release 1 (11.1) in http://docs.oracle.com/cd/B28359_01/server.111/b28286/toc.htm
  • Oracle� Database PL / SQL Language Reference 11g Release 2 (11.2) in http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm
  • Saurabh, Gupta: "Oracle Advanced PL / SQL Developer Professional Guide", Packt Publishing Limited, 2012, ISBN 978-1-84968-722-5

Category: Oracle-PL-SQL, SQL, Object Relational SQL, D