Oracle Structures

This chapter provides an overview of the Oracle database environment with an emphasis on the concerns of a report writer. It covers the concept of schemas and describes each type of object that can be created in an Oracle database including tables, views, materialized views, dimensions, sequence generators, synonyms, indexes, and primary keys. Table relationships and their definitions are also covered. Oracle datatypes are described, including both built-in types and possible user-defined types, and the behavior of null values is explained. Oracle’s built-in data dictionary views are described.

Schemas

Unlike desktop databases such as Microsoft Access, Oracle uses the concept of schemas to add a level of organization and security to the database objects. Each database user has a corresponding schema. For example, user Xtreme owned the sample data that was imported in Chapter 1. When those objects were imported, they were imported into the XTREME schema. A user’s own schema is analogous to a local scope. To access objects within your schema, you may use just the name of the object. To access objects in another user’s schema, you must prefix the object name with the schema name using a dot notation. Therefore, if user Joe wanted to select data from the Supplier table owned by Xtreme, he would use the syntax SELECT ... FROM XTREME.SUPPLIER.

Report writing usually involves selecting from objects in schemas not owned by the report writer. Most production databases will have schemas that reflect a desired organizational concept rather than real individuals.

Objects

Oracle contains many different object types, both built-in and user defined. Report writers are most concerned with tables, views, and a certain type of stored procedure, but all object types will be covered briefly.

Tables

Tables are the basic Oracle database objects that contain data. Tables are organized in columns and rows: they are defined with certain columns or fields and data is entered in rows. When a row is added to a table, each column may be required to be populated. If a column is defined as NOT NULL, it must have a value. A column may also have a default value defined. If a column has a default value defined, the default value will be stored in the column if no value is specifically inserted for that column. If a column does not have a NOT NULL constraint, or if it has a NOT NULL constraint and a default value, it may be left out of any insert statement.

The column or field type is usually scalar and is one of the Oracle built-in datatypes that are discussed in the “Built-In” section later in this chapter, but it may also be a complex user-defined type such as a record, varray, or nested table. Object tables are tables whose rows are of a user-defined object type. Nested tables are tables that have a column that is a user-defined table type. Nested tables are problematic for report writers as none of the access methods available from Crystal Reports directly support nested tables. See the workaround for reporting on nested tables in the “Nested Tables” section later in this chapter.

Temporary tables are a special kind of table that can be created to store data that is only needed for the duration of a session or a transaction. Temporary tables can be used for reporting just like permanent tables as long as you are aware of their special characteristics. Temporary tables can be defined such that the data they contain persists for the duration of either a single transaction or a single session. The data in a temporary table is private to the session or transaction that inserts the data. This means that a given session can see only the rows that it has inserted into the temporary table. It cannot see any other session’s rows. Temporary tables are emptied when the session terminates or the transaction completes, depending on its definition. Reporting from temporary tables might be useful when the reports are embedded in an application and could be used when reporting from a stored procedure, but this would have little meaning in an independent reporting environment where Crystal controls the session. Temporary tables used in stored procedures are discussed further in Chapter 5.

External tables are another special kind of table that can be included in reports. External tables are tables whose data resides outside of the Oracle database tablespaces, usually in a flat file. External tables are read-only. Reporting from external tables would be useful in situations where some portion of the report must be based on data in a comma-delimited file. If that file is defined as an external table in Oracle, it can be reported from in the same manner as any regular Oracle table. See Chapter 9 for an example of using an external table.

Note

External tables are not available in Oracle 8i or earlier versions.

Another option for including flat file data in a Crystal Report would be to use the text driver for ODBC. In that case, all joining, filtering, and so on would occur on the client machine where Crystal resides, whereas using an Oracle external table will push that processing to the server.

Table Types

Oracle tables can be organized and stored on disk in different ways. The type of table organization is transparent to the report writer, but that organization can be helpful to understand when trying to optimize the query upon which a report is based. The most common table type is a heap table. In a heap table, rows are not stored in any particular order. This reflects the traditional relational concept of unordered sets. Any ordering is accomplished by separate indexes on the tables. Index organized tables are tables that are stored in a particular order based on an index. Clustered tables are useful for tables whose data is often joined. In clustered tables, the related data from each table is stored together and can be accessed quickly using cluster keys.

Views

Views are stored queries. In many ways, a view can be treated as if it was a table, but thinking of views as virtual tables can be detrimental in some respects. There is no stored data associated with a view; a view is simply a query. When a view is referenced in a SELECT statement, its definition is merged with the other components of the SELECT statement and the resulting statement is parsed and executed.

