Understanding Datatypes

1. Understanding Datatypes

2. Overview of Data types • Every constant, variable, and parameter has a datatype (or type), which specifies a storage format, constraints, and valid range of values. • PL/SQL lets you define your own subtypes based on pre-defined data types. • Following topics will be covered: • Predefined Datatypes • User-Defined Subtypes • Datatype Conversion

3. Predefined Datatypes • Scalar Types - has no internal components. Scalar types fall into 4 families to store: • Number • Character • Boolean • Date-Time • Composite type - has internal components that can be manipulated individually • Reference type - holds values, called pointers, that designate other program items • LOB type - holds values, called lob locators, that specify the location of large objects (graphic images for example)

5. Number Types Number types let you store numeric data (integers, real numbers, and floating-point numbers), represent quantities, and do calculations. • BINARY_INTEGER - to store signed integers. The subtypes NATURAL and POSITIVE let you restrict an integer variable to non-negative or positive values, respectively. • NUMBER - to store fixed-point or floating-point numbers. The sub-types are FLOAT, INTEGER etc. NUMBER[(precision,scale)] • PLS_INTEGER – to store signed integers. Theses are faster than NUMBER and BINARY_INTEGER operations.

6. Character Types Character types let you store alphanumeric data, represent words and text, and manipulate character strings. • CHAR - to store fixed-length character data with maximum size up to 32767 bytes. Although PL/SQL character variables can be relatively long, the maximum width of a CHAR database column is 2000 bytes CHAR[(maximum_size [CHAR | BYTE] )] • VARCHAR2 - to store variable-length character data with a maximum size up to 32767 bytes. Although PL/SQL character variables can be relatively long, the maximum width of a VARCHAR2 database column is 4000 bytes. VARCHAR2[(maximum_size [CHAR | BYTE] )] Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable.

7. Character Types........ • LONG - to store variable-length character strings. The LONG datatype is like the VARCHAR2 datatype, except that the maximum size of a LONG value is 32760 bytes. LONG columns can store text, arrays of characters, or even short documents. You can reference LONG columns in UPDATE, INSERT, and (most) SELECT statements, but not in expressions, SQL function calls, or certain SQL clauses such as WHERE, GROUP BY, and CONNECT BY. • RAW – to store binary data or byte strings. A RAW variable might store a sequence of graphics characters or a digitized picture. Raw data is like VARCHAR2 data, except that PL/SQL does not interpret raw data. The maximum width of a RAW database column is 2000 bytes.

8. Character Types........ • ROWID and UROWID - The ROWID datatype can store only physical rowids. However, the UROWID (universal rowid) datatype can store physical, logical, or foreign (non-Oracle) rowids. • A physical rowid identifies a row in an ordinary table. A logical rowid identifies a row in an index-organized table. • When you select or fetch a rowid into a ROWID variable, you can use the built-in function ROWIDTOCHAR, which converts the binary value into an 18-byte character string. Conversely, the function CHARTOROWID converts a ROWID character string into a rowid • A ROWID representation : AAAAqcAABAAADFNAAH => OOOOOOFFFBBBBBBRRR(format) • OOOOOO: The data object number identifies the database segment. • FFF: The file number identifies the datafile that contains the row. • BBBBBB: The block number identifies the data block of row. • RRR: The row number identifies the row in the block.

9. National Character Types • The widely used one-byte ASCII and EBCDIC character sets are adequate to represent the Roman alphabet, but some Asian languages, such as Japanese, contain thousands of characters. • These languages require two or three bytes to represent each character. To deal with such languages, NCHAR and NVARCHAR2 are used, which lets you process single-byte and multibyte character data and convert between character sets. It also lets your applications run in different language environments. • You can interchange VARCHAR2 and NVARCHAR2 values & CHAR and NCHAR values in statements and expressions. But there can be some data loss while converting NCHAR/NVARCHAR2 to CHAR/VARCHAR2.

10. LOB Types • The LOB (large object) datatypes BFILE, BLOB, CLOB, and NCLOB let you store blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) up to four gigabytes in size. And, they allow efficient, random, piece-wise access to the data. • LOB types store lob locators, which point to large objects stored in an external file, in-line (inside the row) or out-of-line (outside the row) • BLOB, CLOB, and NCLOB data is stored in the database, in or outside the row. BFILE data is stored in operating system files outside the database.

11. LOB Types....... • BFILE : to store large binary objects in operating system files outside the database. • BFILEs are read-only, so you cannot modify them • BFILEs do not participate in transactions, are not recoverable, and cannot be replicated • BLOB : to store large binary objects in the database, in-line or out-of-line. • CLOB : to store large blocks of character data in the database, in-line or out-of-line. • NCLOB : to store large blocks of NCHAR data in the database, in-line or out-of-line • BLOB, CLOB and NCLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back

12. BOOLEAN Type • You use the BOOLEAN datatype to store the logical values TRUE, FALSE, and NULL • Only logical operations are allowed on BOOLEAN variables. • The BOOLEAN datatype takes no parameters. Only the values TRUE, FALSE, and NULL can be assigned to a BOOLEAN variable. • You cannot insert the values TRUE and FALSE into a database column. • Also, you cannot select or fetch column values into a BOOLEAN variable.

