It explains the features and functionality of its objects, including data blocks, items, windows, record structures, and menus. It also discusses events associated with each object, triggers they fire, and how you can use them to enhance Forms applications.
Part Four. This part covers advanced programming with Oracle Forms. It begins with debugging techniques and activities. I dedicate an entire chapter to the reusable components provided by Oracle Developer and the benefits you draw by including them in your applications.
Another important subject I cover in this part is the development and deployment of Forms applications on the Web in a three-tier architecture. In a separate chapter, I present tips and techniques to simplify Forms development and installation using the Project Builder.
I conclude this part discussing the use of Oracle Designer in creating, maintaining, and generating Form Builder modules. Part Five. As such, this book addresses the needs of this group of IT professionals. Because of the way the material is organized, the book can be used by application developers at all levels of proficiency. The first part is for newcomers to the Oracle Forms environment.
A single row or tuple representing all the data required for a particular employee. Each row in a table should be identified by a primary key, which permits no duplicate rows. A column or attribute containing the employee number.
In this example, the employee number U I n column is designated as the primary key. A primary key must contain a value and the value must be unique. A column that is not a key value. A column represents one kind of data in a table; in this c example, the data is the salaries of all the employees.
Column order is insignificant r a when storing data; specify the column order when the data is retrieved. A column containing the department number, which is also a foreign key. A foreign key is a column that defines how tables relate to each other. A foreign key refers to a primary key or a unique key in the same table or in another table.
A field can be found at the intersection of a row and a column. There can be only one value in it. A field may have no value in it. This is called a null value. Oracle Corporation r a ensures future compliance with evolving standards by actively involving key personnel in SQL standards committees. SQL Developer version 3. Some r aemployees may not be assigned to any department. If an employee changes departments within a job or changes jobs within a department, a new row is inserted into this table with the earlier job information of the employee.
They are A l managed by operations and governed by data integrity constraints. SQL is the c l e language that is used to communicate with the server to access, manipulate, and control data. The ora1 account contains the HR schema tables. Perform the practices slowly and precisely. You can experiment with saving and running l e command files. If you have any questions at any time, ask your instructor.
However, A l you may need to restrict the columns that are displayed. Selects a few or as many of the columns as required. The table contains eight rows, one for each department. The output displayed on the Results tabbed page appears as shown in the slide. Semicolons are required when you execute multiple SQL statements. You can override the column heading display with an alias. Column aliases are covered later in this lesson. All these are possible using arithmetic expressions.
If operators in an expression are of the same priority, evaluation is done from left to right. Note that multiplication is performed before addition. Note: Use parentheses to reinforce the standard order of precedence and to improve clarity. The second example in the slide displays the last name, salary, and annual compensation of I n employees.
Because of the parentheses, l e addition takes priority over multiplication. Null is not the same as zero or a blank space. Zero is a number and blank space is a character.
O ly Columns of any data type can contain nulls. Other employees are not entitled to earn t e r U s commissions. A null represents that fact. However, you I n can set it to something more relevant to you. To do so, select Preferences from the Tools menu. In the Preferences dialog box, expand the Database node. For example, if you l attempt to perform division by zero, you get an error.
However, if you divide a number by null, c the result is a null or unknown. This heading may not be descriptive and, therefore, may be c r a difficult to understand. You can change a column heading by using a column alias. By O ly default, alias headings appear in uppercase. The result of c r a the query is the same whether the AS keyword is used or not. Also, note that the SQL statement has the column aliases, name and comm, in lowercase, whereas the result of the O ly query displays the column headings in uppercase.
As mentioned in the preceding slide, column headings appear in uppercase by default. Note that the column heading in the output is exactly the same as the column alias. Columns on either side of the c r a operator are combined to make a single output column. O ly Note that the last name of the employee and the job code are combined to make a single output column. I n Note: You can also concatenate date expressions with other expressions or columns.
It is not a l column name or a column alias. It is printed for each row returned. The column l has the heading Employee Details. The spaces improve the readability of the output. If the literal itself l contains a single quotation mark, you can use the quote q operator and select your own c quotation mark delimiter.
By using the q operator, however, brackets [ ] a e are used as the quotation mark delimiters. The string between the brackets delimiters is n t e r U s interpreted as a literal character string. Note that the department numbers are repeated.
In the second example in the slide, the numbers in the table. The command A l displays the column names and the data types, and it shows you whether a column must c r a contain data that is, whether the column has a NOT NULL constraint. In the syntax, table name is the name of any existing table, view, or synonym that is accessible to the user.
It is abbreviated as DESC. Type displays the data type for a column. If c In the syntax: r a the condition is true, the row meeting the condition is returned. Number constants, however, need not be enclosed with single quotation marks. The range A l that you specify contains a lower limit and an upper limit. However, you must specify the lower limit first. The condition defined l using the IN operator is also known as the membership condition.
The IN operator can be used with any data type. Therefore, using the IN operator has no performance benefits and is used only for logical simplicity. The character pattern—matching operation is c Symbol Description r a referred to as a wildcard search.
Two symbols can be used to construct the search string. This option specifies what the escape character is. This causes the Oracle server to interpret the underscore literally.
A null value means that the value is unavailable, r a unassigned, unknown, or inapplicable. The example in the slide retrieves the last names O ly and managers of all employees who do not have a manager. A row is returned only if c r a the overall result of the condition is true. Further, character strings must be enclosed with quotation marks. The table in the slide lists the default order of precedence.
However, you can c calculate first. The example in the slide sorts the result by the most recently hired employee. The slide example sorts the data by annual salary. You can sort query results by more than one column.
If you want to reverse the order of a column, specify a e DESC after its name. I want to query different values. You can edit the WHERE clause to provide a different value each time you run the command, but there is also an easier way.
You can create reports that prompt users to supply their own values to restrict the range of a e data returned, by using substitution variables. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type's default precision. Valid in timestamp and interval formats, but not in DATE formats.
Number specified with J must be integers. MI Yes Minute MON Yes Abbreviated name of month. PM Yes Meridian indicator with or without periods. RR Yes Lets you store 20th century dates in the 21st century using only two digits. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SS Yes Second TS Yes Returns a value in the short time format. TZD Yes Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. TZH Yes Time zone hour. See TZM format element. TZM Yes Time zone minute. See TZH format element. TZR Yes Time zone region information.
The value must be one of the time zone region names supported in the database. W Week of month where week 1 starts on the first day of the month and ends on the seventh. X Yes Local radix character. Oracle Database converts strings to dates with some flexibility. Any non-alphanumeric character is allowed to match the punctuation characters in the format model. Datetime Format Elements and Globalization Support The functionality of some datetime format elements depends on the country and language in which you are using Oracle Database.
The datetime format element D returns the number of the day of the week The RR Datetime Format Element The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.
If you use the RR datetime format element instead, then the century of the return value varies according to the specified two-digit year and the last two digits of the current year. The following examples demonstrate the behavior of the RR datetime format element.
Note that the queries return the same values regardless of whether they are issued before or after the year The RR datetime format element lets you write SQL statements that will return the same values from years whose first two digits are different. Datetime Format Element Suffixes Table 3—16 lists suffixes that can be added to datetime format elements:.
You cannot use them to insert a date into the database. A modifier can appear in a format model more than once. In such a case, each subsequent occurrence toggles the effects of the modifier. Its effects are enabled for the portion of the model following its first occurrence, and then disabled for the portion following its second, and then reenabled for the portion following its third, and so on.
FM Fill mode. Oracle uses trailing blank characters and leading zeroes to fill format elements to a constant width. FX Format exact. Without FX, Oracle ignores extra blanks. Without FX, numbers in the character argument can omit leading zeros.
When FX is enabled, you can disable this check for leading zeros by using the FM modifier as well. If any portion of the character argument violates any of these conditions, then Oracle returns an error message.
The preceding statement also uses the FM modifier. Ides 03RD of April , Menu Tuesday's Special. Two consecutive single quotation marks can be used for the same purpose within a character literal in a format model.
Format of Return Values: Examples You can use a format model to specify the format for Oracle to use to return values from the database to you. Because of this format model, Oracle returns salaries with leading dollar signs, commas every three digits, and two decimal places. With this format model, Oracle returns the hire dates without blank padding as specified by fm , two digits for the day, and the century included in the year. See Also: "Format Model Modifiers" on page for a description of the fm format element.
Supplying the Correct Format Model: Examples When you insert or update a column value, the data type of the value that you specify must correspond to the column data type of the column.
You can use format models to specify the format of a value that you are converting from one data type to another data type required for a column. For example, a value that you insert into a DATE column must be a value of the DATE data type or a character string in the default date format Oracle implicitly converts character strings in the default date format to the DATE data type.
You must also use a format model to specify the format of the character string. Table 3—18 Cont. Table 3—19 lists and describes the attributes of the XMLFormat object. The function that implements this type follows the table. If the input to the function is a column name, then the default is the column name. Otherwise the default is ROW. If you specify schemaName, then Oracle uses the enclosing tag as the element name.
Nulls If a column in a row has no value, then the column is said to be null, or to contain null. Use a null when the actual value is not known or when a value would not be meaningful. Oracle Database treats a character value with a length of zero as null. However, do not use null to represent a numeric value of zero, because they are not equivalent. Note: Oracle Database currently treats a character value with a length of zero as null.
However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls. Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators except concatenation return null when given a null operand. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. Oracle also considers two nulls to be equal if they appear in compound keys.
That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal. Table 3—20 shows examples of various evaluations involving nulls in conditions. For the truth tables showing the results of logical conditions containing nulls, see Table 7—5 on page , Table 7—6 on page , and Table 7—7 on page For example, you can include a comment in a statement that describes the purpose of the statement within your application.
With the exception of hints, comments within SQL statements do not affect the statement execution. Refer to "Hints" on page on using this particular form of comment. A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement in two ways:. Proceed with the text of the comment. This text can span multiple lines. The opening and terminating characters need not be separated from the text by a space or a line break.
This text cannot extend to a new line. End the comment with a line break. Some of the tools used to enter SQL have additional restrictions. For more information, refer to the documentation for the tool you use as an interface to the database. A SQL statement can contain multiple comments of both styles.
The text of a comment can contain any printable characters in your database character set. You can also create a comment on a column, which is part of a table schema object.
Comments associated with schema and nonschema objects are stored in the data dictionary. The optimizer uses these hints to choose an execution plan for the statement, unless some condition exists that prevents the optimizer from doing so.
Hints were introduced in Oracle7, when users had little recourse if the optimizer generated suboptimal plans. Oracle strongly recommends that you use those tools rather than hints. The tools are far superior to hints, because when used on an ongoing basis, they provide fresh solutions as your data and database environment change.
Changing database conditions as well as query performance enhancements in subsequent releases can have significant impact on how hints in your code affect performance. The remainder of this section provides information on some commonly used hints. If you decide to use hints rather than the more advanced tuning tools, be aware that any short-term benefit resulting from the use of hints may not continue to result in improved performance over the long term.
The following syntax diagram shows hints contained in both styles of comments that Oracle supports within a statement block. The plus sign must follow immediately after the comment delimiter. No space is permitted. The space between the plus sign and the hint is optional.
If the comment contains multiple hints, then separate the hints by at least one space. However, the database does consider other correctly specified hints in the same comment. However, the database does consider other hints in the same comment. Specifying a Query Block in a Hint You can specify an optional query block name in many hints to specify the query block to which the hint applies.
This syntax lets you specify in the outer query a hint that applies to an inline view. The syntax of the query block argument is of the form queryblock, where queryblock is an identifier that specifies a query block in the query. The queryblock identifier can either be system-generated or user-specified. When you specify a hint in the query block itself to which the hint applies, you omit the queryblock syntax.
Specifying Global Hints Many hints can apply both to specific tables or indexes and more globally to tables within a view or to columns that are part of indexes. The syntactic elements tablespec and indexspec define these global hints.
You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint. However, do not include the schema name with the table name within the hint, even if the schema name appears in the statement. When tablespec is followed by indexspec in the specification of a hint, a comma separating the table name and index name is permitted but not required. Commas are also permitted, but not required, to separate multiple occurrences of indexspec.
To avoid this issue, Oracle recommends that you specify the object alias in the hint instead of using tablespec and indexspec.
Hints by Functional Category Table 3—21 lists the hints by functional category and contains cross-references to the syntax and semantics for each hint.
An alphabetical reference of the hints follows the table. Table 3—21 Cont. Alphabetical Listing of Hints This section provides syntax and semantics for all hints in alphabetical order. In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table.
This hint is useful for small lookup tables. The general philosophy explained in "Hints" on page does not apply for these three hints. If you specify an index, then the index must exist and be unique. If you specify a column list instead of an index, then a unique index whose columns match the specified columns in number and order must exist. This use of this hint results in error messages if specific rules are violated. This hint applies only to tables in an index cluster.
Oracle can replace literals in SQL statements with bind variables, when it is safe to do so. When you specify this hint, Oracle executes the SQL statement without any attempt to replace literals with bind variables.
This hint is useful if you are using distributed query optimization. If this query is executed without the hint, then rows from departments are sent to the local site, and the join is executed there.
With the hint, the rows from employees are sent to the remote site, and the query is executed there and the result set is returned to the local site. The higher the level, the more effort the compiler puts into dynamic sampling and the more broadly it is applied. Sampling defaults to cursor level unless you specify tablespec. The integer value is 0 to 10, indicating the degree of sampling.
If a cardinality statistic already exists for the table, then the optimizer uses it. Otherwise, the optimizer enables dynamic sampling to estimate the cardinality statistic. See Also: Oracle Database Performance Tuning Guide for information about dynamic sampling and the sampling levels that you can set. It instructs the optimizer that the table specified in tablespec should be considered as a fact table.
For integer, specify the number of rows to return. In this example each department contains many employees. The user wants the first 10 employees of department 20 to be displayed as quickly as possible. Such statements cannot be optimized for best response time, because Oracle Database must retrieve all rows accessed by the statement before returning the. If you specify this hint in any such statement, then the database optimizes for best throughput.
The employees table has alias e in the FROM clause, so the hint must refer to the table by its alias rather than by its name. Do not specify schema names in the hint even if they are specified in the FROM clause.
This hint applies only to tables in a hash cluster. When a unique key violation is encountered, a row-level rollback occurs and execution. If you specify this hint when inserting data with DML error logging enabled, then the unique key violation is not logged and does not cause statement termination.
Otherwise, the statement causes ORA If you specify no index, then the statement causes ORA If you specify more than one index, then the statement causes ORA If you specify both, then the statement causes ORA As with all hints, a syntax error in the hint causes it to be silently ignored.
The result will be that ORA will be caused, just as if no hint were used. The optimizer does not consider a full table scan or a scan of another index on the table.
If the INDEX hint specifies a list of available indexes, then the optimizer considers the cost of a scan on each index in the list and then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost. The database does not consider a full table scan or a scan on an index not listed in the hint. The database can also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost.
The optimizer does not consider a full table scan. If the statement uses an index range scan, then Oracle Database scans the index entries in ascending order of their indexed values. The default behavior for a range scan is to scan index entries in ascending order of their indexed values, or in descending order for a descending index.
This hint does not change the default order of the index, and therefore does not specify anything more than the INDEX hint. If you specify indexspec, then the optimizer tries to use some Boolean combination of the specified indexes. If the statement uses an index range scan and the index is ascending, then Oracle scans the index entries in descending order of their indexed values.
For a descending index, this hint effectively cancels out the descending order, resulting in a scan of the index entries in ascending order. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values. In a partitioned index, the results are in ascending order within each partition.
In a partitioned index, the results are in descending order within each partition. The LEADING hint is ignored if the tables specified cannot be joined first in the order specified because of dependencies in the join graph.
Complex merging can also be used to merge an IN subquery into the accessing statement if the subquery is uncorrelated.
Other spreadsheet optimizations, such as creating filters to selectively populate spreadsheet access structures and limited rule pruning, are still used by the optimizer. This hint reduces compilation time because spreadsheet analysis can be lengthy if the number of spreadsheet rules is more than several hundreds. This is the normal behavior of blocks in the buffer cache. See Also: Oracle Database Performance Tuning Guide for information on automatic caching of tables, depending on their size.
Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it. It instruct the optimizer that the queried table should not be considered as a fact table. Other indexes not specified are still considered. Other indexes not specified in the list are still considered. This hint lets you have more influence over the way in which the view is accessed. Instead, the full outer join is executed as a union of left outer join and anti-join.
See Also: "Note on Parallel Hints" on page for more information on the parallel hints. Doing so can improve performance if the subquery is relatively expensive or does not reduce the number of rows significantly. When this hint is specified, only hash join and sort-merge joins are considered for the specified tables.
However, in some cases tables can be joined only by using nested loops. In such cases, the optimizer ignores the hint for those tables. By prohibiting the rewriting of XPath expressions, this hint also prohibits the use of any XMLIndexes for the current query.
Parameter values that are strings are enclosed in single quotation marks. Numeric parameter values are specified without quotation marks. You might want to use the ORDERED hint to specify a join order if you know something that the optimizer does not know about the number of rows selected from each table. Such information lets you choose an inner and outer table better than the optimizer could. If you omit integer, then the database computes the degree of parallelism.
All the access paths that can use parallelism will use the specified or computed degree of parallelism. Object-level hints are supported for backward compatibility, and are superseded by statement-level hints. If any parallel restrictions are violated, then the hint is ignored. If the computed degree of parallelism is 1, then the statement runs serially. In the following example, the optimizer calculates the degree of parallelism. The statement always runs in parallel.
In the following example, the optimizer calculates the degree of parallelism, but that degree may be 1, in which case the statement will run serially. Oracle ignores parallel hints on temporary tables. Changes to each chunk are independently committed when there are no errors. The integer value indicates the degree of parallelism for the specified index. Specifying DEFAULT or no value signifies that the query coordinator should examine the settings of the initialization parameters to determine the default degree of parallelism.
You can control the distribution of rows for either joins or for load. Use the upper branch of the syntax by specifying a single distribution method. The values of the distribution methods and their semantics are described in Table 3—22 on page That is the query and load operation are combined into each query server.
All servers will load all partitions. This lack of distribution is useful to avoid the overhead of distributing rows where there is no skew. Skew can occur due to empty segments or to a predicate in the statement that filters out all rows evaluated by the query.
Note: Use this distribution with care. If you also use compression, then approximately 1. Use this distribution method when it is not possible or desirable to combine the query and load operations, when the number of partitions being loaded is greater than or equal to the number of load servers, and the input data will be evenly distributed across the partitions being loaded—that is, there is no skew.
Use this distribution method when the input data is highly skewed. Two or more servers can be loading the same partition, but no servers are loading all partitions. Use this distribution method when the input data is skewed and combining query and load operations is not possible due to memory constraints.
Control of Distribution for Joins You control the distribution method for joins by specifying two distribution methods, as shown in the lower branch of the syntax diagram, one distribution for the outer table and one distribution for the inner table.
Only six combinations table distributions are valid, as described in Table 3— When mapping is complete, each query server performs the join between a pair of resulting partitions. This distribution is recommended when the tables are comparable in size and the join operation is implemented by hash-join or sort merge join.
The inner table rows are randomly partitioned. This distribution is recommended when the outer table is very small compared with the inner table.
As a general rule, use this distribution when the inner table size multiplied by the number of query servers is greater than the outer table size. The outer table rows are randomly partitioned. This distribution is recommended when the inner table is very small compared with the outer table.
As a general rule, use this distribution when the inner table size multiplied by the number of query servers is less than the outer table size. The inner table must be partitioned on the join keys.
This distribution is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers.
Note: The optimizer ignores this hint if the inner table is not partitioned or not equijoined on the partitioning key. The outer table must be partitioned on the join keys. Note: The optimizer ignores this hint if the outer table is not partitioned or not equijoined on the partitioning key. Both tables must be equipartitioned on the join keys. Generally, subqueries that are not merged are executed as the last step in the execution plan.
If the subquery is relatively inexpensive and reduces the number of rows significantly, then evaluating the subquery earlier can improve performance.
This hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join. This name can then be used in a hint in the outer query or even in a hint in an inline view to affect query execution on the tables appearing in the named query block. If two or more query blocks have the same name, or if the same query block is hinted twice with different names, then the optimizer ignores all the names and the hints referencing that query block.
Query blocks that are not named using this hint have unique system-generated names. These names can be displayed in the plan table and can also be used in hints within the query block, or in query block hints.
For example:. The cached results reside in the result cache memory portion of the shared pool. A cached result is automatically invalidated whenever a database object used in its creation is successfully modified.
The query is eligible for result caching only if all functions entailed in the query—for example, built-in or user-defined functions or virtual columns—are deterministic. Oracle does not consider views outside of the list.
It is a function that takes number of columns as an argument and return the smallest value among them. It is an operator that is used to filter the result set to contain only the rows where either condition is TRUE.
It is a clause used to sort the result set by a particular column either numerically or alphabetically. It is a function that takes the column name and a integer as an argument, and rounds the values in a column to the number of decimal places specified by an integer. It is used to specify that the statement is a query which returns unique values in specified columns.
It is a clause used to filter the result set to include the rows which where the condition is TRUE. It is used to store the result of a particular query in a temporary table using an alias.
0コメント