For example, the XTREME schema contains the TOP_CUSTOMERS view, which is defined by the following statement:

CREATE OR REPLACE VIEW "XTREME"."TOP_CUSTOMERS"
 AS
SELECT XTREME.CUSTOMER.ADDRESS1,
 XTREME.CUSTOMER.ADDRESS2, 
 XTREME.CUSTOMER.CITY, 
 XTREME.CUSTOMER.CONTACT_FIRST_NAME, 
 XTREME.CUSTOMER.CONTACT_LAST_NAME, 
 XTREME.CUSTOMER.CONTACT_POSITION, 
 XTREME.CUSTOMER.CONTACT_TITLE, 
 XTREME.CUSTOMER.COUNTRY, 
 XTREME.CUSTOMER.CUSTOMER_CREDIT_ID, 
 XTREME.CUSTOMER.CUSTOMER_ID, 
 XTREME.CUSTOMER.CUSTOMER_NAME, 
 XTREME.CUSTOMER.FAX, 
 XTREME.CUSTOMER.LAST_YEARS_SALES, 
 XTREME.CUSTOMER.PHONE, 
 XTREME.CUSTOMER.POSTAL_CODE, 
 XTREME.CUSTOMER.REGION 
 FROM XTREME.CUSTOMER 
 WHERE ( XTREME.CUSTOMER.LAST_YEARS_SALES > '50000' );

If you create a Crystal Report using this view, showing Customer_ID, Customer_Name, Last_Years_Sales, and Country and specifying that the country must be ‘USA’, Crystal would construct the following query to send to the Oracle server:

SELECT "TOP_CUSTOMERS"."CUSTOMER_ID", "TOP_CUSTOMERS"."CUSTOMER_NAME",
 "TOP_CUSTOMERS"."LAST_YEARS_SALES", "TOP_CUSTOMERS"."COUNTRY"
 FROM "XTREME"."TOP_CUSTOMERS" "TOP_CUSTOMERS"
 WHERE "TOP_CUSTOMERS"."COUNTRY"='USA'

The example report is available as Chapter 2View Rewrite Example Report.rpt in the accompanying download file. The report will work only in an Oracle 9i environment because the embedded subreport uses tables that do not exist in Oracle 8i.

However, Oracle would merge the view definition with the query and actually parse and execute something like the following:

SELECT "CUSTOMER"."CUSTOMER_ID", "CUSTOMER"."CUSTOMER_NAME",
 "CUSTOMER"."LAST_YEARS_SALES", "CUSTOMER"."COUNTRY"
 FROM "XTREME"."CUSTOMER" "CUSTOMER"
 WHERE ( XTREME.CUSTOMER.LAST_YEARS_SALES > '50000' ) 
 AND "CUSTOMER"."COUNTRY"='USA'

Figure 2-1 displays the report and contains a subreport showing the execution plan. As expected, the execution plan indicates that the Customer table will be accessed, not the view, Top_Customers.

Figure 2-1: View rewrite example

Views are very useful constructs. They can be used to hide complex join logic or otherwise simplify data for the users. However, they are not tables, and the report writer needs to understand what will happen on the Oracle server when views are used.

Materialized Views

Oracle materialized views are views whose contents are stored on disk, as they do contain data. Materialized views are often created to store summaries or other complex operations where using a normal view, which would need to be re-executed each time it was called, would be inefficient. Materialized views are also used to replicate data in a distributed environment. They are refreshed as defined by the DBA, and they might be refreshed on demand, on a time schedule, or when the underlying data changes. Materialized views can be reported on just like ordinary tables and show up in Crystal Reports under the table folder.

Materialized views can also be used for transparent query rewrite. If query rewrite is enabled and a user submits a query that would execute faster against a materialized view, the query is rewritten by Oracle to use the materialized view. The user does not know that the query has been rewritten; the user just gets the results faster. See Chapter 9 for an example using materialized views.

Dimensions

Dimensions are Oracle objects that define hierarchical relationships between columns. Dimensions cannot be reported on directly. Dimensions are used in data warehousing environments.

Sequence Generators

Oracle sequence generators generate and return sequential numbers. They are often used to generate unique keys for inserted rows and are of little, if any, use in a report. However, they can be called using a Crystal SQL Expression field or in a SQL Command or stored procedure. The syntax to generate sequence numbers is [sequence name] .NEXTVAL.

Synonyms

Synonyms allow the creation of aliases for tables, views, materialized views, sequences, procedures, functions, and packages. They can be used to hide the location of, or simplify the names of, objects. There can be private synonyms that are available only to the user who created them and public synonyms which can be used by anyone connected to the database. Synonyms are often created by the DBA for tables, views, and packages that are used frequently.

