Chapter 11. ICMP: Internet Control Message Protocol

Team-Fly     TCP/IP Illustrated, Volume 2: The Implementation By Gary R. Wright, W. Richard Stevens Table of Contents Chapter 11. ICMP: Internet Control Message Protocol Section 11.1.  Introduction Section 11.2.  Code Introduction Section 11.3.  icmp Structure Section 11.4.  ICMP protosw Structure Section 11.5.  Input Processing: icmp_input Function Section 11.6.  Error Processing Section 11.7.  Request Processing Section 11.8.  Redirect Processing Section 11.9.  Reply Processing Section 11.10.  Output Processing Section 11.11.  icmp_error Function Section 11.12.  icmp_reflect Function Section 11.13.  icmp_send Function Section 11.14.  icmp_sysctl Function Section 11.15.  Summary Team-Fly     Top

6.9. Scrapbook Pages

< Day Day Up >

6.9. Scrapbook Pages A scrapbook page is a way to create and test snippets of code without all the trappings of normal Java code. In some ways, it's like working in a scripting language, but you have the full expressiveness of Java in addition to being able to make calls into any of your code or any of the system libraries. To create a scrapbook page, select File New Other… Java Java Run/Debug Scrapbook Page. Enter the name of the page�for example, test�and click Finish (or just press Enter). A new editor page will open for test.jpage. In the blank scrapbook page, try typing in an expression like 123/456, press Ctrl+A to select the expression, and press Ctrl+Shift+D (Run Display) to run it and display the result. (The answer in this case is (int) 0 because both numbers are integers and the result was truncated.) Note that the result is selected, so you can copy it quickly (or press Backspace to remove it from the page). Next, try entering Math.PI and displaying its result. This works because the scrapbook page already has all the system libraries imported, including the Math class. If you need a particular import, you can bring up the context menu and select Set Imports…. Let's try something a little more complicated. Type in this snippet of code: double d = 3.14; System.out.println(d); Now select the snippet and press Ctrl+U (Run Execute) to execute it. The output will appear in the Console window. Execute is exactly like Display except that Execute doesn't show the return value (if any). You can execute loops or even call methods in your regular programs from the scrapbook page. This is useful for trying out new ideas or just for simple debugging.

< Day Day Up >

3.2 J2EE Applications

< Day Day Up >

3.2 J2EE Applications A J2EE application, an enterprise application that conforms to the J2EE specification, is structured as shown in Figure 3.2 and consists of the following: Zero or more EJB modules Zero or more Web modules Zero or more application client modules Optionally, JAR files containing dependent classes or components required by the application Any combination of the preceding, as long as it contains at least one module Figure 3.2. Contents of a J2EE Application A J2EE application is represented by, and packaged in, an Enterprise Archive (EAR) file. The modules that comprise the EAR file are themselves packaged in archive files specific to their types. For example, a Web module is packaged in a Web Archive (WAR) file, and an EJB module, containing one or more enterprise beans, is packaged in a JAR file. WAR files can exist as independent deployment units from EAR files. EAR files also contain a deployment descriptor file�an XML document describing the contents of the application and containing instructions for the deployment of the application. In particular, the deployment descriptor specifies the security settings to be enforced by the runtime environment. Each WAR file packaging a Web module, JAR file packaging enterprise beans, or JAR file packaging an application client module contains its own deployment descriptor as well. 3.2.1 EJB Modules An enterprise bean is a Java component that can be combined with other resources to create distributed client/server applications. Instantiated enterprise beans reside in enterprise bean containers, or EJB containers. An EJB container provides an interface between the enterprise beans and the application server on which the enterprise beans reside. An enterprise bean is typically accessed using Java RMI-IIOP. An ORB manages the interaction between clients and enterprise beans, using IIOP. ORBs enable clients to make requests and receive responses from servers in distributed computing environments. Alternatively, enterprise beans are accessible through JMS. It is also possible to invoke an enterprise bean as a Web service via SOAP, as explained in Chapter 14 on page 497. There are three types of enterprise beans: entity beans, session beans, and message-driven beans. Entity beans store persistent data and typically use database connections. Entity beans are of two types: CMP entity beans and BMP entity beans. Entity beans with container-managed persistence (CMP) let the EJB container transparently and implicitly manage the persistent state. The enterprise bean developer does not need to code any database access functions within the enterprise bean class methods. Entity beans with bean-managed persistence (BMP) manage persistent data in a manner defined by the application developer in the bean code. This usually includes writing to databases. Session beans do not require database access, although they can obtain it indirectly, as needed, by accessing entity beans. Session beans can also obtain direct access to databases and other resources through the use of resource references, which include the use of JDBC. Session beans can be either stateless or stateful. A session bean is said to be stateless if it provides a stateless service to the client. A business method on a stateless session bean is similar to a procedural application or static method; there is no instance state. Therefore, all the data needed to execute a stateless session bean's method is provided by the method arguments. A session bean is said to be stateful if it acts as a server-side extension of the client that uses it. A stateful session bean is created by a client and will work for only that client until the client connection is dropped or the bean is explicitly removed. Unlike a stateless session bean, a stateful session bean has state or instance fields that can be initialized and changed by the client with each method invocation. Message-driven beans are enterprise beans accessible asynchronously via JMS rather than synchronously through such protocols as RMI-IIOP. The EJB V2.1 specification expands the scope of message-driven beans beyond JMS to support any messaging system. An EJB module is one or more enterprise beans assembled into a single deployable unit. As we have observed, an EJB module is stored in a standard JAR file, commonly referred to as ejb-jar. This file contains One or more deployable enterprise beans A deployment descriptor, stored in an XML file Specifically, an EJB module's deployment descriptor file declares the contents of the module, specifies the structure and external dependencies of the enterprise beans in the module, explains how the enterprise beans are to be used at runtime, and defines the security policies applicable to the enterprise beans within the module. The format of the security policy is defined by the EJB specification (see Chapter 5 on page 157). 3.2.2 Web Modules A Web module represents a Web application�an application that can be accessed over the Web using HTTP. A Web module is used to assemble servlets and JSP files, as well as static content, such as HTML pages, into a single deployable unit. As we said earlier, Web modules are stored in WAR files, which are enhanced JAR files with a .war file extension, and contain One or more servlets, JSP files, and other supporting files A deployment descriptor, stored in an XML file The deployment descriptor file, web.xml, declares the contents of the Web module. This file contains information about the structure and external dependencies of the components in the Web module and describes the components' runtime use. In addition, the deployment description file is used to declare the security policies applicable to the universal resource identifiers (URIs) that are mapped to the resources within the Web module. These security policies include both the authorization policy and the login configuration information. The format of the security policy is defined by the Java Servlet specification. Servlets are Java programs running on a WAS and extend the Web server's capabilities. For example, servlets support generation of dynamic Web page content, provide database access, concurrently serve multiple clients, and filter data by MIME type. Servlets use the Java Servlet API. By analogy, servlets are the server-side equivalent of client-side browser applets. JSP files enable the separation of the HTML coding from the business logic in Web pages, allowing HTML programmers and Java programmers to more easily collaborate in creating and maintaining pages. This process is described in greater detail in Section 4.1.2 on page 104. 3.2.3 Application Client Modules Application clients are first-tier Java-based client programs. Even though it is a regular Java application, an application client depends on an application client container to provide system services. An application client module packages application client code in a JAR file. This JAR file includes a deployment descriptor XML file, which specifies the enterprise beans and external resources referenced by the application. The security configuration of an application client determines how the application will access enterprise beans and Web resources. If the J2EE components that the client application accesses are secured, the client will be authenticated accordingly. In order for an application client to retrieve authentication data from an end user, configuration information must be specified in a deployment descriptor XML file, application-client.xml, associated with the client application. Application clients typically run in an environment that has a Java 2 security manager installed and the security policies enforced based on the J2SE security policy framework (see Chapter 8 on page 253).