13. Datetime and Interval types • DATE: to store fixed-length datetimes, which include the time of day in seconds since midnight. Valid dates range from January 1, 4712 BC to December 31, 9999 AD. • TIMESTAMP: The datatype TIMESTAMP extends the datatype DATE, stores the year, month, day, hour, minute, and second and fraction of seconds. TIMESTAMP[(precision)] • TIMESTAMP WITH TIME ZONE: extends the datatype TIMESTAMP, includes a time-zone displacement TIMESTAMP[(precision)] WITH TIME ZONE

14. User-Defined Subtypes • Subtypes specify the same set of operations as their base type but only a subset of its values. Thus, a subtype does not introduce a new type; it merely places an optional constraint on its base type. • PL/SQL predefines the subtypes CHARACTER and INTEGER as follows: SUBTYPE CHARACTER IS CHAR; SUBTYPE INTEGER IS NUMBER(38,0); -- allows only whole numbers The subtype CHARACTER specifies the same set of values as its base type CHAR, so CHARACTER is an unconstrained subtype. But, the subtype INTEGER specifies only a subset of the values of its base type NUMBER, so INTEGER is a constrained subtype.

15. Defining Subtypes • Subtypes can increase reliability, provide compatibility with ANSI/ISO types, and improve readability by indicating the intended use of constants and variables. • You can define your own subtypes in the declarative part of any PL/SQL block, subprogram, or package using the syntax: SUBTYPE subtype_name IS base_type[(constraint)] [NOT NULL]; Ex. SUBTYPE BirthDate IS DATE NOT NULL; -- based on DATE type SUBTYPE Counter IS NATURAL; -- based on NATURAL subtype TYPE NameList IS TABLE OF VARCHAR2(10); SUBTYPE DutyRoster IS NameList; -- based on TABLE type TYPE TimeRec IS RECORD (minutes INTEGER, hours INTEGER); SUBTYPE FinishTime IS TimeRec; -- based on RECORD type SUBTYPE ID_Num IS emp.empno%TYPE; -- based on column type

16. Using Subtypes • Once you define a subtype, you can declare items of that type Ex. DECLARE SUBTYPE Accumulator IS NUMBER; total Accumulator(7,2); -- you can constrain a user-defined subtype SUBTYPE Numeral IS NUMBER(1,0); x_axis Numeral; -- magnitude range is -9 .. 9 y_axis Numeral; BEGIN x_axis := 10; -- raises VALUE_ERROR ... END;

17. Subtype Compatibility • An unconstrained subtype is interchangeable with its base type • Different subtypes are interchangeable if they have the same base type DECLARE SUBTYPE Accumulator IS NUMBER; amount NUMBER(7,2); total Accumulator; SUBTYPE Sentinel IS BOOLEAN; SUBTYPE Switch IS BOOLEAN; finished Sentinel; debugging Switch; BEGIN total := amount;-- Subtype to Base type debugging := finished; -- Same Base tye END;

18. Subtype Compatibility...... • Different subtypes are also interchangeable if their base types are in the same datatype family. • Given the following declarations, the value of verb can be assigned to sentence: DECLARE SUBTYPE Word IS CHAR(15); SUBTYPE Text IS VARCHAR2(1500); verb Word; sentence Text(150); BEGIN ... sentence := verb; ... END;

19. Datatype Conversion • Explicit Conversion: To convert values from one datatype to another, you use built-in functions. For example, to convert a CHAR value to a DATE or NUMBER value, you use the function TO_DATE or TO_NUMBER, respectively. Conversely, to convert a DATE or NUMBER value to a CHAR value, you use the function TO_CHAR. • Explicit Conversion: When it makes sense, PL/SQL can convert the datatype of a value implicitly. This lets you use literals, variables, and parameters of one type where another type is expected. • PL/SQL will, if necessary, convert the value from the datatype of the variable to the datatype of the target column and vice-versa.

20. Datatype Conversion.... Ex.Here, PL/SQL converts the CHAR values to NUMBER values automatically. DECLARE start_time CHAR(5); finish_time CHAR(5); elapsed_time NUMBER(5); BEGIN /* Get system time as seconds past midnight. */ SELECT TO_CHAR(SYSDATE,'SSSSS') INTO start_time FROM sys.dual; -- do something /* Get system time again. */ SELECT TO_CHAR(SYSDATE,'SSSSS') INTO finish_time FROM sys.dual; /* Compute elapsed time in seconds. */ elapsed_time := finish_time – start_time;-- Implicit Coversion INSERT INTO results VALUES (elapsed_time, ...); END;

21. Implicit Datatype Conversion

22. Understanding Cursor variables

23. Overview • Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. But, cursors differ from cursor variables the way constants differ from variables • Whereas a cursor is static, a cursor variable is dynamic because it is not tied to a specific query. • You can open a cursor variable for any type-compatible query • Cursor variables give more flexibility.