For example, without synonyms, if a user other than Xtreme needed to query the table XTREME.CUSTOMER, that user would have to use the entire qualified name of the table:

SELECT * FROM XTREME.CUSTOMER;

However, if a public synonym has been created such as the following:

CREATE PUBLIC SYNONYM "CUST" FOR "XTREME"."CUSTOMER";

the user can type a simpler statement:

SELECT * FROM CUST;

In Crystal Reports, the display of synonyms is dependent on the options set for the database. In the Database Expert, right-click a database name and choose Options. If you want to see synonyms, check the Synonyms box, as shown in Figure 2-2.

Figure 2-2: Database options

Private synonyms will show up under the schema folder of the logged-in user, as shown in Figure 2-3. Public synonyms will be listed under the public folder.

Figure 2-3: Synonyms folders

There are many predefined public synonyms in any Oracle database. Expect to see a long list of objects under the public folder, if you choose to see synonyms. The majority of the pre-existing public synonyms are for the static data dictionary views or for the dynamic performance views. These special views are discussed in the section “Data Dictionary Views” later in this chapter.

Indexes

Indexes are Oracle objects that help to decrease the time required to return data to the user. An index is associated with a table, including materialized view tables, but they can be created and dropped independently of the table. Indexes use storage space but cannot be reported on directly. An index is based on one or more columns in a table and speeds access to the rows of the table in the defined sort order of the indexed columns. Proper table indexing is of primary importance to the report developer concerned with optimizing report display.

Oracle supports several different types of indexes including B-tree, reverse key, bitmap, bitmap join, cluster, domain, and function-based indexes. An index can be defined as unique, which guarantees that no two rows in the table will have the same value for the indexed column(s). If an index is not defined as unique, identical values can exist. An index can be defined on a single column or on multiple columns. A multiple column index is called a compound or concatenated index.

A B-tree is the default index type, and a B-tree index is a hierarchy. The leaf nodes contain pointers to the actual row data. The higher-level branches contain a path that becomes more refined the closer it gets to the leaf nodes. The number of levels of a B-tree index is an indication of its efficiency; the fewer the levels, the faster the access. If you imagine a tree where a leaf on one twig is the record that you need, to get to that leaf, you would start at the trunk and at each fork you would take the branch that leads to the leaf you need.

A reverse key index is also a B-tree, but the column values are reversed during storage. Therefore, a column that contains “ABCD” would be indexed as “DCBA.” Reverse key indexes are used to spread key values out across the B-tree and help balance the index. This is useful in cases where the indexed column is populated by a sequence.

A bitmap index is useful for columns of low cardinality that are used frequently in select queries. They are used extensively in data warehouse environments. The cardinality of a column refers to the number of different values stored in that column for the table. For example, if the country column contains only the values “USA” or “CAN” in a table with 10,000 rows, that would be low cardinality. The cardinality is computed as the number of distinct values over the number of rows. The suggested cardinality for using a bitmap index is 1 percent. In this case, our cardinality is 2/10,000 or 0.02 percent, and a bitmap index would be advantageous.

Bitmap join indexes are indexes that precalculate a join. They are also used in a data warehouse environment.

Note

Bitmap join indexes are not available in Oracle 8i or previous versions.

Indexing for query optimization is discussed in detail in Chapter 7.

Though Crystal Reports can tag the indexed fields in the Database Expert for some data access types, it does not make any distinction for other types. For example, Figure 2-4 shows the linking dialog for a native connection. The native connection shows no indexes.

Figure 2-4: Native connection linking dialog

Figure 2-5 shows the linking dialog for an ODBC or OLE DB connection. The indexed fields are tagged with different colors.

Figure 2-5: ODBC connection linking dialog

Primary Keys

Primary keys are maintained via non-null unique indexes. A table can have only one primary key, which can contain one or more columns. The fields of the primary key uniquely identify the rows in a table.

Database Links

A database link defines a connection to another database. Once a database link is created, it can be used to query data in the remote database to which it links, as if that data was in the original database. Database links are not displayed in Crystal’s Database Expert, so they cannot be selected like tables. To use a database link, you can use a SQL Command, view, or stored procedure. To access a table in a linked database, you must append the link name to the table reference as shown:

SELECT * FROM scott.emp@link_name

Relationships