< Day Day Up >

17.2 The PickNPop implementation

[ Team LiB ]

17.2 The PickNPop implementation A lot of the work of designing software goes into improving the way that the program looks onscreen. Software engineering is a little like theater, or like stage-magic. Your goal is to give the user the illusion that your program is a very solid, tangible kind of thing. Getting everything in place requires solid design and a lot of tweaking. One thing differentiating PickNPop from Spacewar and Airhockey is that we chose to make the _border of the world have a non-zero z size so that the shapes can pass above and below each other when we show the game in the OpenGL 3D mode. Making the score come out even Though not all games must have a numerical score, if you have one, then it should be easy to understand. On the one hand you might require that your game events have simple, round-number score values assigned to them. On the other hand you might require that your maximum possible game score total be a round easy number like 100, 1000, or even 1,000,000. If you are able to control the number of things that can happen in your game, then you can satisfy both conditions. If not, then you have to settle for one of the conditions: round-number values or round-number maximum score. In PickNPop, we allow for varying sizes of worlds, and, since the game might still be developed further, we allow for recompiling the program with different values of JEWEL_PERCENT. So it's not possible both to have round-number values and to have a round-number max score. Our decision here was to go for the round-number maximum score. In the CPopDoc::seedBubbles(int gametype, int count) method we figure out how many jewels and peanuts to make, and then we figure out how much they should be worth, and finally we calculate a _scorecorrection value that we add in at the game's end to make it possible for the user's score to exactly equal the nice round number MAX_SCORE. In cGamePickNPop::seedCritters() we compute the peanutstoadd peanuts and jewelstoadd jewels needed, and then bury the jewels 'under' the peanuts by adding them in second. The default behavior of cBiota is to draw the earlier array members after the later array members. When using the two-dimensional cGraphicsMFC, this causes a 'painter's algorithm' effect of having the later-listed critters appear behind the earlier-listed ones. When using the three-dimensional cGraphicsOpenGL, the critters are actually sorted according to the z-value of their _position values. The cheap and dirty cGame::zStackCritters() call gives the critters different z-values, again arranging them so the earlier-listed critters have larger z-values than the later-listed critters' z-values and end up appearing on top in the default view from up on the positive side of the z-axis. void cGamePickNPop::seedCritters() { /* First we'll set the _bubble array to have room for count bubbles. Then we'll add jewels and peanuts, randomizing their radii, positions, and colors as we go along. In the case of PGT_3D, we go back and change the radii at the end. */ int i; int jewelstoadd, peanutstoadd; Real jewelprobability = cGamePickNPop::JEWEL_WEIGHT; int jewelvalue(0), peanutvalue(0); cCritter *pcritternew; /* I use the jewelprobability to decide how many jewels and how many peanuts to have. These are the jewelstoadd and peanutstoadd numbers. We think of randomly drawing from this supply and adding them into the game. I want my standard game score to be MAX_SCORE, with JEWEL_GAME_WEIGHT portion of the score coming from the jewels and the rest and from the peanuts. The scores have to be integers, so it may be that the total isn't quite MAX_SCORE, so I will give the rest to the user as game-end bonus. */ //----------Get the counts and the scorevalues ready---------- jewelstoadd = int(jewelprobability * _seedcount); peanutstoadd = _seedcount � jewelstoadd; jewelvalue = int(_maxscore*cGamePickNPop::JEWEL_GAME_SCORE_WEIGHT)/ (jewelstoadd?jewelstoadd:1); peanutvalue = (_maxscore � jewelvalue*jewelstoadd)/(peanutstoadd?peanutstoadd:1); _scorecorrection = _maxscore � (jewelstoadd*jewelvalue + peanutstoadd*peanutvalue); /* We'll add this in at the end, so that user's maximum score is the same as the targeted _maxscore). */ //--------------------Renew the _bubble contents ---------- _pbiota->purgeNonPlayerNonWallCritters(); // Need to delete any from last round /* Regarding the stacking, it's worth mentioning that cBiota::draw draws the critters in reverse order, last index to first, so the first-added members appear on top in 2D. We want the peanuts "on top", so we add them first. Of course in 3D, the zStackCritters is going to take care of this irregardless of what order the critters are drawn. */ for(i=0; i<peanutstoadd; i++) { pcritternew = new cCritterPeanut(this); /* White bubble that we call a "Peanut", can't move out of _packingbox */ pcritternew->setValue(peanutvalue); } for (i=0; i<jewelstoadd; i++) { /* Make a pcritternew and then add it into _bubble at the bottom of loop. */ pcritternew = new cCritterJewel(this); /* Colored bubble that we call a "Jewel", can move all over within _border.*/ pcritternew->setValue(jewelvalue); } zStackCritters(); } The world rectangles In PickNPop we want to try and fit our game as nicely as possible into our window. We give the CDocument a cGraphicRealBox _packingbox and _targetbox field. These are to be rectangles that fit nicely inside the _border. Rather than setting their values with brute numbers, we set their values as proportions of the _border. The cRealBox::innerBox function returns a cRealBox slightly inside the caller box. And we give them some nice colors and edges. Converting a critter One of the parts of the code the author initially had trouble with was in the cCritterJewel method where we react to moving the critter inside the _targetbox. Here we have to replace one class of object by a different class of object, while still having the object be in some ways the 'same.' It turns out that you can't do this with something so simple as a type-cast of the sort you'd use to turn an int into a float. Class instances carry too much baggage for that. What we do instead is to create a brand-new object which copies the desired properties of the object that you wanted to 'cast.' We do this by means of a cCritterUnpackedJewel copy constructor. void cCritterJewel::update(CPopView *pactiveview) { cGamePickNPop *pgamepnp = NULL; //(1) Apply force if turned on. cCritter::update(pactiveview); //Always call this. cVector safevelocity(_velocity); /* To be safe, don't let any z get into velocity. */ safevelocity.setZ(0.0); setVelocity(safevelocity); //(2) Check if in targetbox, and if so, replace yourself with a good // jewel. if (pgame()->IsKindOf(RUNTIME_CLASS(cGamePickNPop))) /* We need to do the cast to access the targetbox field, and to be safe we check that the cast will work. */ pgamepnp = (cGamePickNPop*)(pgame()); else return; cRealBox effectivebox = pgamepnp- >targetbox().innerBox(cGamePickNPop::JEWELBOXTOLERANCE*radius()); if (!effectivebox.inside(_position)) return; //Reaction to being inside _targetbox. playSound("Ding"); cCritterUnpackedJewel *pcritternew = new cCritterUnpackedJewel(this); //Copy constructor pcritternew->setMoveBox(pgamepnp->targetbox()); pcritternew->setDragBox(pgamepnp->targetbox()); delete_me(); /* Just tell cBiota to just remove the old critter. Don't use the overridden cCritterJewel::die to make a noise and subtract _value from score.*/ pcritternew->add_me(_pownerbiota); //Tell cBiota add new critter. pgamepnp->pplayer()->addScore(_value); } The delete_me makes a service request to the _pownerbiota cBiota object. The add_me makes a service request as well, but since pcritternew isn't yet a member of _pownerbiota, we need to pass this pointer into the add_me method.

[ Team LiB ]

Section 24.6.  Date/Time

