[Chapter 4] 4.2 Scalar Datatypes Chapter 4Variables and Program Data 4.2 Scalar Datatypes Each constant and variable element you use in your programs has a datatype. The datatype dictates the storage format, the restrictions on how the variable can be used, and the valid values which may be placed in that variable. PL/SQL offers a comprehensive set of predefined scalar and composite datatypes. A scalar datatype is an atomic; it is not made up of other variable components. A composite datatype has internal structure or components. The two composite types currently supported by PL/SQL are the record and table (described in Chapter 9, Records in PL/SQL, and Chapter 10, PL/SQL Tables, respectively). The scalar datatypes fall into one of four categories or families: number, character, Boolean, and date-time, as shown in Table 4.1. Table 4.1: Datatype Categories Category Datatype Number BINARY_INTEGER DEC DECIMAL DOUBLE PRECISION FLOAT INT INTEGER NATURAL NUMBER NUMERIC PLS_INTEGER POSITIVE REAL SMALLINT Character CHAR CHARACTER LONG LONG RAW NCHAR NVARCHAR2 RAW ROWID STRING VARCHAR VARCHAR2 Boolean BOOLEAN Date-time DATE Large object (LOB) BFILE BLOB CLOB NCLOB Let's take a closer look at each of the scalar datatypes. 4.2.1 Numeric Datatypes PL/SQL, just like the Oracle RDBMS, offers a variety of numeric datatypes to suit different purposes. There are generally two types of numeric data: whole number and decimal (in which digits to the right of the decimal point are allowed). 4.2.1.1 Binary integer datatypes The whole number, or integer, datatypes are: BINARY_INTEGERINTEGERSMALLINTINTPOSITIVENATURALThe BINARY_INTEGER datatype allows you to store signed integers. The range of magnitude of a BINARY_INTEGER is -231 + 1 through 231 - 1 (231 is equal to 2147483647). BINARY_INTEGERs are represented in the PL/SQL compiler as signed binary numbers. They do not, as a result, need to be converted before PL/SQL performs numeric calculations. Variables of type NUMBER (see Section 4.2.1.2, "Decimal numeric datatypes"") do, however, need to be converted. So if you will be performing intensive calculations with integer values, you might see a performance improvement by declaring your variables as BINARY_INTEGER. In most situations, to be honest, the slight savings offered by BINARY_INTEGER will not be noticeable. NATURAL and POSITIVE are both subtypes of BINARY_INTEGER. A subtype uses the storage format and restrictions on how the variable of this type can be used, but it allows only a subset of the valid values allowed by the full datatype. In the case of BINARY_INTEGER subtypes, we have the following value subsets: NATURAL0 through 231POSITIVE1 through 231If you have a variable whose values must always be non-negative (0 or greater), you should declare that variable to be NATURAL or POSITIVE. This improves the self-documenting aspect of your code. 4.2.1.2 Decimal numeric datatypesThe decimal numeric datatypes are: NUMBERFLOATDECDECIMALDOUBLE PRECISIONNUMBERNUMERICREAL Use the NUMBER datatype to store fixed or floating-point numbers of just about any size. The maximum precision of a variable with NUMBER type is 38 digits. This means that the range of magnitude of values is 1.0E-129 through 9.999E125; you are unlikely to require numbers outside of this range. When you declare a variable type NUMBER, you can also optionally specify the variable's precision and scale, as follows: NUMBER (precision, scale) The precision of a NUMBER is the total number of digits. The scale dictates the number of digits to the right or left of the decimal point at which rounding occurs. Both the precision and scale values must be literal values (and integers at that); you cannot use variables or constants in the declaration. Legal values for the scale range from -84 to 127. Rounding works as follows: If the scale is positive, then the scale determines the point at which rounding occurs to the right of the decimal point. If the scale is negative, then the scale determines the point at which rounding occurs to the left of the decimal point. If the scale is zero, then rounding occurs to the nearest whole number. If the scale is not specified, then no rounding occurs.The following examples demonstrate the different ways you can declare variables of type NUMBER: The bean_counter variable can hold values with up to ten digits of precision, three of which are to the right of the decimal point. If you assign 12345.6784 to bean_counter, it is rounded to 12345.678. If you assign 1234567891.23 to the variable, the operation will return an error because there are more digits than allowed for in the precision. bean_counter NUMBER (10,3);The big_whole_number variable contains whole numbers spanning the full range of supported values, because the default precision is 38 and the default scale is 0. big_whole_number NUMBER;The rounded_million variable is declared with a negative scale. This causes rounding to the left of the decimal point. Just as a scale of -1 would cause rounding to the nearest tenth, a scale of -2 would round to the nearest hundred and a scale of -6 would round to the nearest million. If you assign 53.35 to rounded_million, it will be rounded to 0. If you assign 1,567,899 to rounded_million, it will be rounded to two million (2,000,000). rounded_million NUMBER (10,-6);In the following unusual but perfectly legitimate declaration, the scale is larger than the precision. In this case, the precision indicates the maximum number of digits allowed -- all to the right of the decimal point. If you assign .003566 to small_value, it will be rounded to .00357. Because the scale is two greater than the precision, any value assigned to small_value must have two zeros directly to the right of the decimal point, followed by up to three nonzero digits. small_value NUMBER (3, 5);4.2.1.3 The PLS_INTEGER datatype This datatype is available in PL/SQL Release 2.3 and above. Variables declared as PLS_INTEGER store signed integers. The magnitude range for this datatype is -2147483647 through 2147483647. Oracle recommends that you use PLS_INTEGER for all integer calculations which do not fall outside of its range. PLS_INTEGER values require less storage than NUMBER values, and operations on PLS_INTEGER's use machine arithmetic, making them more efficient. Variables declared as pls_integer and binary_integer have the same range, but they are treated differently. When a calculation involving pls_integer overflows, pl/sql raises an exception. However, similar overflow involving binary_integers will not raise an exception if the result is being assigned to a number variable. 4.2.2 Numeric SubtypesThe remainder of the datatypes in the numeric category are all subtypes of NUMBER. They are provided in ORACLE's SQL and in PL/SQL in order to offer compatibility with ANSI SQL, SQL/DS, and DB2 datatypes. They have the same range of legal values as their base type, as displayed in Table 4.2. The NUMERIC, DECIMAL, and DEC datatypes can declare only fixed-point numbers. FLOAT, DOUBLE PRECISION, and REAL allow floating decimal points with binary precisions that range from 63 to 126. Table 4.2: Predefined Numeric Subtypes Subtype Compatibility Corresponding Oracle Datatype 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) Prec, scale, and binary have the following meanings:precPrecision for the subtypescale Scale of the subtypebinaryBinary precision of the subtype4.2.3 Character Datatypes Variables with character datatypes store text and are manipulated by character functions. Because character strings are "free-form," there are few rules concerning their content. You can, for example, store numbers and letters, as well as any combination of special characters, in a character-type variable. There are, however, several different kinds of character datatypes, each of which serves a particular purpose. 4.2.3.1 The CHAR datatypeThe CHAR datatype specifies that the character string has a fixed length. When you declare a fixed-length string, you also specify a maximum length for the string, which can range from 1 to 32767 bytes (this is much higher than that for the CHAR datatype in the Oracle RDBMS, which is only 255). If you do not specify a length for the string, then PL/SQL declares a string of one byte. Note that this is the opposite of the situation with the NUMBER datatype. For example, a declaration of: fit_almost_anything NUMBER;results in a numeric variable with up to 38 digits of precision. You could easily get into a bad habit of declaring all your whole number variables simply as NUMBER, even if the range of legal values is much smaller than the default. However, if you try a similar tactic with CHAR, you may be in for a nasty surprise. If you declare a variable as follows: line_of_text CHAR;then as soon as you assign a string of more than one character to line_of_text, PL/SQL will raise the generic VALUE_ERROR exception. It will not tell you where it encountered this problem. So if you do get this error, check your variable declarations for a lazy use of CHAR. Just to be sure, you should always specify a length when you use the CHAR datatype. Several examples follow: yes_or_no CHAR (1) DEFAULT 'Y'; line_of_text CHAR (80); --Always a full 80 characters! whole_paragraph CHAR (10000); --Think of all the spaces...Remember that even though you can declare a CHAR variable with 10,000 characters, you will not be able to stuff that PL/SQL variable's value into a database column of type CHAR. It will take up to 255 characters. So if you want to insert a CHAR value into the database and its declared length is greater than 255, you will have to use the SUBSTR function (described in Chapter 11, Character Functions) to trim the value down to size: INSERT INTO customer_note (customer_id, full_text /* Declared as CHAR(255) */) VALUES (1000, SUBSTR (whole_paragraph, 1, 255));Because CHAR is fixed-length, PL/SQL will right-pad any value assigned to a CHAR variable with spaces to the maximum length specified in the declaration. Prior to Oracle7, the CHAR datatype was variable-length; Oracle did not, in fact, support a fixed-length character string datatype and prided itself on that fact. To improve compatibility with IBM relational databases and to comply with ANSI standards, Oracle7 reintroduced CHAR as a fixed-length datatype and offered VARCHAR2 as the variable-length datatype. When a Version 6 RDBMS is upgraded to Oracle7, all CHAR columns are automatically converted to VARCHAR2. (VARCHAR2 is discussed in the next section.) You will rarely need or want to use the CHAR datatype in Oracle-based applications. In fact, I recommend that you never use CHAR unless there is a specific requirement for fixed-length strings or unless you are working with data sources like DB2. Character data in DB2 is almost always stored in fixed-length format due to performance problems associated with variable-length storage. So, if you build applications that are based on DB2, you may have to take fixed-length data into account in your SQL statements and in your procedural code. You may, for example, need to use RTRIM to remove trailing spaces from (or RPAD to pad spaces onto) many of your variables in order to allow string comparisons to function properly. (These character functions are described in Chapter 11.) 4.2.3.2 The VARCHAR2 and VARCHAR datatypes VARCHAR2 variables store variable-length character strings. When you declare a variable-length string, you must also specify a maximum length for the string, which can range from 1 to 32767 bytes. The general format for a VARCHAR2 declaration is: <variable_name> VARCHAR2 (<max_length>);as in:DECLARE small_string VARCHAR2(4); line_of_text VARCHAR2(2000);NOTE: In Version 1.1 of PL/SQL, which you use in Oracle Developer/2000 tools like Oracle Forms, the compiler does not insist that you include a maximum length for a VARCHAR2 declaration. As a result, you could mistakenly leave off the length in the declaration and end up with a variable with a maximum length of a single character. As discussed in the section on the fixed-length CHAR datatype, this can cause PL/SQL to raise runtime VALUE_ERROR exceptions. Always include a maximum length in your character variable declarations. The maximum length allowed for PL/SQL VARCHAR2 variables is a much higher maximum than that for the VARCHAR2 datatype in the Oracle RDBMS, which is only 2000. As a result, if you plan to store a PL/SQL VARCHAR2 value into a VARCHAR2 database column, you must remember that only the first 2000 can be inserted. Neither PL/SQL nor SQL automatically resolves this inconsistency, though. You will need to make sure you don't try to pass more than the maximum 2000 (actually, the maximum length specified for the column) through the use of the SUBSTR function. Because the length of a LONG column is two gigabytes, on the other hand, you can insert PL/SQL VARCHAR2 values into a LONG column without any worry of overflow. (LONG is discussed in the next section.) The VARCHAR datatype is actually a subtype of VARCHAR2, with the same range of values found in VARCHAR2. VARCHAR, in other words, is currently synonymous with VARCHAR2. Use of VARCHAR offers compatibility with ANSI and IBM relational databases. There is a strong possibility, however, that VARCHAR's meaning might change in a new version of the ANSI SQL standards. Oracle recommends that you avoid using VARCHAR if at all possible, and instead stick with VARCHAR2 to declare variable-length PL/SQL variables (and table columns as well). If you make use of both fixed-length (CHAR) and variable-length (VARCHAR2) strings in your PL/SQL code, you should be aware of the following interactions between these two datatypes: Database-to-variable conversion. When you SELECT or FETCH data from a CHAR database column into a VARCHAR2 variable, the trailing spaces are retained. If you SELECT or FETCH from a VARCHAR2 database column into a CHAR variable, PL/SQL automatically pads the value with spaces out to the maximum length. In other words, the type of the variable, not the column, determines the variable's resulting value. Variable-to-database conversion. When you INSERT or UPDATE a CHAR variable into a VARCHAR2 database column, the SQL kernel does not trim the trailing blanks before performing the change. When the following PL/SQL is executed, the company_name in the new database record is set to `ACME SHOWERS········' (where · indicates a space). It is, in other words, padded out to 20 characters, even though the default value was a string of only 12 characters: DECLARE comp_id# NUMBER; comp_name CHAR(20) := 'ACME SHOWERS'; BEGIN SELECT company_id_seq.NEXTVAL INTO comp_id# FROM dual; INSERT INTO company (company_id, company_name) VALUES (comp_id#, comp_name); END;On the other hand, when you INSERT or UPDATE a VARCHAR2 variable into a CHAR database column, the SQL kernel automatically pads the variable-length string with spaces out to the maximum (fixed) length specified when the table was created, and places that expanded value into the database. String comparisons. Suppose your code contains a string comparison such as the following: IF company_name = parent_company_name ...PL/SQL must compare company_name to parent_company_name. It performs the comparison in one of two ways, depending on the types of the two variables: If a comparison is made between two CHAR variables, then PL/SQL uses a blank-padding comparison. With this approach, PL/SQL blank-pads the shorter of the two values out to the length of the longer value. It then performs the comparison. So with the above example, if company_name is declared CHAR(30) and parent_company_name is declared CHAR(35), then PL/SQL adds five spaces to the end of the value in company_name and then performs the comparison. Note that PL/SQL does not actually change the variable's value. It copies the value to another memory structure and then modifies this temporary data for the comparison. If at least one of the strings involved in the comparison is variable-length, then PL/SQL performs a nonblank-padding comparison. It makes no changes to any of the values, uses the existing lengths, and performs the comparison. This comparison analysis is true of evaluations which involve more than two variables as well, as may occur with the IN operator: IF menu_selection NOT IN (save_and_close, cancel_and_exit, 'OPEN_SCREEN') THEN ...If any of the four variables (menu_selection, the two named constants, and the single literal) is declared VARCHAR2, then exact comparisons without modification are performed to determine if the user has made a valid selection. Note that a literal like OPEN_SCREEN is always considered a fixed-length CHAR datatype. These rules can make your life very complicated. Logic which looks perfectly correct may not operate as expected if you have a blend of fixed-length and variable-length data. Consider the following fragment: DECLARE company_name CHAR (30) DEFAULT 'PC HEAVEN'; parent_company_name VARCHAR2 (25) DEFAULT 'PC HEAVEN'; BEGIN IF company_name = parent_company_name THEN -- This code will never be executed. END IF; END;The conditional test will never return TRUE because the value company_name has been padded to the length of 30 with 21 spaces. To get around problems like this, you should always RTRIM your CHAR values when they are involved in any kind of comparison or database modification. It makes more sense to use RTRIM (to remove trailing spaces) than it does to use RPAD (to pad variable-length strings with spaces). With RPAD you have to know what length you wish to pad the variable-length string to get it in order to match the fixed-length string. With RTRIM you just get rid of all the blanks and let PL/SQL perform its nonblank-padding comparison. It was easy to spot the problem in this anonymous PL/SQL block because all the related statements are close together. In the real world, unfortunately, the variables' values are usually set in a much less obvious manner and are usually in a different part of the code from the conditional statement which fails. So if you have to use fixed-length variables, be on the lookout for logic which naively believes that trailing spaces are not an issue. 4.2.3.3 The LONG datatypeA variable declared LONG can store variable-length strings of up to 32760 bytes -- this is actually seven fewer bytes than allowed in VARCHAR2 type variables! The LONG datatype for PL/SQL variables is quite different from the LONG datatype for columns in the Oracle Server. The LONG datatype in Oracle7 can store character strings of up to two gigabytes or 231-1 bytes; this large size makes the LONG column a possible repository of multimedia information, such as graphics images. As a result of these maximum length differences, you can always insert a PL/SQL LONG variable value into a LONG database column, but you cannot select a LONG database value larger than 32760 bytes into a PL/SQL LONG variable. In the Oracle database, there are many restrictions on how the LONG column can be used in a SQL statement; for example: A table may not contain more than one single LONG column. You may not use the LONG column in a GROUP BY, ORDER BY, WHERE, or CONNECT BY clause. You may not apply character functions (such as SUBSTR, INSTR, or LENGTH), to the LONG column. PL/SQL LONG variables are free of these restrictions. In your PL/SQL code you can use a variable declared LONG just as you would a variable declared VARCHAR2. You can apply character functions to the variable. You can use it in the WHERE clause of a SELECT or UPDATE statement. This all makes sense given that, at least from the standpoint of the maximum size of the variables, there is really little difference between VARCHAR2 and LONG in PL/SQL. Given the fact that a VARCHAR2 variable actually has a higher maximum length than the LONG and has no restrictions attached to it, I recommend that you always use the VARCHAR2 datatype in PL/SQL programs. LONGs have a place in the RDBMS, but that role is not duplicated in PL/SQL. This makes some sense since you will very rarely want to manipulate truly enormous strings within your program using such functions as SUBSTR or LENGTH or INSTR. 4.2.3.4 The RAW datatype The RAW datatype is used to store binary data or other kinds of raw data, such as a digitized picture or image. A RAW variable has the same maximum length as VARCHAR2 (32767 bytes), which must also be specified when the variable is declared. The difference between RAW and VARCHAR2 is that PL/SQL will not try to interpret raw data. Within the Oracle RDBMS this means 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. Once again, there is an inconsistency between the PL/SQL maximum length for a RAW variable (32767) and the RDBMS maximum length (255). As a result, you cannot insert more than 255 bytes of your PL/SQL RAW variable's value into a database column. You can, on the other hand, insert the full value of a PL/SQL RAW variable into a column with type LONG RAW, which is a two-gigabyte container for raw data in the database. 4.2.3.5 The LONG RAW datatypeThe LONG RAW datatype stores raw data of up to 32760 bytes and is just like the LONG datatype except that the data in a LONG RAW variable is not interpreted by PL/SQL. Given the fact that a RAW variable actually has a higher maximum length than the LONG RAW and has no restrictions attached to it, I recommend that you always use the RAW datatype in PL/SQL programs. LONG RAWs have a place in the RDBMS, but that role is not duplicated in PL/SQL. 4.2.3.6 The ROWID datatypeIn the Oracle RDBMS, ROWID is a pseudocolumn that is a part of every table you create. The rowid is an internally generated and maintained binary value which identifies a row of data in your table. It is called a pseudocolumn because a SQL statement includes it in places where you would normally use a column. However, it is not a column that you create for the table. Instead, the RDBMS generates the rowid for each row as it is inserted into the database. The information in the rowid provides the exact physical location of the row in the database. You cannot change the value of a rowid. You can use the ROWID datatype to store rowids from the database in your pl/sql program. You can SELECT or FETCH the rowid for a row into a ROWID variable. To manipulate rowids in Oracle8, you will want to use the built-in package, dbms_rowid (see Appendix A, What's on the Companion Disk?). In Oracle7, you will use the rowidtochar function to convert the rowid to a fixed-length string and then perform operations against that string. In Oracle7, the format of the fixed-length rowid is as follows:BBBBBBB.RRRR.FFFFFComponents of this format have the following meanings:BBBBBBBThe block in the database fileRRRRThe row in the block (where the first row is zero, not one)FFFFFThe database fileAll these numbers are hexadecimal; the database file is a number which you would then use to look up the actual name of the database file through the data dictionary. In Oracle8, rowid have been "extended" to support partitioned tables and indexes. The new, extended rowids include a data object number, identifying the database segment. Any schema object found in the same segment, such as a cluster of tables, will have the same object number. In Oracle8, then, a rowid contains the following information: The data object numberThe data file (where the first file is 1)The data block within the data fileThe row in the data block (where the first row is 0)Oracle8 provides functions in the dbms_rowid package to convert between the new formats of rowids. Usually (and always in Oracle7), a rowid will uniquely identify a row of data. Within Oracle8, however, rows in different tables stored in the same cluster can have the same rowid value. You are now probably thinking, "Why is he telling me this? Do I actually have to know about the physical blocks in the Oracle RDBMS? I thought the whole point of the relational approach is that I can focus on the logical design of my data and ignore the physical representation. Rowids are scary!" Calm down. Very rarely would you want to use a rowid, and in those cases you probably wouldn't care about its internal structure. You would simply use it to find a row in the database. Access by rowid is typically the fastest way to locate or retrieve a particular row in the database: faster even than a search by primary key. You could make use of the rowid in an Oracle Forms application to access the row in the database corresponding to the record on the screen. When you create a base-table block in Oracle Forms, it automatically includes the rowid in the block as an "invisible pseudoitem." You do not see it on your item list, but you can reference it in your triggers and PL/SQL program units. For example, to update the name of an employee displayed on the screen, you could issue the following statement: UPDATE employee SET last_name = :employee.last_name WHERE rowid = :employee.rowid;You can also use rowid inside a cursor FOR loop (or any other loop which FETCHes records from a cursor) to make changes to the row just FETCHed, as follows: PROCEDURE remove_internal_competitors IS BEGIN FOR emp_rec IN (SELECT connections, rowid FROM employee WHERE sal > 50000) LOOP IF emp_rec.connections IN ('President', 'CEO') THEN send_holiday_greetings; ELSE DELETE FROM employee WHERE rowid = emp_rec.rowid; END IF; END LOOP; END;The DELETE uses the rowid stored in the emp_rec record to immediately get rid of anyone making more than $50,000 who does not have known connections to the President or CEO. Note that the DBA controls who may have EXECUTE privilege to this stored procedure. So one must now wonder: does the DBA have connections to the President or CEO? Well, in any case, use of the rowid guarantees the fastest possible DELETE of that employee. Of course, the above procedure could also simply have fetched the employee_id (primary key of the employee table) and executed a DELETE based on that real column, as in: DELETE FROM employee WHERE employee_id = emp_id;I am not convinced that the theoretical performance gains of searching by rowid justify its use. The resulting code is harder to understand than the application-specific use of the primary key. Furthermore, references to rowid could cause portability problems in the future.[1] [1] The rowid is not a part of the ANSI SQL standard; instead, it reflects directly the internal storage structure of the Oracle RDBMS. Use of this proprietary pseudo-column is akin to coding a clever trick in FORTRAN 77 which takes advantage of a loophole in the compiler to gain performance. The improvements could be wiped out in a future release of the software. If you are building applications which may need to work against both Oracle and non-Oracle data sources, you should avoid any references to the rowid pseudo-column and the ROWID datatype.4.2.4 The Boolean Datatype The Oracle RDBMS/SQL language offers features not found in PL/SQL, such as the Oracle SQL DECODE construct. PL/SQL, on the other hand, has a few tricks up its sleeve which are unavailable in native SQL. One particularly pleasant example of this is the BOOLEAN datatype.[2] Boolean data may only be TRUE, FALSE, or NULL. A Boolean is a "logical" datatype. [2] The Boolean is named after George Boole, who lived in the first half of the 19th century and is considered "the father of symbolic logic." One therefore capitalizes "Boolean," whereas the other datatypes get no respect.The Oracle RDBMS does not support a Boolean datatype. You can create a table with a column of datatype CHAR(1) and store either "Y" or "N" in that column to indicate TRUE or FALSE. That is a poor substitute, however, for a datatype which stores those actual Boolean values (or NULL). Because there is no counterpart for the PL/SQL Boolean in the Oracle RDBMS, you can neither SELECT into a Boolean variable nor insert a TRUE or FALSE value directly into a database column. Boolean values and variables are very useful in PL/SQL. Because a Boolean variable can only be TRUE, FALSE, or NULL, you can use that variable to explain what is happening in your code. With Booleans you can write code which is easily readable, because it is more English-like. You can replace a complicated Boolean expression involving many different variables and tests with a single Boolean variable that directly expresses the intention and meaning of the text. 4.2.5 The Date-Time DatatypeMost of our applications require the storage and manipulation of dates and times. Dates are quite complicated: not only are they highly-formatted data, but there are myriad rules for determining valid values and valid calculations (leap days and years, national and company holidays, date ranges, etc.). Fortunately, the Oracle RDBMS and PL/SQL offer us help in many ways to handle date information. The RDBMS provides a true DATE datatype which stores both date and time information. While you can enter a date value in a variety of formats, the RDBMS stores the date in a standard, internal format. It is a fixed-length value which uses seven bytes. You cannot actually specify this internal or literal value with an assignment. Instead you rely on implicit conversion of character and numeric values to an actual date, or explicit conversion with the TO_DATE function. (The next section describes these types of conversion.) PL/SQL provides a DATE datatype which corresponds directly to the RDBMS DATE. An Oracle DATE stores the following information:centuryyearmonthdayhourminutesecondPL/SQL validates and stores dates which fall between January 1, 4712 B.C. to December 31, 4712 A.D. The time component of a date is stored as the number of seconds past midnight. If you enter a date without a time (many applications do not require the tracking of time, so PL/SQL lets you leave it off), the time portion of the database value defaults to midnight (12:00:00 AM). Neither the Oracle RDBMS DATE nor the PL/SQL DATE datatypes store times in increments of less than single seconds. The DATE datatype, therefore, is not very useful for tracking real-time activities which occur in subsecond intervals. If you need to track time at subsecond intervals, you could instead store this information as a number. You can obtain subsecond timings using the DBMS_UTILITY package's GET_TIME function described in Appendix C, Built-In Packages. Because a variable declared DATE is a true date and not simply a character representation of a date, you can perform arithmetic on date variables, such as the subtraction of one date from another, or the addition/subtraction of numbers from a date. You can make use of date functions, described in Chapter 12, Date Functions, which offer a wide range of powerful operations on dates. Use the SYSDATE function to return the current system date and time. You can also use the TO_CHAR conversion function (described in Chapter 14, Conversion Functions) to convert a date to character string or to a number. In PL/SQL, a Julian date is the number of days since the first valid date, January 1, 4712 BC. Use Julian dates if you need to perform calculations or display date information with a single point of reference and continuous dating. 4.2.6 NLS Character DatatypesWhen working with languages like Japanese, the 8-bit ASCII character set is simply not able to represent all of the available characters. Such languages require 16 bits (two bytes) to represent each character. Oracle offers National Language Support (NLS) to process single-byte and multibyte character data. NLS features also allow you to convert between character sets. PL/SQL8 supports two character sets which allow for the storage and manipulation of strings in either single-byte or multibyte formats. The two character sets are: Database character set: used for PL/SQL identifiers and source codeNational character set: used for NLS dataPL/SQL offers two datatypes, NCHAR and NVARCHAR2, to store character strings formed from the national character set. 4.2.6.1 The NCHAR datatypeUse the NCHAR datatype to store fixed-length nls character data. The internal representation of the data is determined by the national character set. When you declare a variable of this type, you can also specify its length. If you do not provide a length, the default of 1 is used. Here is the declaration of a NCHAR variable with a length of 10:ssn NCHAR (10);Here is the declaration of a NCHAR variable with a default length of 1: yes_no NCHAR;The maximum length for NCHAR variables is 32767.But what does "a length of 10" actually mean? If the national character set is a fixed-width character set, then the length indicates length in characters. If the national character set is a variable-width character set (JA16SJIS is one example), then the length indicates length in bytes. 4.2.6.2 The NVARCHAR2 datatypeUse the nvarchar2 datatype to store variable-length nls character data. The internal representation of the data is determined by the national character set. When you declare a variable of this type, you must also specify its length. Here is the declaration of an nvarchar2 variable with a maximum length of 200: any_name NVARCHAR2 (200);The maximum length allowed for nvarchar2 variables is 32767. Length has the same meaning described above for nchar. 4.2.7 LOB DatatypesOracle8 and PL/SQL8 support several variations of LOB (large object) datatypes. LOBs can store large amounts (up to four gigabytes) of raw data, binary data (such as images), or character text data. Within PL/SQL you can declare LOB variables of the following datatypes: BFILEDeclares variables that hold a file locator pointing to large binary objects in operating system files outside of the database. BLOBDeclares variables that hold a LOB locator pointing to a large binary object. CLOBDeclares variables that hold a LOB locator pointing to a large block of single-byte, fixed-width character data. NCLOBDeclares variables that hold a LOB locator pointing to a large block of single-byte or fixed-width multibyte character data. There are two types of LOBs in Oracle8: internal and external. Internal LOBs (BLOB, CLOB, and NCLOB) are stored in the database and can participate in a transaction in the database server. External LOBs (BFILE) are large binary data stored in operating system files outside the database tablespaces. External LOBs cannot participate in transactions. You cannot, in other words, commit or roll back changes to a BFILE. Instead, you rely on the underlying filesystem for data integrity. 4.2.7.1 The BFILE datatypeUse the BFILE datatype to store large binary objects (up to four gigabytes in size) in files outside of the database. This variable gives you read-only, byte-stream I/O access to these files (which can reside on a hard disk, CD-ROM, or other such device). When you declare a BFILE variable, you allocate memory to store the file locator of the BFILE, not the BFILE contents itself. This file locator contains a directory alias as well as a file name. See Section 4.2.7.7, "Working with BFILEs" later in this chapter for more information about the file locator. Here is an example of a declaration of a BFILE variable:DECLARE book_part1 BFILE;4.2.7.2 The BLOB datatypeUse the BLOB datatype to store large binary objects "out of line" inside the database. This means that when a table has a BLOB column, a row of data for that table contains a pointer or a locator to the actual location of the BLOB data (so it is not "in line" with the other column values of the row). A BLOB variable contains a locator, which then points to the large binary object. BLOBs can be up to four gigabytes in size, and they participate fully in transactions. In other words, any changes you make to a BLOB (via the DBMS_LOB built-in package) can be rolled back or committed along with other outstanding changes in your transaction. BLOB locators cannot, however, span transactions or sessions. Here is an example of a declaration of a BLOB variable:DECLARE family_portrait BLOB;4.2.7.3 The CLOB datatypeUse the CLOB datatype to store large blocks of single-byte character data "out of line" inside the database. This means that when a table has a CLOB column, a row of data for that table contains a pointer or locator to the actual location of the CLOB data (so it is not "in line" with the other column values of the row). A CLOB variable contains a locator, which then points to the large block of single-byte character data. CLOBs can be up to four gigabytes in size, and they participate fully in transactions. In other words, any changes you make to a CLOB (via the DBMS_LOB built-in package) can be rolled back or committed along with other outstanding changes in your transaction. CLOB locators cannot, however, span transactions or sessions. Variable-width character sets are not supported in CLOBs.Here is an example of a declaration of a CLOB variable:DECLARE war_and_peace_text CLOB;4.2.7.4 The NCLOB datatypeUse the NCLOB datatype to store large blocks of single-byte or fixed-width multibyte character data "out of line" inside the database. This means that when a table has a NCLOB column, a row of data for that table contains a pointer or locator to the actual location of the NCLOB data (so it is not "in line" with the other column values of the row). A NCLOB variable contains a locator, which then points to the large block of single-byte character data. NCLOBs can be up to four gigabytes in size, and they participate fully in transactions. In other words, any changes you make to a NCLOB (via the DBMS_LOB built-in package) can be rolled back or committed along with other outstanding changes in your transaction. NCLOB locators cannot, however, span transactions or sessions. Variable-width character sets are not supported in NCLOBs.Here is an example of a declaration of a NCLOB variable:DECLARE war_and_peace_in japanese NCLOB;4.2.7.5 LOBs and LONGsLOB types are different from, and preferable to, LONG and LONG RAW. The maximum size of a LONG is two gigabytes, whereas the maximum size of a LOB is four gigabytes. Oracle offers a powerful new built-in package, DBMS_LOB, to help you manipulate the contents of LOBs in ways not possible with LONGs. Generally, Oracle offers you random access to LOB contents, whereas with LONGs you have only sequential access. For example, with DBMS_LOB you can perform SUBSTR and INSTR operations against a LOB. This is not possible with LONG data. Oracle recommends that you no longer use LONG or LONG RAW in your applications and instead take advantage of the new and improved features of the LOB datatypes. If you are going to be working with object types, you really don't have much choice: a LOB (except for NCLOB) can be an attribute of an object type, but LONGs cannot. 4.2.7.6 Working with LOBsLOB values are not stored "in line" with other row data. Instead, a LOB locator, which points to the LOB, is stored in the row. Suppose that I have created the following table: CREATE TABLE favorite_books (isbn VARCHAR2(50), title VARCHAR2(100), contents_loc CLOB);I can then display the number of characters in the book, The Bell Curve, with the following code: CREATE OR REPLACE PROCEDURE howbig (title_in IN VARCHAR2) IS CURSOR book_cur IS SELECT contents_loc FROM favorite_books WHERE title = UPPER (title_in); book_loc CLOB; BEGIN OPEN book_cur; FETCH book_cur INTO book_loc; IF book_cur%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE ('Remember? You don''t like "' || INITCAP (title_in) || '".'); ELSE DBMS_OUTPUT.PUT_LINE (title_in || ' contains ' || TO_CHAR (DBMS_LOB.GETLENGTH (book_loc)) || ' characters.'); END IF; CLOSE book_cur; END; / SQL> exec howbig ('the bell curve'); Remember? You don't like "The Bell Curve".Here is an example of copying a BLOB from one row to another in SQL:INSERT INTO favorite_books (isbn, title, contents_loc) SELECT isbn, title || ', Second Edition', contents_loc FROM favorite_books WHERE title = 'Oracle PL/SQL Programming';In this situation, I have assigned a new LOB locator for my second edition. I have also copied the LOB value (the contents of my first edition) to this new row, not merely created another locator or pointer back to the same text. Notice that I copied the entire contents of my book. DML operations such as INSERT and UPDATE always affect an entire LOB. If you want to change or delete just a portion of a LOB, you need to call the appropriate functions in the DBMS_LOB package. You cannot directly copy values between a character LOB and a VARCHAR2 variable, even if the LOB value is small and "fits" inside the specified VARCHAR2 variable. You can, however, use functions in the DBMS_LOB package to extract some or all of a CLOB value and place it in a VARCHAR2 variable (as the following example shows): DECLARE big_kahuna CLOB; little_kahuna VARCHAR2(2000); BEGIN /* I know it's in here. */ SELECT contents_loc INTO big_kahuna FROM favorite_books WHERE title = 'WAR AND PEACE'; /* Get first 2000 characters of book. */ little_kahuna := DBMS_LOB.SUBSTR (big_kahuna, 2000, 1); END;4.2.7.7 Working with BFILEsBFILEs are very different from internal LOBs in a number of ways: The value of a BFILE is stored in an operating system file, not within the database at all. BFILEs do not participate in transactions (i.e., changes to a BFILE cannot be rolled back or committed). When you work with BFILEs in PL/SQL, you still do work with a LOB locator. In the case of a BFILE, however, the locator simply points to the file stored on the server. For this reason, two different rows in a database table can have a BFILE column which point to the same file. A BFILE locator is composed of a directory alias and a file name. You use the BFILENAME function (see Chapter 13, Numeric, LOB, and Miscellaneous Functions) to return a locator based on those two pieces of information. In the following block, I declare a BFILE variable and assign it a locator for a file named family.ipg located in the photos "directory": DECLARE all_of_us BFILE; BEGIN all_of_us := BFILENAME ('photos', 'family.ipg'); END;But what precisely is "photos"? It doesn't conform to the format used for directories in UNIX, Windows NT, etc. It is, in fact, a database object called a DIRECTORY. Here is the statement I would use to create a directory: CREATE DIRECTORY photos AS 'c:\photos';You will need the CREATE DIRECTORY or CREATE ANY DIRECTORY privileges to create a directory. To be able to reference this directory you must be granted the READ privilege, as in: GRANT READ ON DIRECTORY photos TO SCOTT;For more information on directory aliases, see Section 13.2.1, "The BFILENAME function" in Chapter 13. The maximum number of BFILEs that can be opened within a session is established by the database initialization parameter, SESSION_MAX_OPEN_FILES. This parameter defines an upper limit on the number of files opened simultaneously in a session (not just BFILEs, but all kinds of files, including those opened using the UTL_FILE package). 4.2.8 Conversion Between DatatypesBoth SQL and PL/SQL offer many different types of data. In many situations -- more frequently than you perhaps might like to admit -- you will find it necessary to convert your data from one datatype to another. You might have one table which stores primary key information as a character string, and another table which stores that same key as a foreign key in numeric format. When you perform an assignment, you will need to convert the information: :employee.department_num -- the numeric format := :department.depno -- the character formatYou might wish to view a rowid value, in which case it is necessary to convert that value to character (hex) format, as follows: ROWIDTOCHAR (:employee.rowid);Or you might perform date comparisons by specifying dates as literals, as in the following: IF start_date BETWEEN '01-JAN-95' AND last_sales_date THEN ...Whenever PL/SQL performs an operation involving one or more values, it must first convert the data so that it is in the right format for the operation. There are two kinds of conversion: explicit and implicit. 4.2.8.1 Explicit data conversionsAn explicit conversion takes place when you use a built-in conversion function to force the conversion of a value from one datatype to another. In the earlier example which demonstrated viewing a rowid value, I used the ROWIDTOCHAR conversion function so that the PUT_LINE function could display the resulting character string. PL/SQL provides a full set of conversion functions to enable conversion from one datatype to another. (These functions are explored more fully in Chapter 14.) 4.2.8.2 Implicit data conversionsWhenever PL/SQL detects that a conversion is necessary, it will attempt to change the values as necessary to perform the operation. You would probably be surprised to learn how often PL/SQL is performing conversions on your behalf. Figure 4.1 shows what kinds of implicit conversions PL/SQL can perform. Figure 4.1: Implicit conversions performed by PL/SQLWith implicit conversions you can specify literal values in place of data with the correct internal format, and PL/SQL will convert that literal as necessary. In the example below, PL/SQL converts the literal string "125" to the numeric value 125 in the process of assigning a value to the numeric variable: DECLARE a_number NUMBER; BEGIN a_number := '125'; END;You can also pass parameters of one datatype into a module and then have PL/SQL convert that data into another format for use inside the program. In the following procedure, the first parameter is a date. When I call that procedure, I pass a string value in the form DD-MON-YY, and PL/SQL converts that string automatically to a date: PROCEDURE change_hiredate (emp_id_in IN INTEGER, hiredate_in IN DATE) change_hiredate (1004, '12-DEC-94');As shown in Figure 4.1, conversions are limited; PL/SQL cannot convert any datatype to any other datatype. Furthermore, some implicit conversions raise exceptions. Consider the following assignment: DECLARE a_number NUMBER; BEGIN a_number := 'abc'; END;PL/SQL cannot convert "abc" to a number and so will raise the VALUE_ERROR exception when it executes this code. It is up to you to make sure that if PL/SQL is going to perform implicit conversions, it is given values it can convert without error. 4.2.8.3 Drawbacks of implicit conversionsThere are several drawbacks to implicit conversion:Each implicit conversion PL/SQL performs represents a loss, however small, in the control you have over your program. You do not expressly perform or direct the performance of that conversion; you make an assumption that the conversion will take place, and that it will have the intended effect. There is always a danger in making this assumption: If Oracle changes the way and circumstances under which it performs conversions; your code could then be affected. The implicit conversion that PL/SQL performs depends on the context in which the code occurs. As a result, a conversion might occur in one program and not in another even though they seem to be the same. The conversion PL/SQL performs is not necessarily always the one you might expect. Implicit conversions can actually degrade performance. A dependence on implicit conversions can result in excessive conversions taking place, or in the conversion of a column value in a SQL statement instead of in a constant. Your code is easier to read and understand if you explicitly convert data where needed. Such conversions document variances in datatypes between tables or between code and tables. By removing an assumption and a hidden action from your code, you remove a potential misunderstanding as well. As a consequence, I recommend that you avoid allowing either the SQL or PL/SQL languages to perform implicit conversions on your behalf. Whenever possible, use a conversion function to guarantee that the right kind of conversion takes place. 4.1 Identifiers4.3 NULLs in PL/SQL Copyright (c) 2000 O'Reilly & Associates. All rights reserved.