It is vital for report writers to understand the proper relationships between tables for the data structures on which they need to report. Linking tables inappropriately can lead to erroneous results. In Oracle databases, relationships between tables are maintained via referential integrity constraints. Foreign key constraints are created that link the foreign key field in the child table to the primary key field in the parent table. For example, Figure 2-6 shows the relationships in the XTREME sample tables. The Customer_ID in the Orders table is a foreign key pointing to the Customer_ID in the Customer table. Because of this defined referential integrity constraint, no Customer_ID can be entered into a row in the Orders table if it does not first exist in the Customer table. Note, however, that in this case, Orders.Customer_ID is nullable, so a null value could be entered into Orders.Customer_ID even if no null Customer.Customer_ID existed.

Figure 2-6: XTREME data model

Datatypes

Oracle has a wide variety of built-in datatypes. Crystal Reports recognizes some of them and not others. In addition to the built-in datatypes, users may create their own datatypes.

Built In

Among the built-in datatypes are the categories of character, numeric, dates, timestamps, intervals, and binary.

Character

The character types include CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB, and LONG. All character types store string data in the declared character set for the database. The character set is declared when the database is created and can be a foreign language as well as the typical ASCII set. A character set can also be single byte or multibyte. The ASCII character set is single byte, where each character is defined by a single byte. Multibyte character sets define each character with multiple bytes.

All Oracle character datatypes map to the Crystal Reports String type. Crystal Reports seems to truncate all string fields at 64K for display. For some data access methods, CLOBs, NCLOBs, LONGs, NCHARs, and NVARCHAR2s cause errors in Crystal Reports. See Table 1-1, at the end of the first chapter, for details.

Table 2-1: Description of Selected Data Dictionary Views

Table Name	Description
ALL_ALL_TABLES	Lists table level information for all object and relational tables
ALL_ARGUMENTS	Describes the arguments of procedures and functions
ALL_CATALOG	Lists all indexes, tables, clusters, views, synonyms, and sequences
ALL_COL_COMMENTS	Shows the comments on table and view columns
ALL_COL_PRIVS	Shows the privileges on columns where the current user is the owner, grantor, or grantee, including columns in objects owned by PUBLIC
ALL_COL_PRIVS_MADE	Shows the privileges on columns where the current user is the owner or grantor
ALL_COL_PRIVS_RECD	Shows the privileges on columns where the current user or PUBLIC is the grantee
ALL_COLL_TYPES	Describes all varrays and nested tables
ALL_CONS_COLUMNS	Shows all columns used in constraints
ALL_CONS_OBJ_COLUMNS	Shows all object columns used in constraints
ALL_CONSTRAINTS	Describes constraint definitions
ALL_DB_LINKS	Shows all DB links
ALL_DEPENDENCIES	Shows all object dependencies
ALL_DIMENSIONS	Lists all dimension objects
ALL_DIRECTORIES	Lists all directories
ALL_EXTERNAL_LOCATIONS	Describes the locations of external tables
ALL_EXTERNAL_TABLES	Lists all external tables
ALL_IND_COLUMNS	Lists all columns in indexes
ALL_IND_EXPRESSIONS	Lists all function-based index expressions
ALL_INDEXES	Lists all indexes
ALL_JOIN_IND_COLUMNS	Shows the join conditions of bitmap join indexes
ALL_MVIEWS	Lists all materialized views
ALL_NESTED_TABLES	Lists all nested tables
ALL_OBJ_COLATTRS	Shows all object columns and attributes
ALL_OBJECT_TABLES	Lists all object tables
ALL_OBJECTS	Lists all database objects
ALL_PROCEDURES	Lists all procedures
ALL_SEQUENCES	Lists all sequences
ALL_SOURCE	Shows the source text for all objects
ALL_SYNONYMS	Lists all synonyms
ALL_TAB_COLUMNS	Describes the columns of all tables, views, and clusters
ALL_TAB_COMMENTS	Shows the comments on all tables and views
ALL_TAB_PRIVS	Lists the grants on objects where the user or PUBLIC is the grantee
ALL_TAB_PRIVS_MADE	Lists the object grants made by the current user or made on objects the current user owns
ALL_TAB_PRIVS_RECD	Lists the object grants where the user is the grantee
ALL_TABLES	Lists all relational tables
ALL_TRIGGER_COLS	Describes the use of columns in triggers
ALL_TRIGGERS	Lists all triggers owned by the user or on objects owned by the user
ALL_TYPES	Lists all object types
ALL_USERS	Lists all user IDs
ALL_VARRAYS	Lists all varrays
ALL_VIEWS	Lists all views

CHAR fields are fixed length up to 2,000 bytes. For single-byte character sets, that would mean 2,000 characters. If a double-byte character set was used, 1,000 characters would be the maximum. They are padded with trailing blanks up to the defined size of the field. However, Crystal Reports treats CHAR fields like any other String field and ignores the trailing blanks.