24.6. Date/Time Our globalization discussion thus far has been focused on strings. Date/time issues, however, can be every bit as troublesome when localizing an application. Users may be on the other side of the world from your database and web server, but they still require accurate information relating to their time zone, and the format of the date and time must be in a recognized structure. Consider the following issues related to date/time: There are different time zones around the world. Daylight savings time exists for some regions, and not for others. Certain locales use different calendars. Date/time formatting is not consistent throughout the world. 24.6.1. Timestamp Datatypes Until Oracle9i Database, working with dates and times was fairly straightforward. You had the DATE type and the TO_DATE function. The limited functionality of the DATE type made application development of global applications somewhat tedious, though. All time zone adjustments involved manual calculations. Sadly, if your application is to work with Oracle8i Database or earlier versions, I'm afraid you are still stuck with this as your only option. Those of us working with Oracle9i Database and higher, however, benefit greatly from the TIMESTAMP and INTERVAL datatypes that were discussed in detail inChapter 10. If you have not read that chapter yet, we'll provide a quick overview here, but I do recommend that you go back and read that chapter to obtain a thorough understanding of the topic. Lets take a look at an example of the TIMESTAMP, TIMESTAMP WITH TIME ZONE, AND TIMESTAMP WITH LOCAL TIME ZONE datatypes in action: SET SERVEROUTPUT ON DECLARE v_date_timestamp TIMESTAMP ( 3 ) := SYSDATE; v_date_timestamp_tz TIMESTAMP ( 3 ) WITH TIME ZONE := SYSDATE; v_date_timestamp_ltz TIMESTAMP ( 3 ) WITH LOCAL TIME ZONE := SYSDATE; BEGIN DBMS_OUTPUT.put_line ('TIMESTAMP: ' || v_date_timestamp); DBMS_OUTPUT.put_line ('TIMESTAMP WITH TIME ZONE: ' || v_date_timestamp_tz); DBMS_OUTPUT.put_line ( 'TIMESTAMP WITH LOCAL TIME ZONE: ' || v_date_timestamp_ltz ); END; / The following dates and times are returned: TIMESTAMP: 08-JAN-05 07.28.39.000 PM TIMESTAMP WITH TIME ZONE: 08-JAN-05 07.28.39.000 PM -07:00 TIMESTAMP WITH LOCAL TIME ZONE: 08-JAN-05 07.28.39.000 PM TIMEZONE and TIMEZONE WITH LOCAL TIMESTAMP are identical because the database time is in the same locale as my session. The value for TIMESTAMP WITH TIMEZONE shows that I am in the Mountain time zone. If I were in accessing my Colorado database via a session in California, the result would be slightly different. TIMESTAMP: 08-JAN-05 07.28.39.000 PM TIMESTAMP WITH TIME ZONE: 08-JAN-05 07.28.39.000 PM -07:00 TIMESTAMP WITH LOCAL TIME ZONE: 08-JAN-05 06.28.39.000 PM The value for TIMESTAMP WITH LOCAL TIMEZONE used the time zone of my session, which is now Pacific, or -08:00, and automatically converted the value. 24.6.2. Date/Time Formatting One localization challenge we face is related to date and time formatting . Japan, for example, may prefer the format yyyy/MM/dd hh:mi:ssxff AMwhile in the United States, you would expect to see dd-MON-yyyy hh:mi:ssxff AM. A common way to handle this situation is to include a list of format masks in a locale table that maps to the user. When the user logs in, his assigned locale maps to the correct date/time format for his region. The g11n schema has a USERS table and a LOCALE table, joined by a locale_id. Let's take a look at some examples using date/time functions (discussed in detail inChapter 10), and the format masks provided in the g11n.locale table. The registration_date column in the table uses the TIMESTAMP WITH TIME ZONE datatype. Using the TO_CHAR function and passing the format mask for each user's locale displays the date in the correct format. CREATE OR REPLACE FUNCTION date_format_func RETURN sys_refcursor IS v_date sys_refcursor; BEGIN OPEN v_date FOR SELECT locale.locale_desc "Locale Description", TO_CHAR (users.registration_date, locale.DATE_FORMAT ) "Registration Date" FROM users, locale WHERE users.locale_id = locale.locale_id;   RETURN v_date; END date_format_func; / To execute it, do the following: variable v_format refcursor CALL date_format_func( ) INTO :v_format; PRINT v_format This prints the following result set: Locale Description Registration Date ----------------------- ------------------ English 01-JAN-2005 11:34:21.000000 AM US/MOUNTAIN Japanese 2005/01/01 11:34:21.000000 AM JAPAN German 01 January 05 11:34:21.000000 AM EUROPE/WARSAW The three locales have different date format masks assigned. Using this method allows each user to see an appropriate date format for his locale based on his profile. If we now add NLS_DATE_FORMAT, the dates and times will use the appropriate locale language. We have this mapped in our tables to ensure that each locale is displayed correctly. CREATE OR REPLACE FUNCTION date_format_lang_func RETURN sys_refcursor IS v_date sys_refcursor; BEGIN OPEN v_date FOR SELECT locale.locale_desc "Locale Description", TO_CHAR (users.registration_date, locale.DATE_FORMAT, 'NLS_DATE_LANGUAGE= ' || locale_desc ) "Registration Date" FROM users, locale WHERE users.locale_id = locale.locale_id;   RETURN v_date; END date_format_lang_func; / Execute the function as follows: variable v_format refcursor CALL date_format_lang_func( ) INTO :v_format; PRINT v_format This prints the following: Locale Description Registration Date ----------------------- ------------------ English 01-JAN-2005 11:34:21.000000 AM US/MOUNTAIN Japanese 2005/01/01 11:34:21.000000 JAPAN German 01 Januar 05 11:34:21.000000 AM EUROPE/WARSAW The same data is stored in the USERS table, but the time is displayed in locale-specific format. You can modify the function in the same way to use the time zone and timestamp functions to distinguish between time zones for various locales. NLS_DATE_LANGUAGE is customized for each territory, so AM is in Japanese for the Japanese locale, and the month for the German locale is displayed in German. We can extend our function to include the session time zone either by converting the value to the TIMESTAMP WITH TIME ZONE datatype, or by converting the value to our session's local time zone with TIMESTAMP WITH LOCAL TIME ZONE. We do this with the CAST function (described inChapter 7), which will change the datatype of the value stored in our table. CREATE OR REPLACE FUNCTION date_ltz_lang_func RETURN sys_refcursor IS v_date sys_refcursor; BEGIN OPEN v_date FOR SELECT locale.locale_desc, TO_CHAR (CAST (users.registration_date AS TIMESTAMP WITH LOCAL TIME ZONE ), locale.DATE_FORMAT, 'NLS_DATE_LANGUAGE= ' || locale_desc ) "Registration Date" FROM users, locale WHERE users.locale_id = locale.locale_id;   RETURN v_date; END date_ltz_lang_func; / The function is executed by doing the following: variable v_format refcursor CALL date_ltz_lang_func( ) INTO :v_format; PRINT v_format The registration dates are returned as follows: Locale Description Registration Date ----------------------- ------------------ English 01-JAN-2005 11:34:21.000000 AM -07:00 Japanese 2004/12/31 07:34:21.000000 -07:00 German 01 Januar 05 03:34:21.000000 AM -07:00 There is a lot going on here: Date/time language is converted to the locale-specific terms. Formatting is locale-specific. We use CAST to convert the values stored as TIMESTAMP WITH TIMEZONE to TIMESTAMP WITH LOCAL TIMEZONE. The displayed time is relative to our session's time zone, which is U.S./Mountain in this example, or -07:00. Many of our examples thus far have shown the time zone as a UTC offset. This is not necessarily the easiest display for a user to understand. Oracle maintains a list of region names and abbreviations that can be substituted by modifying the format mask. In fact, the three records we have been working with were inserted using these region names rather than the UTC offset. For a complete list of time zones, query the V$TIMEZONE_NAMES view. Examine the INSERT statements into the USERS table in the g11n schema for more examples using region names. I want to discuss one more NLS parameter related to date/time. Our times are inserted into the table using the Gregorian calendar, which is the value for NLS_CALENDAR on our test system. Not all locales use the same calendar, however, and no amount of formatting will adjust the base calendar. With NLS_CALENDAR, we can change our default calendar from Gregorian to a number of other seeded calendarsJapanese Imperial for example. A simple SELECT of SYSDATE after setting the session results in the following: ALTER SESSION SET NLS_CALENDAR = 'JAPANESE IMPERIAL'; ALTER SESSION SET NLS_DATE_FORMAT = 'E RR-MM-DD'; After altering the session, run the following SELECT: SELECT sysdate FROM dual; The SELECT shows the modified SYSDATE: SYSDATE --------- H 17-02-08 Default values are controlled by your NLS settings. If you have a primary locale you are working with, you may find that setting your NLS parameters for your database is a much easier approach than explicitly stating them in your application. For applications in which these settings need to be dynamic, however, I recommend that you include NLS settings as part of your user/locale settings and store them with your application. This allows your code to function in any locale simply by setting a user's profile correctly.