24. What Are Cursor Variables? • Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself. So, declaring a cursor variable creates a pointer, not an item. • A cursor variable has datatype REF CURSOR • Whereas a cursor always refers to the same query work area, a cursor variable can refer to different work areas. So, cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected.

25. Why Use Cursor Variables? • Mainly, you use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients • Neither PL/SQL nor any of its clients owns a result set; they simply share a pointer to the query work area in which the result set is stored. Therefore, you can pass the value of a cursor variable freely from one scope to another. • If you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side

26. Defining REF Cursor type • You can define REF CURSOR types in any PL/SQL block, subprogram, or package using the syntax TYPE ref_type_name IS REF CURSOR [RETURN return_type] where ref_type_name is a type specifier used in subsequent declarations of cursor variables and return_type must represent a record or a row in a database table. • REF CURSOR types can be strong (restrictive) or weak (nonrestrictive). A strong REF CURSOR type definition specifies a return type, but a weak definition does not. DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong TYPE GenericCurTyp IS REF CURSOR; -- weak

27. Declaring REF Cursor • Once you define a REF CURSOR type, you can declare cursor variables of that type in any PL/SQL block or subprogram DECLARE TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE; dept_cv DeptCurTyp; -- declare cursor variable dept_rec dept%ROWTYPE; -- declare record variable TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE; dept_cv DeptCurTyp; -- declare cursor variable TYPE EmpRecTyp IS RECORD ( empno NUMBER(4), ename VARCHAR2(1O), sal NUMBER(7,2)); -- declare a record type TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp; emp_cv EmpCurTyp; -- declare cursor variable

28. Declaring REF Cursor.... • In the RETURN clause of a REF CURSOR type definition, you can use %ROWTYPE to specify a record type that represents a row returned by a strongly (not weakly) typed cursor variable • You cannot declare cursor variables in a package. Unlike packaged variables, cursor variables do not have persistent state. Remember, declaring a cursor variable creates a pointer, not an item. So, cursor variables cannot be saved in the database

29. Cursor Variables As Parameters • You can declare cursor variables as the formal parameters of functions and procedures. In the following example, you define the REF CURSOR type EmpCurTyp, then declare a cursor variable of that type as the formal parameter of a procedure: DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS ... When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the IN OUT mode. That way, the subprogram can pass an open cursor back to the caller.

30. Cursor Variables As Parameters..... Ex. CREATE PACKAGE emp_data AS TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT); END emp_data; CREATE PACKAGE BODY emp_data AS PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT) IS BEGIN IF choice = 1 THEN OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL; ELSIF choice = 2 THEN OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500; END IF; END; END emp_data;

31. Cursor Variable as a Host Variable • You can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program. To use the cursor variable, you must pass it as a host variable to PL/SQL. • In the following Pro*C example, you pass a host cursor variable and selector to a PL/SQL block, which opens the cursor variable for the chosen query: EXEC SQL BEGIN DECLARE SECTION; ... /* Declare host cursor variable. */ SQL_CURSOR generic_cv; int choice; EXEC SQL END DECLARE SECTION; ...

32. Cursor Variable as a Host Variable /* Initialize host cursor variable. */ EXEC SQL ALLOCATE :generic_cv; ... /* Pass host cursor variable and selector to PL/SQL block. */ EXEC SQL EXECUTE BEGIN IF :choice = 1 THEN OPEN :generic_cv FOR SELECT * FROM emp; ELSIF :choice = 2 THEN OPEN :generic_cv FOR SELECT * FROM dept; ELSIF :choice = 3 THEN OPEN :generic_cv FOR SELECT * FROM salgrade; END IF; END; END-EXEC;

33. Fetching from Cursor variable • Using the BULK COLLECT clause, you can bulk fetch rows from a cursor variable into one or more collections. An example follows: DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; TYPE NameList IS TABLE OF emp.ename%TYPE; TYPE SalList IS TABLE OF emp.sal%TYPE; emp_cv EmpCurTyp; names NameList; sals SalList; BEGIN OPEN emp_cv FOR SELECT ename, sal FROM emp; FETCH emp_cv BULK COLLECT INTO names, sals; ... END;

34. Static Cursor and Cursor variable • Ref cursors may be dynamically opened or opened based on logic. Declare type rc is ref cursor; cursor c is select * from dual; l_cursor rc; begin if ( to_char(sysdate,'dd') = 30 ) then open l_cursor for 'select * from emp'; elsif ( to_char(sysdate,'dd') = 29 ) then open l_cursor for select * from dept; else open l_cursor for select * from dual; end if; open c; -- Static cursor end;

35. Static Cursor and Cursor variable • A ref cursor can be returned to a client. a plsql "cursor cursor" cannot be returned to a client. • A cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function). • A ref cursor can be passed from subroutine to subroutine -- a cursor cannot be. • Static sql (not using a ref cursor) is much more efficient then using ref cursors and that use of ref cursors should be limited to - returning result sets to clients. - when there is NO other efficient/effective means of achieving the goal