VARCHAR2 fields are variable length, where the maximum size is declared at creation. They can hold up to 4,000 bytes. The number of characters that can be stored is dependent on the character set, as with CHAR fields. Note that the VARCHAR datatype is still supported but not recommended for use.

NCHAR fields are fixed-length Unicode character fields. The size for NCHAR fields is declared in number of characters rather than number of bytes, but the maximum size is 2,000 bytes. Therefore, for a double-byte character set, the maximum length would be 1,000 characters.

NVARCHAR2 fields are similar to VARCHAR2 fields but are for Unicode characters. The size is declared in characters as with the NCHAR type, and the maximum size is 4,000 bytes.

LONG fields are similar to Access Memo fields, and they can hold up to 2GB of characters. The LONG datatype is being phased out and should not be used. Oracle allows only one LONG column per table.

CLOBs (Character Large Objects) are intended to replace the LONG type and can contain up to 4GB of characters. Multiple CLOB fields are allowed in a table.

NCLOBs are the Unicode version of CLOBs.

Numeric

The numeric category contains the types NUMBER and FLOAT.

NUMBER fields contain variable length signed values between 1.010-130 and 9.9…910125 with 38 degrees of precision. A number field can be declared with a precision and a scale. The precision is the total number of digits, and the scale is the number of digits to the right of the decimal point. If no precision or scale is provided, the maximum is assumed.

FLOAT fields are a subtype of NUMBER. FLOAT fields contain floating-point values with up to 30 degrees of precision.

Dates

The date category contains only one type, DATE.

The Oracle DATE datatype contains both a date component and a time component. Oracle DATE fields will be mapped to Crystal Reports Date and Time fields. The range of dates that Oracle can store is from January 1, 4712 BCE through December 31, 4712 CE. The time component of an Oracle DATE field is stored in 24-hour format as HH:MM:SS. The time component cannot store fractions of seconds. Unless a time is specifically inserted, the time portion will be 00:00:00. Both Oracle and Crystal Reports interpret this empty time of 00:00:00 as 12:00:00 AM.

If most of your Oracle DATE fields do not contain a time component, it will save you formatting time if you set the default format for Crystal Reports Date and Time fields to exclude the time portion. Go to File | Options and choose the Fields tab as shown in Figure 2-7. Click the Date and Time button and choose the Date and Time tab.

Figure 2-7: Crystal field options

Set the Style to a choice that excludes time components (see Figure 2-8) and click OK. Hereafter, your Oracle date fields will be displayed by default with no time component. If you need to display the time component for a particular Oracle date field, just format that individual field.

Figure 2-8: Date and Time format editor

Timestamps

The timestamp category contains TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. TIMESTAMP fields contain a date and time, including fractional seconds. The precision of the fractional seconds can be declared between zero and nine, but defaults to six. TIMESTAMP WITH TIME ZONE fields are TIMESTAMP fields plus a time zone indicator. The time zone indicator can be either a region name or an offset from UTC (Universal Time Coordinate, or Greenwich Meantime). TIMESTAMP WITH LOCAL TIME ZONE fields are identical to TIMESTAMP WITH TIME ZONE fields except that the time zone is normalized to the database time zone when data is stored. When a field of type TIMESTAMP WITH LOCAL TIME ZONE is queried, the value and time zone returned are adjusted to the client’s time zone.

Note

The timestamp datatypes are new to Oracle 9i.

None of the time zone datatypes is supported by any of the currently available Crystal Reports access methods. The workarounds for accessing this type of Oracle data from Crystal Reports involves converting these datatypes to the DATE datatype or a string datatype. This can be done internally via a SQL Expression field. If you only need to display the field values, you can use the TO_CHAR function. Create a new SQL Expression field and enter the formula shown here:

This will return a TIMESTAMP field formatted with the default timestamp format. In this case, something like 04-FEB-03 06.20.55.981000 PM will be returned. If you want to format the field differently, you can append a format string such as the one shown here:

This will return something like 04-FEB-2003 18:20:55. See Oracle documentation for possible formatting strings.

If you need to use the timestamp as a date, you can create a SQL Expression like the one shown here:

TIMESTAMP Conversion Function

If you need to report often on TIMESTAMP types, you should create a conversion function in Oracle. Because SQL Expressions cannot be stored in the Repository for reuse, creating an Oracle function will minimize the effort involved.

Here is the DDL for a timestamp to date conversion function:

CREATE OR REPLACE FUNCTION "XTREME"."TIMESTAMP_TO_DATE"
 (TimestampVar IN TIMESTAMP)
 RETURN DATE AS