16.3 Using the Query Cache

< Day Day Up >

16.3 Using the Query Cache MySQL supports a query cache that greatly increases performance if the server's query mix includes SELECT statements that are processed repeatedly and return the same results each time. If you enable the query cache, the server uses it as follows: The server compares each SELECT query that arrives to any already in the cache. If the query is already present and none of the tables that it uses have changed since the result was cached, the server returns the result immediately without executing the query. If the query is not present in the cache or if any of the tables that it uses have changed (thus invalidating the saved result), the server executes the query and caches its result. The server determines whether a query is in the cache based on exact case-sensitive comparison of query strings. That means the following two queries are not considered the same: SELECT * FROM table_name; select * from table_name; The server also takes into account any factor that distinguishes otherwise identical queries. Among these are the character set used by each client and the default database. For example, two SELECT * FROM table_name queries might be lexically identical but are semantically different if each applies to a different default database. The query cache is global, so a query result placed in the cache can be returned to any client. Using the query cache can result in a tremendous performance boost and reduction in server load, especially for disk- or processor-intensive queries. Three system variables control query cache operation: mysql> SHOW VARIABLES LIKE 'query_cache%'; +-------------------+---------+ | Variable_name | Value | +-------------------+---------+ | query_cache_limit | 1048576 | | query_cache_size | 0 | | query_cache_type | ON | +-------------------+---------+ The default value of query_cache_type is ON (caching allowed). However, the cache is not operational unless its size is set larger than the default value of zero. To enable the query cache, set the value of query_cache_size to a nonzero size in bytes to indicate how much memory to allocate to it. You may optionally set the query_cache_limit variable as well. It places an upper bound on how large an individual query result can be and still be eligible for caching. The default limit is 1MB. Typically, you set the query cache variables in an option file where you list the server's startup options. For example, to allocate 10MB of memory to the query cache and allow individual query results up to 2MB to be cached, put the following lines in the option file and restart the server: [mysqld] query_cache_size = 10M query_cache_limit = 2M If you have the SUPER privilege, you can change these variables for a running server without restarting it by using the following statements: SET GLOBAL query_cache_size = 10485760; SET GLOBAL query_cache_limit = 2097152; If you set the variables that way, the changes will be lost at the next server restart, so SET is useful primarily for testing cache settings. When you find suitable values, record them in the option file. The server provides information about the operation of the query cache by means of a set of status variables. To view these variables, use the following statement: mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+--------+ | Variable_name | Value | +-------------------------+--------+ | Qcache_queries_in_cache | 360 | | Qcache_inserts | 12823 | | Qcache_hits | 21145 | | Qcache_lowmem_prunes | 584 | | Qcache_not_cached | 10899 | | Qcache_free_memory | 231008 | | Qcache_free_blocks | 98 | | Qcache_total_blocks | 861 | +-------------------------+--------+ Qcache_inserts is the total number of queries that have been put in the cache. Qcache_queries_in_cache indicates the number of queries currently registered in the cache. The difference between the two values indicates how many cached queries were displaced to make room for newer queries. Qcache_hits indicates how many times a query did not have to be executed because its result could be served from the cache.

< Day Day Up >

UML Package Diagram

UML Package Diagram For our sample application, Time Expression, we will use the prefix com.visualpatterns.timex for our package name. If you have worked with Java already, you probably know that the first part of the package name is typically tied to an organization's domain name, just used backwards. For example, com.visualpatterns is the reverse for visualpatterns.com, which happens to be my website. The timex portion of our package name is derived from the name of our sample application. The remainder, the suffixes, for our package names are shown in Figure 3.6, a rudimentary UML package diagram. Figure 3.6. UML package diagram for Time Expression. [View full size image] Note I have chosen very basic Java packages names to match our MVC pattern-based design. For example, we could have called our model package something like domain, but I prefer to match things upfor example, matching the package names with the architecture or application flow map. That way, someone new taking over my code can easily follow its organization. So, a fully qualified package name for the model package would be com.visualpatterns.timex.model. As you might guess, the controller package will have controller-related classes in it. The job package will contain our email reminder job. The util package contains common and/or utility code. Last but not least, the test package will contain our unit test code. Although I have chosen to place our test classes in a separate package, many developers prefer keeping the test classes in the same directory as the implementation code they are testing. This is a matter of preference, but in my opinion, having a separate package/directory for the test classes keeps things nice and clean in the actual implementation package directories.

Lab 9.2 Exercise Answers

[ Team LiB ]