BEGIN
 RETURN TO_DATE(TO_CHAR(TimestampVar,'DD-MON-YYYY HH24:MI:SS'),
 'DD-MON-YYYY HH24:MI:SS');
END;

To use this function from Crystal Reports, you still must create a SQL Expression field as shown in the following illustration, but the complexity of the operation is hidden.

Intervals

The interval category contains the types INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.

Note

The interval datatypes are new to Oracle 9i.

INTERVAL datatypes represent periods of time. INTERVAL YEAR TO MONTH is used for intervals containing numbers of years and months and might be used for something like age. INTERVAL DAY TO SECOND is used for intervals containing days, hours, minutes, and seconds and might be used for something like runtime. Neither of the INTERVAL datatypes is supported by any current Crystal Reports access method. Workarounds involve converting the INTERVAL fields to NUMBER. The Oracle function EXTRACT can be used to pull out the individual year, month, day, hour, minutes, and seconds from the INTERVAL types. Here is a formula that will convert an INTERVAL YEAR TO MONTH to a NUMBER of months:

EXTRACT(YEAR FROM "EMPLOYEE_WITH_PHONES_ARRAY"." AGE")*12 
+EXTRACT(MONTH FROM "EMPLOYEE_WITH_PHONES_ARRAY"."AGE");

Binary

The binary category contains the types BLOB, BFILE, RAW, LONG RAW, and ROWID.

BLOB fields contain unstructured binary data up to 4GB. They can be used to store graphics, video, and sound files. Some of the Crystal Reports access methods do not support BLOBs. BLOBs do not support some graphics formats. See Table 1-1 for details.

BFILE fields are similar to other binary fields, but their contents are stored outside the database in files and are read-only. The BFILE field contains a locator for the file.

RAW and LONG RAW types are being phased out by Oracle and should no longer be used.

ROWID fields are binary fields used for the storage of Oracle row addresses. Some of the Crystal Reports access methods do not support ROWIDs. See Table 1-1 for details. Oracle has built-in functions to convert ROWIDs to character if you need to report on ROWIDs and your access method will not allow it. The Oracle function ROWID_TO_CHAR can be used in a SQL Expression.

User Defined

Oracle allows the creation of user-defined types. There are four kinds of user-defined types; object types, REFs, varrays, and nested tables.

Object types have a name and attributes and may have methods. The attributes of an object type can be Oracle built-in datatypes or other user-defined types. Object types are similar to object types in programming languages such as C. REFs are pointers to objects. Varrays and nested tables are collection types and are discussed in the “Collections” section later in this chapter. Any of these user-defined types can be used as column types in Oracle tables. However, object type columns cannot be used directly in Crystal Reports. An example of a workaround for simple object type columns follows. See the “Collections” section for workarounds for varrays and nested tables.

Though object types may contain other object types, here we will discuss a simple case where the object column is defined by a record type that in turn consists of two columns of Oracle built-in datatypes. The object type description for the phone_rec type follows:

SQL> DESC phone_rec
Name Null? Type
----------------------------------------- -------- ------------
PHONE_TYPE VARCHAR2(20)
PHONE_NO VARCHAR2(20)

The pertinent fields from the table using the phone_rec type are described next:

SQL> DESC employee_with_phones_array
Name Null? Type
----------------------------------------- -------- ------------
EMPLOYEE_ID NOT NULL NUMBER(10)
....
PHONE_RECORD PHONE_REC

To report on object columns like these using native Oracle SQL, the object field names are concatenated to the object column name, and a table alias is required. For example, the following command fails with an invalid identifier error:

SQL> SELECT employee_id, phone_record.phone_type, 
 2 phone_record.phone_no
 3 FROM employee_with_phones_array;
SELECT employee_id, phone_record.phone_type, phone_record.phone_no
 *
ERROR at line 1:
ORA-00904: "PHONE_RECORD"."PHONE_NO": invalid identifier

However, the following command succeeds:

SQL> SELECT e.employee_id, e.phone_record.phone_type,
 2 e.phone_record.phone_no
 3 FROM employee_with_phones_array e;


EMPLOYEE_ID PHONE_RECORD.PHONE_T PHONE_RECORD.PHONE_N
----------- -------------------- --------------------
 4 HOME [phone]
 7 HOME [phone]
 12 HOME [phone]

To report from object types in Crystal Reports, you must use pass-through SQL. You can use a Crystal Reports SQL Command but, surprisingly, not a Crystal Reports SQL Expression. Of course, an Oracle view or stored procedure that pulls out the attributes of the object can also be used.