Lab 9.2 Exercise Answers 9.2.1 Answers a) Write a PL/SQL block that will reduce the cost of all courses by 5% for courses having an enrollment of eight students or more. Use a cursor FOR loop that will update the course table. A1: Answer: Your block should look like this: -- ch09_10a.sql DECLARE CURSOR c_group_discount IS SELECT DISTINCT s.course_no FROM section s, enrollment e WHERE s.section_id = e.section_id GROUP BY s.course_no, e.section_id, s.section_id HAVING COUNT(*)>=8; BEGIN FOR r_group_discount IN c_group_discount LOOP UPDATE course SET cost = cost * .95 WHERE course_no = r_group_discount.course_no; END LOOP; COMMIT; END; The cursor c_group_discount is declared in the declarative section. The proper SQL is used to generate the select statement to answer the question given. The cursor is processed in a FOR loop�in each iteration of the loop the SQL update statement will be executed. This means it does not have to be opened, fetched, and closed. Also, it means that a cursor attribute does not have to be used to create an exit condition for the loop that is processing the cursor. 9.2.2 Answers a) Write a PL/SQL block with two cursor FOR loops. The parent cursor will call the student_id, first_name, and last_name from the student table for students with a student_id less than 110 and output one line with this information. For each student, the child cursor will loop through all the courses that the student is enrolled in, outputting the course_no and the description. A1: Answer: Your block should look be similar to this: -- ch09_11a.sql DECLARE v_sid student.student_id%TYPE; CURSOR c_student IS SELECT student_id, first_name, last_name FROM student WHERE student_id < 110; CURSOR c_course IS SELECT c.course_no, c.description FROM course c, section s, enrollment e WHERE c.course_no = s.course_no AND s.section_id = e.section_id AND e.student_id = v_sid; BEGIN FOR r_student IN c_student LOOP v_sid := r_student.student_id; DBMS_OUTPUT.PUT_LINE(chr(10)); DBMS_OUTPUT.PUT_LINE(' The Student '|| r_student.student_id||' '|| r_student.first_name||' '|| r_student.last_name); DBMS_OUTPUT.PUT_LINE(' is enrolled in the '|| 'following courses: '); FOR r_course IN c_course LOOP DBMS_OUTPUT.PUT_LINE(r_course.course_no|| ' '||r_course.description); END LOOP; END LOOP; END; The select statements for the two cursors are defined in the declarative section of the PL/SQL block. A variable to store the student_id from the parent cursor is also declared. The course cursor is the child cursor, and, since it makes use of the variable v_sid, the variable must be declared first. Both cursors are processed with a FOR loop, which eliminates the need for OPEN, FETCH, and CLOSE. When the parent student loop is processed, the first step is to initialize the variable v_sid, and the value is then used when the child loop is processed. DBMS_OUTPUT is used so that display is generated for each cursor loop. The parent cursor will display the student name once, and the child cursor will display the name of each course in which the student is enrolled. b) Before you run the preceding code, analyze what it is doing and determine what you think the result would be. Explain what is happening in each phase of the PL/SQL block and what is happening to the variables as control is passing through parent and child cursor. A1: Answer: The declaration section contains a declaration for two variables. The first is v_amount of the datatype matching that of the cost in the course table; the second is the v_instructor_id of the datatype matching the instructor_id in the instructor table. There are also two declarations for two cursors. The first is for c_inst, which is comprised of the first_name, last_name, and instructor_id for an instructor from the instructor table. The second cursor, c_cost, will produce a result set of the cost of the course taken for each student enrolled in a course by the instructor that matches the variable v_instructor_id. These two cursors will be run in nested fashion. First, the cursor c_inst is opened in a FOR loop. The value of the variable v_instructor_id is initialized to match the instructor_id of the current row of the c_inst cursor. The variable v_amount is initialized to 0. The second cursor is open within the loop for the first cursor. This means that for each iteration of the cursor c_inst, the second cursor will be opened, fetched, and closed. The second cursor will loop through all the cost generated by each student enrolled in a course for the instructor, which is current of the c_inst cursor. Each time the nest loop iterates, it will increase the variable v_amount by adding the current cost in the c_cost loop. Prior to opening the c_cost loop, there is a DBMS_OUTPUT to display the instructor name. After the c_cost cursor loop is closed, it will display the total amount generated by all the enrollments of the current instructor. c) Run the code and see what the result is. Is it what you expected? Explain the difference. A2: Answer: The result set would be as follows: Generated by instructor Fernand Hanks $16,915 Generated by instructor Tom Wojick $18,504 Generated by instructor Nina Schorin $30,137 Generated by instructor Gary Pertez $24,044 Generated by instructor Anita Morris $13,389 Generated by instructor Todd Smythe $14,940 Generated by instructor Rick Chow $0 Generated by instructor Charles Lowry $12,175 Generated by instructor Marilyn Frantzen $13,224 PL/SQL procedure successfully completed. In this example, the nested cursor is tied to the current row of the outer cursor by means of the variable v_instructor_id. A more common way of doing this is to pass a parameter to a cursor. You will learn more about how to achieve this in Chapter 15, "Advanced Cursors."

[ Team LiB ]

14.5 Eliminating Duplicates from a Query Result

I l@ve RuBoard

14.5 Eliminating Duplicates from a Query Result 14.5.1 Problem You want to select rows in a query result in such a way that it contains no duplicates. 14.5.2 Solution Use SELECT DISTINCT. 14.5.3 Discussion Rows in query results sometimes contain duplicate rows. This is particularly common when you select only a subset of the columns in a table, because that reduces the amount of information available that might otherwise distinguish one row from another. To obtain only the unique rows in a result, eliminate the duplicates by adding the DISTINCT keyword. That tells MySQL to return only one instance of each set of column values. For example, if you select the name columns from the cat_mailing table without using DISTINCT, several duplicates occur: mysql> SELECT last_name, first_name -> FROM cat_mailing ORDER BY last_name, first_name; +-----------+-------------+ | last_name | first_name | +-----------+-------------+ | Baxter | Wallace | | BAXTER | WALLACE | | Baxter | Wallace | | Brown | Bartholomew | | Isaacson | Jim | | McTavish | Taylor | | Pinter | Marlene | | Pinter | Marlene | +-----------+-------------+ With DISTINCT, the duplicates are eliminated: mysql> SELECT DISTINCT last_name, first_name -> FROM cat_mailing ORDER BY last_name; +-----------+-------------+ | last_name | first_name | +-----------+-------------+ | Baxter | Wallace | | Brown | Bartholomew | | Isaacson | Jim | | McTavish | Taylor | | Pinter | Marlene | +-----------+-------------+ An alternative to DISTINCT is to add a GROUP BY clause that names the columns you're selecting. This has the effect of removing duplicates and selecting only the unique combinations of values in the specified columns: mysql> SELECT last_name, first_name FROM cat_mailing -> GROUP BY last_name, first_name; +-----------+-------------+ | last_name | first_name | +-----------+-------------+ | Baxter | Wallace | | Brown | Bartholomew | | Isaacson | Jim | | McTavish | Taylor | | Pinter | Marlene | +-----------+-------------+ 14.5.4 See Also SELECT DISTINCT is discussed further in Recipe 7.5.

I l@ve RuBoard

4.1 Creating a JavaScript Variable

[ Team LiB ]

4.1 Creating a JavaScript Variable NN 2, IE 3 4.1.1 Problem You want to create a JavaScript variable value either in the global space or privately within a function. 4.1.2 Solution Use the var keyword to define the first instance of every variable, whether you assign a value to the variable immediately or delay the assignment until later. Any variable defined outside of a function is part of the global variable scope: var myVar = someValue; All script statements on the page, including those inside functions, have read/write access to a global variable. When you define a variable with var inside a function, only statements inside the function can access that variable: function myFunction( ) { var myFuncVar = someValue; ... } Statements outside of the function cannot reach the value of a variable whose scope is limited to its containing function. 4.1.3 Discussion A JavaScript variable has no inherent limit to the amount of data it can hold. Maximum capacity is determined strictly by memory available to the browser application�information not accessible to your scripts. Variable scope is an important concept to understand in JavaScript. Not only is a global variable accessible by all script statements in the current window or frame, but statements in other frames or windows (served from the same domain and server) can access those global variables by way of the window or frame reference. For example, a statement in a menu frame can reference a global variable named myVar in a frame named content as follows: parent.content.myVar You don't have to worry about the same global variable names colliding when they exist in other windows or frames, because the references to those variables will always be different. Where you must exercise care is in defining a new variable inside a function with the var keyword. If you fail to use the keyword inside the function, the variable is treated as a global variable. If you have defined a global variable with the same name, the function's assignment statement overwrites the value originally assigned to the global variable. The safest way to avoid these kinds of problems is to always use the var keyword with the first instance of any variable, regardless of where it's located in your scripts. Even though the keyword is optional for global variable declarations, it is good coding style to use var for globals as well. That way you can readily see where a variable is first used in a script. Although some programming languages distinguish between the tasks of declaring a variable (essentially reserving memory space for its value) and initializing a variable (stuffing a value into it), JavaScript's dynamic memory allocation for variable values unburdens the scripter of memory concerns. A variable is truly variable in JavaScript in that not only can the value stored in the variable change with later reassignments of values, but even the data type of the variable's value can change (not that this is necessarily good programming practice, but that's simply a by-product of JavaScript's loose data typing). Speaking of good programming practice, it is generally advisable to define global variables near the top of the script, just as it's also advisable to define heavily used variables inside a function at the top of the function. Even if you don't have a value ready to assign to the variable, you can simply declare the variable as undefined with a statement like the following: var myVar; If you have multiple variables that you'd like to declare, you may do so compactly by separating the variable names with commas: var myVar, counter, fred, i, j; You may even combine declarations and initializations in a comma-delimited statement: var myVar, counter = 0, fred, i, j; In examples throughout this book, you typically find variables being declared or initialized at the top of their scope regions, but not always. It's not unusual to find variables that are about to be used inside a for loop defined (with their var keywords) just before the loop statements. For example, if a nested pair of loops is in the offing, I may define the loop counter variables prior to the outer loop's start: var i, j; for (i = 0; i < array1.length; i++) { for (j = 0; j < array1[i].array2.length; j++) { ... } } This is merely my style preference. But in any case, this situation definitely calls for declaring the variables outside of the loops for another reason. If you were to use the var keywords in the loop counter initialization statements (e.g., var j = 0;), the nested loop would repeatedly invoke the var declaration keyword each time the outer loop executes. Internally, the JavaScript interpreter creates a new variable space for each var keyword. Fortunately, the interpreter is also able to keep track of which variable repeatedly declared is the current one, but it places an unnecessary burden on resources. Declare once, then initialize and reassign values as often as needed. Thus, in complex functions that have two or more outer for loops, you should declare the counter variable at the top of the function, and simply initialize the value at the start of each loop. As for selecting the names for your variables, there are some explicit rules and implicit customs to follow. The explicit rules are more important. A variable name cannot: Begin with a numeral Contain any spaces or other whitespace characters Contain punctuation or symbols except the underscore character Be surrounded by quote marks Be a reserved ECMAScript keyword (see Appendix C) Conventions among programmers with respect to devising names for variables are not rigid, nor do they affect the operation of your scripts. They do, however, help in readability and maintenance when it comes time to remember what your script does six months from now. The main idea behind a variable name is to help you identify what kind of value the variable contains (in fact, names are commonly called identifiers). Littering your scripts with a bunch of one- or two-letter variables won't help you track values or logic when reading the script. On the other hand, there are performance reasons (see Recipe 4.8) to keep names from getting outrageously long. The shorter the better, but not to the point of cryptic ciphers. If you need two or more words to describe the value, join the words together via underscore characters, or capitalize the first character of any words after the first word (a convention used throughout this book). Thus, either of the variable names in the following initializations are fine: var teamMember = "George"; var team_member = "George"; Apply these rules and concepts to the identifiers you assign to element name and id attributes, as well. Your scripts will then have no trouble using these identifiers in DOM object references. Variable names are case-sensitive. Therefore, it is permissible (although not necessarily advisable) to reuse an identifier with different case letters to carry different values. One convention that you might employ is to determine which variables won't be changing their values during the execution of your scripts (i.e., you will treat them as constants) and make their names all uppercase. Netscape 6 and later implement a forthcoming ECMAScript keyword called const, which you use in place of var to define a true constant value. No other browser supports this keyword yet, so you can use variables as constants and keep modification statements away from them. JavaScript assigns data to a variable both "by reference" and "by value," depending on the type of data. If the data is a true object of any kind (e.g., DOM object, array, custom object), the variable contains a "live" reference to the object. You may then use that variable as a substitute reference to the object: var elem = document.getElementById("myTable"); var padWidth = elem.cellPadding; But if the data is a simple value (string, number, Boolean), the variable holds only a copy of the value, with no connection to the object from which the value came. Therefore, the padWidth variable shown above simply holds a string value; if you were to assign a new value to the variable, it would have no impact on the table element. To set the object's property, go back to the object reference and assign a value to the property: elem.cellPadding = "10"; If an object's property value is itself another object, the variable receives that data as an object reference, still connected to its object: var elem = document.getElementById("myTable"); var elemStyle = elem.style; elemStyle.fontSize = "18px"; Exercise care with DOM objects assigned to variables. It may seem as though the variable is a mere copy of the object reference, but changes you make to the variable value affect the document node tree. 4.1.4 See Also Chapter 1, Chapter 2, and Chapter 3 discuss assigning values of different types�strings, numbers, arrays, and objects�to variables; Recipe 4.8 for the impact of variable name length on performance.

[ Team LiB ]

Chapter 7: Control Visual Basic

Chapter at a Glance

In this chapter, you will learn to:

• 
  

  Use conditional statements.

  
  


• 
  

  Create loops using three different blocks.

  
  


• 
  

  Retrieve the names of files in a folder.

  
  


• 
  

  Create breakpoints to debug long loops.

  
  


• 
  

  Show progress while a macro executes a loop.

  
  

The first successful underwater tunnel ever built was begun in 1825. It is the Thames Tunnel. It was a financial disaster at the time, but amazingly it is still in use as part of the London Underground system. The genius behind the the tunnel’s engineering was a man named Marc Brunel. Twenty years before launching the Thames Tunnel, Brunel made a name for himself by devising a way of inexpensively producing the pulley blocks needed to build ships for the British shipping industry. Brunel’s technique later came to be known as an “assembly line,” and Henry Ford turned the invention into an industry, supplying America with Model T cars that cost only $3,500 in today’s dollars.

Repetition can have a dramatic effect on efficiency. Computer programs-including macros that you write-become more powerful when you add a multiplier effect. In this chapter, you’ll learn how to add loops to your macros. And to make those loops more effective, you’ll learn how to create conditional expressions that let the macro make decisions.

On The CD-Important

Before you complete this chapter, you need to install the practice files from the book’s companion CD to their default locations. See “Using the Book’s CD” on page xv for more information. USE the Flow.xlsx workbook, the Flow.txt text file, and the Orders.xlsx workbook. These practice files are located in the Documents\MSP\ExcelVBA07SBS folder. The Flow text file contains some initial macros that you will copy into your workbook and modify during this chapter. The initial macros are stored in a simple text file so that you can be certain there is no malicious code before you put the code into a trusted location. BE SURE TO save the Flow.xlsx workbook as a macro-enabled workbook named Chapter07.xlsm in the trusted location you created in Chapter 1. OPEN the Flow text file. Then open the Chapter07 workbook, right-click any sheet tab, and click View Code to open the Microsoft Visual Basic editor. In the Visual Basic editor, from the Insert menu, click Module to create a new module for your macros, and then save the file. Arrange the Microsoft Office Excel 2007 and Visual Basic editor windows so that you can see both of them side by side.

Section 8.4. Bringing It All Together