It is not clear why a SQL Expression field cannot be used for reporting on simple object columns. Crystal Reports always constructs its queries using table aliases where the table alias is the entire table name minus the schema name. Using the preceding example, if a SQL Expression field is created whose definition is "EMPLOYEE_WITH_PHONES_ARRAY"."PHONE_RECORD"."PHONE_NO", and that SQL Expression field is placed on the report along with the Employee_ID field, you would expect Crystal to generate a query such as the following:

SELECT "EMPLOYEE_WITH_PHONES_ARRAY"."EMPLOYEE_ID",
 "EMPLOYEE_WITH_PHONES_ARRAY"."PHONE_RECORD"."PHONE_NO"
 FROM "XTREME"."EMPLOYEE_WITH_PHONES_ARRAY" 
 "EMPLOYEE_WITH_PHONES_ARRAY"

This query works when executed from SQL*Plus. However, attempting this in the report developer returns an invalid identifier error from the SQL Expression Editor. Therefore, you must use a SQL Command, Oracle view, or stored procedure.

An object table is a special kind of table defined completely by an object type. Two object tables have been added to the XTREME schema. The first is called Phone_Object_Table and is a table of the user-defined type Xtreme.Phone_Rec. This table can be reported on from Crystal Reports. It is a table of a simple object type and Oracle will automatically translate the fields in the object type into regular table fields for querying. The second object table is the Contact_Object_Table. It is defined by a more complex object type where one type is nested inside another. In this case, Crystal can directly access fields from the parent object type but not from the nested object type.

Collections

There are two types of collection objects in Oracle, varrays and nested tables. Both collection types can be used to define columns in Oracle tables.

Varrays

Varrays are one kind of user-defined collection type. Varray is short for variable array and is an ordered list of some variable type. The type can be a scalar type, such as number or varchar2, or a complex type, such as a record. Varrays differ from nested tables in several ways. Varrays have an inherent order and an upper limit put on their size, whereas nested tables are unordered and their size is not limited. They also differ in the manner in which they are stored in Oracle. However, from the report writer’s perspective, they can be treated identically.

Varrays can appear as columns in tables, be returned from procedures, or used to create a more complex type. The table XTREME.EMPLOYEE_WITH_PHONES_ARRAY is a sample table containing a varray. It is not one of the original Crystal Decisions sample tables but one that has been created by duplicating the XTREME.EMPLOYEE table and moving the two phone numbers into an array. The Oracle types involved are shown next:

SQL> DESC xtreme.phone_array_type;
 xtreme.phone_array_type VARRAY(5) OF XTREME.PHONE_REC
 Name Null? Type
 ---------------------------- -------- -----------------------
 PHONE_TYPE VARCHAR2(20) 
 PHONE_NO VARCHAR2(20)
 SQL> DESC xtreme.employee_with_phones_array; 
Name Null? Type
----------------------------- -------- ------------------------
EMPLOYEE_ID NOT NULL NUMBER(10) 
SUPERVISOR_ID NUMBER(10) 
LAST_NAME NOT NULL VARCHAR2(20) 
FIRST_NAME NOT NULL VARCHAR2(10) 
POSITION VARCHAR2(30) 
BIRTH_DATE DATE
HIRE_DATE DATE
EXTENSION VARCHAR2(4) 
PHOTO BLOB
NOTES CLOB
REPORTS_TO NUMBER(10) 
SALARY NUMBER(19,4) 
SSN VARCHAR2(12) 
EMERGENCY_CONTACT_FIRST_NAME VARCHAR2(20) 
EMERGENCY_CONTACT_LAST_NAME VARCHAR2(20) 
EMERGENCY_CONTACT_RELATIONSHIP VARCHAR2(20) 
PHONES XTREME.PHONE_ARRAY_TYPE

Attempting to report on a varray field directly from Crystal Reports fails for every connection type. If the varray is listed as a normal field in a select query executed from an Oracle tool, Oracle returns the contents of the varray in a single formatted field as shown in this SQL*Plus query. The field wraps to two lines, but it is just one field.

SQL> SELECT employee_id, phones
 2 FROM xtreme.employee_with_phones_array; 


EMPLOYEE_ID
-----------
PHONES(PHONE_TYPE, PHONE_NO) 
--------------------------------------------------------------
 1
PHONE_ARRAY_TYPE(PHONE_REC('Home', '[phone]'), 
 PHONE_REC('Emergency', '2065555321')) 


 2
PHONE_ARRAY_TYPE(PHONE_REC('Home', '[phone]'), 
 PHONE_REC('Emergency', '2065553656'))

The phone field is returned as a LOB. This causes an inconsistent datatypes error when used in Crystal Reports.

Oracle SQL has the capability to unnest the varray, so reporting workarounds will need to use pass-through SQL, as in a Crystal Reports SQL Command object, an Oracle view, or an Oracle stored procedure. The Oracle TABLE command must be used, and the result is as if the varray were a child table joined to the parent table, returning a row for every element of each original row’s varray, as shown next. The number of elements in the varray can differ from row to row.

SQL> SELECT employee_id, p.* 
 2 FROM xtreme.employee_with_phones_array e, 
 3 table(e.phones) p; 


EMPLOYEE_ID PHONE_TYPE PHONE_NO
----------- -------------------- --------------------
 1 Home [phone]
 1 Emergency 2065555321
 2 Home [phone]
 2 Emergency 2065553656

The Crystal Reports example solution is a very simple report of employee phone numbers. The main report uses XTREME.EMPLOYEE_WITH_PHONES_ARRAY and shows only the first and last names of the employees. To get the phone numbers for each employee, you create a subreport. In the subreport, you use a SQL Command defined as shown here:

Linking the subreport to the main report on employee_id will then result in the report shown in Figure 2-9.

Figure 2-9: Varray example report

This report is saved as Chapter 2Varray Example Report.rpt in the download file. This workaround is usable for all connections types.

Attempts to link the SQL Command directly into the main report will result in an error. If you need to do this, the solution is to create an Oracle view containing the same query as the SQL Command and then use that to link to the main table in the main report. This works for all connection types.

Nested Tables

Nested tables can be treated the same as varrays for reporting purposes. You must use the TABLE command to unnest the table.

Note

Varrays and nested tables can be multidimensional in Oracle 9i. For example, you can create a varray of a varray type.

Nulls

The Oracle null value is an important concept for report writers to understand. A null indicates the lack of a value. Any column of any datatype may contain nulls if the NOT NULL constraint has not been implemented for that column. Null is not equivalent to zero nor to an empty string. In addition, while Oracle currently treats a zero-length string as equivalent to a null, that may not continue in the future.

Any null appearing in an expression, except concatenation, will cause that expression to return null. All Oracle built-in scalar functions will return null if one of their arguments is null except NVL, REPLACE, and CONCAT. Most Oracle built-in aggregate functions ignore nulls. This may or may not be the behavior you desire. For example, if you use the AVERAGE function on ten values where two of those values are null, the numerator will be the sum of the eight non-null values and the denominator will be eight, not ten. If you need to treat the null values as zero for aggregation purposes, use the NVL function to convert nulls to zero before applying the aggregation function.

You must also be cautious when doing comparisons involving columns that may contain null values. Comparing a null value to anything (even another null value) will return UNKNOWN. If you do such a comparison in a WHERE clause, no records will be returned when the condition evaluates to UNKNOWN, but there will be no indication that it is because of nulls or that the condition returns no rows. You should always use the NVL function on any columns that might contain nulls in comparison operations.

The IS NULL condition can be used in the WHERE clause to filter rows out of result sets where a specific column, or an expression, is null. If you want to specifically check for and filter null values, use IS NULL. If you want to compare two fields where one or both of them may contain null values, use NVL. IS NOT NULL is the negation of IS NULL.

Linking on Nulls

Understand the fields that you use for linking in Crystal Reports. If you link from a child table column that allows nulls, unless there is a null value in the corresponding parent table column, none of the rows with null foreign key values will be in the result set.

Data Dictionary Views

Oracle maintains certain system tables and views that describe the database itself. The tables are not directly accessible, but the system creates views that users can query to explore the database structure. These views are the data dictionary views. They are sometimes called “System” tables or “Catalog” tables. For the report writer, these views are a vital source of information about existing tables, views, columns, and referential integrity constraints.

Data dictionary views are customized for three user types. The views prefixed with USER_ show all objects in the logged in user’s schema. The views prefixed with ALL_ show all objects that the logged in user has access to. The ALL_ views will show all objects in the logged-in user’s schema as well as any other for which objects the user has been granted privileges. The DBA_ views show all objects in the database, but a user must have the SELECT_ANY_TABLE privilege or the SELECT_CATALOG_ROLE role to select from the DBA_ views.

Table 2-1 lists a subset of the available data dictionary views along with a short description of the view content. The ALL_ views are shown, but DBA_ or USER_ can be substituted if desired. Consider that “for all objects that the user has privileges to” is appended to each description. For a full listing of the data dictionary views and an expanded description, see Oracle documentation.

This chapter presented a high-level overview of Oracle structures and described how they are seen or used in Crystal Reports. The next chapter will cover basic Oracle SELECT statements.