8.4. Bringing It All Together Now that we have a skeletal device driver, we can load it and exercise it. Listing 8-11 is a simple user space application that exercises our device driver. We've already seen how to load the driver. Simply compile it and issue the make modules_install command to place it on your file system, as previously described. Listing 8-11. Exercising Our Device Driver #include <stdio.h> #include <stdlib.h> #include <sys/types.h> #include <sys/stat.h> #include <fcntl.h> #include <unistd.h> int main(int argc, char **argv) { /* Our file descriptor */ int fd; int rc = 0; char *rd_buf[16]; printf("%s: entered\n", argv[0]); /* Open the device */ fd = open("/dev/hello1", O_RDWR); if ( fd == -1 ) { perror("open failed"); rc = fd; exit(-1); } printf("%s: open: successful\n", argv[0]); /* Issue a read */ rc = read(fd, rd_buf, 0); if ( rc == -1 ) { perror("read failed"); close(fd); exit(-1); } printf("%s: read: returning %d bytes!\n", argv[0], rc); close(fd); return 0; } This simple file, compiled on an ARM XScale system, demonstrates the binding of application to device driver, through the device node. Like the device driver, it doesn't do any useful work, but it does demonstrate the concepts as it exercises some of the methods we introduced in the device driver of Listing 8-10. First we issue an open() system call [7] on our device node created earlier. If the open succeeds, we indicate that with a message to the console. Next we issue a read() command and again print a message to the console on success. Notice that a read of 0 bytes is perfectly acceptable as far as the kernel is concerned and, in actual practice, indicates an end-of-file or out-of-data condition. Your device driver defines that special condition. When complete, we simply close the file and exit. Listing 8-12 captures the output of running this example application on an ARM XScale target: [7] Actually, the open() call is a C library wrapper function around the Linux sys_open() system call. Listing 8-12. Using the Example Driver $ modprobe hello1 Hello Example Init - debug mode is disabled Hello: registered module successfully! $ ./use-hello ./use-hello: entered ./use-hello: open: successful ./use-hello: read: returning zero bytes! $

Section 13.6.  Predefined Object Types

13.6. Predefined Object Types Starting with Oracle9i Database Release 1, Oracle provides a collection of useful, predefined object types: XMLType Use this to store and manipulate XML data. Various URI types Use these to store uniform resource identifiers (such as HTML addresses). Various Any types Use these to define a PL/SQL variable that can hold any type of data. The following subsections discuss these predefined object types in more detail. 13.6.1. The XMLType Type Oracle9i Database Release 1 introduced a native object type called XMLType. You can use XMLType to define database columns and PL/SQL variables containing XML documents. Methods defined on XMLType enable you to instantiate new XMLType values, to extract portions of an XML document, and to otherwise manipulate the contents of an XML document in various ways. In Oracle9i Database Release 1, you needed to use the "SYS." prefix when referencing the XMLType object type. Staring with Oracle9i Database Release 2, Oracle allows synonyms to point to object types, and the database creation script ($ORACLE_HOME/rdbms/admin/dbmsxmlt.sql) that creates XMLType now also creates the public synonym XMLTYPE, which points to the SYS.XMLType predefined object type. XML in PL/SQL XML is a huge subject that we can't hope to cover in any detail in this book. However, if you're working with XML from PL/SQL, there are at least two things you need to know about: XMLType A built-in object type, introduced in this section, which enables you to store XML documents in a database column or in a PL/SQL variable. XMLType was introduced in Oracle9i Database Release 1. XQuery A query language to use in retrieving and constructing XML documents. XQuery is new in Oracle Database 10g Release 2. If you begin with these two things and drill down, you'll be led to many other XMLish topics you need to know about: XPath for referring to portions of an XML document, XML Schema for describing document structure, and so forth. Using XMLType, you can easily create a table to hold XML data: CREATE TABLE falls ( fall_id NUMBER, fall XMLType ); The fall column in this table is of XMLType and can hold XML data. To store XML data into this column, you must invoke the static CreateXML method, passing it your XML data. CreateXML accepts XML data as input and instantiates a new XMLType object to hold that data. The new object is then returned as the method's result, and it is that object that you must store in the column. CreateXML is overloaded to accept both VARCHAR2 strings and CLOBs as input. Use the following INSERT statements to create three XML documents in the falls table: INSERT INTO falls VALUES (1, XMLType.CreateXML( '<?xml version="1.0"?> <fall> <name>Munising Falls</name> <county>Alger</county> <state>MI</state> <url> http://michiganwaterfalls.com/munising_falls/munising_falls.html </url> </fall>')); INSERT INTO falls VALUES (2, XMLType.CreateXML( '<?xml version="1.0"?> <fall> <name>Au Train Falls</name> <county>Alger</county> <state>MI</state> <url> http://michiganwaterfalls.com/autrain_falls/autrain_falls.html </url> </fall>')); INSERT INTO falls VALUES (3, XMLType.CreateXML( '<?xml version="1.0"?> <fall> <name>Laughing Whitefish Falls</name> <county>Alger</county> <state>MI</state> </fall>')); You can query XML data in the table using various XMLType methods. The existsNode method used in the following example allows you to test for the existence of a specific XML node in an XML document. The built-in SQL EXISTSNODE function, also in the example, performs the same test. Whether you use the method or the built-in function, you identify the node of interest using an XPath expression.[*] [*] XPath is a syntax that describes parts of an XML document. Among other things, you can use XPath to specify a particular node or attribute value in an XML document. Both of the following statements produce the same output: SQL> SELECT fall_id 2 FROM falls f 3 WHERE f.fall.existsNode('/fall/url') > 0; SQL> SELECT fall_id 2 FROM falls 3 WHERE EXISTSNODE(fall,'/fall/url') > 0; FALL_ID ---------- 1 2 You can, of course, also work with XML data from within PL/SQL. In the following example, we retrieve the fall column for Munising Falls into a PL/SQL variable that is also of XMLType. Thus, we retrieve the entire XML document into our PL/SQL program, where we can work further with it. After retrieving the document, we extract and print the text from the /fall/url node. <<demo_block>> DECLARE fall XMLType; url VARCHAR2(100); BEGIN --Retrieve XML for Munising Falls SELECT fall INTO demo_block.fall FROM falls f WHERE f.fall_id = 1; --Extract and display the URL for Munising Falls url := fall.extract('/fall/url/text( )').getStringVal; DBMS_OUTPUT.PUT_LINE(url); END; We'd like to call your attention to the following two lines: SELECT fall INTO demo_block.fall Our variable name, fall, matches the name of the column in the database table. In our SQL query, therefore, we qualify our variable name with the name of our PL/SQL block. url := fall.extract('/fall/url/text( )').getStringVal; To get the text of the URL, we invoke two of XMLType's methods: extract Returns an XML document, of XMLType, containing only the specified fragment of the original XML document. Use XPath notation to specify the fragment you want returned. getStringVal Returns the text of an XML document. In our example, we apply the getStringVal method to the XML document returned by the extract method, thus retrieving the text for the Munising Fall's URL. The extract method returns the contents of the <url> node as a XMLType object, and getStringVal then returns that content as a text string that we can display. You can even index XMLType columns to allow for efficient retrieval of XML documents based on their content. You do this by creating a function-based index, for which you need the QUERY REWRITE privilege. The following example creates a function-based index on the first 80 characters of each falls name: CREATE INDEX falls_by_name ON falls f ( SUBSTR( XMLType.getStringVal( XMLType.extract(f.fall,'/fall/name/text( )') ),1,80 ) ); We had to use the SUBSTR function in the creation of this index. The getStringVal method returns a string that is too long to index, resulting in an ORA-01450: maximum key length (3166) exceeded error. Thus, when creating an index like this, you need to use SUBSTR to restrict the results to some reasonable length. If you decide to use XMLType in any of your applications, be sure to consult Oracle's documentation for more complete and current information. The XML DB Developer's Guide for Oracle Database 10g Release 2 is an important, if not critical, reference for developers working with XML. The SQL Reference also has some useful information on XMLType and on the built-in SQL functions that support XML. 13.6.2. The URI Types The URI types introduced in Oracle9i Database Release 1 consist of a supertype and a collection of subtypes that provide support for storing URIs in PL/SQL variables and in database columns. UriType is the supertype, and a UriType variable can hold any instance of one of the subtypes: HttpUriType A subtype of UriType that is specific to HTTP URLs, which usually point to web pages. DBUriType A subtype of UriType that supports URLs that are XPath expressions. XDBUriType A subtype of UriType that supports URLs that reference Oracle XML DB objects. XML DB is Oracle's name for a set of XML technologies built into the database. To facilitate your work with URIs, Oracle also provides a UriFactory package that automatically generates the appropriate URI type for whatever URI you pass to it. The URI types are created by the script named dbmsuri.sql $ORACLE_HOME/rdbms/admin. All the types and subtypes are owned by the user SYS. In Oracle9i Database Release 1, you must use the "SYS." prefix to reference the URI types. From Oracle9i Database Release 2 onwards, you do not need to use the "SYS." prefix. The following code example demonstrates the use of HttpUriType: DECLARE WebPageURL HttpUriType; WebPage CLOB; BEGIN --Create an instance of the type pointing --to a message from Jonathan Gennick WebPageURL := HttpUriType.createUri( 'http://gennick.com/message.plsql'); --Retrieve the message via HTTP WebPage := WebPageURL.getclob( ); --Display the message DBMS_OUTPUT.PUT_LINE((SUBSTR(WebPage,1,60))); END; The output from this code example will be: Brighten the corner where you are. For more information on the use of the UriType family, see Chapter 9, Accessing Data Through URIs, of the XML DB Developer's Guide for Oracle Database 10g Release 2. 13.6.3. The Any Types Beginning with Oracle9i Database Release 1, a family of types known as the Any types enables you to write programs to manipulate data when you don't know the type of that data until runtime. Member functions support introspection, allowing you to determine the type of a value at runtime and to access that value.[*] [*] An introspection function is one that you can use in a program to examine and learn about variables declared by your program. In essence, your program learns about itselfhence the term introspection. The following predefined types belong to this family: AnyData Can hold a single value of any type, whether it's a built-in scalar datatype, a user-defined object type, a nested table, a large object, a varying array (VARRAY), or any other type not listed here. AnyDataSet Can hold a set of values of any type, as long as all values are of the same type. AnyType Can hold a description of a type. Think of this as an AnyData without the data. The Any types are created by a script named dbmsany.sql found in $ORACLE_HOME/rdbms/admin, and are owned by the user SYS. As with the URI types, in Oracle9i Database Release 1 you must use a "SYS." prefix to reference the Any types, but this is no longer necessary beginning in Oracle 9i Database Release 2). In addition to creating the Any types, the dbmsany.sql script also creates a package named DBMS_TYPES that defines the constants in the following list. You can use these constants in conjunction with introspection functions such as GETTYPE in order to determine the type of data held by a given AnyData or AnyDataSet variable. The specific numeric values assigned to the constants are not important; rely on the constants, not on their underlying values. TYPECODE_DATE TYPECODE_NUMBER TYPECODE_RAW TYPECODE_CHAR TYPECODE_VARCHAR2 TYPECODE_VARCHAR TYPECODE_MLSLABEL TYPECODE_BLOB TYPECODE_BFILE TYPECODE_CLOB TYPECODE_CFILE TYPECODE_TIMESTAMP TYPECODE_TIMESTAMP_TZ TYPECODE_TIMESTAMP_LTZ TYPECODE_INTERVAL_YM TYPECODE_INTERVAL_DS TYPECODE_REF TYPECODE_OBJECT TYPECODE_VARRAY TYPECODE_TABLE TYPECODE_NAMEDCOLLECTION TYPECODE_OPAQUE The following example creates two user-defined types representing two kinds of geographic features. The subsequent PL/SQL block then uses SYS.AnyType to define a heterogeneous array of features (i.e., each array element can be of a different datatype). First, you'll need to create the following two types: CREATE OR REPLACE TYPE waterfall AS OBJECT ( name VARCHAR2(30), height NUMBER ); CREATE OR REPLACE TYPE river AS OBJECT ( name VARCHAR2(30), length NUMBER ); Next, execute the following PL/SQL code block: DECLARE TYPE feature_array IS VARRAY(2) OF SYS.AnyData; features feature_array; wf waterfall; rv river; ret_val NUMBER; BEGIN --Create an array where each element is of --a different object type features := feature_array( AnyData.ConvertObject( waterfall('Grand Sable Falls',30)), AnyData.ConvertObject( river('Manistique River', 85.40)) ); --Display the feature data FOR x IN 1..features.COUNT LOOP --Execute code pertaining to whatever object type --we are currently looking at. NOTE! Replace GENNICK --with whatever schema you are using. CASE features(x).GetTypeName WHEN 'GENNICK.WATERFALL' THEN ret_val := features(x).GetObject(wf); DBMS_OUTPUT.PUT_LINE('Waterfall: ' || wf.name || ', Height = ' || wf.height || ' feet.'); WHEN 'GENNICK.RIVER' THEN ret_val := features(x).GetObject(rv); DBMS_OUTPUT.PUT_LINE('River: ' || rv.name || ', Length = ' || rv.length || ' miles.'); END CASE; END LOOP; END; Finally, your output should appear as follows: Waterfall: Grand Sable Falls, Height = 30 feet. River: Manistique River, Length = 85.4 miles. Let's look at this code one piece at a time. The features are stored in a VARRAY, which is initialized as follows: features := feature_array( AnyData.ConvertObject( waterfall('Grand Sable Falls',30)), AnyData.ConvertObject( river('Manistique River, 85.40)) ); Working from the inside out and focusing on Grand Sable Falls, you can interpret this code as follows: waterfall('Grand Sable Falls',30) Invokes the constructor for the waterfall type to create an object of that type. AnyData.ConvertObject( Converts the waterfall object into an instance of SYS.AnyData, allowing it to be stored in our array of SYS.AnyData objects. feature_array( Invokes the constructor for the array. Each argument to feature_array is of type AnyData. The array is built from the two arguments we pass. VARRAYs were discussed in Chapter 12, and you can read about object types in more detail in Chapter 25. The next significant part of the code is the FOR loop in which each object in the features array is examined. A call to: features(x).GetTypeName returns the fully qualified type name of the current features object. For user-defined objects, the type name is prefixed with the schema name of the user who created the object. We had to include this schema name in our WHEN clauses; for example: WHEN 'GENNICK.WATERFALL' THEN If you're running this example on your own system, be sure to replace the schema we used (GENNICK) with the one that is valid for you. For built-in types such as NUMBER, DATE, and VARCHAR2, GetTypeName will return just the type name. Schema names apply only to user-defined types (i.e., those created using CREATE TYPE). Once we determined which datatype we were dealing with, we retrieved the specific object using the following call: ret_val := features(x).GetObject(wf); In our example, we ignored the return code. There are two possible return code values: DBMS_TYPES.SUCCESS The value (or object, in our case) was successfully returned. DBMS_TYPES.NO_DATA No data was ever stored in the AnyData variable in question, so no data can be returned. Once we had the object in a variable, it was an easy enough task to write a DBMS_OUTPUT statement specific to that object type. For example, to print information about waterfalls, we used: DBMS_OUTPUT.PUT_LINE('Waterfall: ' || wf.name || ', Height = ' || wf.height || ' feet.'); For more information on the "Any" family of types: Visit Chapter 25, which examines the Any datatypes from an object-oriented perspective. Check out Oracle's PL/SQL Packages and Types Reference and the SQL Reference. Try out the anynums.pkg and anynums.tst scripts on the book's web site. From an object-oriented design standpoint, there are better ways to deal with multiple feature types than the method we used in this section's example. In the real world, however, not everything is ideal, and our example does serve the purpose of demonstrating the utility of the SYS.AnyData predefined object type.