Navigating in the Data Editor

  Related Topics

To view the next cell down in the same column:

•	Press RETURN or DOWN-ARROW.  If you highlighted a range of cells, RETURN will move you through only those cells, while DOWN- ARROW will deselect the range of cells and move you down the column containing the active cell.

To view the previous cell in the same column:

•	Hold down SHIFT and press RETURN, or press UP-ARROW.  If you highlighted a range of cells, SHIFT-RETURN will move you through only those cells, while UP-ARROW will deselect the range of cells and move you up the column containing the active cell.

To move to the next column in the same row:

•	Press TAB or RIGHT-ARROW.

To move to the previous column:

•	Hold down SHIFT and press TAB, or press LEFT-ARROW.


Inserting rows

  Related Topics

To insert one or more new row(s) into a table:

1	Include  the table in a query.

2	Click one or more row numbers in your table.

3	Run the query..

4	In the Results window, choose Edit [image] Insert Row.  Query Builder inserts the same number of rows into the table as you have selected.  A plus sign (+) appears on either side of the row number for each row you insert.

5	Use the Data Editor to enter data in cells in the new row.

6	Commit your changes.

Note:  To insert rows you must provide values for all required columns in that table, whether or not those columns are displayed in the Results window.


Updating a row

  Related Topics

To update a row in a table:

1	Include the table in your query..

2	Display the Data Editor.

3	In the editing field in the Results window, enter changes to your data.  Note that the datatype icon, which indicates the datatype of the active cell, appears to the left of the editing field.

4	When the asterisk (*) appears on either side of the updated cell's row number, click Accept.

Note:  Query Builder will store each update in a buffer, so you can go on to edit another cell.


Inserting data from a file

  Related Topics

To insert data from a file into a table:

1	Open the table.

2	Display the Data Editor.

3	Choose Edit [image] Insert Rows From File.

4	Under Available Formats, click on the radio button that corresponds to the file format.

5	In the Insert field, type the name of the file.

6	Click Insert.

Note:  If the number of columns in the file is the same as the number of columns in the table, the inserted data is inserted at the end of the table.  When you import data, you should ensure that the external file contains the same number of columns as your Query Builder table, as you will receive an error if the imported data contains a more columns than your table.


Deleting a row

  Related Topics

To delete a row from your table:

1	Open the table.

2	Click one or more row numbers in your table.

3	Choose Edit [image] Remove.  A minus sign (-) displays on either side of the row number for each row marked for deletion.

4	Commit your changes.


Committing a change to a table

  Related Topics

To commit or save changes to your table manually:

•	Choose Data [image] Commit to save any changes to the table after inserting, updating, or deleting rows.

To commit or save changes to your table automatically:

1	Choose Edit [image] Preferences.

2	Click on Data Editor.

3	Check the Auto-Commit check box.

4	Click OK.


Defining a column

  Related Topics

To define a new column in a table:

1	Click on the table containing the column.  If there is no table displayed in the Query window, include a table.

2	Click [image] in the Icon bar.

3	Click inside the Defined Columns box.

4	Type the name for your new column.

5	Click inside the Defined as box.

6	Type the formula or expression that defines your column.

7	Click OK.


Creating an expression

  Related Topics

To create an expression using the Define Column dialog box:

•	In the Defined As box of the Define Column dialog box, type the expression.

To create an expression using the Icon Bar operators:

•	Click on the column names and Icon Bar operators displayed in the Datasource panel .

To create an expression using the Paste Column button:

1	Click [image] in the Icon bar.

2	Click on the existing column upon which the new one will be based.

3	Paste the column into your expression.

4	Click inside the Defined as field to the right of the pasted name to display the text cursor.

5	Type the calculation or other expression that defines the new column.

6	Click Paste Column.

7	In the Paste Column dialog box, type the next expression.


Hiding a defined column

  Related Topics

To hide a defined column in a table:

1	In the Results window, click on the table containing the defined column.

2	Choose Results [image] Display.

3	In the Display dialog box, double-click the column you want to hide.

To hide multiple columns:

1	While the table you wish to edit is displayed in the Results window, click on the table containing the defined column.

2	Choose Results [image] Display.

3	In the Display dialog box, hold down the SHIFT key and click the columns you want to hide.

4	Click Remove to remove the columns, as well as any associated data, breaks, and summary calculations

5	Click Done.

Alternatively, you can remove a column from the Results window by selecting the column and then selecting Data [image] Exclude from Query.


Deleting a defined column

  Related Topics

To delete a defined column from a table:

1	In the Results window, click on the table containing the defined column.

2	Click [image] in the Icon bar.

3	In the Define Column dialog box, click the name of the column to be deleted from the list of Defined Columns.

4	Click Delete.


Creating a simple relationship

  Related Topics

To create a relationship using the mouse:

1	Open the table you want to relate.

2	In the primary table, select the column that you want to relate (the foreign key).

3	While holding down the mouse button, drag the cursor to the related column in the second table (the primary key) to draw a relationship line.

4	Once the target column is highlighted, release the mouse button to anchor the relationship line.  By default, the relationship is inactive (the line appears gray).

To create a relationship using the Set Relationship dialog box:

1	In the Set Relationship dialog box, type the origin (A->) and target (->B) column names.  Note Query Builder draws a relationship line between the two columns in the Datasource panel.

2	Click Close.

To create a relationship using a shortcut:

1	In the Set Relationship dialog box, click inside field A.

2	In the Datasource panel, select the column.  Query Builder automatically places the table and column name in field A.

3	Repeat the procedure for field B.


You must type the complete table and column names (separate the table name from its column name with a period).  A-> represents the table's foreign key(s), and ->B represents the table's primary key(s).  Separate additional primary and foreign keys with commas.


Deleting a relationship

  Related Topics

To delete a relationship:  Note

1	Click on the relationship line that connects the two table columns.

2	When both the column names and the relationship line are highlighted, press the DELETE key.


Using a simple relationship

  Related Topics

To use a simple relationship:

1	In the Datasource panel, select the column that you want included in the related table.

2	In the Set Relationship dialog box , Query Builder displays a list of tables that have relationships defined with the selected table.  Notice that as you include the new table, the relationship line activates automatically.

3	Select which columns you want to appear in your query results.

4	Run the query.


Finding and including a related table

  Related Topics

To find and include a related table:

1	In the Set Relationship dialog box, Query Builder displays a list of tables that have relationships defined with the selected table.

2	Click the table name and click Include.

3	Click OK.

Note: The Show radio buttons enable you to filter by table type what is displayed in the dialog box.  If all the boxes are active, the list box shows all available tables in a particular account.  If you uncheck any of the Show check boxes, the list box hides all table(s) of that type.


Creating a self-relationship

  Related Topics

To create a self-relationship:

1	In the Set Relationship dialog box, Query Builder displays a list of tables that have relationships defined with the selected table.

2	Click the table name.

3	Click Include.

4	Click OK.

5	Repeat the above procedure for the same table name.

6	Query Builder automatically names the copy of the related table with an alias.  Rename the table to match the name of the original.

Note:  If the self-relationship has already been identified in the underlying data dictionary, the two related columns in the table and the alias are connected with a relationship line.  If the self-relationship has not been identified in the data dictionary, you can create a self-relationship by drawing a temporary relationship line, to connect the two counterpart columns in the table and the alias.


Creating a composite relationship

  Related Topics

To create a composite relationship:

1	In the Set Relationship dialog box, enter the origin (A->) and target (->B) column names.  Note

2	Click OK.  Query Builder draws a composite relationship line between the columns and tables you have specified.

Note: You can use as many composite relationships as are appropriate for your query, and you can include columns in composite relationships regardless of whether they are used in other relationships.

You can also create a composite relationship using your mouse, as you would a simple relationship.


Creating an unmatched relationship

  Related Topics

To create an unmatched, or outer, relationship:

1	Click on the relationship line that connects your tables.

2	In the Set Relationship dialog box, choose one of the display options to either retrieve all records included in a simple relationship or to retrieve only the rows where there is a corresponding value in both tables.

Note: A row that returns a value of 0 (zero) is not the same as an unmatched row.  Suppress Mismatched only works on rows that return null values, not 0 values.  Use this feature if you want to display data where one table has no values entered, such as orders with no line items, customers who have not made orders, sales representatives who do not have customers, and so forth.


Removing an unmatched relationship

  Related Topics

To remove an unmatched relationship:

1	Click on the relationship line that connects your target tables.

2	In the Set Relationship dialog box, click the Suppress Mismatches radio button.

3	Click OK.


Creating a dynamic link

  Related Topics

To create a dynamic link between data in the Results window and data in another Windows application:

1	In the Results window, select the cells you want to link.

2	Click [image] in the Icon bar.

3	In the other application, highlight the target area for the data and choose Edit [image] Paste Link.  The data you copied to the clipboard appears in this application.  This data is updated every time you re-execute your query in Query Builder.

In some applications you need only click in the document and then choose Edit [image] Paste Link.  In other applications, you may need to highlight a range of cells or tables to copy all of the data.

Note: Because Query Builder is designed to query data from a database, it is a DDE server, not a DDE client.  You cannot set up DDE links with another application to change the data displayed in the Results window.


Locating available tables to use in your query

  Related Topics

To view a list of available tables you can use in your query:

1	Click [image] in the Icon bar.

2	In the Select Data Tables dialog box, view the available tables.

Note:  You can also click [image] in the Icon Bar.


Renaming a table

  Related Topics

To rename a table:   Note

1	In the Query window, click [image].

2	Click on the table whose name you want to change from the Available Tables list.

3	Click inside the New Name field.

4	Delete the unwanted information.

5	Enter the new name for your table. The new name can be up to 30 characters long.

6	Click Define.

7	Click OK.


Repositioning columns in a table

  Related Topics

To reposition columns in a table: Note

1	In the Results window, click the column you want to move.

2	Press the mouse button and drag the column to its new location.

3	Release the mouse button to anchor the column in its new location.


Changing the column order does not make a corresponding change in the underlying database table.


Finding tables in other accounts and databases

Before you access tables in other accounts and databases, you may want to look up your available related tables.

To find tables in other accounts:

1	In the Select Data Tables dialog box, choose the Databases option from the drop-down box.

2	Use the drop-down box to navigate to other accounts in the current database or to accounts in another database, if you have access to another database.

3	To access other accounts in the current database, click on the current database name.  The list box will display a list of accounts that contain tables you can access.

4	Click the name of the target account in the list.

5	Double-click the target account to display a list of available tables in the account.

Note: <ALL> shows every table in the database to which you have access.  PUBLIC shows all public synonyms.

To find tables in other databases:

1	In the Select Data Tables dialog box, choose the Databases option from the drop-down box.  Query Builder displays the available databases, with the currently active database at the top of the list.

2	Click the name of the target database.

3	Double-click the target database name.  Query Builder connects to the database, and displays a list of accounts containing tables you can access.


Reverting changes to your database

  Related Topics

To undo changes to your database:

1	In the Select Data Tables dialog box, choose the table you want to change.

2	Click on the cell or row containing the changes you want to undo.

3	Choose Edit [image] Revert.


Building or dropping the Query Builder sample tables

To build the Query Builder sample tables:  Note

1	Create a new account with the user name QB and the password QB.  The new account should have the ability to connect to Oracle Server and create tables.

2	Run the brwbld60.sql script to load the system tables with data.  Example

To drop the Query Builder sample tables:

•	Run the brwdrp60.sql script.  Example


On Windows, the .SQL scripts are installed in C:\ORAWIN95\BROWSE60; on UNIX, $ORACLE_HOME/browse60/admin/sql.


On Windows, enter:

sqlplus QB/QB @c:\orawin95\browse60\demo\brwbld60.sql

On UNIX, enter:

sqlplus QB/QB @$ORACLE_HOME/browse60/demo/brwbld60.sql

If you are a remote user, include your network connect string as follows:

sqlplus QB/QB@network_connect_string @$ORACLE_HOME/browse60/demo/brwbld60.sql


On Windows, enter:

sqlplus QB/QB @c:\orawin\browse60\demo\brwdrp60.sql

On UNIX, enter:

sqlplus QB/QB @$ORACLE_HOME/browser/demo/brwdrp60.sql

If you are a remote user, include your network connect string as follows:

sqlplus QB/QB@network_connect_string  @$ORACLE_HOME/browser/demo/brwdrp60.sql


Launching Query Builder

  Related Topics

To launch Query Builder:

1	Double-click the Query Builder icon.

2	Connect to the database.

3	In the Start Query Builder dialog box:

•	To create a new query, select Create a new query

•	To open a query stored in the database, select Open Query from Database

•	To open a query store in a file, select Open Query from File System

4	Click OK.


This task is performed by the database administrator.


To display the Data Editor, choose Data [image] Show Data Editor.


Make sure you are connected to the database.  To display the Set Relationship dialog box, choose Data [image] Set Table Relationship or click [image] in the Icon bar.


Make sure you are connected to the database.  To display the Select Data Tables dialog box, choose Data [image] Select Data Tables or click [image] in the Icon bar.


To connect to a database, you must obtain the following information from your database administrator:  your user name, your password, and the database string of the database to which you need to connect.


To display the Grant Access dialog box, choose File [image] Grant/Revoke.


To display the Sort dialog box, click [image] in the Icon bar.


To access the Display dialog box, choose Results [image] Display while the Results window is active.


The Query window is split into two panels:  the Datasource panel and the Conditions panel.  The Datasource panel displays the tables (such as views and snapshots) used in your current query.  You can use the Conditions panel to enter expressions and conditions for your query.  Use your mouse to resize the panels.


To display the Format dialog box, choose Results [image] Format or double-click on the column you want to format.


To activate the Condition field, click on the Conditions panel in the Query window.  Click in the Condition field.  Note that in order to deactivate the Conditions field, you must click either [image] or [image] in the Icon bar.


When you execute a query, the data is retrieved in 64K blocks.  When As Needed is selected, the additional blocks are fetched as needed when you scroll through your data.


At the top of the Preferences dialog box, you must select the All Documents radio button to use this option.


If the Auto Commit check box is selected after a few rows are edited but before Commit is selected from the Data menu, every modified row is committed immediately.


At the top of the Preferences dialog box, you must select the This Document Only radio button to use this option.


You can toggle between centimeters and inches in the Preferences dialog box.  Click the Results window icon and choose the desired Display Units.


Break columns always display on the left side of the Results window.


Executable names may vary from platform to platform.  For example, the executable name on Windows 95 and Windows NT is obe60.exe.


You can access help for command line statements for both interactive and batch operations.  When you enter HELP=YES anywhere in the command line, and press RETURN, a list of command line options is displayed.  The help file will also appear automatically if you receive errors when running a command.


The EXECUTE command is optional if you use batch destination commands, because if you use the command line to send query data to a printer or to export, Query Builder automatically runs the query.


To connect to the Sample database with user name Scott and password TIGER, you can either:

Enter SCOTT in the Username field and use the connect string:

TIGER@SAMPLE

Or, if you do not enter the user name in the Username field, enter in the Connect field:

SCOTT/TIGER@SAMPLE


The expression in the Defined As field typically consists of a function or operation and the database column(s) to be operated on.  If the column name occurs in more than one table, remember to reference the table name as well, and separate it from the column name with a period.  If the column name is unique, you can specify just the column name.


When you select a table in your query and click [image] in the Icon bar, Query Builder shows you a list of tables that have relationships defined with the selected table.


In Query Builder, if a column is shown in bold typeface in the table, it is a primary key.  If it appears in italics, it is a foreign key. If it appears in bold and italics in the table, it is both a primary key of this table and a foreign key referencing another table.  These are guidelines that can often help you identify columns that have counterparts in another table.  Depending upon the way a database is set up, key columns might also appear in normal typeface, if the relationships have not been defined in the underlying data dictionary.


In general Query Builder allows you to relate any columns that can be interpreted using the implicit conversion feature of the Oracle database.  For technical information on implicit conversions, refer to the Oracle8 Server SQL Language Reference Manual.


You can delete any user-defined relationships created in Query Builder.  When you select a permanent relationship, the Edit [image] Clear and Edit [image] Cut options are inactive, because you do not have privileges to delete that relationship.


To run a query means to submit the query to the database.  Once you run a query, Query Builder locates, retrieves, and places the selected data in the Results window.


Choose File [image] Save SQL to access your computer's operating system's Save dialog box.  Here, you can select a file name and destination for your SQL statement.  If you do not enter a file extension, Query Builder automatically appends the .sql file extension.  To preview the SQL statement before exporting, click [image] in the Icon bar before saving the file.


To access the Define Column dialog box, click [image] in the Icon bar.


To view comments on the selected table or column, click [image] in the Icon bar.


Renaming a table does not result in any corresponding change in its underlying name in the database.  The new name is valid only for the active query; it remains in the query if you save it.


To access the Grant Queries dialog box, choose File [image] Grant/Revoke.


The Break Columns list is in the Break dialog box.  Click [image] to access this dialog box.


To display the Show SQL dialog box, click [image] in the Icon bar.


Boolean variables are specified with values NOT NULL and NULL.


Query Builder creates the sequence BROWSER_SEQ to generate unique QUERY_ID values for queries.


Query Builder accesses all the administration views and sequences through public synonyms of the same name.  Therefore, any account with DBA privileges can own the Query Builder database objects.


When you execute a query on unrelated tables, Query Builder warns you that you may be generating a Cartesian product, a query that returns the product of all rows from the combined tables.


To access the Totals dialog box, click [image] to access the Totals tools while you are in the Results window.  Then, click [image].


To access the Page Setup dialog box, click [image] in the Icon bar while you are in the Results window.


To change your workspace preferences, choose Edit [image] Preferences and click on the name of the window you wish you change.


When you select a column object from a table, Query Builder automatically uses the table alias in your query.  Likewise, if you include a column object in the Condition field and accept the condition, Query Builder automatically inserts the appropriate table aliases into your condition.


You can select (or deselect) a column in either of the following ways:

•	Check the check box next to the column.

OR

•	Double-click on the column name.


The End User Layer for Query Builder has now been disabled.


Including tables in your query

  Related Topics

To include a table in your query:

1	In the Query window, click [image].

2	In the Select Data Tables dialog box, click on a database object name.  Tip

3	Click Include.

4	Click OK.


Building a new query

  Related Topics

To build a new query:

1	While you are in the Query window, click [image] in the Icon bar.

2	Include a table in your query.

3	Add data to your query.


Adding a column to your query

  Related Topics

To add a column:

1	Include an available table in your query.

2	In the displayed table, select the column you want to add.  Tip

To add multiple columns:

1	Include an available table in your query.

2	Hold down the SHIFT key and select each column. Tip

3	Choose Data [image] Include in Query to include all the selected columns.

To add all columns from a table:

•	Check the check box next to the desired table title. Tip

Note: Any time you add a new column to a query, the column header is displayed in the Results window..


Adding a column to your query using the Display dialog box

  Related Topics

To add a column using the Display dialog box:

1	Choose Results [image] Display to display the Display dialog box.

2	In the Available Columns list, double-click on a column.  Tip

3	Click Close.  In the Query window, a black check mark displays to the left of each added column.  A grey check mark displays to left of those columns included by reference only (i.e., those referenced in a defined column).

Note: Any time you add a new column to a query, the column is displayed in the Results window..


Removing a column from your query

  Related Topics

To remove a column from your query:

•	In the Datasource panel, uncheck the checkbox next to the column name.  The column heading and data are removed from the Results window..

If you have already run your query, first remove the column from your query and then do the following:

1	In the Displayed Columns list in the Display dialog box, click the target column.

2	Click Remove.

3	Click Done.  The column heading is removed from the Results window, along with any data that was in the column.

Note:  If you accidentally remove a column, you can re-add the column.


Counting rows before running the query

  Related Topics

To count the rows in your query before running:

•	Choose Query [image] Count Rows to display the number of rows that will be retrieved into your query.

OR

•	Click [image] in the Icon Bar.


Retrieving all rows into your query

  Related Topics

To retrieve all rows into your query:

•	Choose Query [image] Retrieve All Rows.

Note:  Before you choose to retrieve all the rows into your query, you may want to count the number of rows.


Running a query using the menu

  Related Topics

To run a query using the menu:  Note

•	Choose Query [image] Execute Query.

Note:  You can also click [image] in the Icon bar.


Running a query using the Display dialog box

  Related Topics

To run a query from the Display dialog box:  Note

•	In the Display dialog box, click Execute.  The retrieved data is displayed in the Results window.


Automatically running a query

  Related Topics

To select Automatic Query Execution:

1	Choose Edit [image] Preferences.

2	In the Preferences dialog box, click on the Query icon.

3	Click the Automatic radio button.

4	Click OK.  Now, every time you modify the query, it will automatically execute and display the results in the Results window.


Adding comments to your query

  Related Topics

To add comments to your query:

1	Choose Edit [image] Preferences.

2	In the Preferences dialog box, click on the General icon.

3	Click the This Document Only radio button.

4	Type your comment in the Comment box.

5	Click OK.


Saving your query as a SQL statement

  Related Topics

To save your query as a SQL statement:

1	Choose File [image] Save SQL.

2	In the Save As dialog box, type a file name and destination for your SQL statement.  If you do not type a file extension, the .sql file extension is automatically appended.


Opening a query

  Related Topics

To open a query from the connected database:

1	Click [image] in the Icon bar.

2	In the Open from dialog box, click on Database.

3	In the Open dialog box, click on the name of the query.

4	Click Open.

To open a query from the file system:

1	Click [image] in the Icon bar.

2	In the Open from dialog box, click on File System.

3	In your operating system's Open dialog box, click on the name of the query.

4	Click Open.


Deleting a query

  Related Topics

To delete a query from the database:

1	Choose File [image] Delete to display the Delete dialog box.

2	From the Saved Queries list, click on the name of the query you want to delete.

3	Click Delete.

To delete multiple queries from the database:

1	Choose File [image] Delete.

2	In the Delete dialog box, hold down the SHIFT key and click on the names of the queries you want to delete from the Saved Queries list.

3	Click Delete.


Granting or revoking access to your query
Granting or revoking access to a query

  Related Topics

To grant access to a query:

1	Open your query.

2	Choose File [image] Grant/Revoke.

3	In the Grant Queries dialog box, click on the name of the query.

4	From the drop-down box, choose Grant to Users or Grant to Roles.

5	Click on the appropriate users or roles from the Grant To list box. You can select multiple users by holding down the SHIFT or CONTROL keys as you click on each item.  Each user becomes highlighted when you grant access to it.

6	To grant access, click Grant.  To revoke access, click Revoke

To revoke access to a query:

1	In the Queries list box in the Grant Queries dialog box, click on the name of the query.

2	In the Users list box, click on the user from whom you want to revoke access.

3	Click Revoke.  Note that the user's name is greyed.


Changing a column position

  Related Topics

To change the column position using the Display dialog box:

1	Choose Results [image] Display to access the Display dialog box.

2	In the Displayed Columns list, click on the column you want to move.

3	Hold down the mouse button and drag it up or down to reposition it in the corresponding left-to-right position in the Results window.

4	Click Close.


Adjusting column width

  Related Topics

To adjust a single column using the mouse:

1	In the Results window, click on the right boundary line of the target column title

2	Drag the boundary line to its new position.

3	Release the mouse button to set the new column width.

To adjust a single column using the menu:

1	Choose Format [image] Results to display the Format dialog box.

2	Click on the column you want to adjust in the For Column list.

3	In the Column Width field, specify the measurements of your column.  Note that you can specify centimeters or inches by choosing Edit [image] Preferences while you are in the Results window.

4	Click OK.

To adjust multiple columns:

1	In the Results window, click on the first column.

2	Drag your mouse to the right or left until all the columns you want to adjust are highlighted.

3	Click on the right boundary of any of the selected columns.

4	Drag the boundary line to its new position.

5	Release the mouse button to adjust all selected columns.

To automatically resize columns:

1	In the Results window, click on the column you want to automatically resize.

2	Choose Layout [image] Autoresize Column.


Sorting the query results

  Related Topics

To sort query results:

1	Click [image] in the Icon bar to display the Sort dialog box.

2	In the Available Columns list, click on the column you want to sort.  The Available Columns list box contains names for every column in the Query window, not just those selected for your query.

3	Click >>Copy>>.  The column is added to the Sorted Columns list and an up arrow is placed in the list box to indicate the default sort (ascending order).

4	In the Sorted Columns list, click on the column you want to sort.

5	Click the Descending radio button to sort the column data in descending order.

6	Click OK.  The data in the Results window sorted according to the column you selected.  If all the data was not available locally for the resort, you are prompted to rerun the query.


Removing a sort from your query results

  Related Topics

To remove a sort:

1	Click [image] in the Icon bar to display the Sort dialog box.

2	In the Sorted Columns list of the Sort dialog box, double-click on the column from which you want to remove the sort.

3	Click OK.

4	Run the query.


Hiding duplicate records in your query

  Related Topics

To hide duplicate records:

1	Choose Edit [image] Preferences.

2	In the Preferences dialog box, click on the Query icon.

3	Click on the Distinct Rows option.

4	Click OK.


Performing a break

  Related Topics

To perform a break:

1	In the Icon Bar, click [image] to display the Break dialog box.

2	In the Displayed Columns list, double-click the column into which you want to insert the break.  The column appears in the Break Columns list box.  By default, the values in break columns are sorted in ascending order.  You can change the sort order by highlighting a column from the Break Columns list and clicking the Ascending or Descending radio buttons.

3	Click OK.

•	If all rows are retrieved into memory, then the sort is performed locally.

•	If the warning "Data not available for local sort. Re-execute?" displays, click OK to rerun the query.

Note:  The break column is automatically moved to the leftmost column in the Results window and displays the column heading in italics to indicate that it is a break column.


Changing break priority

  Related Topics

To change the break priority of your column:

1	In the Icon bar, click [image] to display the Break dialog box.

2	In the Break Columns list, click on the column whose priority you want to change.

3	Drag and drop the column to the desired location in the list.

4	Click OK.

Note:  You can also change the break priority of a column by reordering them in the Results window.


Removing a break

  Related Topics

To remove a break:

1	In the Icon bar, click [image] to display the Break dialog box.

2	In the Break Columns list of the Break dialog box, double-click on the column from which you want to remove the break.

3	Click OK.  If the data with which you are working is not on a local database, you are prompted to rerun the query.


Aligning column titles

  Related Topics

To align the column titles:

1	In the Results window, click on the column you want to adjust.

2	Choose Results [image] Format.

3	Under Alignment option in the Format dialog box, choose the desired alignment from the drop-down list.

4	Click OK.

Note:  If you click on Follow Data from the Alignment drop-down list, the title is aligned in the same format as the column data.


Changing the column title

  Related Topics

To change the column title:

1	In the Results window, double-click on the column name.

2	In the Print Title box of the Format dialog box, type the new column title.

3	Click OK.

Note:  Column titles cannot exceed 14 characters.


Formatting your query results

  Related Topics

To format your query results:

1	In the Format dialog box, make sure the column you want to format is highlighted in the For Column box.

2	Choose the styles you want to apply to the selected column.

3	Click OK.


Viewing gridlines in the Results window

  Related Topics

To view the gridlines in the Results window:

1	Choose Edit [image] Preferences.

2	In the Preferences dialog box, click on the Results window  icon.

3	Under Gridlines, click on Show Horizontal and/or Show Vertical..

4	Click OK.


Creating panes in the Results window

  Related Topics

To create panes in the Results window:

1	In the Results window, click on the split bar above the vertical scroll bar, or on the split bar to the left of the horizontal scroll bar.

2	While holding down the mouse button, drag the mouse to size the pane.  You can use this technique to create up to four panes in either direction.

To resize a pane in the Results window:

1	In the Results window, click on the split bar.

2	Drag the mouse to resize the pane.

To remove a pane in the Results window:

•	In the Results window, drag the split bar back to its original position.


Creating a column summary

  Related Topics

To create a column summary:

1	Click [image] in the Icon bar to view the Totals tools.

2	Click [image] in the Icon bar.

3	In the Select Column list box of the Totals dialog box, click on the column you want to summarize.

4	Check the check boxes of the operations you want to perform on the selected column.  You can use more than one summary calculation per column.  Each summary is displayed separately.

5	Click OK.


Printing your query results

  Related Topics

To print your query results:

1	Click [image] in the Icon bar.

2	If you have not yet run your query, a warning displays.

3	In the Warning box, click OK.

4	After the query runs, click OK.


Printing a range of cells

  Related Topics

To print a range of cells:

1	Run your query.

2	In the Results window, click [image].

3	In the Page Setup (Margins/Headers/Footers) dialog box, check the Print Selection Only check box.

4	Click OK.

5	In the Page Setup dialog box, click OK.

In the Results window, highlight the range of cells you want to print.


Exporting query results

  Related Topics

To export your query results:

1	Choose File [image] Export Data.

2	Click on the format to which you want to export the data.

3	Type the file name to which you want to export the data.

4	When you export you can choose to:

•	Export only the selected cells.

•	Include the column headings.

5	Click OK.


Saving a subquery in a parent query

To save a subquery in a parent query:

1	Create a query to retrieve the initial value on which the parent query depends.

2	Save the query (which is the subquery) in the database.

3	Close the query document.

4	Create a parent query in a new document.

5	In the Select Data Tables dialog box, click on your subquery to include it in the parent query.

6	Specify the subquery in the Condition field to retrieve the initial value to defined in Step 1.

7	Save the parent query to the same database to which you saved the subquery.

Note:  You can include as many conditions and subqueries in the parent query as you need.


Creating a correlated subquery

  Related Topics

To create a correlated subquery:

1	Create a query to retrieve employees' names and department IDs from the Employee and Department tables.

2	Insert the following condition:

SALARY>AVG(SALARY,DEPARTMENT_ID)

3	Run the query.

Note:  The subquery is based on the same tables as the parent query, making it a correlated subquery.


Recovering a query

  Related Topics

To recover your query due database object modification:

1	Review the query to determine if any of the columns, conditions, or defined columns were altered by the database modification.

2	If none were affected, save the query to ensure that the query and query view comply with the most recent state of the modified database table, view, or synonym.

3	Remove or change any affected columns, conditions, or defined columns to recover affected parts of the query.

4	Save the query again to ensure that the query and query view comply with the most recent state of the database.


Building a condition

  Related Topics

To build a condition using the Icon Bar:

1	Run your query.

2	Activate the Condition field.

3	In the Datasource panel, check the column check box to which you want to apply the condition.  Tip

4	Click the appropriate operator(s) in the Icon Bar.

5	In the Results window, select any value from a cell.

6	Click [image] in the Icon bar to close and validate the condition.

To build a condition using the menu:

1	Activate the Condition field.

2	Choose Edit [image] Paste Column.

3	In the Paste Column dialog box, click on the column to which you want to apply the condition.

4	Click OK.

5	Type the operators into the Condition field. Tip

6	Click [image] in the Icon bar to close and validate the condition.

To build a condition by typing it directly:

1	Type the condition syntax directly into the Condition field. Tip

2	Click [image] in the Icon bar to close and validate the condition.


Changing the top-level operator

  Related Topics

To change the top-level operator:

1	In the Condition field, select the operator.

2	In the Icon Bar, click the new operator to which you want to change the top-level operator.

3	Run the query.


Editing a condition

  Related Topics

To edit one or more conditions:

1	In the Conditions panel, click inside the condition you want to edit.

2	Modify the condition.


Deactivating and reactivating a condition
Deactivating and re-activating a condition

  Related Topics

To deactivate a condition:

1	In the Condition field, click the box to the left of the condition text.  Hold down the SHIFT key to deactivate multiple conditions.

2	Choose Data [image] Exclude from Query.

To re-activate a condition:

1	In the Condition field, click the box to the left of the deactivated condition text.  Hold down the SHIFT key to deactivate multiple conditions.

2	Choose Data [image] Include in Query.

Note:  You can also toggle activating and deactivating the condition by double-clicking the box next to the condition text.


Deleting a condition

  Related Topics

To delete a condition:

1	In the Conditions panel, click inside the condition you want to delete.

2	Choose Edit [image] Clear to delete the condition.

OR

1	In the Conditions panel, click inside the condition you want to delete.

2	Click [image] in the Icon bar.


Building a condition with columns not in the query

  Related Topics

To build a condition with columns not in the query:

1	Activate the Condition field.

2	Click [image] in the Icon bar.

3	In the Paste Column dialog box, click on the column you want to use in the condition.  Notice that the list contains all columns in the Dataource panel, not just those you have selected for your query.  Tip

4	Click Paste.

5	Finish building your condition.

6	Run the query.

Note:  If a column used in a condition is not displayed in the Results window, a grey check mark appears to the left of the column name in the Datasource panel.


Entering arguments for a function

  Related Topics

To enter arguments for a function:

1	Activate the Condition field.

2	Click [image] in the Icon bar.

3	In the Paste Function dialog box, check the Paste Arguments check box.

4	Replace the description or datatype name of the arguments with the argument you want to apply to the function.


Creating a function subquery

  Related Topics

To create a function subquery:

1	In the Condition field, enter a condition for the column you want to query.

2	Click [image] in the Icon bar.

3	In the Paste Function dialog box, click on the function you want to use.

4	Click OK.

5	Run the query.  First the function subquery will be run, and then the parent query.


Creating multiple conditions

  Related Topics

To create multiple conditions:

1	In the Condition field, type the first condition.

2	Click [image] in the Icon bar.

3	Type the next condition.

4	Click [image].  The conditions are automatically separated and enclosed in an OR operator in the Conditions panel.


Creating a nested condition

  Related Topics

To create a nested condition:

1	In the Condition field, type the first expression using a comparison operator, such as:  TOTAL<1000.

2	Type the next expression, using another comparison operator, such as: EMP.LAST_NAME=SMITH

3	Type a third expression, using the same comparison operator (=), such as:  EMP.LAST_NAME=JONES.

4	Select the second and third expressions.

5	Click [image] in the Icon Bar.

6	Click [image].


Creating a negating condition

  Related Topics

To create a negating condition:

1	Build a condition.

2	Activate the Condition field for the condition in which you want to type the negating condition.

3	Click on the logical operator for the condition.

4	Click [image] in the Icon bar.

5	Press the RETURN key to accept the condition.


Reversing a negating condition

  Related Topics

To reverse a negating condition:

1	Activate the Condition field for the negating condition you want to reverse.

2	Click [image] in the Icon bar.  The comparison operator is restored to the original.

3	Press the RETURN key to accept the condition.


Using the concatenation operator

  Related Topics

To use the concatenation operator:

1	In the Icon Bar, click [image] to display the Define Column dialog box.

2	In the Defined Columns field, type the name for the new concatenated column.

3	In the Defined as field, type the first column title, the concatenation operator, and then the second column title.  Example

4	Click Define.

5	Click OK.


FIRST_NAME || ' ' || LAST_NAME


Open dialog box
Open from dialog box

  Related Topics

Description  The Open dialog box enables you to open an existing query.

This dialog box contains:

Open From	Specifies where the query is located:  in the connected database or in your file system.


Open (from database) dialog box

  Related Topics

Description  The Open (from database) dialog box enables you to open an existing query from the connected database.

This dialog box contains:

List box	Lists the existing queries in the connected database.
Open	Opens the selected query.


Delete dialog box

  Related Topics

Description  The Delete dialog box enables you to delete a query from the database to which you are currently connected.

This dialog box contains:

List box	Lists the queries you have privileges to delete from the currently connected database.
Delete	Removes the selected query from the currently connected database.


Save As dialog box

  Related Topics

Description  The Save As dialog box enables you to save your query to the connected database or your file system.

This dialog box contains:

Database	Saves the query in your database account.
File System	Saves the query as an operating system file.
File System (QXF)	Saves the query in QXF (Oracle Data Query) format.


Grant Queries dialog box

  Related Topics

Description  The Grant Queries dialog box enables you to grant and revoke access privileges to queries you own in the connected database.

This dialog box contains:

Queries list box	Lists All Queries, Private Queries (those created by you), or Granted Queries (those that have already been granted to other users/roles).
Grant to Users/Roles	Lists the users or roles to whom you can grant access.  Names or roles in this box which are greyed have already been granted access to the selected query.
Get Info	Displays information on the selected query.
Grant	Grants access to the selected query to the selected user.
Revoke	Revokes access to the selected query from the selected user.


Export Data dialog box

  Related Topics

Description  The Export Data dialog box enables you to export the results of your query into another file format, such as a Microsoft Excel or Lotus 1-2-3 spreadsheet.

This dialog box contains:

Available Formats	Lists the types of file formats to which you can export your query results.
File to Export to	Specifies the name of the file to which you want to export your query results.
Select	Displays a browser for your file system.
Export Select Only	Specifies whether or not to export only the selected items in your query.
Include Column Headings	Specifies whether or not to include the query results column headings in the exported data.


Page Setup dialog box

  Related Topics

Description  The Page Setup dialog box enables you to format printing of your query results.

This dialog box contains:

Margins	Specifies the size of your top, bottom, left, and right margins.
Center Page	Specifies whether or not to center the page horizontally, vertically, or not at all.
Header	Specifies the header for each page using the Query Builder printer codes.
Footer	Specifies the footer for each page using the Query Builder printer codes.
Print Selection Only	Prints only the selected items from your query results.
Print Outer Borders	Prints the outer borders of the table.


Connect dialog box

  Related Topics

Description  The Connect dialog box enables you to connect to a database.

This dialog box contains:

Username	Specifies your user name for the database.  If you do not have this information, contact your database administrator.
Password	Specifies your password for the database.  If you do not have this information, contact your database administrator.
Connect	Specifies the name of the database to which you want to connect.


Set Roles dialog box

  Related Topics

Description  The Set Roles dialog box allows you to enable and disable roles, or sets of privileges, for the current Query Builder session.  Your database administrator usually sets up roles.

This dialog box contains:

Show (Enabled, Disabled, Default)	Displays in the list box all enabled, disabled, and/or default roles.  The default roles are those stored in the database for your user name.
Enable	Enables the selected role for the current Query Builder session.
Disable	Disables the selected role for the current Query Builder session.
Disable All	Disables all the roles listed for the current Query Builder session.
Password	Specifies the password for the selected role, if applicable.


Start Query Builder dialog box

  Related Topics

Description  The Start Query Builder dialog box enables you to choose to create a new query in the connected database, open a query from the connected database, or open a query from your file system.

This dialog box contains:

Create New Query	Specifies whether or not to create a new query immediately.  Displays the Select Data Tables dialog box.
Open Query from the Database	Displays the Open dialog box to open a query from the connected database.
Open Query from the File System	Displays the Open dialog box to open a query from your file system.


Paste Column dialog box

  Related Topics

Description  The Paste Column dialog box enables you to paste an existing column from a selected table into your query.

This dialog box contains:

List box	Lists the columns you can paste. Tip


Paste Function dialog box

  Related Topics

Description  The Paste Function dialog box enables you to paste a function into your condition or query.

This dialog box contains:

Show Categories	Specifies the types of functions that display in the list box:  number, character, date, conversion, group, other.
List box	Lists the available functions under the selected category.
Paste Arguments	Specifies whether or not to include the argument (the expression within the parentheses of a function that supplies information for the function to use).


Insert Rows dialog box

Description  The Insert Rows dialog box enables you to add a new record (row) to a database table using the Data Editor..

This dialog box contains:

Number of rows to insert	Specifies how many rows to insert above the selected row in your table.


Insert From File dialog box

Description  The Insert From File dialog box enables you to add rows of data from a separate, non-Query Builder formatted file.

This dialog box contains:

Available Formats	Specifies the file type of the data you are inserting:  WKS (Lotus), SYLK (Microsoft), Data Interchange Format (DIF), Comma-Delimited (CSV), or Tab-Delimited (TXT).
Insert	Specifies the name of the file from which you are adding the data.
Select	Launches your file system browser so you can choose an existing file from your system.
Insert	Inserts the data into your table.


Select Data Tables dialog box

  Related Topics

Description  The Select Data Tables dialog box enables you to include datasource objects from the connected database into your query.

This dialog box contains:

Show	Specifies whether or not to display tables, views, queries, snapshots, and/or synonyms in the list box.
Drop-down box	Specifies from which datasource to display the objects.  You can also select a table from a separate account or database.
Select Data Tables	Lists the available datasource objects you can include in your query.  Tip
Info	Displays information on the selected object:  the kind, name, owner, date it was created, and comments.
Include	Includes the selected item into the Query window.
Close	Closes the dialog box without including a datasource object.


Select Related Tables dialog box

  Related Topics

Description  The Select Related Tables dialog box enables you to include tables that are related to the selected table in the Query window.

This dialog box contains:

Show	This option is not available.
Drop-down box	Specifies whether or not to display all related tables.
Select Data Tables	Lists the available related tables. Tip
Info	Displays information on the selected table:  the kind, name, owner, date it was created, and comments.
Include	Includes the selected table.
Close	Closes the dialog box without including a related table.


Rename Table dialog box

  Restrictions 	  Related Topics

Description  The Rename Table dialog box enables you to rename the currently active table.

This dialog box contains:

Default Table Name	Specifies the current name of the table.
List box	Lists the tables you have open to rename.
New Name	Specifies the new table name.
Define	Updates the table name while keeping the dialog box open.


Info dialog box

  Related Topics

Description  The Info dialog box enables you to display information and comments about selected tables and columns.

This dialog box contains:

Kind	Specifies if the selected datasource object is a query, table, column, view, synonym, or snapshot.
Table	Specifies the name of the table in which the column is located, if Kind is a column.  Otherwise, it specifies the name of the datasource object.
Owner	Displays the owner name of the selected object.
Datatype	Displays the datatype if a column is selected
Created	Displays the date the selected object (if not a column) was created.
Comment	Displays the comment stored in the database for the selected object.


Set Relationship dialog box

  Related Topics

Description  The Set Relationship dialog box enables you to create simple, self, composite, unmatched, and non-equal relationships between two tables.

This dialog box contains:

Relationship	Specifies the origin (in A->) and target (in B->) column names.
Operator	Specifies the type of relationship between the columns
Use Relationship in Query	Specifies if your relationship will be used in the query.  Default is checked.
A not found in B radio button	Specifies that the data retrieved is all data common to both tables, in addition to data that is in table A, but not in table B.
B not found in A radio button	Specifies that the data retrieved is all data common to both tables, in addition to data that is in table B, but not in table A.
Suppress Mismatches radio button	Default.  Specifies that the data retrieved is common for both tables.


Find dialog box

Description  The Find dialog box enables you to search for specific text in your query results.

This dialog box contains:

Select Columns to Search	Lists the available columns in your query results.
Search Criteria drop-down box	Specifies if the found text must exactly match the search string, begin with the search string, end with the search string, contain the search string, or contains a NULL value.
Case Sensitive	Specifies that the found text must match the case of the text in the Search for box.  Default is unchecked.
Search for	Specifies the text for which you are searching.
Create Condition	Places the search string into the Condition field in the Query window.
Find	Runs the search within the search parameters.


Preferences dialog box

  Related Topics

Description  The Preferences dialog box enables you to modify display and default settings of your Query Builder options.

This dialog box contains:

This Document Only	Specifies that the settings apply to only the active document.
All Documents	Specifies that the settings apply to all documents.
  Query Window icon

  Results Window icon

  Query icon

  Database icon

  Data Editor icon

  File IO icon

  Appearance icon

  Command Line icon

  General icon


Query Window Panel Split Direction
Panel Split Direction

  Related Topics

Description  Panel Split Direction specifies how the Query window displays your query.

Values

Horizontal	Displays the Conditions and Datasource panels side by side.
Vertical	Displays the Conditions panel above the Datasource panel.
Default

Horizontal


Limit Datasource Display

  Related Topics

Description  Limit Datasource Display specifies the maximum number of columns that tables display when included in the Datasource panel.  The default is unchecked, and the full table is displayed.

Values

Max Datasource	Specifies the maximum number of columns that tables display in the Datasource panel.  No value indicates there is no limit.   The default is 5.


Condition Panel Size

  Related Topics

Description  Condition Panel Size specifies the size of the Conditions panel as a percentage of the overall size of the Query window.

Default

33%


Show Defined Columns in Tables

  Related Topics

Description  The Show Defined Columns in Tables check box enables you to display all defined columns as columns in the query.

Default

Checked


Show Gridlines
Gridlines

  Related Topics

Description  Gridlines displays or hides the gridlines in the Results window .

Values

Show Horizontal	Specifies whether or not to display the horizontal gridlines in the Results window.  If blank, the horizontal gridlines do not display.
Show Vertical	Specifies whether or not to display the vertical gridlines in the Results window.  If blank, the vertical gridlines do not display.
Default

Both checked


Show Print Titles

  Related Topics

Description  Show Print Titles displays or hides the print titles.  The default print title is the column name.

Default

Checked


Direction

  Related Topics

Description  Direction specifies how to display your results in the Results window.

Values

Default	Displays the columns according to the source table.
Left-To-right	Displays the columns from left to right, aligned on the left side of the window.
Right-To-left	Displays the columns from right to left, aligned on the right side of the window.


Display Units

  Related Topics

Description  Display Units specifies the units for margin and column size settings.

Default

Inches


Headings

Description  Headings displays the labels for the rows and columns in the Results window.

Default

Both Columns and Rows are checked.


Query Execution

  Related Topics

Description  Query Execution specifies if the query runs automatically any time you change the data request criteria (such as when you add a new column), or manually.  When set to Manual, you must run the query.

Default

Manual


Display

Description  Display specifies whether to display all retrieved rows in the Results window, regardless of duplicate rows, or to hide duplicate rows.

Values

All Rows	Retrieves all rows, including duplicates.
Distinct Rows	Retrieves only unique rows.
Default

All Rows


Retrieve Data

  Related Topics

Description  Retrieve Data specifies how to retrieve the data for a query.  Note

Values

All at Once	Retrieves all data for a query from the database before displaying it.
As Needed	Retrieves only the needed data.
Default

As Needed


Show Data Tables

  Related Topics

Description  Show Data Tables specifies the default selection of database objects when you choose Data [image] Select Data Tables or Data [image] Select Related Tables.

Default for Tables, Views, and Queries

Checked

Default for Synonyms and Snapshots

Unchecked


Username

  Related Topics

Description  In the Username field, type the default Username and (optionally) the Connect string that is used when you first run Query Builder, or when you File [image] Reconnect.  Note


Connect

  Related Topics

Description  If you did not specify the Connect string in the Username field, you can specify it in the Connect field.  This connect string is used when you first run Query Builder, or when you select File [image] Reconnect.  Note


Auto-Commit

  Related Topics

Description  Auto-Commit specifies that each edited row in the Data Editor is sent to the database as soon as you are done editing that row.  If unchecked, you must choose Data [image] Commit to commit your data.  Note

Default

Unchecked


Default Paths

  Related Topics

Description  Default Paths specifies the default directory paths for your file system.  Note

Values

Export Path	Specifies the default path in the Export Data dialog box.
BRW Path	Specifies the default path in the Save dialog box when you save queries in BRW (Query Builder) format.
SQL Path	Specifies the default path in the Save dialog box when you save queries in SQL format.
QXF Path	Specifies the default path in the Save dialog box when you save queries in QXF (shared by Query Builder and Oracle Data Query) format.


Export File Type

  Related Topics

Description  Export File Type specifies the default format in the Available Formats list in the Export Data dialog box.  That dialog box has a drop-down list of formats from which to choose.  Note

Values

WKS	Specifies to export the data in Lotus 1-2-3 format.
SYLK	Specifies to export the data in Microsoft Excel format.
DIF	Specifies to export the data in Data Interchange Format.
CSV	Specifies to export the data in comma-separated values format.
TXT	Specifies to export the data in tab-delimited text format.
PRN	Specifies to export the data in PRN format, where the first character of each data column is placed in a fixed-character column position.


Save File Type

  Related Topics

Description  Save File Type specifies the default file type that displays in the Save dialog box.

Values

Ask	Specifies to prompt you for the file type when you select File [image] Save.
Database	Specifies to save the query to the connected database.
BRW	Specifies to save the query as a Query Builder file.
QXF	Specifies to save the query in the format shared by Query Builder and Oracle Data Query.


Query Window

Description  The Query Window section specifies the default settings for the Query window.

Values

Font	The fonts in this drop-down list vary according to those available on your file system. The default is MS Sans Serif.
Size	Specifies the font point size.  The default is 8.


Results Window

Description  The Results Window section specifies the default settings for the Results window.

Values

Font	The fonts in this drop-down list vary according to those available on your file system. The default is MS Sans Serif.
Size	Specifies the font point size.  The default is 8.


Maximum Open Queries

  Related Topics

Description  Maximum Open Queries controls the size of your Query Builder windows every time you open a query.  Note

Values

Limit by memory	Specifies that the number of queries you can open is limited according to your computer's memory capacity.
No more than	Specifies the number of queries you can have open simultaneously.
Default

Limit by memory


SWAPDIR

  Related Topics

Description  SWAPDIR specifies the swap directory default.

Value

<path><filename>	Specifies the path and file name.


OWNERLIST

  Related Topics

Description  OWNERLIST specifies the tables to be loaded when you first open a query or when you initially choose Data [image] Select Data Tables.

Values

ACCESS	Loads a list users who own at least on table to which you have access.
ALL	Loads a complete list of database users, regardless of whether you have access to any tables for a particular user.
USER	Loads only the current user (the user you specify when you connect to the database).
NAME<>	Loads the named user.


MAXMEM

  Related Topics

Description  MAXMEM specifies the maximum memory allocation in kilobytes.  Note that you can only change this value when All Documents is selected.

Default

16000K


MAXVMEM

  Related Topics

Description  MAXVMEM specifies the maximum virtual memory allocation in kilobytes.  Note that you can only change this value when All Documents is selected.

Default

65536K


BUFSIZE

  Related Topics

Description  BUFSIZE specifies the memory buffer size in kilobytes. Note that you can only change this value when All Documents is selected.

Default

256K


MAXROWS

  Related Topics

Description  MAXROWS specifies the maximum number of rows that can be retrieved by any given query. Note that you can only change this value when All Documents is selected.

Default

16000 rows


VERBOSE

Description  VERBOSE specifies whether or not to suppress warning messages that may halt batch processing.

Values

YES	Does not suppress warning messages.
NO	Suppresses warning messages.
Default

YES


Show Function Categories

  Related Topics

Description  Show Function Categories specifies what types of functions to display in the Paste Function dialog box.

Values

Number	Specifies whether or not to display number functions.
Date	Specifies whether or not to display date functions.
Group	Specifies whether or not to display group functions.
Character	Specifies whether or not to display character functions.
Conversion	Specifies whether or not to display conversion functions.
Other	Specifies whether or not to display user-defined functions.
Default

Display all categories.


Comment

  Related Topics

Description  The Comment field specifies a comment or description for the current document.  Note.

Default

Blank


On Startup

  Related Topics

Description  The On Startup radio buttons specify whether Query Builder will automatically create a new document, create no document, or prompt you to choose when you launch the application.

Values

New Document	Creates a new document.
No Document	Opens Query Builder to a blank screen.
Prompt	Prompts you to choose to open a new document or an existing query.  Default.


Define Query Parameters dialog box

  Related Topics

Description  The Define Query Parameters dialog box enables you to pass variables and parameters to the conditions in your query.

This dialog box contains:

Parameters	Lists the existing parameters (if any) for the query.
Name	Defines the name of the parameter you are creating.
User Prompt	Specifies the text that requests information from the users.
Default Value	Specifies the default information for the parameter if no information is entered by the user.
Delete	Deletes the selected parameter.
Revert	Removes all information entered before clicking Delete, Apply, or OK.
Apply	Applies the parameter to the query and places the parameter name under Parameters.


Display dialog box

  Related Topics

Description  The Display dialog box enables you to adjust your query results in the Results window.

This dialog box contains:

Available Columns	Lists the table columns you can display in the Results window. Tip
Displayed Columns	Lists the table columns that are currently displayed in the Results window.
Copy	Copies the selected column from the Available Columns list to the Displayed Columns list.
Remove	Removes the selected column from the Displayed Columns list.
Format	Displays the Format dialog box.
Totals	Displays the Totals dialog box.
Execute	Runs the query.


Define Columns dialog box
Define Column dialog box

  Related Topics

Description  The Define Column dialog box enables you to create a new column in your query based on values in another column. Defined columns appear in both the  Datasource panel and the Results window.

This dialog box contains:

Defined Columns	Specifies the name of the column you want to create.
List box	Lists existing defined columns.
Defined as	Specifies the expression of the selected column.
Define	Applies the definition to the column.
Remove	Deletes the selected column.
Paste Column	Displays the Paste Column dialog box.
Paste Func	Displays the Paste Function dialog box.
Note:  The defined column is only a part of your query and will not modify the table definition saved in the database.


Format dialog box

  Related Topics

Description  The Format dialog box enables you to modify the appearance of data in the Results window.

This dialog box contains:

Style	Specifies the selected data to be displayed as bold, italic, or underlined.  Can also specify to use Word Wrap to display the entire text.
Alignment Data	Places the data in general format, centered, left-justified, right-justified, at the beginning, or at the end.  Default is General.
Alignment Title	Places the column title according to the related data, centered, left-justified, right-justified, at the beginning, or at the end.  Default is Center.
Column Width	Specifies the width of the column in inches or centimeters.  If in inches, you can modify the BiDi options.
Reading	Specifies whether your data is displayed left-to-right, right-to-left, or in the default mode of the current language.
Keyboard	Specifies if data typed into the Results window is displayed left-to-right, right-to-left, or in the default mode of the current language.
Format Mask	Specifies the data format according to the datatype.
Remove	Removes the selected format mask.
Print Title	Specifies the column heading that is displayed in the Results window, in printed reports, and in exported data files.
For Column	Displays the selected column that is being formatted.


Sort dialog box

  Related Topics

Description  The Sort dialog box enables you to sort the data in your query according to columns in your table.

This dialog box contains:

Available Columns	Lists the table columns you can sort in the Results window. Tip
Sorted Columns	Lists the table columns that are currently sorted in the Results window.
Copy	Copies the selected column from the Available Columns list to the Sorted Columns list.
Remove	Removes the selected column from the Sorted Columns list.
Sorting Order	Sorts the selected column in Ascending or Descending order. Default is Ascending.


Break dialog box

  Related Topics

Description  The Break dialog box enables you to sort your data according to a break column, and displays the values for that column only in the rows where the value changes.

This dialog box contains:

Displayed Columns	Lists the columns currently in the Results window.
Break Columns	Lists your break columns.  Note
Copy	Copies the selected column from the Displayed Columns list to the Break Columns list.
Remove	Removes the selected column from the Break Columns list.
Sorting Order	Sorts the data in the Results window in Ascending or Descending order.  Default is ascending.
Page Break	Specifies whether or not to create a page break after each section of sorted data in the Results window.  Default is no page break.


Group dialog box

  Related Topics

Description  The Group dialog box enables you to group your data by a column that is not displayed in the Results window.

This dialog box contains:

Available Columns	Lists the columns on which you can base your group expression.
Grouped Columns	Lists your grouped columns.
Copy	Copies the selected column from the Available Columns list to the Grouped Columns list.
Remove	Removes the selected column from the Grouped Columns list.


Totals dialog box

  Related Topics

Description  The Totals dialog box enables you to perform summary calculations on data in the Results window.

This dialog box contains:

Select Column	Lists the columns included in your query on which you can perform summary operations.
Summary Operations	Specifies which summary operation(s) you want to use on the selected column.  Default is none.  Note that these options may not be available, as not all operations can be performed on all datatypes.


Administrator setup

  Related Topics

To run Query Builder, you need access to Oracle Server version 7.0 or higher.

Note: You can save files to the file system without building the system tables.  However, each time you run Query Builder, you will be notified that the tables are not present (Query Builder will display errors BRW-15950 and ORA-00942) unless you use the command line option VERBOSE=NO.


Database accounts

Each Oracle database contains a number of accounts.  Each account belongs to a user, who has a user name and password.  The user name and password allow each user access to the database.  Users can store information in their own accounts.

Note:  If you do not have a user name and password for your database, you should contact your database administrator.


Ownership and administration

In an Oracle database, the user is the owner of his or her account.  An owner controls access to all the information in the account.  An owner can grant other users access to the account's information and likewise, can be granted access to information in accounts belonging to other owners.

The database administrator (also known as the DBA) sets up all accounts, user names, and passwords.  The DBA also allocates privileges (database permissions) to enable each user to work with the data appropriate to the user's needs


About key references

  Related Topics

You should ensure that all foreign-to-primary key relationships are defined in the Oracle Data Dictionary.  Oracle Query Builder uses the foreign-to-primary key relationships to:

•	Generate relationship lines between related tables.  When a relationship exists between two tables, Oracle Query Builder automatically draws a relationship line between them.  The line is an object that can generate a join condition between the tables when activated.

•	Include other tables related to the selected table.  Query Builder's Select Related Tables dialog box lists tables that have a foreign-to-primary key relationship with the selected table.

For information about when and how to create primary and foreign keys, refer to the Oracle SQL Language Reference Manual.


National Language Support (NLS)

Oracle Query Builder provides 16-bit national language support.

Sorting

All local sorting in Oracle Query Builder uses NLS_SORT().  If your Oracle Server is using a binary sort instead of NLS_SORT(),Query Builder may sort data in a different order than the RDBMS.  For more information on NLS_SORT(), consult the ORACLE RDBMS Database Administrator's Guide.

Character set conversion

Query Builder can properly retrieve data stored in a different character set than the default character set for the operating environment.  Query Builder automatically determines the character set by issuing the following SQL statement to the Oracle Server upon connection.


About the Data Dictionary

Oracle maintains all Data Dictionary views in the SYS account and creates public synonyms of the same name.  Oracle Query Builder accesses the views through the corresponding public synonyms.

For further information on the Oracle Data Dictionary, refer to the ORACLE RDBMS Database Administrator's Guide.  For more information on the SQL language, refer to the Oracle SQL Language Reference Manual.


About the command line

  Related Topics

You can use the command line to perform Query Builder operations, regardless of whether your operating system allows you to operate from a command prompt.  You can use command line options to set up command files to automate such processes as:

•	connecting to the database

•	opening a query

•	running a query

•	printing or exporting a query

These commands are particularly useful if you are setting up batch processing runs. Note


Using the command line

  Examples	  Related Topics

For both batch and online operations, you run Query Builder command line options by typing the application name obe60.exe at the operating system prompt, followed by a space, the command, and the argument that completes the command. Note

Entering multiple command line options

You can enter several command line options at a time, in any sequence, separating each command with a space.  When you press RETURN, Query Builder evaluates all commands entered, stacks them in the proper sequence, and runs them.  However, you can only use each command once at the command line, because duplicating or conflicting commands are ignored in favor of the last command encountered.

Using the command line in Windows

If you are running Windows in enhanced mode, Query Builder uses memory (RAM) provided by Windows for all of its data.  You can specify a value for this memory using the MAXMEM command line parameter.  The default maximum memory is 16000K, and the minimum maximum memory is 640K.

If you are running Windows in standard mode, Query Builder uses an internal virtual memory manager for data retrieved from the database.  This allows you to retrieve more rows than can fit in memory by storing some rows on your hard disk temporarily.  You can specify the buffer size, the location of the swap file, and the maximum virtual memory using the BUFSIZE, SWAPDIR, and MAXVMEM command line parameters, respectively.

The default buffer size is 256K, the default location for the swap file depends on the temporary environment variable for your operating system, and the default maximum virtual memory is  64 Mb.  If the temporary environment variable is not defined, the location is the current working directory.  To set MAXMEM to 8M, MAXVMEM to 40M, and the virtual memory file location is c:\windows\temp, type the following:

obe60.exe MAXMEM=8000 MAXVMEM=40000 SWAPDIR=C:\WINDOWS\TEMP


Using the command line examples

To start Query Builder using SQL*Net Version 2 on a local database under the user ID scott with the password tiger, you would type:  Note

obe60.exe USERID=scott/tiger

When you press RETURN, Query Builder runs with the selected options, bypassing the Connect dialog box.

To issue one Open command (FILE=, QUERY=, or IMPORT=) to determine the query to which Query Builder will open.  If you type:

obe60.exe FILE=myfile.brw QUERY=emp_dept

you are asking Query Builder to open to the query contained in myfile.brw, as well as open to the database query emp.dept.  Since Query Builder will only open one query from the command line, it will ignore the first command once it receives the second.


Using a command file

  Examples	  Related Topics

You can create a file that contains a set of commands and arguments for Query Builder.  The syntax for the command file is the same as for the command line.  Command files are regular text files.  Note that white space in a command file is ignored.

You can use this feature to specify many arguments or a standard set of arguments without typing them in each time.  Command files are especially useful for reports that are run regularly.

Command files can contain CMDFILE parameters for the "nesting" of command files.  Top level parameters take precedence over parameters specified in a nested command file.  Therefore, command line arguments override those specified in a command file. Note


 Using a command file example

The following command file "monthrep" is used for monthly reporting:

scott/tiger@db

query=monthly_report

execute=yes

destype=printer

desname=printer1

To run this report, you can type:

obe60.exe cmdfile=monthrep


Command line options

  Related Topics

Command line options enable you to log on and start Query Builder, open a query, import a SQL statement and convert it to a Query Builder query, execute a query, control memory management in Windows, and load tables.  For batch processing operations you can also print a query (either to a printer or to a printer file) and export query results to an operating system file in ASCII, .WKS, .SLK, or .DIF formats.  Note

Command	Argument(s)	Description
USERID=	<userid>/<password> [@<connect string>]	Enables you to log on to Query Builder without using the Connect dialog box.
FILE=	<path><filename>	Opens the query stored in an operating system file specified by the path and filename.
QUERY=	<database name><account name><query name>	Opens the database query specified by the database, account, and query name.  Only <query name> is required to open a query in the user's home account and database.
IMPORT=	<path><filename>	Opens a SQL statement from the designated path and filename and converts it to a Query Builder graphical query.
EXECUTE=	YES or NO	YES causes Query Builder to execute the named query immediately upon opening.  NO indicates not to execute the query.
DESTYPE=	PRINTER, FILE, (batch mode only)or EXPORT	PRINTER or FILE sends the data from the query named in the QUERY= command to the printer or file specified by the DESNAME= command.  EXPORT exports the data from the named query in the format specified by the DESFORMAT= command.
DESNAME=(batch mode only)	<printername> or <filename>	Specifies the printer or printer file for printing the named query.
DESFORMAT=(batch mode only)	LANDSCAPE or PORTRAIT, or (for exporting), WKS, SYLK, DIF, CSV, TEXT, or FIXED	Specifies the page orientation if DESTYPE=PRINTER or DESTYPE=FILE.  Specifies the file format for exporting the named query if DESTYPE=EXPORT.
SWAPDIR	<path><filename>	Swap directory location.
BUFSIZE	<value> (in kbytes)	Memory buffer size.
MAXMEM	<value> (in kbytes)	Memory maximum allocation.
MAXVMEM	<value> (in kbytes)	Virtual memory maximum allocation.
OWNERLIST=	ACCESS, ALL, USER, <account name>, or<list of account names>	Specifies the tables to be loaded when Query Builder first opens a query or when you open the Select Data Tables dialog box for the first time.
VERBOSE=	YES or NO	NO causes Query Builder to suppress warning messages that may halt batch processing.
MAXROWS=	<number>	Specify the maximum number of rows that can be returned from a query.
CMDFILE=	<path><filename>	Retrieves commands from the designated path and file name.
HELP=	YES	Displays and explains the command line options.


CONNECT command

  Related Topics

Description

You can use the command line to start Query Builder and connect to a database.  Logging onto Query Builder using the command line allows you to bypass the Connect dialog box.  If there is an error, such as an invalid user name or password, Query Builder displays the Connect dialog box so you can log on in normal graphical dialog box.

Syntax

Type the application name Query Builder, a space, then the command USERID=, followed by your user ID, password, and connect string, using the command line syntax, such as:

USERID=scott/tiger@db

Here, the user SCOTT with password TIGER is connecting to the db database.  If you do not know your user name, password, or database information, you should contact your database administrator.

Note:  If you just enter the application name Query Builder and press RETURN, the Connect dialog box displays.


OPEN command

  Related Topics

Description

You can use the OPEN command in the command line to:

•	open a query that has been saved in an operating system file

•	open a database query

•	import a supported SQL statement.

Syntax

To open an operating system query, enter the command FILE=, then the path and filename.  For example:

FILE=c:/qb/myquery.brw

To open a database query, enter the command QUERY=, then the query name.  For example:

QUERY=myquery

To import a SQL statement, enter the command IMPORT=, followed by the path and filename.  For example:

IMPORT=myquery.sql

Query Builder enables you to specify a search path for operating system queries, SQL statements, and command files referenced in command line options.  Use of the search path varies by platform.


EXECUTE command

  Related Topics

Description

The EXECUTE command enables you to run a query from the command line.

Syntax

To run a query immediately upon opening it, you can enter EXECUTE=YES on the command line.

To suppress the immediate execution of a query, enter EXECUTE=NO.

Note:  If Automatic Query Execution is selected in the Preferences dialog box, typing EXECUTE=NO will not override it; the query will be executed as soon as it is opened.


Batch destination commands

  Related Topics

You can use the CONNECT, OPEN, or EXECUTE commands for both online (interactive) and batch processing.  Destination commands, however, can only be specified for batch processing.  Note

The three arguments you can use to specify the destination are:

•	destination type

•	destination name

•	destination format

Destination Type

Entering DESTYPE=PRINTER sends the output directly to a printer.  Entering DESTYPE=FILE saves the output to a printer file (a file containing printer commands) for printing later. Entering DESTYPE=EXPORT exports query results to an operating system file in any of the formats specified by DESFORMAT=.

Destination Name

If you send query results to a printer or printer file you must specify either the printer name or the filename, using the DESNAME= command.

For example, entering DESNAME=lpt1 sends your query to the printer called lpt1.  Entering DESNAME=c:\temp\print1.ps saves your query to a printer file called print1.ps in the temp directory on your c: drive.

DESNAME= has no function on Windows if DESTYPE=printer.  Any value it may contain is ignored.  To specify an output device, set your default printer using the Control Panel.

Destination Format

You use the destination format command DESFORMAT= to indicate page orientation for printers and printer files.  DESFORMAT= LANDSCAPE specifies a landscape page orientation.  DESFORMAT= PORTRAIT specifies a portrait page orientation.

You also use the DESFORMAT= command to indicate the file format for files you export.  Entering DESFORMAT=TEXT exports an ASCII tab delimited file.  DESFORMAT=WKS exports a .WKS file that can be used by spreadsheet programs.  DESFORMAT=SYLK exports a .SLK file that can be used by spreadsheet programs such as Microsoft Excel.   DESFORMAT=DIF exports a file in Data Interchange Format (.DIF) that can be used in a number of computer applications.  Entering DESFORMAT=CSV exports an ASCII comma-delimited file.  DESFORMAT=FIXED exports a fixed-format file.


Memory commands

  Related Topics

There are four command line parameters that control memory management

•	SWAPDIR

•	BUFSIZE

•	MAXVMEM

•	MAXMEM

Query Builder can use an internal virtual memory manager for data retrieved from the database.  This allows you to retrieve more rows than can fit in memory by storing some rows on your hard disk temporarily.  You can specify the buffer size, the location of the swap file, and the maximum virtual memory using the BUFSIZE, SWAPDIR, and MAXVMEM command line parameters, respectively.

The default buffer size is 256K, the default location for the swap file depends on the temporary environment variable for your operating system, and the default maximum virtual memory size is 64MB.  If the temporary environment variable is not defined, the location is the current working directory.

You can specify a value for the maximum amount of RAM (memory) using the MAXMEM command line parameter.  The default maximum memory is 16000K, and the minimum maximum memory is 640K.


OWNERLIST command

  Related Topics

Description

The OWNERLIST= command specifies the tables to be loaded when Query Builder first opens a query or when you initially open the Select Data Tables dialog box in a session.  By default, Query Builder loads a list of all users that own at least one table to which you have access.  If the database has many tables, this could be very time-consuming (even if you have access to only a few of these tables).

Syntax

OWNERLIST={ACCESS|ALL|USER|<name>|<list of names>}

where ACCESS, ALL, and USER are the arguments that enable you to control the list of users, <name> is a database account name, and <list of names> is a list of account names.  If you later attempt to open a query or include a synonym which refers to tables owned by users other than those already loaded, Query Builder loads those users as well.


OWNERLIST command arguments

  Related Topics

When you use the OWNERLIST command in the command line, you can use a series of arguments:

OWNERLIST={ACCESS|ALL|USER|<name>|<list of names>}

ACCESS

Specifying ACCESS(the default) loads a list of users who own at least on table to which you have access.  This argument should be used in the case of a database which has many users, and where you normally create more than one query at a time based on tables owned by more than a few users.

ALL

If you specify ALL, the complete list of database users is loaded regardless of whether you have access to any tables for a particular user.  In the case of a database with many users (more than 500), specifying ALL is not recommended.  In the case of a database with a small number of users but a large number of objects (tables, views, synonyms, indexes, etc.), specifying ALL may be preferable to the default ACCESS.

USER

If you specify USER, only the current user (the user you specify when you connect to the database) is loaded.  USER should be used if you open only existing queries or construct new queries from your own tables.  In the following example, Query Builder is invoked using the user SCOTT, and only the user SCOTT is loaded:

obe60.exe USERID=SCOTT OWNERLIST=USER

<name>

If you specify <name>, the named user is loaded.  <name> should be used if you open only existing queries or construct new queries based on a single user's tables.  In the following example, Query Builder is invoked using the account SCOTT and the user INVENTORY is loaded:

obe60.exe USERID=SCOTT OWNERLIST=INVENTORY

<list of names>

If you specify <list of names>, the listed users are loaded.  <list of names> should be used if you open only existing queries or create queries based on a short list of users.  The argument <list of names> is a list of database user names separated by commas, plusses, or colons.  The list may not contain spaces; the list may be enclosed in parentheses.

The following lists are valid:

OWNERLIST=SCOTT,WARD

OWNERLIST=(SCOTT+WARD)

OWNERLIST=SCOTT:WARD,SMITH+JONES

The following lists are not valid:

OWNERLIST=SCOTT WARD

OWNERLIST=SCOTT, WARD

In the following example, Query Builder is invoked and the INVENTORY, SALES, and SCOTT users are loaded:

obe60.exe OWNERLIST=(INVENTORY,SALES,SCOTT)


Command line errors

  Related Topics

If command line statements are not entered properly, an error message displays:

Message	Reason
Null Keyword	No command was entered at the command line, such as obe60.exe=scott/tiger instead of obe60.exe USERID=scott/tiger.
Unrecognized keyword	Invalid or misspelled command was entered.
Unrecognized option	Invalid or misspelled option.
Null argument value	The command was entered without specifying the parameter that goes with it.
Multiple specifications of a keyword	The same command was used more than once.
Multiple log ons	The USERID was used more than once.
Multiple "open" commands	More than one "open" command was entered, such as FILE=my-file.brw QUERY=myquery
"Open" command without log on	You tried to open a file or query without first logging onto the database.
EXECUTE command without "open."	You tried to execute a query without first opening it.
EXECUTE=NO and a DESTYPE=any parameter	You used a DESTYPE= command while setting query execution off.
DESTYPE=EXPORT without DESFORMAT	You tried to export a query without specifying the type of destination file.
DESNAME without DESTYPE	You tried to name your printer or output file name without specifying what type of device or file you are writing to (PRINTER, FILE, or EXPORT).


Dynamic Data Exchange (DDE)

  Related Topics

In Microsoft Windows, you can create a dynamic link between data in the Oracle Query Builder Results window and data in another Windows application using Dynamic Data Exchange (DDE).  When you create a dynamic link, data in the other application is automatically updated every time you rerun your query (while that application and document are open).

When you create a DDE link between Query Builder and another application, the DDE application, topic, and item are set to default values:

DDE Application	The DDE application for a link from Query Builder should always be obe60.
DDE Topic	The DDE topic for a link from Query Builder is by default of the form:  QUERY=NAME EXECUTE=YES The DDE topic may be edited to include other command line options, such as to include the user name and password needed to log in.  The topic is essentially an abbreviated command line which does not include the application name or location. The following DDE topic would be used to open a query stored in a file, connect to a remote database, and automatically execute the query so that data will be returned from the link: FILE=c:\obe60\my_query.brw EXECUTE=yes USERID=scott/tiger@database
DDE Item	The DDE item is used to specify the range of cells to get data from.  A range of cells is specified as two cell locations separated by a colon.  For example, to retrieve the cells described within row 2, column 1, and row 7, column 5, the item would be R2C1:R7C5.


Updating a DDE link

  Related Topics

Many DDE client applications allow you to update the links you create when a document (such as a word processing file or spreadsheet) is opened.  If Query Builder is not running, updating a link will launch Query Builder and open the query needed to copy the data once more into your document.

Because many client applications do not wait until Query Builder has successfully been started, connected to the database, and data returned, you may find that when a DDE link launches Query Builder, the data is not updated.  Should this occur, update the link again manually, and the data will be brought into your document.

Note: In order for links to be updated properly, the directory that contains the QUERY BUILDER.EXE executable (normally located in the \ORA7WIN\BIN directory) must be included in the DOS path.  By default, this directory is not included in the path when you install Query Builder.


Using a DDE client-compliant application

  Related Topics

When Microsoft Excel refreshes data in the spreadsheet, NULL values are treated differently than when you enter DDE formulas directly.  Instead of displaying 0 (zero), Excel Version 3.0 displays #VALUE and Excel 4.0 displays the previous value that was in the cell.


About the Data Editor

  Related Topics

The Data Editor enables you to edit the data that appears in the Results window and write those changes back to the database.  In order to use the Data Editor, you must execute a query on no more than one table.  You can have more than one table displayed in the Datasource panel, but you cannot invoke the Data Editor unless the Results window displays data for only one table.  If you are displaying more than one table in the Query window, make certain that your conditions do not draw active relationships with other tables.

You can also use the Data Editor on views (or synonyms) as long as they are based on a single table.

You can edit data in the Results window only under the conditions described below.  Before you attempt to insert, update, or delete data, you should first make certain you have the appropriate database privilege(s).

A privilege is permission granted by the creator of an object to another user, to allow them to perform operations on that object.  Database objects include tables, views, or indexes.  Types of privileges include ALTER, DELETE, INSERT, SELECT, and UPDATE.

Note:  Because the Data Editor enables you to change or delete vital data, Query Builder is shipped with the Data Editor disabled.  Contact your system administrator if you cannot activate the Data Editor and you feel you should have access to it.


Editable queries

  Related Topics

A query must meet the following constraints to enable any of the three DML operations (INSERT, UPDATE, and DELETE):

•	All items in the query's select list are from a single table.  Specifically, the query must return <table>.rowid (an Oracle database convention identifying each unique row) for Query Builder to perform a DML operation.

•	The query contains no derived columns or relationships.

•	The table does not involve a database link.


Updatable columns

  Related Topics

The following constraints must be met in order to update a column:

•	You must have UPDATE privileges on a specific column.

•	The column must be a simple field.  A simple field is one which directly refers to a column without any functions or additional operators.  In the Employee table of our sample database, for example, SALARY is a simple field, while SALARY*10 is not.


Insertable rows

  Related Topics

The following constraints must be met in order to insert new rows:

•	You must have INSERT privileges on the table being queried.

•	Values must be provided for all required columns (NOT NULL constrained), whether or not those columns are displayed in the Results window.  The columns may be assigned default values (using a DEFAULT clause).


Uneditable rows

  Related Topics

You cannot edit the following rows:

•	summary rows

•	deleted but not yet committed rows

•	rows locked by another user

•	rows that have been modified by another user since the data was last retrieved


Deletable rows

  Related Topics

You must have DELETE privileges on the table being queried in order to delete rows which have been retrieved from the database.  You cannot delete uneditable rows.


Locked and altered rows

  Related Topics

To support concurrency, a lock occurs when multiple users are using the same database resource.  Concurrency is the simultaneous manipulation of a resource by multiple users. A lock is a ''hold'' on a resource that occurs when a user performs a DML operation  (an insert, update, or delete command). This enables many users to work within the same table at the same time.  Locking is performed on a row automatically by the Oracle RDBMS.  Locking doesn't occur during a query, because you aren't changing the data.  Only INSERT, UPDATE, and DELETE commands cause locking.

While a row is locked the entire record will be greyed in the Results window.  You will be able to view but not edit these rows.  Query Builder prevents you from modifying data that is currently being edited by another user.  Once the other user releases the data, and if the other user has not committed changes to the database, you will be able to proceed.  If the other user has committed changes, you will need to rerun your query before you can edit the data.

You may run across data that has been explicitly locked by another user with the SQL statement LOCK TABLE.  You will not be able to modify locked data until the user removes the lock.


Elements of the Data Editor

  Related Topics

When you activate the Data Editor, an edit bar displays above the worksheet in the Results window where you can enter changes to your data.  The contents of the active cell are displayed and highlighted in this field.  The Data Editor edit bar displays only one cell at a time.

The active cell is the cell in the worksheet where the cursor was when you invoked the Data Editor.  It is distinguished by a heavy grey border.  If you select a range of cells, all cells are highlighted except the active cell.

The datatype icon, indicating the datatype of the active cell, appears to the left of the edit bar.   and [image] appear to the left of the datatype icon.


About defined (and computed) columns

  Examples	  Related Topics

You can perform calculations on the data Query Builder retrieves from the database, then display the calculated data in the Results window as a new column (also known as a defined field).  You can name, define, and display these columns just as you would any other column in your query.

You can use defined columns in a Query Builder query to construct "what if" scenarios, forecasts, projections, and other analyses, such as variances and percent-to-total.

Note:  When you display a defined column (field) that contains an aggregate function, Query Builder automatically generates the corresponding GROUP BY clause for other displayed columns not involving aggregate functions.  Advanced users can also specify GROUP BY columns manually for grouping by non-displayed columns.

While columns are generally synonymous with the columns of data stored in your tables, they can also refer to new columns of data in a query that are derived or calculated from the values in another column.  These computed columns are sometimes known as derived or defined fields. For example, the Employee table may include two columns that describe compensation: Salary and Commission.  You can create a defined column that combines the values in these columns into a new column called Net Pay that represents the summation of salary plus commission.

You can base your defined columns on any combination of single or multiple database columns, according to your needs.

Defined columns exist in your query, not in the database.  You can choose to display them in the Results window as columns, or keep them hidden (such as when using them to create other defined columns).

Note:  The defined column is only a part of your query and will not modify the table definition saved in the database.


Defined (and computed) column examples

Suppose you wanted to project a budget for next year's net pay (NET_PAY_NEXT) assuming a 10% salary increase.  You could enter the following in the Expression field:

(SALARY*1.1) + COMMISSION

Or, if you wanted to review a 10% increase in both salary and commission, you could enter:

(SALARY+COMMISSION) * 1.1

Since NET_PAY is SALARY+COMMISSION, this is the same as:

NET_PAY * 1.1

You can also create a defined column that displays the difference between the current net pay and next year's projections.  Call this column VARIANCE, and define it as:

NET_PAY_NEXT - NET_PAY

When you define new columns, the base columns described in your expression need not exist in the underlying database, although they should ultimately be derived from columns in the underlying table.  VARIANCE, for example, is the same as:

((SALARY+COMMISSION) * 1.1) - (SALARY+COMMISSION)


About deleting defined (or computed) columns

  Related Topics

When you delete a defined column, Query Builder removes it from the Results window and removes any associated sort or break lists that may be in the query, as well as any conditions which reference the defined column.

If you delete a defined column that is used to define a second or aggregate column, the second column is invalidated.  If you delete a defined column that is included as a column in another query, that column in the other query is also deleted.


Function-defined columns

  Examples	  Related Topics

A function performs a calculation and returns a result, much how an operator performs.  However, functions are used with a different format  This format allows them to operate on any number of arguments, including none.  Every time you associate a column with a function name, Query Builder performs the prescribed operation on the value(s) in that column.

Functions consist of a function name followed by parentheses, in which you indicate the column name(s) the function will operate on, for example:

SUM(SALARY)

SUM(SALARY+COMMISSION)

Oracle Server provides a variety of built-in arithmetic, statistical, and editing functions that you can use in Query Builder.   An example is the function ROUND(), which rounds a number in a given column to the degree you specify.

If you enter the function ROUND (CREDIT_LIMIT,-2), Query Builder would round each value for CREDIT_LIMIT up to the nearest hundred (negative numbers indicate precision to the left of the decimal).  If you enter ROUND(CREDIT_LIMIT,1), Query Builder would round the values for CREDIT_LIMIT down to the nearest 10th.

An argument is an expression within the parentheses of a function that supplies information for the function to use.  For example, in the expression ROUND(CREDIT_LIMIT,-2), ROUND() is the function and CREDIT_LIMIT and -2 are the arguments that indicate which column to round and what type of rounding to use.


Function-defined column example

Suppose you want to define a column that returns the larger value, SALARY or COMMISSION.  You can use the function GREATEST along with the appropriate arguments (the columns you want to apply the function to):

GREATEST(SALARY,COMMISSION)

This expression returns whichever value is greater, salary or commission (provided that values exist in both columns).


About column datatypes

  Related Topics

The most common datatypes in Query Builder are:

CHAR or VARCHAR2	CHAR is used for fixed length character data, to a maximum size of 255 bytes and a default size of 1 byte.  VARCHAR2 is for variable length character strings to a maximum size of 2000 bytes.  You must specify size for VARCHAR2.
NUMBER	NUMBER indicates that the database column contains numbers (integers, decimals, or any other type of numeric values).  You can perform calculations on numbers represented in the NUMBER datatype, but not on numbers represented by the CHAR datatype.
NUMBER(p,s)	The range of p (decimal precision) is 1 to 38. The range of s (scale) is -84 to 127.  Decimal precision is the number of digits to store.  The scale is the number of digits to the right of the decimal point.  If the scale is negative, the actual data is rounded to the specified number of places to the left of the decimal point.  For example, a specification of (10, -2) means round to the hundreds.
DATE	DATE indicates that the database column contains date values.  Valid dates ranging from January 1, 4712 BC to December 31, 4712 AD.  Default format is DD-MON-YY as in '01-JAN-92'.
Additionally, Query Builder provides limited support for the following Oracle datatypes:

LONG	A LONG column contains alphanumeric data of variable length up to 2 gigabytes (or 231-1).  When you include a LONG column in a Query Builder query, the Results window displays the first 2000 characters.
RAW	RAW indicates raw binary data.  Maximum size is 255 bytes.  Values must be inserted as character strings in hexadecimal notation.  Values are displayed in hexadecimal.  You must specify size for RAW datatypes.
LONG RAW	LONG RAW displays raw binary data up to 2 gigabytes (this appears in hexadecimal in the Results window).  LONG RAW datatypes are subject to the same limits as RAW datatype.
Note:  Query Builder supports datatype conversions in cases where conditions and expressions reference different datatypes in their comparison.  Refer to the Oracle8 Server SQL Language Reference Manual for additional information.


About relationships

  Examples	  Related Topics

A relational database consists of a number of related tables.  Each table is made up of columns (sometimes called fields) which are arranged vertically.  Each column has a name, and contains a unique type of data.

Relationships, or joins, define how a column in one table relates to a column in another table.  In many cases, the relationships you need are already defined in the database.  Relationships also reduce data redundancy.  Whenever there is a relationship between columns in different tables, you can use the tables together in a query.  Note

Related columns do not always share the same names.  You can always relate columns that share the same datatype, and in some instances you can relate columns of different datatypes, if the relationship makes sense.  For example, you can always relate a Number datatype column with a Character column, since a number can also be interpreted as a character.  In fact, almost any column (except long text columns) can be related to Character columns.

Relationships can be user-defined (temporary) or established in the underlying data dictionary.  Relationships in the data dictionary automatically appear when you include related tables (tables that have a primary/foreign key relationship).  You do not have to do anything to use these relationships, since they are always valid.  These can be created or modified using the Schema Builder.

Note: Relationships are normally displayed as lines connecting columns (primary and foreign keys ). Note

User-defined relationships are valid only for the query in which you create them.  Creating a user-defined relationship does not establish a corresponding permanent relationship in the underlying table.  The main difference between the two is that in the case of a user-defined relationship, you must draw the relationship line; in a relationship stored in the database, Query Builder draws it for you.


Relationship example

Suppose Summit Sporting Goods maintains a CUSTOMER table, which includes information about each customer, and a SALES_ORDER table, which includes information about the orders made by each customer.

These two tables are related because they both contain the column CUSTOMER_ID--customers have ID numbers and orders are arranged according to the ID number of the customer who placed the order.  These two columns have a relationship, called an equal relationship, because the unique key CUSTOMER_ID in the CUSTOMER table is the same as the CUSTOMER_ID in the SALES_ORDER table.

You could create a query that retrieves the NAME, ORDER_DATE, and TOTAL for each customer, even though NAME is in one table and ORDER_DATE and TOTAL are in another table.  Query Builder matches data from both tables using the common column (CUSTOMER_ID), then displays the requested data in the Results window as if it came from a single table.   Note

Summit also has a Department table that stores each department by ID.  Those IDs are related to the Department ID column in the Employee table.  So you don't need to repeat the same department Names and Locations over and over in the Employee table; instead you only need to store, for each employee, their Department ID.


About primary keys

  Examples	  Related Topics

Each horizontal line in a table is called a row (or record).  Rows contain actual data, stored according to a unique identifier called a primary key.  Primary keys can be made up of single columns or several columns, and are used to locate specific rows of data.  These keys ensure that each row is unique, so that duplicate records are eliminated and so that the system can easily pinpoint the exact record you want. Note


Primary key example

Suppose the primary key in the Employee table is the Employee ID.  If we wanted to locate the employee record for John Smith, we might find that there are two people in our database with this name.  But since the Employee ID is unique, as long as we know the appropriate Employee ID value, we can find any employee record in the database.


About foreign keys

  Related Topics

Relational databases use two types of keys, primary keys and foreign keys.  The foreign key is the counterpart column in another table (but it is not necessarily a unique identifier for that table). Note

For example, EMPLOYEE_ID is the primary key in the Employee table, while DEPARTMENT_ID is the primary key in the Department table.  DEPARTMENT_ID is also a foreign key in the Employee table, so these two tables are related using the DEPARTMENT_ID column.  Note


Relationship types

  Related Topics

You can create these relationships using Query Builder:

•	simple relationship

•	self-relationship

•	composite relationship

•	unmatched (outer) relationship

•	non-equal relationships

You can combine these relationships anywhere within the same query.


Keyboard commands for relationships

  Related Topics

You can use the following keyboard commands when working with tables and relationship lines.

UP/LEFT/DOWN/ RIGHT arrows	Highlights the next column in the table, or moves along the relationship line to the related column, in the direction of the arrow key.
TAB/SHIFT-TAB	Selects the next/previous relationship with the same foreign key.


About simple relationships

  Examples	  Related Topics

You create a simple user-defined relationship to relate two tables that have a common column not previously defined in the underlying database.  When you create a user-defined relationship, Query Builder draws a relationship line connecting the related columns.  The relationship is saved in the current query, not in the underlying data dictionary.

You can always relate columns that share the same datatype, and in some instances you can relate columns of different datatypes, if the relationship makes sense.  For example, you can always relate a Number datatype column with a Character column, since a number can also be interpreted as a character string.  In fact, almost any column (except long text columns) can be related to the Character columns.  Note

You do not have to select the related columns in your query to make use of a relationship.  However, it is necessary to bring the related tables into the Query window to take advantage of any relationships.

Note:  Not every table in a query needs to be related to every other table in the query.  There must be a way, however, to relate or chain all tables within your query.


Simple relationship example

Suppose the Summit Sporting Goods database contains the Customer, Department, and Employee tables.

•	Customer and Employee are related through the column EMPLOYEE_ID (SALESPERSON_ID).

•	Employee and Department are related through the column DEPARTMENT_ID.

•	The Customer and Department tables are not directly related.

A query can use all three tables, because there is at least one relationship path through which they are all related.


Activating and deactivating relationships

  Related Topics

Relationships defined in the database and those that you create using the mouse are automatically activated and deactivated as they are needed in a query.  Active relationships are displayed with a heavy black line; inactive relationships are shown as gray lines.


About self-relationships

  Related Topics

A self-relationship relates a table to itself.  Self-relationships have a limited, very specific use:  to establish a parent-child link between two columns that are contained in the same table.  For example, you could use a self-relationship to relate subsidiaries of a company to their parent company, or employees to their managers.

A self-relationship cannot be effective unless the columns in the table are counterparts of one another.  In the sample database Employee table, for example, the Manager ID is the same as the Employee ID for that manager.  So the self-relationship icon is displayed in the Manager ID column.


About composite relationships

  Related Topics

A composite relationship uses a single relationship to connect two or more columns in one table with columns in another table.  A composite relationship is required when the primary or foreign keys are comprised of two or more columns.


About unmatched (null) rows

  Examples 	  Related Topics

Query Builder lets you choose whether to retrieve unmatched rows when you are using a relationship in a query.  An unmatched row occurs when the relationship connects tables where there are values on one side that have no corresponding values on the other side.  SQL users will be familiar with this as a query condition known as an outer join.  Unmatched rows usually return null values.

Note: A row that returns a value of 0 (zero) is not the same as an unmatched row.  The Suppress Unmatched Rows option in the Set Relationship dialog box only works on rows that return null values, not 0 values.  Use this feature if you want to display data where one table has no values entered, such as orders with no line items.  When using an unmatched relationship, you can only choose one side on which to display unmatched records.


Unmatched (null) row example

Suppose Summit Sporting Goods wants a list of all customers and their corresponding sales.  In other words, they will retrieve a list of all Customers from the Customer table that have produced sales, in addition to those that have produced no sales.  If you run the query using a simple relationship, selecting the Customer NAME and Sales AMOUNT columns, you will only retrieve customers who have amounts posted in both tables.  Creating an outer relationship, using the option  A not found in B, allows you to also retrieve the names of those customers that do not have values in the Sales AMOUNT column.


About non-equal relationships

  Examples	  Related Topics

A non-equal relationship causes Query Builder to retrieve rows from two or more tables based on a relationship other than equality.  You can create a non-equal relationship through the Set Relationship dialog box, selecting the appropriate operator, or by entering it as a condition.

If you enter a simple non-equal relationship as a condition, where you are relating two tables using a single operator (such as <, >, or =), the condition is automatically converted to a non-equal relationship line, with an icon appearing on the relationship line, and the conditions text disappears from the Conditions panel.  To remove or modify these relationships, use the Set Table Relationship dialog box.


Non-equal relationship example

Suppose you want to create a query that tells you which customers have credit limits greater than their monthly sales.  You can enter a condition that defines the relationship:

CUSTOMER.CREDIT_LIMIT>SALES.AMOUNT


Datatype icons

  Related Topics

The following supported datatypes for all the columns in a datasource object are represented by icons located in the right hand column of the object in the Datasource panel.

Datatype	Icon	Description
Character		Use the Character datatype for fixed length character data, to a maximum size of 255 bytes and a default size of 1 byte. Note:  You can represent numbers using the Character datatype, but you can only perform calculations with numeric datatypes.
Varchar2		Use the Varchar2 datatype for variable length character strings, to a maximum size of 2000 bytes.  You must specify a size for the Varchar2 datatype.
Number		Use the Number datatype for integer or floating data. You must enter a precision length (number of digits) for integer data. Floating datatype is for decimal numbers. When you assign this datatype to a column, you must specify both the precision and the scale. The precision (total number of digits in the number) can range from 1 to 38.  The scale (number of digits to the right of the decimal point) can range from -84 to 127.  If the scale is negative, the actual data is rounded to the specified number of places to the left of the decimal point.  For example, a specification of (10,2) means 10 digits with 2 to the right of the decimal point.  A specification of (10,-2) effectively means a 10-digit integer, rounded to the nearest hundred.
Date		Use the Date datatype for any type of date.  Valid dates range from January 1, 4712 BC to December 31,4712 AD.  The default date format is DD-MON-YY, as in ‘01-JAN-97.’
Long		Use the Long datatype for long text fields of alphanumeric data of variable length, up to 2 gigabytes.
Raw		Use the Raw datatype for raw binary data.  The maximum size is 255 bytes.  Values must be inserted as character strings in hexadecimal notation, and values are displayed in hexadecimal.  You must specify a size for raw data.
Long Raw		The Long Raw datatype is for storing up to 2 gigabytes of raw binary data (which is entered and displayed in hexidecimal form).  Long Raw datatypes do not require a specified size.
MLSLABEL		Use the MLSLABEL datatype for storing the binary format of a label used on a secure operating system.  Labels are used by Trusted ORACLE to mediate access to information.  You can also define columns with this datatype if you are using the standard ORACLE Server.  For more information on Trusted ORACLE, including this datatype and labels, see the Trusted ORACLE8 Server Administrator's Guide.


Datasource object notation

  Related Topics

Shown below is an example of two datasource objects, linked by a relationship.

Click any icon, indicator, or part of these objects to display a description.


Object type indicator

The icon in the upper left corner of any datasource object indicates its type:

  Table

  View

  Query

  Snapshot

  Synonym


Object title

This is the name of the datasource object.  You can change the name by clicking on the object title.


Comment indicator

This icon indicates that this object (or column) has a comment associated with it.  Double-click this icon to open the Info dialog box, where you can view the comments.


Column Name

This is the name of one of the columns in this object.  If you double-click a column name, the column will be included in your query, and displays in the Results window.


Datatype indicators

These icons indicate the datatypes of their respective columns. If you click and hold on one of these icons, a pop-up menu appears, and you can choose a different datatype.


Relationship line

A gray line drawn between two columns indicates that a relationship has been created between the columns at either end of the line. The line is drawn from the foreign key to the primary key.


Self-relationship indicator

This icon indicates that a self-relationship has been created between this column (foreign key) and another column (primary key) in the same table.


Primary key

A column name displayed in boldface indicates that this column has been defined as a primary key for the object.


Foreign key

A column name displayed in italics indicates that this column is a foreign key, related to a primary key in another column.  Setting up a relationship between two columns is the only way to define a foreign key.


About the Query Builder user interface

  Related Topics

Query Builder uses two windows:

•	the Query window, where you build your queries

•	the Results window, where data retrieved from the query is displayed

The title bar displays the name of the window and name of the active document.  You can use either the Query or Results window for the overall management of your Query Builder session.

The Icon Bar enables you to issue menu commands with a click of the mouse.  Each icon corresponds to a menu item.  If you hold the mouse pointer over an icon, a popup label displays the associated command.  When the Query window is active, the Icon Bar includes Data menu commands.  If the Results window is active, the Icon Bar includes Results menu commands.

Query Builder is in edit mode whenever the mouse pointer changes from an arrow to a crossbar (such as when editing a condition).  To select an object in an inactive window while in edit mode, first click the window to activate it, then click on the object to select it.


About the Query window

  Related Topics

When you open a query, Query Builder displays it in the Query window, split into two panels:

•	the Conditions panel, where the Condition field is located.  It is used to refine the query.

•	the Datasource panel, which displays the tables (views, snapshots, and so forth) used in the query.

Depending on your preferences, the panels may be split vertically or horizontally.

Each table is displayed in a box containing the name of the table and the names of columns in that table.  There are a number of icons that help identify the current database object.  The icons indicate whether descriptive comments are attached, the type of datasource, the column's datatype, and whether indexes have been defined for tables in the query.

Lines are drawn to indicate the relationships between columns.  Relationships are set up between tables to identify how a column in one table relates to a column in another table.

A check mark to the left of the column name indicates the column has been included in the query.  This means that Query Builder will search for data corresponding to the column name when you run the query.  No check mark indicates that column's data will not be included.


About queries

  Restrictions 	 Related Topics

A query is simply a request for information from the database, such as:

•	What information do we have on every department?

•	What do we know about every employee?

You can also request specific information, such as:

•	What are the New York and California sales figures by city for our best-selling products?

•	What was January's commission for each salesperson named Pat who drives a grey Volvo?

You can tailor your queries to return data from a single table or a variety of tables, and you can include calculations and break columns.

Query Builder is the perfect ad hoc query tool, enabling you to run queries as you need data.  It is also a reporting tool, enabling you to design and save query templates that automate your reports and analyses.

The tables you can access may be contained in one database account or spread across many accounts.  You can also access multiple databases in more than one physical location, which may be shared by a number of users.

Note:  Query Builder conserves database server resources by minimizing calls to the database.  It retrieves small blocks of data as you scroll through the Results window or whenever it must fetch all the rows to complete a request, such as a sort.  Query Builder has a virtual memory manager that writes blocks of data to your computer's local disk.

The specific implementation of the virtual memory manager is dependent on your operating system.


About storing queries

  Restrictions	  Related Topics

Query Builder stores queries differently, depending upon whether they are saved in the database or as a file.

Database

When Query Builder saves a query in the database, either one or two database objects may be created:

1	One object contains the entire state of the query.  The elements saved include the query preferences, window layout options, included tables, defined columns, temporary relationships, conditions, groups, sorts, formats, breaks, summaries, and attributes.  This object is always created when a query is saved in the database, regardless of whether the query is complete (executable).

2	The second object is a query view.  This object is created only if the query contains a complete SQL statement.  Query Builder executes a CREATE VIEW statement in SQL and saves valid queries as views in the user's database account.  This is the object that you select from the Select Data Tables dialog box when you want to include a query as a datasource in another query.

File

When Query Builder saves a query as a file, it writes the entire state of the query to a binary file, without creating a query view or other database object.  Operating system files can be retrieved and updated, but they cannot be used as datasources to build other queries, because no database view has been created.

Note:  Whichever method of saving queries you choose, it is recommended that you be consistent, especially if you plan to modify and update them.  Modifications made to a database query do not take effect in the file of the same name and vice versa.  However, you may want to consider the following when choosing where to store your query:

•	Query Builder automatically places the database queries in your database account, whereas you must name a path (or folder) and filename for files.

•	A file may be easier to backup locally.

•	You cannot use a file as a datasource for building a new query.

•	Query Builder users can easily share queries without special file sharing network software across multiple graphical environments if queries are saved in the database.


About granting and revoking access queries
About granting and revoking access to queries

 Related Topics

Database

In Query Builder, you can view, update, and delete any query that you own.  Query Builder assigns you ownership of a query when you create it.  If you save your query in the database, you can control access to it with the Grant Queries dialog box.

You can grant access privileges to any other user who also has access to the datasources used in creating the query.  Other users cannot delete or change the query; but they can save a copy of it in their own database account and they can change the copy.  Executable queries can also be used as datasources in building new queries.

File

If you save your query as a file, users who have access to your operating system directory, folder, or account can view the query in Query Builder provided they also have access to the underlying datasources used in creating the query.

Anyone who can write to the directory, folder, or account can view and change the query in Query Builder, if they also have access to datasources used in creating the query.  They can delete the query using an operating system command, regardless of whether they have access to the datasources, just as with any operating system file.


About the Datasource panel

  Related Topics

The Datasource panel is the section of the Query window where items, such as tables and relationships, display.


About datasources

  Related Topics

Query Builder displays a datasource as a graphic in the Query window to depict database objects and column names.  Database objects include tables, views, synonyms, snapshots, and queries stored as views.  All of these objects can be manipulated and queried in a similar fashion.

To request information from the database, you need to specify which database objects to include in your query.


Access to datasources

  Related Topics

Your database administrator determines your overall access privileges to datasources in the underlying Oracle database.  Either the database administrator, a datasource owner, or someone else with grant authority on a datasource can assign you access to it.

Any changes to datasources (except queries) must be done through the Schema Builder or by SQL commands in the underlying database.


Using a query as a datasource

  Examples 	  Related Topics

Any time you save a valid query to the database, Query Builder automatically also saves the query as a datasource, which you can use in building a new query.  Query datasources can be included in the same manner as tables, using the Select Data Tables dialog box.

Notes:

•	If you delete the query where the original calculations were made, any subsequent use of the calculation in a different query is also deleted.

•	Using a query as a datasource is particularly useful if your saved query uses any special information, such as a column you've defined.  Defined columns cannot be saved across queries, but if you include a query that contains a defined column, that column appears as a column in the query datasource.


Using a query as a datasource example

Suppose you saved a query called CREDIT that contains a defined column called NEW_CREDIT_LIMIT, a column calculated as 1.15 times the values in the column CREDIT_LIMIT.  You can carry over the NEW_CREDIT_LIMIT column to any new query, by including the CREDIT query (where the calculations were originally made) as a datasource.


About querying all rows

  Related Topics

To conserve computing resources when you run a query, Query Builder retrieves data in 64K blocks.  As you scroll through your data, Query Builder fetches additional blocks as needed.

The advantage of retrieving all rows is that all data resides in memory.  You can perform local sorts and apply break operations locally without rerunning the query.  The disadvantage of retrieving all rows is that it takes more time and computing resources to run the query and fetch all rows.


About automatic query execution

Automatic query execution enables you to run a query automatically any time you change the request for data, such as when you add a new column.  You can choose this option in the Preferences dialog box.

Automatic query execution means you do not need to rerun the query from the menu each time you make a change in the query.  However, it can be more time consuming if you are making several changes at once.


SQL conversion

  Related Topics

SQL users can export the SQL equivalent of any Query Builder query to a file for use in SQL*Plus, Report Builder, or a similar database application.  You can save any Query Builder query as a SQL query and open that file from within one of the other applications.

Note that Query Builder does not export column alias names created with the Print Title feature, but does export column aliases created as defined columns in the Define Column dialog box.

The SQL SELECT statement that is created when you save queries in those other applications can be imported into Query Builder and converted to a graphical query, using the Open dialog box.


Query naming conventions

  Restrictions 	  Related Topics

In Query Builder, names for database queries must follow the Oracle Server naming conventions:

•	may be no longer than 30 characters (except for database names, which are limited to 8 characters)

•	may not contain an apostrophe

•	is not case-sensitive

•	must begin with a letter

•	may only contain the characters A-Z, 0-9, _, $, and # (the use of $ and # is discouraged)

•	must not duplicate an Oracle reserved word

•	cannot have the same name as another object (such as a query, table, or view) in the same account

Note:  Any name within double quotes is acceptable.  If you enter a name that does not conform to the above conventions, Query Builder automatically adds double quotes around it.

Qualifiers

Query Builder uses standard Oracle methods to qualify table and column names by account and database.  In working with tables from your organization's database, you could find as many as three qualifiers in the table heading, each separated by a period or the @ symbol.

The following indicators apply:

•	A period to the left of a table name indicates that the table comes from an account other than the account that you are logged into.  The name to the left of the period is the name of that account.

•	The @ symbol in a table indicates that the table comes from a database other than your home database.  The name to the right of the @ symbol denotes the name of the database link you are using to connect to the remote database.

Note:  If you don't use Oracle naming conventions when naming database queries, Query Builder will convert the name to a "literal" value (enclosed in double quotes).


Query Builder reserved words

  Restrictions 	  Related Topics

The following words are reserved in the Oracle8 Server, Query Builder, and SQL.  You should not use them as names of queries or defined columns.

ACCESS	ADD	ALL
ALTER	AND	ANY
AS	ASC	AUDIT
BETWEEN	BY	CHAR
CHECK	CLUSTER	COLUMN
COMMENT	COMPRESS	CONNECT
CREATE	CURRENT	DATE
DECIMAL	DEFAULT	DELETE
DESC	DISTINCT	DROP
ELSE	EXCLUSIVE	EXISTS
FILE	FLOAT	FOR
FROM	GRANT	GROUP
HAVING	IDENTIFIED	IMMEDIATE
IN	INCREMENT	INDEX
INITIAL	INSERT	INTEGER
INTERSECT	INTO	IS
LEVEL	LIKE	LOCK
LONG	MAXEXTENTS	MINUS
MODE	MODIFY	NOAUDIT
NOCOMPRESS	NOT	NOWAIT
NULL	NUMBER	OF
OFFLINE	ON	ONLINE
OPTION	OR	ORDER
PCTFREE	PRIOR	PRIVILEGES
PUBLIC	RAW	RENAME
RESOURCE	REVOKE	ROW
ROWID	ROWLABEL	ROWNUM
ROWS	SELECT	SESSION
SET	SHARE	SIZE
SMALLINT	START	SUCCESSFUL
SYNONYM	SYSDATE	TABLE
THEN	TO	TRIGGER
UID	UNION	UNIQUE
UPDATE	USER	VALIDATE
VALUES	VARCHAR	VARCHAR2
VIEW	WHENEVER	WHERE
WITH
The following words are reserved in Query Builder and PL/SQL.  You should not use them as names of queries or defined columns.

ABORT	ACCEPT	ACCESS
ADD	ALL	ALTER
AND	ANY	ARRAY
ARRAYLEN	AS	ASC
ASSERT	ASSIGN	AT
AUTHORIZATION	AVG	BASE_TABLE
BEGIN	BETWEEN	BINARY_INTEGER
BODY	BOOLEAN	BY
CASE	CHAR	CHAR_BASE
CHECK	CLOSE	CLUSTER
CLUSTERS	COLAUTH	COLUMNS
COMMIT	COMPRESS	CONNECT
CONSTANT	COUNT	CRASH
CREATE	CURRENT	CURSOR
CURRVAL	DATABASE	DATA_BASE
DATE	DBA	DEBUGOFF
DEBUGON	DECLARE	DECIMAL
DEFAULT	DEFINITION	DELAY
DELETE	DELTA	DESC
DIGITS	DISPOSE	DISTINCT
DO	DROP	ELSE
ELSIF	END	ENTRY
EXCEPTION	EXCEPTION_INIT	EXISTS
EXIT	FALSE	FETCH
FLOAT	FOR	FORM
FROM	FUNCTION	GENERIC
GOTO	GRANT	GROUP
HAVING	IDENTIFIED	IF
IN	INDEX	INDEXES
INDICATOR	INSERT	INTEGER
INTERSECT	INTO	IS
LEVEL	LIKE	LIMITED
LOOP	MAX	MIN
MINUS	MLSLABEL	MOD
NATURAL	NEW	NEXTVAL
NOCOMPRESS	NOT	NULL
NUMBER	NUMBER_BASE	OF
ON	OPEN	OPTION
OR	ORDER	OTHERS
OUT	PACKAGE	PARTITION
PCTFREE	POSITIVE	PRAGMA
PRIOR	PRIVATE	PROCEDURE
PUBLIC	RAISE	RANGE
REAL	RECORD	RELEASE
REM	RENAME	RESOURCE
RETURN	REVERSE	REVOKE
ROLLBACK	ROWID	ROWLABEL
ROWNUM	ROWTYPE	RUN
SAVEPOINT	SCHEMA	SELECT
SEPARATE	SET	SIZE
SMALLINT	SPACE	SQL
SQLCODE	SQLERRM	START
STATEMENT	STDDEV	SUBTYPE
SUM	TABAUTH	TABLE
TABLES	TASK	TERMINATE
THEN	TO	TRUE
TYPE	UNION	UNIQUE
UPDATE	USE	VALUES
VARCHAR	VARCHAR2	VARIANCE
VIEW	VIEWS	WHEN
WHERE	WHILE	WITH
WORK	XOR


Restrictions

  Related Topics

The maximum number of tables and columns that you can select in a Query Builder query is 254.  Each column can contain up to 2000 characters.

Query Builder stores queries in the database or operating system up to a maximum file size of 64K.  If you try to save a query that is larger than 64K, an error message displays, and the query will not be saved.  (This should almost never occur.)  You can reduce the size of the query by eliminating any unused objects, such as joins, defined columns, and tables.


About opening queries

  Related Topics

Typically, a user maintains a set of predefined queries to run with little or no modification.  Query Builder enables you to maintain libraries of queries, or common query templates, so you do not have to create every query from scratch.  Query Builder also provides you with two places to store queries:  in the database, or as a file.

Importing SQL Statements

In addition to Query Builder's stored query files, SQL users can import any valid, supported SQL statement from a text editor or database product, such as a .sql file in SQL*Plus.  Valid means that the SQL statement contains no errors.  Supported means that the file contains only SELECT statements that can be used in a Query Builder query.

Opening Multiple Queries

You can open as many queries as your computer's operating system and memory will allow.  You can simultaneously open database queries, SQL statements, and file queries, or start new queries, in the same Query Builder session.

For each query, Query Builder opens a new Query window and Results window.  Each query is layered on top of the one before it.  Query Builder displays the name of the active query in the title bar.


About table and column comments

  Related Topics

Some details about the kind of data stored in the database (specified by the database administrator or the table owner) are not reflected in the table or column names.  This can sometimes make it difficult to decide which objects to include in your query.

In the CREDIT query, for example, you know all the names and datatypes for the CUSTOMER table, but you do not know if CUSTOMER contains only current customer records, both current and past customer records, or if it includes prospective, past, and current customers.

You can view existing comments on the selected table or column.  When you create a query, you can also enter your own comments, up to 254 characters in length.


About the Results window

  Related Topics

Query Builder displays the requested data in the Results window, which you can scroll through and manipulate with a mouse and the keyboard.  The results are displayed in columns and rows, much like a spreadsheet.  Here the columns appear horizontally, as you might expect.  The rows represent the actual data stored in each column.

The top row of the Results window contains column headings, which correspond to the names of columns selected for a query.  The leftmost column contains row numbers for retrieved data.  Each data value is enclosed in a cell.


Visual elements of the Results window

  Related Topics

The Results window contains scrollbars, gridlines, and panes that enable you to easily view your data.

You can view the gridlines in the Results window.

A pane is an independently scrollable division in the Results window.  You can also create or resize panes to enable you to view separate parts of the Results window at once.


About formatting columns

  Related Topics

You can perform a number of operations on the columns of data displayed in the Results window that will enhance the layout of your query results and improve reporting and analysis.  In the Format dialog box, you can adjust the column width to fit your data.

Note:  Columns you add to a query appear to the right of existing columns in the Results window.


About summary operations

  Related topics

You can set up operations that perform summary calculations on data in the Results window.  A summary operation may include:

•	average

•	total

•	minimum value

•	maximum value

•	count

•	standard deviation

•	variance

The type of summary operations you may perform varies by datatype.  For example, you cannot compute a standard deviation or variance on a character column, so those choices are not available for character columns in the Totals dialog box.

Summary Operations on Break Columns

You can perform summary operations on break columns, and there can be more than one summary calculation per break.  Query Builder displays each one separately in the Results window.  Summary operations are automatically applied to each break if they were previously selected for the entire query.

For example, suppose we used the Totals command to obtain the average CREDIT_LIMIT in the Summit Sporting Goods CREDIT query.  If we also use the Break command to create a break by STATE, Query Builder will display the average of each state's credit limit, in addition to the average of all credit limits, listed at the end.


About break columns

  Related Topics

A break is a set of rows grouped around a common value.  If you specify a break column in a query, Query Builder sorts the data according to the break column and displays the values for that column only in the rows where those values first appear (i.e., only when the value changes).

For example, you may want to create a list of employees broken out by department, but not repeat the department name on every row.  To display the department name only when the department changes, you would specify Department as the break column.

When you create a break column, Query Builder automatically moves the break column to the leftmost column in the Results window and displays the column heading in italics to indicate that it is a break column.

You can break by more than one column.  Query Builder performs the break operation in the order columns appear in the Break Columns list.  If you choose more than one break column, the break columns are positioned left-to-right according to their appearance top-to-bottom in the Break Columns list box.

When you remove a break in Query Builder, the column remains in the Results window, but data is no longer broken on that column.  When you remove one of several breaks, the affected column moves to the right so that it is positioned after all break columns.


About sorts

  Related Topics

Query Builder converts each sort into the appropriate ORDER BY clause, the SQL command used for sorting data.  Sort columns are listed in the ORDER BY clause shown in the Show SQL dialog box, and can be exported by choosing File [image] Save as SQL.

Query Builder implicitly generates an ORDER BY clause for each break column.  Break columns appear in the ORDER BY clause before sort columns.  If you specify a column as both a break column and a sort column, Query Builder generates an ORDER BY for the break column but not for the sort column.

For queries containing aggregate expressions, Query Builder also implicitly generates an ORDER BY for each column in the GROUP BY clause that is neither a break column nor a sort column.  ORDER BY clauses generated for GROUP BY columns are appended to the end of the ORDER BY clause.

Query Builder automatically creates a GROUP BY for all non-aggregate displayed columns whenever a defined column containing an aggregate function is displayed.  The GROUP BY clause appears in the Show SQL dialog box and in exported SQL statements.


About formatting query results

  Related Topics

A format is a control you apply in Query Builder to specify the way data appears in the Results window.  For example, you can apply a currency format to convert the display of numbers into dollars, pounds, or some other currency, and you can apply an uppercase format to display a character column in uppercase.

The following are the standard formats provided with Query Builder, which you can modify to create custom formats.

•	number

•	date

•	time

•	text

•	character

•	formats using SQL

Query Builder provides a variety of formats in the Format dialog box.  Unless you specify a format for each column in the Results window, Query Builder uses the GENERAL format type, which displays data in whatever format it is stored in the database.  You can also specify formats when you are defining columns and conditions using a function taking the type fmt.


Number format

  Related Topics

You can use number formats with any type of numeric data.  All number formats cause the number to be displayed rounded to the specified number of significant figures.  If a value has more digits to the left of the decimal place than are specified in the format, Query Builder displays pound signs (######).


Date format

  Related Topics

You can use date formats on any date data.  Some format elements specify a unit of time, while others add indicators and literal text.  Capitalization in a spelled-out word or abbreviation follows the capitalization in the corresponding format element.  For example, DAY in a format model produces capitalized words like MONDAY; Day produces mixed case Monday; day produces lower case monday.  The standard date format is DD-MON-YY.


Date format element prefixes and suffixes

  Related Topics

You can add this prefix to format elements in a date format:

Format	Description
FM	Fill mode.  This prefix suppresses blank padding for all subsequent character elements (such as MONTH) and suppresses leading zeroes for all subsequent number elements (such as MI) in a date format model.
	FM is a toggle.  If FM appears twice in a date format mode, blank padding and leading zeroes are suppressed for elements after the first FM and re-enable for elements after the second.
You can add these suffixes to format elements in a date format:

Format	Resulting Display
TH	Ordinal number (e.g., DDTH produces 4TH).
SP	Spelled-out number (e.g., DDSP produces FOUR).
SPTH or THSP	Spelled-out ordinal number (e.g. DDSPTH produces FOURTH).


Time format

  Related Topics

All elements specifying a unit of time can be used with the ROUND and TRUNC functions, except those representing units in seconds.  Year units round up on July 1.  Quarter units round up on the 16th day of the 2nd month.  Month units round up on the 16th day of the month.

Format	Resulting Display
SCC or CC	Century; S prefixes BC date with -.
YYYY or SYYYY	-digit year; S prefixes BC date with-.
YYY or YY or Y	Last 3, 2, or 1 digit(s) of year.
SYEAR or YEAR	Year, spelled out; S prefixes BC date with -.
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MONTH	Name of month, padded with blanks to length of 9 characters.
MON	Name of month, 3-letter abbreviation.
WW	Week of year (1-52) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first of the month and continues to the seventh of the month.
DDD	Day of year (1-366).
DD	Day of month (1-31).
D	Day of week (1-7).
DAY	Name of day, padded with blanks to length of 9 characters.
DY	Name of day, 3-letter abbreviation.
J	Julian day; the number of days since January 1, 4712 BC.
HH or HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).


Time format indicators

  Related Topics

The indicators that modify time formats are:

Format	Resulting Display
BC or AD	BC/AD indicator.
B.C. or A.D.	BC/AD indicator with periods.
AM or PM	Meridian indicator.
A.M. or P.M.	Meridian indicator with periods.


Text format

  Related Topics

The format elements used to add literal text are:

Format	Resulting Display
/ . , -	Punctuation is reproduced in the result.
...	Quoted string is reproduced in the result.


Character format

  Related Topics

The following default character format operations can be used on any character data.  You cannot modify these to create custom formats.

Format	Resulting Display
UPPERCASE	Force all letters to uppercase.
lowercase	Force all letters to lowercase.
Capitalize	Force the first letter in each word to uppercase, all others to lowercase


Number format elements affecting cell width

  Related Topics

Most format elements affect the width of the cell inside the results column.  For all formats except MI (display minus sign), the width of the element is added to the width of the cell.

Nines

The number of nines determines the display width.  To the left of the decimal point, leading zeros are suppressed.  To the right of the decimal point, trailing zeros are displayed.  A format of 9999 applied to 0 would return four blanks.  The following table shows how each format would affect the value 1234.5:

Format	Resulting Display
999	###
9999	1235
99999	1235
999999.99	1234.50
Zeros

The number of zeros determines the display width.  To the left of the decimal point, leading zeros are displayed.  To the right of the decimal point, trailing zeros are displayed.  A format of 0000 applied to 0 would return four zeros.  A format of 9990 would return 1 zero.  The following table shows how each format affects the value 1234.5:

Format	Resulting Display
000	###
0000	1235
00000	01235
00000.00	1234.50
Currency $

The following table shows how each prefix value with a dollar sign affects the value 1234.5:

Format	Resulting Display
$9999	$1235
$99999	$1235
$999999.99	$1234.50
Other

The following special number format codes are also supported by Query Builder.

Format	Resulting Display
MI	Display minus sign (-) after a negative value.  This element must be at the end of a format.  For example, the format 9999MI applied to -1234.5 returns 1235-.
PR	Display a negative value in <angle brackets>.  This element must be at the end of a format.  For example, the format 9999PR applied to -1234.5 returns <1234.5>.
,	Display a comma (,) in this position.  For example, the format 9,999 applied to 1234.5 returns 1,234.5.
.	Display a period (.) in this position.  For example, the format 9999.9 applied to 1234.5 returns 1234.5.
EEEE	Display in scientific notation.  For example, the format 9.999EEEE applied to 1234.5 returns 1.235E+03.
L	Display the defined international currency symbol in this position.  For example, if L is defined as the pound sterling symbol £, the format L9999 applied to 1234.5 returns £1235.
C	Returns the ISO currency symbol in this position.  For example, if C is defined as FF for French francs, C9999 applied to 1234.5 returns FF1235.
D	Display the defined decimal symbol in this position.  For example, if D is defined as a period (.), the format 9G999 applied to 1234 returns 1,234.


Number format elements not affecting cell width

  Related Topics

The following formats do not affect the width of the column.

Format	Resulting Display
B	Display zero value as blank, not 0.  (This is the default when using 9).
V	Multiply the value by 10 raised to the nth power, where n is the number of 9s or 0s after the V. For example, the format 99V99 applied to 12.345 returns 1235.


Formats using SQL

  Related Topics

To format cells to display hyphenated numbers, such as Social Security Numbers or Zip Codes, you must use a SQL command.  The datatype of the column can be either character or number.

To display a number as a Social Security Number, define a column in the appropriate table using the following syntax

substr(column_name,1,3)||'-'||substr(column_name,4,2)||'-'||substr(column_name,6,4)

To display a number as a Zip Code, define a column in the appropriate table using the following syntax:

substr(column_name,1,5)||'-'||substr(column_name,6,4)


About printer headers and footers

  Related Topics

Query Builder uses letter codes (each preceded by an ampersand) to format the information in your headers and footers and to insert variables, such as page numbers or the file (query) name.  You can use these codes in conjunction with any text you desire.

Use the Query Builder default settings or customize your own.  The default header inserts the date in the upper left corner of each page (&l&d) and centers the file or query name at the top of each page (&c&f).  The default footer centers the page number (&p) at the bottom.

Headers and footers can be a maximum of 256 characters in length.  Make sure that the number of lines in your header does not exceed the margin, as Query Builder suppresses the printing of headers and footers where margins are less than the minimum required.

To tailor the look of your document's headers and footers, simply type the codes and text you want into the Header: and Footer: fields.  Any changes you make are saved with your query, so they will remain in effect every time you open it (unless you change them again).


Codes in headers and footers

  Examples	  Related Topics

There are two types of codes you can type in the Page Setup dialog box for formatting headers and footers: those that affect how the header or footer information is displayed, and those that select system-generated variables, such as page numbers.  You will frequently use a combination of each, such as a format code followed by a variable.

The following codes position and format text and information in your header and footer.  You can use upper or lower case letters.

&l	left aligned
&c	center aligned
&r	right aligned
&b	bold
&i	italic
Type any text or variables you desire immediately after the format codes.  You can also combine codes (such as bold and center aligned) with variables and text.  To print the literal "&" type &&.

Any header/footer text or variable that follows &l appears at the left margin.  Text that follows &c appears in the center of the page, and text that follows &r appears at the right edge of the page.  Similarly, text or variables that follow &b or &i will appear bold or italic.

These codes select system-generated variables:

&p	page number
&d	date
&t	time
&f	file/query name
&n	line break


Printer code example
Printer code examples

To print the name of the query in the right top of the page, type in the Header box:

&r&f

To print the time in the lower left of the page, type in the Footer box:

&l&t

To print a department name (such as "Sales") in bold text at the top left of the page, in addition to the query name at the top right of the page, type in the Header box:

&l&bSales&r&f

To print the word "Page" in italics before the page number at the lower left of the page, type in the Footer box:

&L&IPage&P


About conditions

  Related Topics

In the Datasource panel, you specify which database objects (tables, views, queries, synonyms, and snapshots) to include in your query, and which columns to query from those tables.  If you run a query without further narrowing your focus, every row of data for the selected columns will be returned from the database.  This may not be practical or useful in all circumstances.

Conditions enable you to retrieve specific information from the database.  Conditions are fundamental to most queries, because often you will want to see only those records that meet certain specific criteria.  For example:

•	the monthly salary is greater than $2000."

•	the last name is Smith."

•	the job is not Manager."

A condition will return only those rows from the database that satisfy the condition.  You can also add compound or multiple conditions, such as:

•	the monthly salary is greater than $1000 and less than $2000

•	the monthly commission is greater than $2000 and the department is located in New York or California

Conditions can also use functions and defined columns:

•	the employee has worked over three years

•	the salary is greater than the average salary

Note:  There is no limit to the number of conditions you can include in a query.  You can add conditions to a query either before or after query execution.


About the Conditions panel

  Related Topics

The Conditions panel is a section of the Query window that contains the Condition field where you enter expressions and conditions.  Query Builder conditions automatically generate WHERE or HAVING clauses, as appropriate.

The table and column names indicate which columns will be filtered in the condition.  You may omit the table name if the column name is unique.  Tip

Note:  When you activate the Condition field, the cursor will have  a (+) sign, indicating that any item you click on will be added to your condition.  To deactivate this, you can click [image] in the Icon Bar.


Conditions panel keyboard commands

  Related Topics

The keyboard equivalents for menu commands appear in the menus beside the name of each command.

The following keyboard commands are not associated with menu commands:

TAB/SHIFT-TAB	Saves the condition and selects the next/previous valid condition regardless of nesting level.
LEFT ARROW	Moves the cursor to the left; selects the parent condition.
RIGHT ARROW	Moves the cursor to the right; selects the first child of a parent condition.
UP/DOWN-ARROW	Selects the previous/next condition within the current nesting level.
RETURN	Saves the condition and exits the Conditions panel.
SHIFT-RETURN	Saves the condition and moves the cursor down to create or edit the next condition.
CONTROL-ARROW	Moves the condition in the direction of the arrow key.


Condition syntax

  Examples	  Related Topics

The general format of a condition is:

[expression] [operator] [expression]

where

expression	specifies the values that define the condition; expressions can specify dates, characters, literal values, numbers, columns, functions, and database columns.
operator	specifies a comparison operator, such as greater than (>), less than (<), and equal to (=).  The most common operators are listed in the Data menu and the Icon Bar.  There are also a number of special comparison operators such as BETWEEN, LIKE, and IN.
Date values

When working with dates, the comparison operators refer to relative time.  So "less than" (<) means "before."

Date values must be enclosed in single quotes and specified in DD-MON-YY format, where DD represents the calendar day, MON represents the month, and YY represents the year.

Character values

With character columns, operators refer to alphanumeric order (A comes before B, so names that begin with A are "less than" names beginning with B, and so forth).

Literal values

Literal values must be enclosed in single quotes to distinguish them from column names.  Comparison between the database values and the values you specify are case-sensitive.

Number values

If the value you enter is a NUMBER datatype, type only the number.  That is, do not use formatting such as commas or the currency ($) symbol.  Numeric values in numeric columns do not need to be in quotes.

Note:  There are also three logical (Boolean) operators (AND, OR, NOT) that enable you to create multiple or negative conditions.

Additional Information:  For more information about Oracle syntax for various datatypes, refer to the Oracle8 Server SQL Language Reference Manual.


Condition syntax example
Condition syntax examples

To select employees whose salaries are greater than $3000:

SALARY > 3000

To select departments with ID number is less than 40:

DEPARTMENT_ID < 40

To select employees whose last name is Smith:

LAST_NAME = 'SMITH'

To select employees whose last names begin with M through Z and who were hired in 1986:

LAST_NAME > 'M' AND HIRE_DATE = 1986

Note:  The WHERE clause is implied in all conditions, so you do not need to specify it in the Conditions panel.


Condition validation

  Related Topics

Query Builder automatically validates a condition when you close the Condition field.  An incomplete, invalid, or incorrectly formatted condition generates a message indicating the error.  Query Builder then gives you the opportunity to correct your condition with the appropriate condition syntax.

Note:  You can temporarily deactivate a condition (or combination of conditions) so that you can test different scenarios without having to re-create the conditions each time.  To include or exclude nested conditions, you can highlight the logical operator rather than the individual conditions.


Multiple conditions

  Related Topics

Multiple conditions are always combined using logical operators.  You can create multiple conditions at many levels, or you can combine them all at one level.  The top-level operator represents the first level, and always appears at the left.  You can also combine conditions at lower levels, according to your needs.

By default, Query Builder combines multiple conditions with the AND operator.  To change the logical operator, click to highlight the operator and select a new one from the Icon Bar or the Data menu.

All conditions combined using AND must be true for your query to return results.  For example, if you combine FIRST_NAME='Pat' AND FIRST_NAME='Stacy', your query will not return any rows, because a person's first name would not be both Pat and Stacy.  To return rows where either condition may be true, use OR.


About nested conditions

  Examples	  Related Topics

Nested conditions combine logical operators to produce complex queries made up of multiple levels of conditions.


Nested conditions example

Suppose you want to see a list of customers who are from either New York or California, and who have credit limits below $6,000.  A single AND or OR won't work in this case because you are combining an AND condition with a subset that includes an OR condition.

In this case you would first type all three conditions:

STATE = 'CA'

STATE = 'NY

CREDIT_LIMIT < 6000

By default, they all appear at one level, combined using the AND operator.  To create the nested condition, you can select the STATE conditions and click the OR operator in the Icon Bar.


About operators

  Related Topics

An operator is a character or reserved word that is used to perform some operation.  The three principal types of operators used in Query Builder are:

•	comparison

•	arithmetic

•	logical

Note:  You can also create negating conditions based on these operators.


Comparison operators

  Related Topics

Comparison operators are used to compare two values.  When you insert the operator between a column name and a value in the Conditions panel, Query Builder will compare all items in that column against the value, and return only those rows where the condition statement is true

For example, to return only those rows where the value in the CREDIT_LIMIT column is less than $6,000, use the < (less than) operator:

CREDIT_LIMIT < 6000

The Query Builder comparison operators are:

Operator	Usage
=	Equal
<>	Not Equal
<	Less Than
<=	Less Than or Equal To
>	Greater Than
>=	Greater Than or Equal
BETWEEN...AND...	Between two values
IN (LIST)	Equal to any member of the following list
IS NULL	Is a NULL value.  A row without a value in one column is said to contain a NULL value.


Arithmetic operators

  Related Topics

Arithmetic operators are used to perform calculations on numeric and date columns.  In handling calculations, Query Builder first evaluates any multiplication or division, then evaluates any addition or subtraction.

•	For example, in the expression 1+2x3, first 2 is multiplied by 3, then the result is added to 1, yielding a final result of 7.

To override these precedence rules, place part of the expression in parentheses; Query Builder evaluates expressions in parentheses first.

•	For example, in the calculation (1+2)x3, (1+2) is evaluated first, then the result is multiplied by 3, yielding a final result of 9.

The Query Builder arithmetic operators are:

Operator	Usage
+	Addition
-	Subtraction
*	Multiplication
/	Division


Logical operators

  Related Topics

The logical (also called Boolean) operators are AND, OR, and NOT:

•	Use AND to retrieve only the data that meets all conditions.

•	Use OR to retrieve all data that meets at least one of the specified conditions.

•	Use NOT negate the operator(s) or condition(s).

The Query window Icon Bar includes icons for the logical operators.  The Icon Bar is active whenever the Condition field is active in the Conditions panel or when the Define Column dialog box is open.  You can insert an operator into the text of the condition or defined column by clicking the operator in the Icon Bar.


Top-level operators

  Related Topics

The principal or first operator used in combining conditions is called the top-level operator.  By default, Query Builder combines multiple conditions in a query with the AND operator, although this can be changed in the Preferences dialog box.


About negating conditions

  Related Topics

You can create a negating condition to negate any operator, combination of conditions, or nested conditions.  For example, NOT AND returns all rows except where all the conditions are met, and NOT OR returns all rows except where any of the conditions are met.

Negated operators (NOT AND, NOT OR) are displayed using an overscore.

For example, to review sales records showing customers from all states and having any credit limit, excluding data for California customers with credit limits higher than $7,000:  create both conditions, STATE='CA' AND CREDIT_LIMIT>7000, highlight the AND operator, and click NOT in the Icon Bar.  This query will return all rows except where the state is California and the credit limit is greater than $7,000.

Note:  You can also reverse a negating condition.


About functions in conditions

  Related Topics

A function is a short program that executes a predefined operation.  When you add the function's name, followed by the appropriate arguments, Query Builder carries out the specified operation (usually arithmetic or conversion).

Examples of functions are ROUND, which rounds numbers (or dates) to a specified precision, or the function MONTHS_BETWEEN, which calculates the months between values in two date columns.

The arguments in a function are usually columns in a query.  Specify the arguments by placing them in parentheses after the function name.

Commonly-used functions in Query Builder are:

Function	Example	Result
MOD	MOD(7,5)	The remainder of 7 divided by 5
SQRT	SQRT(25)	The square root of 25
ROUND	ROUND(CREDIT_LIMIT,2)	The column CREDIT_LIMIT is rounded to two digits after the decimal
TRUNC	TRUNC(CREDIT_LIMIT,2)	The column CREDIT_LIMIT is truncated to two digits after the decimal
ADD_MONTHS	ADD_MONTHS(HIRE-DATE,6)	Returns the date six months after HIRE-DATE
	ADD_MONTHS(HIRE-DATE,-6)	Returns the date six months before HIRE-DATE
LAST_DAY	LAST_DAY(HIREDATE)	Returns the last day of the month containing HIREDATE
SYSDATE	SYSDATE	Returns today's date and time
SUBSTR	SUBSTR(JOB,1,5)	Returns the first five characters of JOB values, such as MANAG, CLERK, SALES
GREATEST	GREATEST(SALARY,COMM)	Returns the larger value, SALARY or COMM
LEAST	LEAST(SALARY,COMM)	Returns the smaller value, SALARY or COMM


About function subqueries

  Examples	  Related Topics

A function subquery uses functions to create complex queries.  As with normal subqueries, a function subquery is a nested query that returns a single value to the parent query.  Rather than creating two separate queries, however, a function subquery is simply entered as the right side of an expression in the condition text of the parent query.


Function subqueries example

To create a list of all salespersons who earn a higher-than-average commission, type the following in the Conditions panel:

COMMISSION > AVG(COMMISSION)

When you run the query, Query Builder will first run the function subquery on the right side of the expression (in this example the function subquery will calculate the average commission of all salespeople).  Then the parent query will complete the condition (in this case returning the names of everyone who has a commission higher than that average).


Non-equal relationships as conditions

  Examples	  Related Topics

Tables are often joined by relationships, known in SQL as references or referential integrity constraints, that help enforce data integrity and identify primary/foreign key relationships.  Because Query Builder handles relationships graphically, all you need to know is that you can set them up between tables to identify how a column in one table relates to a column in another table.  Note

A non-equal relationship connects columns from two (or more) tables, based on a relationship other than equality.  Non-equal relationships are entered as conditions in the Conditions panel, or are created using the Set Relationship dialog box.  A non-equal relationship is commonly used.

Note:  If you create a simple non-equal relationship as a condition, where you are relating two tables using a single operator (such as <, >, or =), the condition is automatically converted to a non-equal relationship line and the conditions text disappears from the Conditions panel.  You can use the Set Relationship dialog box to remove or modify these relationships.


Non-equal relationships as conditions example

Consider a query that seeks the salary grade and the upper and lower bounds of each salary range for employees performing a specified job function.  You want to know which clerks are receiving salaries higher or lower than their salary grade range.

The SALARY_GRADE table contains the upper and lower salary ranges for each salary grade.  The JOB table contains the job ID and function, and the EMPLOYEE table contains the job ID for each employee.  The EMPLOYEE and JOB tables are connected by a relationship, but in this case there is no relationship line connecting the EMPLOYEE and SALARY_GRADE tables.

To create a report listing each employee, their salary, function, and the upper and lower bound of their salary grade, you need to specify the relationship between the Salary column in the EMPLOYEE table and the Upper and Lower Bound columns in the JOB table.

In this case, use the BETWEEN operator to select the salary that meets the condition (i.e., salaries that are not between the upper and lower bounds for the salary grade):

SALARY NOT BETWEEN LOWER_BOUND AND UPPER_BOUND

This produces a report that lists only those clerks whose salary falls outside the salary range for their salary grade.


About independent subqueries for ad hoc reporting

  Examples	  Related Topics

If you need to retrieve data on a one-time-only basis, the simplest way to handle the parent/subquery relationship may be to run separate queries.  First, you run the subquery to retrieve the value you need for the parent query, then run the parent query using that value.


Independent subqueries for ad hoc reporting example

Suppose you want to create a list of all employees with the same Job ID as Lynn Dennis, in the first query you select the Job ID column in the Employee table, and specify in the Conditions panel:

FIRSTNAME='DENNIS'

LASTNAME='LYNN'

This will return Lynn's Job ID, 671.

Now you can run a new query listing all employees who match Lynn's Job ID, using the condition Job_ID=671.

To create this report, you executed two queries--the first query retrieved the Job ID, while the second query retrieved the list of employees, based on the Job ID retrieved in the first query.  Running the subquery and parent query separately is useful for ad hoc reporting, and is probably the easiest way to handle one-time subqueries.


About SQL in subqueries

  Related Topics

SQL users can use a SELECT statement in the Conditions panel to create a subquery condition.  This runs both the subquery and the parent query at once, without having to save the subquery separately

Any SELECT statement must appear on one line, to the right of the operator in the subquery condition.  For example, to use SQL to answer the question: "Which customers have a lower credit limit than Rebound Sports?" you would enter the following condition:

CREDIT_LIMIT<(SELECT CREDIT_LIMIT FROM CUSTOMER WHERE NAME='REBOUND SPORTS')

In this example, the SELECT statement appears in parentheses, to the right of the operator.

Note: The maximum length of any individual condition is 256 characters.


About multiple relationship paths

  Related Topics

When you relate tables in a query, Query Builder chooses the shortest relationship path to relate all required tables.  If two or more paths are equal, Query Builder chooses the relationship path for those tables according to their order of insertion into the query.

You can deactivate any relationship in Query Builder by double-clicking on the active relationship line.  This is generally not recommended unless multiple relationship paths exist and you want to activate a relationship across an alternate relationship path.

You may need to exclude explicitly specific relationships to use the desired path in your query.  The following example *** do we need a graphic here?  shows relationships labeled 1, 2, 3 and 4, among tables labeled A, B, C and D.

The number 1 represents the relationship between A and B; 2 represents the relationship between B and D; 3 represents the relationship between B and C; and 4 represents the relationship between C and D.

1 and 2 are the shortest paths to relate A, B, and D.  But you can also relate the tables using relationships 1, 3, and 4.  The query results could vary depending upon which of the relationship paths you choose.

To use the path represented by 1, 3, and 4, you must explicitly deactivate relationship 2.


About Cartesian products

  Related Topics

When you deactivate a relationship (or non-equal relationship) that is the only path relating two tables, and run a query using columns from both sides of the relationship, the result is called a Cartesian product.

A query that generates a Cartesian product combines all rows from all included tables.  The number of rows that a Cartesian product returns is the product of the number of rows in the tables on each side of the relationship.  For example, if two tables each contain 100 rows, the resulting Cartesian product would be ten thousand rows.  The information retrieved from a Cartesian product is seldom useful.

If a table is included as part of a multiple-table query, and this table is not joined to another table through a database or user-defined equijoin (a type of join where the join condition is an equality, such as WHERE EMP.DEPTNO = DEPT.DEPTNO), Query Builder displays a warning.


About query recovery

  Related Topics

Changes in the underlying database can affect the state of any query.  You can use several different procedures to recover a query if it is altered by database changes.

Query recovery for modified query views

If the query view created by Query Builder is modified using another tool, then Query Builder retains all information that was present in the query view at the time the query was last saved.  However, the datasource changes type from query view to standard view.  The standard view retains the original name of the query.

Query Builder allows you to open the original query document, but you must save the query under a new name to resolve name duplication with the standard view.

The standard view with the old information remains in the database until you or your DBA remove it using SQL*Plus or another tool that supports the SQL DROP VIEW command.

Query recovery for modified tables, views, or synonyms

If any of the tables, views, or synonyms contained in a Query Builder query are modified in the database using SQL, you will be alerted when you try to open the query document.  Query Builder provides a warning message indicating that a datasource was changed.  You can recover your query if this happens.

If any columns, conditions, and/or defined columns were altered by the database changes, the affected portion of your query may no longer be valid.  For example, if a column in a table was deleted, you can no longer use that column in your query.  You must remove references to that column from your query.

Query recovery for a query used as a datasource

Your ability to use a query view as a datasource in another query may be affected if one of the tables, views, or synonyms it contains is modified in the database.

Query Builder allows you to include columns from the query view in a second query.  However, if Query Builder cannot resolve discrepancies between the old and modified version of the table, it returns a warning message when you try to run the second query.  If this message occurs, open the affected query document and recover your query.


About correlated subqueries

  Examples 	  Related Topics

A correlated subquery is a subquery that references columns in the tables included in its parent query.  It is executed repeatedly, once for each value specified in a break column.  In Query Builder, the break column is specified through an argument in the group function (AVG, SUM, etc.)

A correlated subquery is created in the same manner as a function subquery, except that in the correlated query, the break columns are included between the function's parentheses and must be separated by a comma.


Correlated subquery example

Suppose you include the following condition in your query:

salary<AVG(salary, department_id)

The first argument in the function contains the column that the function is being performed on (in this case SALARY).  The subsequent arguments refer to the columns that the query should break on (i.e., the columns whose values are grouped together for each iteration of the correlated query).  In this example, the function subquery will be performed for each department_id.


About subqueries

  Related Topics

You may need to retrieve data that depends on values from another query, referred to as the subquery.  The subquery is executed inside the main or parent query.

Subqueries can be run independently of parent queries, as is typical with ad hoc reporting, or they can be saved and combined with parent queries for more regular use.

Saving subqueries in parent queries is particularly useful in recurring reports if the actual value returned by the subquery changes over time, as with salary figures, account balances, and inventory counts.

In addition, subqueries can bring you the results of calculations.  For example, suppose you want to create a list of all salespersons who earn a higher-than-average commission.  Your subquery will calculate the average commission of all salespeople; the parent query will return the names of everyone who has a commission higher than that average amount.

Queries can be made up of multiple subqueries.  Subqueries can, in turn, be parent queries of other subqueries.


About Query Builder

  Related Topics

Query Builder is an easy-to-use data access tool designed for analysts, managers, and other business professionals.  It provides a logical and intuitive means to access information from your organization's databases for analysis and reporting.

Query Builder is designed for professionals who do not have a computer programming or database background.  Because of its powerful query features and its support of Structured Query Language (SQL) statements, experienced database users and programmers will find that Query Builder serves many of their needs as well.

In Query Builder's graphical Query window, you can specify a request for data from your organization's database(s).  The request for data is called a query.

You can use Query Builder to define almost any query that you would build using a SQL SELECT statement.  Query Builder automatically generates the appropriate SELECT FROM [table.column] clause based on columns displayed in the Datasource panel.  You can view the generated SQL in the Show SQL dialog box.

Data editing is performed through the Query Builder Data Editor.  This tool enables you to perform SQL DML (Data Manipulation Language) statements, such as INSERT, UPDATE, and DELETE, used to maintain the data in rows (records) of your database.


Databases and Query Builder

  Related Topics

Relational databases, such as your Oracle Server database, are made up of tables and other database objects.  These objects (views, synonyms, and snapshots) are based on tables.  Since these objects serve as sources for the data you retrieve through your query, they are called datasources.  In a Query Builder query, all of these objects behave just like tables, so they are referred to as tables.

Because Query Builder is designed for use with the Oracle database, it enables you to access your organization's data regardless of where the data is stored or the type of hardware or system storage used. The graphical representation of a table in Query Builder contains the table name and column names, but does not display any of the associated records.

While tables are the only database objects that actually store data in a database, there are other types of database objects that you can query just like a table.  For example, views are a kind of virtual table--an object that acts like a table when you execute a query, but is really a pointer to either a subset of a table, a combination of tables, or a join of two or more tables.

There are a number of database objects you can manipulate using Query Builder.  Since all datasources behave similarly in a query, you don't need to know anything about datasource types to build a query.  All you need to know is that datasources serve as the sources for your data, independent of the way the data is actually stored in your database.

When you build a database query using Query Builder, you use the mouse to select one or more columns from the table.  Then you run the query--you send your request for data to the database.  Query Builder retrieves the records corresponding to your column selection and displays them.


About manipulating data

  Related Topics

Selecting information and defining conditions are fundamental to most queries.  You can also:

•	sort query results

•	generate summary calculations (for example, average sales for the first quarter)

•	use data from different parts of the database (for example, compare first quarter sales to first quarter expenses)

•	perform calculations on the data to construct "what if" scenarios and forecasts (project revenue figures if sales increase by 15% for the third quarter)

•	change fonts and formats

•	print and save the retrieved data

•	export the data to other applications, such as Lotus 1-2-3 and Microsoft Excel

•	maintain a set of predefined queries for your more common reports and analyses, you won't have to create every query from scratch.


Elements of the user interface

  Related Topics

Click a user interface element.

Conditions panel

Data Editor

Datasource object notation

Datasource panel

Datatype icons

Icon Bar

Query window

Results window


About error messages

Each component has a unique error message prefix:

Prefix	Component
FRM	Form Builder
OG	Graphics Builder
PDE	Procedure Builder
REP	Report Builder
BRW	Query Builder
BRS	Schema Builder
NX, XBD	Translation Builder
Note:  On UNIX, Report Builder sets the value of the $status variable that appears in some messages to be 0 (successful completion) or 1 (unsuccessful completion).

In addition to the Forms Developer or Reports Developer error messages, you may encounter error messages from other sources:

Prefix	Source	Help
ORA	Oracle Server	Oracle8 Error Messages manual
PLS	PL/SQL	Oracle8 Error Messages manual
NET	SQL*Net	Oracle Network Products Messages manual
The Oracle8 Error Messages manual contains error message codes and descriptions for all database utilities such as SQL*DBA, SQL*Loader,SQL*Module,Export, Import, and the Oracle Precompilers.

System-specific error messages are documented in your operating system documentation.


Calling Oracle Support Services

Some errors (that is, internal errors) will require that you call Oracle Support Services to report the error.  When you call Support Services, please have the following information at hand:

1	Your name, company, and Oracle Support ID number.

2	The hardware, operating system, and release number of the operating system you are using.

3	The complete version number of the component you were using when you encountered the error, including revision number and port release number, if any.  (If you are unsure of the component's version number, choose Help [image] About.)

4	All other products (and version numbers) in use when you encountered the error (for example, Oracle 8.0.4).

5	If you encountered one or more error messages, the exact message numbers and descriptions, in the order they appeared.

6	A description of the problem, including any unusual conditions.

7	The problem severity, according to the following codes:

•	1:  Program not usable.  Critical impact on operations.

•	2:  Program usable.  Operations severely restricted.

•	3:  Program usable with limited functions.  Not critical to overall operations.

•	4:  Problem circumvented by customer.  Minimal effect, if any, on operations.


Abnormal conditions

Sometimes you may encounter an error that is known as an abnormal condition, which may arise from one of the following causes:

•	Someone has done something that prevented your application from completing the requested task.  For example, if someone bypassed the user interface and changed data in a database table, they might have typed an invalid value.

•	A software bug prevented the requested task from completing.

Action   First contact your system administrator to determine if the message was caused by something other than Forms Developer or Reports Developer.  If you determine that the message was not the result of user error or a system problem, then contact Oracle Support Services.


Query Builder messages

  Related Topics

Click a message range, then page using the browse buttons to find the message you are looking for.

BRW-15000 through BRW-15089

BRW-15090 through BRW-15149

BRW-15150 through BRW-15199

BRW-15200 through BRW-15399

BRW-15400 through BRW-15519

BRW-15520 through BRW-15569

BRW-15570 through BRW-15909

BRW-15910 through BRW-16549

BRW-16550 through BRW-16660


BRW-15000: Internal error in Oracle Query Builder error handler

Cause:	Error handler couldn't be started.

Action:	Contact Oracle Customer Support.


BRW-15001: <message number>

Cause:	The message file could not be found or is incompatible with this version of Query Builder.

Action:	Check the Query Builder directory in Oracle Home for the proper message file.


BRW-15002: Out of memory!

Cause:	Out of memory.

Action:	Save documents and restart Oracle Query Builder.


BRW-15003: Invalid file name: <name>

Cause:	User entered an invalid file name (for example, too many characters).

Action:	Enter a new, valid file name.


BRW-15004: Internal error (<value1>:<value2>)

Cause:	Internal Error Occurred--Oracle Query Builder is in an unreliable state.

Action:	Contact Oracle Customer Support.


BRW-15005: No file name specified

Cause:	User did not enter a filename in the Export Data dialog box.

Action:	Enter a filename.


BRW-15006: Unable to DESCRIBE data table

Cause:	Internal message.

Action:	Contact Oracle Customer Support.


BRW-15007: SQL statement is too complex to display

Cause:	Exceeded the edit buffer size on MSWINDOWS.

Action:	Exclude some columns from the query to reduce the SQL statement size.


BRW-15009: Insufficient privileges to log in through Oracle Query Builder

Cause:	Connect privileges are disabled in the SYSTEM.BROWSER_PROFILE table.

Action:	Contact your DBA.


BRW-15065: Syntax error

Cause:	User typed a formula incorrectly.

Action:	Re-type the expression.


BRW-15066: Expression is too complex

Cause:	Expression is too complex.

Action:	Simplify the expression.


BRW-15068: Invalid function: <name>

Cause:	User entered an invalid function name.

Action:	Specify a valid function.


BRW-15069: Invalid column: <name>

Cause:	User entered an invalid column name.

Action:	Enter a valid column name.


BRW-15070: Invalid table: <name>

Cause:	User entered an invalid table name.

Action:	Enter a valid table name.


BRW-15071: Invalid owner: <name>

Cause:	User entered an invalid user or business area name.

Action:	Enter a valid user or business area name.


BRW-15072: Invalid condition

Cause:	User entered an invalid condition.

Action:	Delete the condition and enter a valid condition.


BRW-15073: Invalid expression

Cause:	User entered an invalid expression (usually blank).

Action:	Enter a valid expression.


BRW-15074: Invalid use of reserved word <name>

Cause:	User used a reserved word as an identifier.

Action:	Identifiers cannot be reserved words. Refer to the help or documentation for details.


BRW-15075: Invalid number of arguments to function <name>

Cause:	User entered too few or too many arguments for the given function.

Action:	Enter the appropriate arguments. Refer to the help or documentation for details.


BRW-15077: Ambiguous name: <name>

Cause:	User entered an ambiguous column name.

Action:	Enter a specific column name. You may have to include qualifiers.


BRW-15078: Redefinition of name: <name>

Cause:	User redefined a column or data table using a name that already exists.

Action:	Define the column or table using a new name.


BRW-15079: No columns selected in query

Cause:	Query was either built or entered with no displayed columns.

Action:	Select columns before re-executing the query.


BRW-15080: No tables specified in query

Cause:	User entered a query without specifying any tables.

Action:	Select tables before re-executing the query.


BRW-15081: START WITH requires CONNECT BY

Cause:	SQL statement contained a START WITH clause without a CONNECT BY clause.

Action:	Specify a CONNECT BY clause if you intend to use START WITH.


BRW-15082: PRIOR requires CONNECT BY

Cause:	SQL statement contained a PRIOR clause without a CONNECT BY clause.

Action:	Specify a CONNECT BY clause if you intend to use PRIOR.


BRW-15083: Bad argument type to function <name>

Cause:	User entered the wrong argument type for the function.

Action:	Enter the correct argument type.


BRW-15084: Unterminated comment
BRW-15084: Unerminated comment

Cause:	Buffer being read in has an unerminated C-type comment in it.

Action:	Edit the file (see that it terminates correctly) and reopen from Query Builder.


BRW-15085: Circular reference detected in defined column

Cause:	Modification of a defined column has led to a circular reference.

Action:	Redefine the column so that it doesn't refer to itself in its own definition.


BRW-15086: Arguments to a correlated subquery function must be columns

Cause:	A complex argument was given, such as avg (sal, deptno*2).

Action:	Specify only valid columns, not expressions, in the subquery.


BRW-15087: Invalid location: <name>

Cause:	User entered an invalid database location for a table.

Action:	Enter a valid database location.


BRW-15088: Ambiguous table: <name>

Cause:	User entered an ambiguous data table name (more than one table uses this name).

Action:	User must further qualify the data table. Refer to the help or documentation for details.


BRW-15089: Only one PRIOR operator allowed on an expression

Cause:	User typed 2 or more PRIOR clauses in an expression.

Action:	Remove one of the PRIOR clauses.


BRW-15090: The name <name> is already in use

Cause:	User entered a name that was already being used.

Action:	Enter a new name.


BRW-15091: Invalid name: <name>

Cause:	User entered an invalid name (with spaces or unprintable chars).

Action:	Enter a new name.


BRW-15092: Bad usage of outer relationship

Cause:	User specified an outer relationship on a non-column element.

Action:	Outer relationships are only valid for columns.


BRW-15093: Invalid number of elements in corresponding record

Cause:	User entered two records with different numbers of elements.

Action:	Re-enter the condition.


BRW-15094: Group function is nested too deeply

Cause:	User entered a double(triple) aggregate condition(expression).

Action:	Simplify aggregate condition/expression.


BRW-15096: Table name conflicts with <name1>, renamed to <name2>

Cause:	When opening a query, a table alias name conflicts with a new column.

Action:	User can rename the alias.


BRW-15097: Name conflicts with <name1>, renamed to <name2>

Cause:	When loading a saved query, an alias name conflicts with a new column.

Action:	Rename the alias.


BRW-15098: Duplicate table <name> in FROM clause

Cause:	Data table appears more than once in FROM clause (without an alias).

Action:	Remove the duplicate data tables in the FROM clause.


BRW-15099: Subquery returns incorrect number of values

Cause:	Subquery returns either too many or too few values.

Action:	Re-enter the condition.


BRW-15100: Correlated expressions are not allowed here

Cause:	User entered a correlated expression outside a condition.

Action:	Re-enter the condition/expression.


BRW-15101: Table alias cannot be reused in the same FROM clause: <name>

Cause:	User tried to use an alias in the same FROM clause it was defined in.

Action:	Remove the reference to the alias.


BRW-15102: Too many elements in record

Cause:	User entered a record with more than 254 elements.

Action:	Reduce the number of elements--split the condition into an OR.


BRW-15103: Unnamed SELECT statement in FROM clause

Cause:	SQL contained a query in the FROM clause without name.

Action:	Add the query name and reparse.


BRW-15110: Invalid number or precision exceeded: <column name>

Cause:	Non-numeric input or the number exceeded this column's precision.

Action:	Re-enter a valid number and precision.


BRW-15111: Invalid date: <value>

Cause:	Couldn't format using the columns format mask or either default mask.

Action:	Re-enter a valid format mask.


BRW-15112: Entry exceeds the size limit for this column: <##>

Cause:	User entered a string longer than the limit for this column.

Action:	Enter a shorter string.


BRW-15113: Not all parameters have been specified

Cause:	User attempted to execute a query without defining all parameters

Action:	Specify parameters or exclude parameterized expressions and retry.


BRW-15114: Parameters can only be constant expressions

Cause:	User attempted to enter a parameter value that depended on a column value.

Action:	Specify a valid parameter, one that does not depend on the value in a column.


BRW-151BRW-15: Bad usage of parameter <name>
BRW-15115: Bad usage of parameter <name>

Cause:	User entered a parameter name for a column or table.

Action:	Specify a valid parameter.


BRW-15116: Invalid parameter name <name>

Cause:	User entered an invalid parameter name.

Action:	Specify a valid parameter name.


BRW-15117: Invalid parameter <name>

Cause:	User entered an invalid parameter.

Action:	Specify valid parameter.


BRW-15128: Parser internal error: <value>

Cause:	Internal error in the parser/scanner.

Action:	Contact Oracle Customer Support.


BRW-15129: Defined column <name> cannot be used in correlated subquery function

Cause:	Use of derived columns (DQ4) in correlated subquery.

Action:	Remove that argument from the correlated subquery.


BRW-15130: Query execution failed

Cause:	Database access error.

Action:	Check database error and rerun query.


BRW-15131: Invalid relational operator

Cause:	User applied an invalid operator to a condition.

Action:	Enter a valid operator.


BRW-15132: FROM clause was not found where expected

Cause:	Encountered no FROM clause or GROUP/HAVING clause before a FROM clause.

Action:	Edit SQL statement.


BRW-15133: FROM clause was found without corresponding SELECT clause

Cause:	Encountered FROM clause before SELECT clause.

Action:	Edit SQL statement.


BRW-15135: Condition cannot be changed

Cause:	User attempted to change the top condition in Schema Editor.

Action:	Perform the operation on the children conditions.


BRW-15136: SQL contains set operators

Cause:	Set operations are not supported.

Action:	Run the query in SQL*Plus.


BRW-15137: Unable to generate names for complex expressions

Cause:	Encountered expressions without names when opening a SQL file.

Action:	Define Names for columns in the .sql file.


BRW-15138: CONNECT BY clause existed, and was ignored

Cause:	Connect By is not supported at this (top) level.

Action:	Run the query in SQL*Plus, if results of CONNECT BY were desired.


BRW-15139: Cannot ungroup condition

Cause:	Attempt to ungroup top condition.

Action:	This operation is not supported.


BRW-15141: Query table cannot contain parameters

Cause:	Encountered one or more parameters when creating a view.

Action:	Exclude parameters from query or ignore.


BRW-15142: Two or more columns have the same name: <name>

Cause:	Encountered duplicate column names when creating view.

Action:	Use the Define command to rename duplicate columns.


BRW-15143: <name> is already a display column

Cause:	User tried to specify a duplicate display column.

Action:	If duplicate column is desired, define a new name for it and re-display it.


BRW-15144: <name> is already a sort column

Cause:	User tried to specify a duplicate sort column.

Action:	If duplicate column is desired, define a new name for it and re-sort.


BRW-15145: <name> is already a group column

Cause:	User tried to specify a duplicate group column.

Action:	If duplicate column is desired, define a new name for it and re-group.


BRW-15146: Internal error in opening query

Cause:	Internal error in opening query.

Action:	Contact Oracle Customer Support.


BRW-15147: Internal error in saving query

Cause:	Internal error in saving query.

Action:	Contact Oracle Customer Support.


BRW-15148: Relationship must be between compatible data types/size

Cause:	User tried to relate two columns with incompatible data types.

Action:	Relate only compatible datatypes. Refer to the help or documentation for details.


BRW-15150: Cannot define a self-relationship

Cause:	User tried to define a relationship within a data table.

Action:	Alias the data table and then define the relationship.


BRW-15151: User-defined relationship already exists

Cause:	User tried to define a relationship that already exists.

Action:	No action required.


BRW-15152: Illegal column in defined relationship

Cause:	User specified an illegal expression when defining a relationship.

Action:	Retype in a new column.


BRW-15153: Table alias name cannot end with _A<number>

Cause:	Illegal alias name.

Action:	Input a new name that does not end with _A<number>.


BRW-15154: Cannot remove database relationship

Cause:	User tried to remove data dictionary relationship.

Action:	Only user-defined relationships can be deleted.


BRW-15155: The following documents may have been affected:<name>

Cause:	A saved query was being used as a data table in other query documents.

Action:	If you change or delete this query, you will affect these other queries.


BRW-15156: Cannot include table with the same name as the query

Cause:	User attempted to include data table D in query document D.

Action:	Rename the query before including this table.


BRW-15157: Cannot sort, group, or break on LONG columns

Cause:	Trying to sort, group or break on LONG column type.

Action:	Cancel the operation. Oracle does not support this.


BRW-15158: Cannot sort, group, or break on RAW columns

Cause:	Trying to sort, group or break on RAW column type.

Action:	Cancel the operation. Oracle does not support this.


BRW-15159: Cannot display LONG columns from remote databases

Cause:	Trying to include LONG column type from remote database data table.

Action:	Cancel the operation. Oracle does not support this.


BRW-15160: Maximum number <##> of columns exceeded

Cause:	User exceeded maximum number of display/group/sort columns supported.

Action:	Delete some unwanted columns, then re-include what you need.


BRW-15161: Error in opening <column name> display column(s)

Cause:	Unable to open DISPLAY column because database has changed since save.

Action:	This column may have changed or no longer exists in the database.


BRW-15162: Error in opening <column name> sort column(s)

Cause:	Unable to open SORT column because database has changed since save.

Action:	This column may have changed or no longer exist in the database.


BRW-15163: Error in opening <column name> group column(s)

Cause:	Unable to open GROUP column because database has changed since save.

Action:	This column may have changed or no longer exist in the database.


BRW-15164: Error in opening <column name> break column(s)

Cause:	Unable to open BREAK column because database has changed since save.

Action:	This column may have changed or no longer exist in the database.


BRW-15165: Maximum number <##> of break columns exceeded

Cause:	User exceeded the maximum number of break columns supported.

Action:	Delete some unwanted break columns, then re-include what you need.


BRW-15166: Error in opening <value> condition(s)

Cause:	Unable to open conditions because database has changed since save.

Action:	Tables or columns used in this condition may no longer exist in the database.


BRW-15167: Alias must be <##> bytes or less: <name>

Cause:	Alias name too long.

Action:	Try typing in an alias less than or equal to 30 bytes long.


BRW-15168: Cannot group on aggregate expression

Cause:	Tried to group on an aggregate expression.

Action:	Group on another expression.


BRW-15169: <name> has already been included as a break column

Cause:	User tried to specify a duplicate break column.

Action:	If duplicate column is desired, define a new name for it and re-break.


BRW-15170: Invalid Constraint for table <name>

Cause:	User entered an invalid constraint for the selected table.

Action:	Reenter constraint.


BRW-15171: Invalid Constraint for table column <name>

Cause:	User entered an invalid constraint for the selected column.

Action:	Reenter constraint.


BRW-15172: Subqueries are not allowed in Constraints

Cause:	User entered an invalid constraint.

Action:	Reenter constraint.


BRW-15173: Aggregate conditions are not allowed in Constraints

Cause:	User entered an invalid constraint.

Action:	Reenter constraint.


BRW-15174: Constraints can only be on one table

Cause:	User entered an invalid constraint.

Action:	Reenter constraint.


BRW-15175: Table not loaded because all columns are hidden

Cause:	All columns are hidden. At least one column needs to be displayed.

Action:	Use the Query Builder Administrator Utility to display hidden columns.


BRW-15188: Query returns 1 row

Cause:	Status message for 1-row Query Count.

Action:	No action required.


BRW-15189: Query returns <##> rows

Cause:	Status message for n-row Query Count.

Action:	No action required.


BRW-15190: Unable to count without executing; Query returns <= <##> row(s)

Cause:	Status message for n-row Query Count Distinct.

Action:	No action required.


BRW-15192: Illegal format <name1> for column '<name2>'

Cause:	User typed an unrecognizable format for a display column.

Action:	Try another format.


BRW-15193: Illegal format

Cause:	User typed an unrecognizable format for a display column

Action:	Try another format.


BRW-15195: Incompatible column type in relationship: <name>

Cause:	Trying to create illegal relationship.

Action:	Column types are incompatible. Refer to the help or documentation for details.


BRW-15196: Error in opening <name> table(s)

Cause:	Unable to open data tables because database was modified or object doesn't exist.

Action:	Check your connect string (or contact your DBA).


BRW-15197: Error in restoring position for <name> column(s)

Cause:	Unable to reposition column because database was modified or object doesn't exist.

Action:	Check your connect string (or contact your DBA).


BRW-15198: Error in restoring join: <value>

Cause:	Unable to restore link during open because database was modified or object doesn't exist.

Action:	Check your connect string (or contact your DBA).


BRW-15199: Error in restoring inclusion state for join: <value>

Cause:	Unable to set the inclusion state during open because database was modified or object doesn't exist.

Action:	Check your connect string (or contact your DBA).


BRW-15200: Error in restoring outer join state for join: <value>

Cause:	Unable to set the outer join during open because database was modified or object doesn't exist.

Action:	Check your connect string (or contact your DBA).


BRW-15258: Invalid document name: <name>

Cause:	User entered an invalid document name.

Action:	Enter a valid document name.


BRW-15259: Document exceeds 64K size limit

Cause:	User attempted to save/open a document larger than 64K.

Action:	Reduce size of document.


BRW-15260: Document <name> does not exist

Cause:	User specified a nonexistent document.

Action:	Specify another document.


BRW-15261: Document <name> already exists

Cause:	User tried to save under the name of an existing document.

Action:	Specify another document name or delete the existing document.


BRW-15262: Multiple documents named <name>

Cause:	Multiple documents with same name exist.

Action:	Delete all but one.


BRW-15263: Database object <name> already exists

Cause:	User tried to save under the name of an existing object.

Action:	Specify another document name.


BRW-15264: Table <name> does not exist

Cause:	View corresponding to a saved document has been removed.

Action:	Re-save the document (it no longer exists as a query view).


BRW-15265: Table <name> has been removed or modified

Cause:	Data Table view corresponding to a saved document has been modified.

Action:	Re-save the document.


BRW-15266: File <name> does not exist

Cause:	User specified a nonexistent file.

Action:	Specify another file.


BRW-15267: File <name> already exists

Cause:	User tried to save under the name of an existing file.

Action:	Specify another file name.


BRW-15268: Unable to open file <name>

Cause:	I/O core was unable to open a file.

Action:	Specify another file.


BRW-15269: Unable to lock file <name>

Cause:	I/O core was unable to lock a file.

Action:	Specify another file.


BRW-15270: Error reading file <name>

Cause:	Operating system error or file was not of the correct format.

Action:	Specify another file.


BRW-15271: Error writing file <name>

Cause:	Operating system error or I/O core error.

Action:	Retry operation.


BRW-15272: Obsolete Oracle Query Builder document <name>

Cause:	User attempted to open an old unsupported document.

Action:	Specify another document.


BRW-15273: File <name> is not a Oracle Query Builder file

Cause:	User attempted to open a non-Query Builder file.

Action:	Specify another file to open.


BRW-15274: File <name> is not a valid SQL file

Cause:	User attempted to import an invalid SQL file.

Action:	Specify another file to import.


BRW-15275: No data to export or print

Cause:	User attempted to export or print a query (or region) with no data.

Action:	Specify another region; execute query; change query.


BRW-15277: Unable to overwrite current table <name>

Cause:	Error in trying to delete/rename old data table in save.

Action:	Fix problem with data table and re-save.


BRW-15278: Document saved, but view <name> could not be created

Cause:	The query is not a valid view. It has been saved, but not as a database view.

Action:	Redefine query as a valid view, or ignore (query will be saved, but not as a view).


BRW-15279: '<name>' is not a recognized SQL literal

Cause:	User attempted to import a non-SQL*Plus file.

Action:	Specify another file to import.


BRW-15320: Internal I/O core error (<value>, line <##>)

Cause:	Internal State is invalid.

Action:	Contact Oracle Customer Support.


BRW-15321: Resource not found (<name>).

Cause:	Resource names do not match Query Builder code (or file is corrupted).

Action:	Get a correct version of the Query Builder resource file.


BRW-15322: Resource file not found.

Cause:	directory structure incorrect, or file is missing for some reason.

Action:	Locate the resource file (check NLS_LANG file name).


BRW-15323: Query already open in current application (<name>)

Cause:	User attempted to open multiple queries with the same name.

Action:	Specify another file.


BRW-15324: Unable to start up windowing system

Cause:	Incorrect/missing environment variables (e.g. DISPLAY, APIPATH).

Action:	All required environment variables must be set correctly.


BRW-15342: Unable to find snapshot information

Cause:	This database does not support snapshots (or database error).

Action:	Check that you have a snapshot dictionary table.


BRW-15387: Login failed

Cause:	Database or SQLNet error occurred.

Action:	Check the login information and try again.


BRW-15388: Unable to load database relationships

Cause:	Schema internal error in loading database relationships.

Action:	Contact Oracle Customer Support if this is not expected.


BRW-15389: Unable to load list of accessible owners

Cause:	Schema internal error in loading list of accessible owners.

Action:	Contact Oracle Customer Support if this is not expected.


BRW-15390: Unable to load list of accessible tables

Cause:	Schema internal error in loading list of data tables.

Action:	Contact Oracle Customer Support if this is not expected.


BRW-15391: Unable to load table

Cause:	Schema internal error in loading data table.

Action:	Contact Oracle Customer Support if this is not expected.


BRW-15392: Unable to load table

Cause:	Schema internal error in loading table.

Action:	Contact Oracle Customer Support if this is not expected.


BRW-15393: Unable to load synonym

Cause:	Schema internal error in loading synonym.

Action:	Contact Oracle Customer Support if this is not expected.


BRW-15394: Unable to load query

Cause:	Schema internal error in loading query.

Action:	Contact Oracle Customer Support if this is not expected.


BRW-15395: Unable to load table column information

Cause:	Schema internal error in loading column information.

Action:	Contact Oracle Customer Support if this is not expected.


BRW-15396: Unable to load synonym column information

Cause:	Schema internal error in loading synonym column information.

Action:	Contact Oracle Customer Support if this is not expected.


BRW-15397: Unable to load constraints

Cause:	Schema internal error in loading constraints.

Action:	Contact Oracle Customer Support if this is not expected.


BRW-15398: Unable to find primary key information

Cause:	Schema internal error in finding primary keys.

Action:	Contact Oracle Customer Support if this is not expected.


BRW-15399: Unable to find foreign key information

Cause:	Schema internal error in finding foreign keys.

Action:	Contact Oracle Customer Support if this is not expected.


BRW-15400: Unable to load table and column comments

Cause:	Schema internal error in getting comments.

Action:	Contact Oracle Customer Support if this is not expected.


BRW-15401: Error in loading database link <name>

Cause:	Schema internal error in loading database link.

Action:	Refer to accompanying error messages for more information.


BRW-15402: Error in loading owner <name>

Cause:	Schema internal error in loading database link.

Action:	Refer to accompanying error messages for more information.


BRW-15403: Error in loading table <name>

Cause:	Schema internal error in loading data table.

Action:	Refer to accompanying error messages for more information.


BRW-15404: Error in loading primary key constraint for <name> (<value>)

Cause:	Schema internal error in loading primary constraint.

Action:	Refer to accompanying error messages for more information.


BRW-15405: Error in loading foreign key constraint <name>

Cause:	Schema internal error in loading foreign constraint.

Action:	Refer to accompanying error messages for more information.


BRW-15406: Error in loading synonym <name>

Cause:	Schema internal error in loading primary constraint.

Action:	Refer to accompanying error messages for more information.


BRW-15407: Table referred to by synonym <name> no longer exists

Cause:	User tried to create a synonym for a nonexistent data table.

Action:	Recreate the synonym.


BRW-15408: Error in loading column information for <name>

Cause:	Oracle error or the data table has been removed.

Action:	Recreate the data table.


BRW-15409: Error in loading constraint information for <name> (<value>)

Cause:	Oracle error.

Action:	Try it again.


BRW-15410: Table that foreign key <name> (<value>) refers to is inaccessible

Cause:	The table that the foreign key refers to is inaccessible.

Action:	Grant the table to the current user.


BRW-15411: Logout failed

Cause:	Internal error in logging out.

Action:	Exit or close the document.


BRW-15412: Table of query <name> was modified. Query loaded as view.

Cause:	The view that the query depends on has been modified after the last save.

Action:	Open the query and save it again to use it as a data table.


BRW-15413: Table of query <name> was removed. Query will not be loaded.

Cause:	The view that the query depends on has been removed.

Action:	Open the query, and save it again to use it as a data table.


BRW-15414: Table <name> no longer exists

Cause:	This data table no longer exists in the database.

Action:	Contact system administrator if you need this table.


BRW-154BRW-15: Maximum number (254) of table columns exceeded
BRW-15415: Maximum number (254) of table columns exceeded

Cause:	User tried to load a data table that has more than 254 columns.

Action:	Contact system administrator.


BRW-15416: Maximum number (8192) of tables per user exceeded

Cause:	User owns too many data tables.

Action:	Contact system administrator.


BRW-15417: Maximum number (1024) of owners and databases exceeded

Cause:	User tried to load too many user, owner, and database links.

Action:	Contact system administrator.


BRW-15418: Unable to find database link information

Cause:	This database does not support database link, or database error.

Action:	Try again later if this is unexpected.


BRW-15419: Unable to find synonym information

Cause:	This database does not support synonyms, or database error.

Action:	Try again later if this is unexpected.


BRW-15420: Unable to find constraint information

Cause:	This database does not support constraints, or database error.

Action:	Try again later if this is unexpected.


BRW-15421: Unable to find table comment table

Cause:	This database does not support comments on table, or database error.

Action:	Try again later if this is unexpected.


BRW-15422: Unable to find table Column comments table

Cause:	This database does not support comments on column, or database error.

Action:	Try again later if this is unexpected.


BRW-15423: Synonym to SEQUENCE is not currently supported

Cause:	User tried to load a synonym that refers to a sequence.

Action:	This action is not supported.


BRW-15424: Maximum number of login attempts (3) exceeded

Cause:	User tried to login too many times.

Action:	Contact system administrator for correct username, password, and/or connect string.


BRW-15430: Index information not loaded

Cause:	Cannot find index table.

Action:	This is a non-Oracle database; try an ODBC driver.


BRW-15434: Maximum columns in table is <##>

Cause:	User tried to create more than the maximum number of allowed columns in a table.

Action:	Create fewer columns.


BRW-15435: DBA privilege is needed to perform this operation

Cause:	Need DBA privileges.

Action:	Ask your system administrator to grant you DBA privilege.


BRW-15447: Cannot remove Query. Use File Delete... from Query window

Cause:	User tried to remove a query.

Action:	This is not legal from the Schema Editor. Use the Query window instead.


BRW-15448: Cannot copy Query. Use File Save As... from Query window

Cause:	User tried to copy a query.

Action:	This is not legal from the Schema Editor. Use the Query window instead.


BRW-15449: Cannot copy with data from table with only LONG columns

Cause:	User tried to copy a table with a LONG column.

Action:	Database doesn't support this.


BRW-15450: Operation not allowed on removed table

Cause:	User tried to do something to a removed data table.

Action:	Undo the remove first.


BRW-15451: Operation not allowed on removed column

Cause:	User tried to do something to a removed column.

Action:	Undo the remove first.


BRW-15452: Cannot rename another owner's table

Cause:	User tried to set the name of a granted data table.

Action:	This is not allowed. Ask the owner to do it for you.


BRW-15466: Table name too long (Max <##> bytes)

Cause:	Data Table name to long.

Action:	Shorten the name and try again.


BRW-15467: Name already in use. Please choose another name.

Cause:	Name already in use.

Action:	Choose another name.


BRW-15468: Column name too long (Max <##> bytes)

Cause:	Column name too long.

Action:	Shorten the name and try again.


BRW-15493: Operation currently not supported

Cause:	Internal error.

Action:	Contact Oracle Customer Support.


BRW-15494: Error in loading SQL text of views

Cause:	Internal error.

Action:	Contact Oracle Customer Support.


BRW-15495: Error in loading SQL text for view <name>

Cause:	Internal error.

Action:	Contact Oracle Customer Support.


BRW-15496: Scale must be between <##> and <##>

Cause:	Illegal value for scale.

Action:	Input legal value for scale.


BRW-15498: Cannot modify a query

Cause:	It is not legal to modify a query.

Action:	Open the query and modify it as a Query Builder document.


BRW-15499: Cannot set column attributes of table duplicated with data

Cause:	User tried to set the column type of a table duplicated with data.

Action:	First commit the table then try again.


BRW-15501: No more application virtual memory

Cause:	No more disk space or system memory for Virtual Memory Manager.

Action:	Close a document or make some room on your disk.


BRW-15502: Can only have <##> rows per document

Cause:	Exceeded self-imposed, compile-time row maximum.

Action:	Consider adding conditions to the query.


BRW-15504: Unable to determine database character set

Cause:	There was a problem with SELECT USERENV('LANGUAGE') FROM DUAL.

Action:	Non-Oracle database--not supported.


BRW-15505: Unable to open virtual memory file

Cause:	No write permission or file system full.

Action:	Change permission on directory or delete some files.


BRW-15506: Unable to close virtual memory file

Cause:	Operating system error.

Action:	Ask system administrator for assistance.


BRW-15507: Unable to read from virtual memory file

Cause:	Virtual memory file does not exist.

Action:	Make sure the virtual memory file is opened.


BRW-15508: Unable to write to virtual memory file

Cause:	File system is full, or virtual memory file was not opened.

Action:	Delete some files. Make sure virtual memory file is opened.


BRW-15509: Unable to create virtual memory manager

Cause:	Internal error, or out of memory.

Action:	Ask system administrator for assistance.


BRW-15511: Maximum virtual memory size exceeded

Cause:	User input is too big for virtual memory size.

Action:	Try again.


BRW-15513: Null value in command '<name>'

Cause:	User entered <command>= on the command line with no option selected.

Action:	Add a value for <command>.


BRW-15514: Null command in command line

Cause:	User entered =<value> at command line but didn't specify a command.

Action:	Add the command.


BRW-155BRW-15: Unrecognized command '<name>' in command line
BRW-15515: Unrecognized command '<name>' in command line

Cause:	User entered an illegal command at command line.

Action:	Change command.


BRW-15516: Command '<name>' specified twice in command line

Cause:	User entered a command twice at command line.

Action:	Remove one.


BRW-15517: Incompatible commands '<name1>' and '<name2>' specified

Cause:	User entered 2 incompatible commands at command line.

Action:	Remove one.


BRW-15518: Invalid option '<name1>' in command '<name2>'

Cause:	User entered an illegal option to a command at command line.

Action:	Change option.


BRW-15519: Multiple login specifications in command line

Cause:	User entered 2 login specifications at command line.

Action:	Remove one.


BRW-15520: Invalid document name '<name>' in command line

Cause:	User entered an illegal document name at command line.

Action:	Change document name.


BRW-15521: '<name>' command specified without a document

Cause:	User entered a command without specifying a document at command line.

Action:	Add missing document.


BRW-15522: '<name1>' command specified without '<name2>'

Cause:	User entered a command without another required command.

Action:	Add missing command.


BRW-15523: Mismatched parentheses in '<name>' command

Cause:	User entered mismatched parentheses in the command line.

Action:	Correct mismatched parentheses.


BRW-15530: Cannot edit a deleted row

Cause:	User tried to edit a row which is marked for deletion.

Action:	Undelete the row first.


BRW-15531: Row has been deleted from the database

Cause:	Another user deleted this row since you last ran the query.

Action:	Contact Oracle Customer Support. This should never happen.


BRW-15532: Row has been modified by another user. Requery.

Cause:	Another user updated this row since you last ran the query.

Action:	Try again.


BRW-15533: Row is currently locked by another user.

Cause:	Someone else is editing this row.

Action:	Try again later.


BRW-15539: Error in inserting <##> row(s)

Cause:	One or more rows in input file failed on insert.

Action:	Modify input file or ignore.


BRW-15540: Operation not allowed on data of this type

Cause:	User tried to edit a cell with data of an uneditable type.

Action:	Unknown.


BRW-15541: Too many edited rows. Please select Commit

Cause:	Too many pending INSERTS, UPDATES or DELETES in a document.

Action:	Select Commit or Revert.


BRW-15545: Error in reading QXF file. Parsing halted.

Cause:	Syntax error in reading QXF file.

Action:	Fix QXF file.


BRW-15546: Parse Error: Line <##>

Cause:	Incorrect syntax at that particular line.

Action:	Debug that line.


BRW-15547: Operator unspecified in relationship block.

Cause:	Operator unspecified in relationship block in QXF File.

Action:	Specify an operator field within the block.


BRW-15548: Error: Argument missing in <name> of relationship block.

Cause:	No argument in a column field in a relationship block.

Action:	Specify the column name in that field.


BRW-15549: CONDITION_TEXT missing within condition block.

Cause:	No argument in the CONDITION_TEXT field within condition block.

Action:	Specify the CONDITION_TEXT field.


BRW-15550: Illegal INCLUSION_ORDER value (<name1>) in expression '<name2>'.

Cause:	Illegal INCLUSION_ORDER value in expression.

Action:	Correct INCLUSION_ORDER value.


BRW-15551: Expression '<name>' included but INCLUSION_ORDER unspecified.

Cause:	The INCLUSION_ORDER unspecified for an included expression block.

Action:	Specify the INCLUSION_ORDER field.


BRW-15552: Expression '<name>' not included but INCLUSION_ORDER is non-null

Cause:	Expression is not included (INCLUDED = FALSE) but INCLUSION_ORDER is non-null.

Action:	Correct the values in these fields.


BRW-15553: Error: Empty TEXT string in expression '<name>'.

Cause:	The TEXT string is unspecified within a QXF expression block.

Action:	Specify the TEXT string.


BRW-15554: Error: SORT_PRIORITY unspecified for sorted expression '<name>'.

Cause:	SORT_PRIORITY unspecified for sorted expression.

Action:	Specify the SORT_PRIORITY.


BRW-15555: Illegal INCLUSION_ORDER value for field '<name1>'in table '<name2>'.

Cause:	Illegal INCLUSION_ORDER value for field.

Action:	Correct the INCLUSION_ORDER value.


BRW-15556: Field '<name1>' in table '<name2>' included but INCLUSION_ORDER is null.

Cause:	Field is included (INCLUDED = FALSE) but INCLUSION_ORDER is unspecified.

Action:	Correct the values in these fields.


BRW-15557: Field '<name1>' (table '<name2>') excluded but INCLUSION_ORDER exists.

Cause:	The field in specified data table is not included but the INCLUSION_ORDER is non-null.

Action:	Correct the values in these fields.


BRW-15558: SORT_PRIORITY unspecified for sorted field '<name1>' in table '<name2>'.

Cause:	SORT_PRIORITY unspecified for a sorted field.

Action:	Specify the SORT_PRIORITY for that field.


BRW-15559: Error: Illegal value '<name1>' for INCLUSION_ORDER in table '<name2>'.

Cause:	A negative value in inclusion order.

Action:	Correct that value.


BRW-15560: Table '<name>' included but INCLUSION_ORDER unspecified.

Cause:	Data Table included (INCLUDED = TRUE) but INCLUSION_ORDER is unspecified.

Action:	Correct the values in these fields.


BRW-15561: Table '<name>' is excluded but INCLUSION_ORDER is non-null.

Cause:	Data Table not included (INCLUDED = FALSE) but INCLUSION_ORDER is non-null.

Action:	Correct the values in these fields.


BRW-15562: Error: There should be no more than one main condition block.

Cause:	There are more than one main (root) condition blocks.

Action:	Eliminate some condition blocks, or use a conjunction.


BRW-15563: Error: QXF file version 1.0 expected.

Cause:	Illegal version number specified in VERSION field.

Action:	Modify the version.


BRW-15564: Parse Error: Line <##>: Invalid font style '<name>'.

Cause:	Invalid font style.

Action:	Check font style and correct it.


BRW-15565: Parse Error: Line <##>: Invalid font style argument.

Cause:	Invalid font style argument.

Action:	Check font style strings and correct them.


BRW-15566: Parse Error: Line <##>: Invalid aggregate function '<name>'

Cause:	Invalid aggregate function name.

Action:	Correct the function name.


BRW-15567: Parse Error: Line <##>: Invalid summarize argument.

Cause:	Invalid summarize field argument.

Action:	Correct the field argument.


BRW-15568: Parse Error: Line <##>: Invalid field argument.

Cause:	Invalid field argument.

Action:	Correct field argument.


BRW-15569: Warning: QXF file is only partially loaded because of error.

Cause:	Error in loading QXF file.

Action:	Correct those errors.


BRW-15570: Parse Error: Line <##>: Invalid operator '<name>' in relationship block.

Cause:	Invalid Operator within relationship block.

Action:	Check operator and correct it.


BRW-15571: Parse Error: Line <##>: Invalid operator field argument.

Cause:	Invalid Object ID within Derived Item Expression.

Action:	Correct Object ID in dictionary.


BRW-15572: Inconsistent object IDs in derived item expression.

Cause:	Invalid operator field argument within relationship block.

Action:	Check operator strings and correct them.


BRW-15580: Unable to load list of accessible business areas

Cause:	End User Layer is missing business area tables.

Action:	Reinstall End User Layer. Ask system administrator for assistance.


BRW-15581: Cannot add columns to table created with duplicate data

Cause:	User tried to add columns to a table created with duplicate table data.

Action:	Commit the duplicate table then try again.


BRW-15582: New or duplicate table is only allowed on existing table.

Cause:	Create new table on another new/duplicate/modified table.

Action:	Commit the new/duplicate/modified table first.


BRW-15583: Index requires at least one column

Cause:	User tried to remove the last column of an index.

Action:	Add other columns then remove.


BRW-15584: Column <name> is of type LONG and will not be copied

Cause:	Cannot copy a LONG column when creating a snapshot.

Action:	Column cannot be copied.


BRW-15600: Syntax not supported

Cause:	Attempted to parse unsupported syntax.

Action:	Use alternate Oracle 8 syntax.


 BRW-15601: Invalid Oracle 8 SQL syntax

Cause:	Cannot use Oracle 8 language syntax.

Action:	Use Oracle 7 SQL.


BRW-15602: Type Constructor in Oracle 8 SQL is not supported

Cause:	Oracle 8 SQL’s type constructors are not supported.

Action:	Revert to Oracle 7.


BRW-15603: REF in Oracle 8 SQL is not supported

Cause:	Oracle 8 SQL’s REF is not supported.

Action:	Use Oracle 7 instead.


BRW-15604: CAST in Oracle 8 SQL is not supported

Cause:	Oracle 8 SQL’s CAST is not supported.

Action:	Use Oracle 7 instead.


BRW-15605: MULTISET in Oracle 8 SQL is not supported.

Cause:	Oracle 8 SQL’s MULTISET is not supported.

Action:	Use Oracle 7 instead.


BRW-15606: METHODS in Oracle 8 SQL are not supported.

Cause:	Oracle 8 SQL’s METHODS are not supported.

Action:	Use Oracle 7 instead.


BRW-15607: THE subquery in Oracle 8 SQL is not supported

Cause:	Oracle 8 SQL’s THE subquery is not supported.

Action:	Use Oracle 7 instead.


BRW-15608: PARTITION of tables in Oracle 8 SQL is not supported

Cause:	Oracle 8 SQL’s PARTITION of tables is not supported.

Action:	Use Oracle 7 instead.


BRW-15609: The TABLE keyword in a subquery in Oracle 8 SQL is not supported

Cause:	TABLE keyword in subquery is not supported.

Action:	Use Oracle 7 instead.


BRW-15610: Rename the Table as a Table Alias

Cause:	Attempted to use a table instead of a table alias/

Action:	Rename the table with a table alias.


BRW-15641: Help system initialization failed. Help will be unavailable.

Cause:	Unable to locate help document or help resource file.

Action:	User may continue but help menu is unavailable. Use online or paper documentation.


BRW-15643: Resource file not found (<name>), trying default resource file.

Cause:	NLS_LANG set but file not found.

Action:	Try to open the resource file using the default language.


BRW-15644: Unable to locate help document (<name>), trying default help document.

Cause:	NLS_LANG set but file not found.

Action:	Try to open the help doc using the default language.


BRW-15647: Unable to locate help document

Cause:	Couldn't find help file in correct directory with correct language.

Action:	Check Query Builder directory structure. Make sure NLS_LANG is set correctly.


BRW-15648: Help system creation error

Cause:	Help system couldn't start up.

Action:	Query Builder couldn't find the help resource file, or NLS_LANG is not set, or memory is low.


BRW-15649: Input for <name> was too low. Minimum valid number is <##>.

Cause:	The value input was too low.

Action:	Try again with a higher value.


BRW-15650: Input for <name> was too high. Maximum valid number is <##>.

Cause:	The value input was too high.

Action:	Try again with a lower value.


BRW-15651: Input for <name> was out of range. Must be between <##> and <##>.

Cause:	The value input was out of the valid range.

Action:	Try again with a valid number.


BRW-15652: Value was reset to <value>.

Cause:	The value input was changed because it was out of the valid range.

Action:	Try again with a valid number or Query Builder will use the value shown.


BRW-15653: Pref file error: Value <value> was too low. Minimum valid number is <##>.

Cause:	The value input was too low.

Action:	Try again with a higher value.


BRW-15654: Pref file error: Value <value> was too high. Maximum valid number is <##>.

Cause:	The value input was too high.

Action:	Try again with a lower value.


BRW-15655: Pref file error: Value <value> out of range. Must be between <##> and <##>.

Cause:	The value input was out of the valid range.

Action:	Try again with a valid number.


BRW-15656: Pref file error: Value for <value> not recognized

Cause:	The value set in the preference file was invalid.

Action:	Try again with a valid number.


BRW-15657: Value for OWNERLIST=NAME must contain at least one name

Cause:	The value for OWNERLIST=NAME was NULL.

Action:	Try again with a valid name


BRW-15900: Unable to find time period information

Cause:	This database does not support time periods, or database error.

Action:	Try again later if this is unexpected.


BRW-15901: Table <name1> referred to by object <name2> no longer exists

Cause:	Base data source of object has been removed.

Action:	Refresh/delete object.


BRW-15902: Table referred to by object <name> no longer exists

Cause:	Base data source of object has been removed.

Action:	Refresh/delete object.


BRW-15903: Opposite table <name> is not in a business area. Join info inaccessible.

Cause:	Relationship: Opposite data table/object not in any group.

Action:	Grant that object to a group.


BRW-15904: Relationship name cannot be longer than <##> bytes

Cause:	Relationship Name longer than limit.

Action:	Use a shorter name.


BRW-15905: Column heading cannot be longer than <##> bytes

Cause:	Item Heading longer than limit.

Action:	Use a shorter heading.


BRW-15906: Column format cannot be longer than <##> bytes

Cause:	Item Format longer than limit.

Action:	Use a shorter format.


BRW-15907: Column display width cannot have more than <##> digits

Cause:	Item Display Width Precision longer than limit.

Action:	Use Shorter Display width.


BRW-15908: Time period name cannot be longer than <##> bytes

Cause:	Time Period Name too long.

Action:	Use a shorter time period name.


BRW-15909: Period column and sequence column of time period are the same

Cause:	Period and sequence columns of time period set to the same column.

Action:	make sure period and sequence columns are different.


BRW-15910: Unable to load time period <name>

Cause:	Schema internal error in loading time period.

Action:	Refer to accompanying error messages for more information.


BRW-15911: Time period <name> already exists

Cause:	Time period name must be unique.

Action:	Create time period under a different name.


BRW-15912: Illegal display width value.

Cause:	Illegal display width value.

Action:	Re-enter a legal value.


BRW-15913: <name> key text cannot be empty.

Cause:	Foreign or Primary Key text is empty.

Action:	Enter text for primary or foreign key.


BRW-15914: Relationship name cannot be empty.

Cause:	Relationship name is null.

Action:	Enter relationship name.


BRW-15924: Business area name cannot be empty

Cause:	Business area name is empty or contains only spaces.

Action:	Enter a new name.


BRW-15925: Table <name1> has already been imported as <name2>

Cause:	Table can only be imported once with a given name.

Action:	Import table under a different name.


BRW-15926: Business area <name> already exists

Cause:	Business area name must be unique.

Action:	Create business area under a different name.


BRW-15927: Business area <name1> already contains a table named <name2>

Cause:	Table name must be unique within a business area.

Action:	Rename/reimport table and add to business area.


BRW-15928: Business area name too long (Max <##> bytes)

Cause:	Business area name too long.

Action:	Shorten the name and try again.


BRW-15929: Business area description too long (Max <##> bytes)

Cause:	Business area description too long.

Action:	Shorten the description and try again.


BRW-15930: Error committing table <name>

Cause:	Error occurred while committing table.

Action:	Try again.


BRW-15931: Error committing business area <name>

Cause:	Error occurred while committing business area.

Action:	Try again.


BRW-15932: Error committing time period <name>

Cause:	Error occurred while committing time period.

Action:	Try again.


BRW-15933: Error committing relationship <name>

Cause:	Error occurred while committing relationship

Action:	Try again.


BRW-15937: Error generating SQL for table <name>

Cause:	Error occurred while generating SQL for a table.

Action:	Try again.


BRW-15938: Error generating SQL for business area <name>

Cause:	Error occurred while generating SQL for a business area.

Action:	Try again.


BRW-15939: Error generating SQL for time period <name>

Cause:	Error occurred while generating SQL for a time period.

Action:	Try again.


BRW-15940: Error generating SQL for relationship <name>

Cause:	Error occurred while generating SQL for relationship.

Action:	Try again.


BRW-15950: No Oracle Query Builder tables--Database open/save is disabled

Cause:	There is no Query Builder system query table in the database.

Action:	Create a system query table (run the Query Builder install script).


BRW-15951: Product user profile information not loaded

Cause:	Cannot load the user profile table.

Action:	Ask system administrator to create one.


BRW-15952: User privilege table not loaded

Cause:	Missing USER_USERS table (Non Oracle or Oracle V7 database).

Action:	None available.


BRW-15953: Table privilege table not loaded

Cause:	Cannot find Privilege table.

Action:	Non-Oracle database or Oracle V7 database.


BRW-15954: Oracle Query Builder 2.5 tables are missing or not installed properly

Cause:	No QUERY BUILDER_DOCS_SQL table.

Action:	Re-install Query Builder 2.5 tables.


BRW-15955: Query returned zero rows

Cause:	Query returned no rows.

Action:	Modify the query so that it requests data that exists in the database.


BRW-16000: Document saved using End User Layer, not Native dictionary. Continue?

Cause:	User tried to open in Native mode a document saved using the End User Layer.

Action:	Run Query Builder with DICTIONARY=ENDUSER or continue (End User Layer objects will be inaccessible).


BRW-16001: Document saved using Native dictionary, not End User Layer. Continue?

Cause:	User tried to open in ENDUSER mode a document saved using the Native dictionary.

Action:	Run Query Builder with DICTIONARY=NATIVE or continue (some database objects may be inaccessible).


BRW-16250: Document already exists. Do you want to overwrite?

Cause:	User tried to Save As over an existing document.

Action:	User may overwrite or cancel.


BRW-16251: Table has been modified. Continue?

Cause:	View was removed and recreated; may be inconsistent with document.

Action:	Either continue or abort save.


BRW-16500: Memory not available for local sort. Re-execute?

Cause:	All rows not yet fetched for local sort.

Action:	Re-execute query now or later.


BRW-16503: Data not available for local sort. Re-execute?

Cause:	All rows not yet fetched for local sort.

Action:	Re-execute query now or later.


BRW-16504: Data not available for local break. Re-execute?

Cause:	All rows not yet fetched for local break.

Action:	Re-execute query now or later.


BRW-16525: Query may require a Cartesian product. Continue?

Cause:	Query combines results of unrelated tables.

Action:	Ignore message or relate tables before executing. Refer to help or documentation for details.


BRW-16531: Redefine parameter <name>?

Cause:	User entered previously deleted parameter in expression or condition.

Action:	Press OK if this is what you intended.


BRW-16532: Parameter <name> does not exist. Define?

Cause:	User entered new parameter in expression or condition.

Action:	Press OK if this is what you intended.


BRW-16549: Revert will undo all changes on these tables. Revert anyway?

Cause:	Confirmation before reverting data tables.

Action:	Click OK to revert all selected data tables, Cancel to cancel the operation.


BRW-16550: Table <name> has primary/unique keys that are referenced. Remove?

Cause:	Table contains constraints.

Action:	Click OK to remove the data table, Cancel to cancel the operation.


BRW-16551: Column has primary/unique keys or indexes. Remove anyway?

Cause:	Column is used by constraints or indexes.

Action:	Click OK to remove the column, Cancel to cancel the operation.


BRW-16552: Disable all constraints?

Cause:	Message to confirm that all constraints should be disabled.

Action:	User may confirm or cancel.


BRW-16553: Enable all constraints?

Cause:	Message to confirm that all constraints should be enabled.

Action:	User may confirm or cancel.


BRW-16575: There are uncommitted edits which will be lost. Commit?

Cause:	User has tried to exit the Data Editor without committing changes.

Action:	Click Commit or lose changes.


BRW-16576: Defined Column, Summary and Break values may not be current. Re-execute?

Cause:	Values edited while displaying breaks, summaries, or defined columns.

Action:	Execute query to update values.


BRW-16623: There is no data available for this operation.

Cause:	Alert message if number of rows or columns available to print is zero.

Action:	User should try re-executing the query, or include some columns.


BRW-16624: Query definition has changed. Data may not be current. Continue?

Cause:	Query document has changed since last execute.

Action:	Either re-execute the query, continue export, or cancel.


BRW-16625: The printer does not support all fonts needed. Substitute fonts?

Cause:	The font or some style or weight does not exist for the selected printer.

Action:	User may continue with font substitution or cancel.


BRW-16626: Not enough memory for Print Preview.

Cause:	Unable to create bitmap for print preview.

Action:	No action required.


BRW-16627: There is no data available for this operation. Execute query?

Cause:	Alert message if export data called with empty worksheet.

Action:	User should try executing the query or cancel.


BRW-16628: Unable to get printer device. Printing will be unavailable. Continue?

Cause:	Error creating a toolkit printer surface with uiprc().

Action:	User may continue with no printing or cancel.


BRW-16629: Do you want to update the container before exiting?

Cause:	Message to prompt user to save changes when closing/quitting OLE doc.

Action:	User may save or abort changes, or cancel.


BRW-16650: The printer does not support all fonts needed. Substitute fonts?

Cause:	The font or some style or weight does not exist for the selected printer.

Action:	User may continue with font substitution or cancel.


BRW-16651: No printer has been chosen. Use Default?

Cause:	The user has not explicitly named a printer to use.

Action:	User may continue with the system default printer or cancel.


BRW-16652: Margins for this document are too wide and must be reset.

Cause:	The user has set the margins to be too large leaving no content.

Action:	User must reset the margins.


BRW-16653: No printer has been chosen. Use Default?

Cause:	The user has not explicitly named a printer to use.

Action:	User may continue with the system default printer or cancel.


BRW-16654: No pages printed.

Cause:	User has set copies to 0 or the start/end page outside the range.

Action:	Reset page range.


BRW-16660: Query definition has changed. Data may not be current. Re-execute?

Cause:	Query document has changed since last DDE update.

Action:	Re-execute the query before DDE update, or update without executing.


About Query Builder functions

  Related Topics

A function is similar to an operator in that it manipulates data items and returns a result.  Functions differ from operators in the format in which they appear with their arguments.  This format allows them to operate on any number of arguments, including none.  Every time you associate a column with a function name, Query Builder performs the defined operation on the value(s) in that column.

Functions that you can use with queries in Query Builder are:

•	number functions

•	character functions

•	character functions returning numeric values

•	conversion functions

•	date functions

•	group functions

•	other functions

Additional Information: For information about any of the other functions, refer to the Oracle8 Server SQL Language Reference Manual.


Types of functions

  Related Topics

The Paste Function dialog box lists all available functions in the Function list box.   You can filter this list by Category.  These categories are explained in the following table:

Category	Explanation
Number	Number functions accept numeric input and return numeric values.
Character	Character functions accept character input and can return both character and numeric values.
Date	Date functions operate on values of the DATE datatype.  All date functions return a value of the DATE datatype except the MONTHS_BETWEEN function, which returns a number.
Conversion	Conversion functions convert a value from one datatype to another.
Group	Group functions return results based on groups of rows, rather than on single rows.
Other	Miscellaneous functions, such as NVL, and user-defined functions.
Note:  Nonexistent (NULL) values are not treated as zeros; if the database returns a NULL, nothing will be displayed in that cell.  Operations performed on NULLs evaluate to NULL.  So, for example, NULL+<value>=NULL.  To have NULL values evaluate to zero, combine your functions with the NVL function to ensure values in all cells.  For example, you would use the NVL function to return 0 instead of NULL if there was a null value for salary or commission.

GREATEST(NVL(SALARY,0),NVL(COMMISSION,0))


Functions and arguments

  Related Topics

A function is used to manipulate data in a particular column.  Each function can take one or more arguments.  An argument is an expression within the parentheses of a function supplying information for the function to use.

An example is the function ROUND, which rounds a number to the degree you specify.  If you enter the expression ROUND (CREDIT_LIMIT, -2), Query Builder would round each value for CREDIT_LIMIT to the nearest hundred.  If you enter ROUND(CREDIT_LIMIT, 1), Query Builder would round the values for CREDIT_LIMIT to the nearest 10th.

The values used within arguments are:

Argument Value	Description
char, set, search,	Character constant surrounded by single quotation
replace	marks or an expression returning character data.
d, e	Date constant surrounded by single quotation marks, or an expression returning date data.
m, n	Any number constant or expression returning numeric data.
x	Argument type depends on the context of the function.
expr	An expression of any type.
fmt	A format used to change the display of data or to enter data into a Query Builder field or condition in a format other than the default format.


Number functions

  Related Topics

Number functions accept numeric data and return numeric data.  You can use number functions when defining columns and conditions.

Function (arguments)	Description
ABS(n)	Absolute value of n.
CEIL(n)	Smallest integer greater than or equal to n.
COS(n)	Returns the cosine of n (an angle expressed in radians).
COSH(n)	Returns the hyperbolic cosine of n.
EXP(n)	Exponential notation.  Returns e (2.71828183...) raised to the nth power.
FLOOR(n)	Largest integer equal to or less than n.
LN(n)	Returns the natural log of n, where n is greater than 0.
LOG(m,n)	Returns the logarithm, base m, of n.  The base m can be any positive number other than 0 or 1, and n can be any positive number.
MOD(m,n)	Remainder of m divided by n.
POWER(m,n)	m raised to the nth power.  If n is not an integer, it will be truncated
ROUND(n[,m])	n rounded to m decimal places; if m is omitted, to 0 places.  m may be negative to round digits left of the decimal point.
SIGN(n)	If n<0, -1; if n=0, 0; if n>0, 1.
SIN(n)	Returns the SINE of n (an angle expressed in radians).
SINH(n)	Returns the hyperbolic sine of n.
SQRT(n)	Square root of n; if n<0, NULL.
TAN(n)	Returns the tangent of n (an angle expressed in radians).
TANH(n)	Returns the hyperbolic tangent of n.
TRUNC(n[,m])	n truncated to m decimal places; if m is omitted, to 0 places.  m may be negative to truncate (make zero) digits left of the decimal point.


Character functions

  Related Topics

You can use character functions that accept character data and return character data when defining columns and conditions.

Function (arguments)	Description
CHR(n)	The character with having the binary equivalent to n in the database character set.
CONCAT(char1,char2)	Returns char1 concatenated with char2.  This function is equivalent to the concatenation operator (||).
INITCAP(char)	char, with first letter of each word capitalized.  Words are delimited by white space or characters that are not alphanumeric.
LOWER(char)	char, with all letters forced to lowercase.
LPAD(char1,n[,char2])	char1, left-padded to length n with the sequence of characters in char2, replicated as many times as necessary; if char2 is omitted, with blanks.
LTRIM(char[,set])	char, with initial characters removed up to the first character not in set.  set defaults to ` ' (a single blank).
NLS_INITCAP(char [,nlsparams])	Returns char, with the first letter of each word capitalized.  Words are delimited by white space or characters that are not alphanumeric.  The parameters specified in nlsparams can have the form NLS_SORT=sort where sort is a linguistic sort sequence for your session or BINARY.  If you specify BINARY, this function returns char.  For information on sort sequences, refer to the Oracle8 Server Concepts Manual.
NLS_LOWER(char[,nlsparams])	Returns char, with all letters forced to lowercase. See NLS_INITCAP for the nlspa-rams argument.
NLS_UPPER(char[,nlsparams])	Returns char, with all letters forced to uppercase. See NLS_INITCAP for the nlspa-rams argument.
REPLACE(char,search[,replace])	char, with every occurrence of search replaced with replace. If replace is not supplied, all occurrences of search are removed.
RPAD(char1,n[,char2])	char1, right-padded to length n with the characters in char2, replicated as many times as necessary; if char2 is omitted, with blanks.
RTRIM(char[,set])	char, with final characters removed after the last character not in set.  set defaults to ` ' (a single blank).
SOUNDEX(char)	A char value representing the sound of the word(s) in char.
SUBSTR(char,m[,n])	A substring of char, beginning at character m, n characters long if n is omitted.
SUBSTRB(char,m[,n])	Same as SUBSTR, except that the arguments m and n are expressed in bytes, rather than characters.  For a single byte database character set, SUBSTRB is the same as SUBSTR.
UPPER(char)	char, with all letters forced to uppercase.


Character functions returning numeric data

  Related Topics

You can use character functions to return numeric data when defining columns and conditions.

Abbreviation	Description
ASCII (char)	ASCII value of first character of char.
INSTR(char1,char2[,n[,m]])	The position of the nth occurrence of char2 in char1, beginning search at position n.  If m is omitted, 1 is assumed.  If n is omitted, 1 is assumed.  Position is given relative to the first character of char1, even when n>1.
INSTRB(char1,char2[,n[,m]])	Same as INSTR, except that n and the returned value are expressed in bytes, rather than characters.  For a single-byte database character set, INSTRB is the same as INSTR.
LENGTH(char)	The length, in characters, of char.
LENGTHB(char)	Same as LENGTH, except that char is expressed in bytes, rather than characters.  If char is NULL, this function returns NULL.  For a single-byte database character set, LENGTHB is the same as LENGTH.
NLSSORT(char[,nlsparams])	Returns the string of bytes used to sort char.  The parameters specified in nlsparams can have the form NLS_SORT=sort where sort is a linguistic sort sequence for your session or BINARY.  If you specify BINARY, this function returns char.  For information on sort sequences, refer to the Oracle8 Server Concepts Manual.


Conversion functions

  Related Topics

You can use conversion functions to convert data from one datatype to another when defining columns and conditions.

Abbreviation	Description
TO_CHAR (d[,fmt])	Converts d to a char value in the format specified by the char value fmt.  If fmt is omitted, d is converted to a char value in Query Builder's default date format, DD-MON-YY.
TO_CHAR(n[,fmt])	Converts n to a char value in the format specified by the char value fmt.  If fmt is omitted, n is converted to a char value exactly long enough to hold the significant digits.
TO_DATE(char[,fmt])	Converts a date from a char value to a date value.  fmt is a char value specifying the format of char.  If fmt is omitted, char must have the Query Builder default date format, DD-MON-YY.
TO_DATE(n,[fmt])	Converts a number into a date.  fmt must be a format that, when used with TO_CHAR, produces a DATE (e.g., J or MM).
TO_LABEL(char[,fmt])	Converts char, a value of datatype CHAR or VARCHAR2 containing a label in the format fmt, to a value of MLSLABEL datatype.  If you omit fmt, char must be in the default label format.
TO_MULTI_BYTE (char)	Returns char with all of its single-byte characters converted to their corresponding multi-byte characters.  Any single-byte characters that have no multi-byte equivalents appear in the output string as single-byte.  (This function is only useful if your database character set contains both types.)
TO_NUMBER (char)	Converts char, character data containing a number, to numeric data.
TO_SINGLE_BYTE (char)	Returns char with all of its multi-byte characters converted to their corresponding single-byte characters.  Any multi-byte characters that have no single-byte equivalents appear in the output string as multi-byte.  (This function is only useful if your database character set contains both types.)


Date functions

  Related Topics

All date functions return date data except MONTHS_BETWEEN, which returns numeric data.  You can use these functions when defining columns and conditions.

Abbreviation	Description
ADD_MONTHS (d,n)	Date d plus n months.
LAST_DAY(d)	Date of last day of month containing d.
MONTHS_BETWEEN(d,e)	Number of months between dates d and e. If d is later than e, result is positive; if earlier, negative.
NEW_TIME(d,a,b)	Date and time in time zone b when date and time in time zone a are d.  a and b are char expressions with the following meaning:
AST, ADT:	Atlantic Standard or Daylight Time
BST, BDT:	Bering Standard or Daylight Time
CST, CDT:	Central Standard or Daylight Time
EST, EDT:	Eastern Standard or Daylight Time
GMT:	Greenwich Mean Time
HST, HDT:	Alaska-Hawaii Standard or Daylight Time
MST, MDT:	Mountain Standard or Daylight Time
NST:	Newfoundland Standard Time
PST, PDT:	Pacific Standard or Daylight Time
YST, YDT:	Yukon Standard or Daylight Time
NEXT_DAY(d,char)	Date of first day of week named by char that is equal to or later than d.
ROUND(d[,fmt])	d rounded as specified by the rounding unit fmt; if fmt omitted, the nearest day.
SYSDATE	Current date and time.  Requires no arguments.
TRUNC(d[,fmt])	d with time of day truncated up to the specified truncation unit, fmt; if fmt omitted, the time of day is removed.
Additional Information: Refer to the Oracle8 Server SQL Language Reference Manual for a table of format models to be used with the ROUND and TRUNC date functions.


Group functions

  Related Topics

Group functions retrieve data based on groups of rows rather than one result per row.  Group functions perform operations such as average, count, sum, and standard deviation.  You can apply multiple group functions to data in your Query Builder queries using the Totals dialog box.

You can also use group functions to define a new column, and the entire result is treated as one group when you display no other columns.  Displaying other columns will divide the result into smaller groups.  You can use group functions in conditions, but only with a comparison operator, so the entire result is treated as one group.  For example, the condition SALARY > AVG(SALARY) is true when the value of SALARY in the current row is greater than the average of SALARY in all of the rows.

Group functions can be used with the DISTINCT or ALL options.  DISTINCT makes a group function consider only distinct functions of an expression.  For example, the average of 1, 1, 1, and 3 is 1.5; the DISTINCT average is 2.  ALL causes a group function to consider all values, including duplicates.

All of the following group functions skip null values except COUNT(*). The datatype of the arguments may be character, number, or date where expr is listed.

Function (argument)	Description
AVG([DISTINCT|ALL]n)	Average value of n, ignoring null values.
COUNT([DISTINCT|ALL] expr)	Number of rows where expr is not null.
COUNT(*)	Number of rows in the table, including duplicates and those with nulls.
GLB([DISTINCT|ALL] label)	Returns the greatest lower bound of label.
LUB([DISTINCT|ALL] label)	Returns the least upper bound of label.
Additional Information:  GLB and LUB return values of the MLSLABEL datatype.  For more information about the GLB and LUB functions, refer to the Trusted Oracle8 Server Administrator's Guide.

Function (argument)	Description
MAX([DISTINCT|ALL] expr)	Maximum value of expr.
MIN([DISTINCT|ALL] expr)	Minimum value of expr.
STDDEV([DISTINCT|ALL]n)	Standard deviation of n, ignoring null values, based on an n-1 (sample) calculation.
SUM([DISTINCT|ALL] n)	Sum of values of n.
VARIANCE([DISTINCT|ALL] n)	Variance of n, ignoring null values.


Other functions

  Related Topics

Query Builder also accepts these functions:

Function (argument)	Description
USER	Returns the name of the current Oracle user.  Requires no arguments.
DECODE(expr,search1, replace1[,search2, replace2,] ... [default])	If expr equals any search, returns the following replace; if not, returns default.  If default is omitted and there is no match, NULL is returned. expr may be any datatype; search must be the same type.  The value returned is forced to the same datatype as the first return.
GREATEST(expr1,expr2,...)	Returns the greatest of a list of values.  All expr after the expr1 are converted to the type of the expr1 before the comparison is done.
GREATEST_LB(label[,label] ...)	Returns the greatest lower bound of the list of labels.  Each label must either have datatype MLSLABEL or RAW MLSLABEL or be a quoted literal in the default label format.  The return value has the datatype RAW MLSLABEL.
LEAST(expr1,expr2,...)	Returns the least of a list of values.  All expr after the expr1 are converted to the type of the expr1 before the comparison is done.
LEAST_UB(label[,label] ...)	Returns the least upper bound of the list of labels.  Each label must have datatype MLSLABEL or be a quoted literal in the default label format.  The return value has the datatype RAW MLSLABEL.
NVL(x,expr)	If x is null, returns expr; if x is not null, returns x.  x and expr may be of any type. The type of the returned value is the same as the type of x.
Additional Information:  For more information about the GREATEST_LB and LEAST_UB functions, refer to the Trusted Oracle8 Server Administrator's Guide.


Using the online help

The following guidelines will help you to make effective use of the online help system:

•	Familiarize yourself with the Contents tab to get an idea of the scope of the topics covered in the online help.

•	Click the Related Topics button directly below topic titles for a list of topics related to the current topic.

•	Click the Options button at the top of any topic to set Keep Help on Top, so that help topics remain on your screen as you develop applications.

•	Click the Glossary button at the top of any topic for definitions of unfamiliar terms.


Documentation roadmap

To get started and become proficient with building applications:

1	Review the Quick Tour for a brief overview of each component:

•	Choose Help [image] Quick Tour.

2	Step through the Cue Cards to learn how to perform the most common tasks:

•	Choose Help [image] Cue Cards.

3	Display the online manuals (also available in hardcopy form):

•	Choose Help [image] Manuals.

4	Access the online help system:

•	Choose Help [image] Help Topics.

•	For context-sensitive help, press [Help] (this is usually the F1 key on most platforms).


About the Icon Bar

  Related Topics

The Icon Bar displays at the top of the Query Builder screen.

Query window

When the Query window is active, the following icons display.  Click on each icon to see a description.

Results window

When the Results window is active, the following icons display.  Note that you can toggle between the Totals Tools icons and the Format Tools icons.  The default when you first display your query results is the Format Tools icons.  Click on each icon to see a description.

When the Results window is active, you can select to display the Totals Tools icons by clicking on [image].  The Totals Tools are generally used on columns containing the number datatype.  Click on each icon to see a description of the Totals Tools.


New document icon

Creates a new document in the active window and brings up the Select Data Tables dialog box.  You can also choose File [image] New or press CTRL+N.


Open document icon

Displays the Open dialog box.  You can also choose File [image] Open or press CTRL+O.


Save document icon

Saves the current document (or query) to the default location.  If the query has never been saved, brings up the Save As dialog box.  You can also choose File [image] Save, File [image] Save As, or press CTRL+S.


Print icon

Displays the Print dialog box.  You can also use File [image] Print.


Page Setup icon

Displays the Page Setup (Margins/Headers/Footers) dialog box.  You can also choose File [image] Page Setup.


Cut icon

Cuts the selected text and places it on your operating system's clipboard.  You can also choose Edit [image] Cut or press CTRL+X.  After cutting text, you can then paste it.


Copy icon

Copies the selected text onto your operating system's clipboard while the original text remains intact.  You can also choose Edit [image] Copy or press CTRL+C.  After copying text, you can then paste it.


Paste icon

Pastes the current clipboard items into the active location.  If there is nothing on the clipboard, nothing will be pasted.  You can paste any text that you have cut or copied.  You can also choose Edit [image] Paste or press CTRL+V.


Select All icon

Selects every item in the active window.  For example, if the Conditions Panel is active, all conditions in the panel will be selected.  You can also choose Edit [image] Select All or press CTRL+A.


Select Data Tables icon

Displays the Select Data Tables dialog box.  This icon is only available when the Datasource panel is active.  You can also choose Data [image] Select Data Tables or press CTRL+H.


Select Related Tables icon

Displays the Select Related Tables dialog box.  This icon is only available when the table you have selected in the Datasource panel has a related table in the connected database.  You can also choose Data [image] Select Related Tables or press CTRL+R.


Rename Table icon

Displays the Rename Table dialog box.  You can also choose Data [image] Rename Table.  This icon is only active when the Datasource panel is active and there is at least one table in the Datasource panel.


Get Info icon

Displays the Info dialog box and information on the selected database object.  You can also choose Data [image] Get Info or press CTRL+I.


Set Table Relationship icon

Displays the Set Relationship dialog box.  This icon is only active when there is more than one table in the Datsource panel to relate.  You can also choose Data [image] Set Table Relationship.


Execute Query icon

Runs the active query.  You can also choose Query [image] Execute Query or press CTRL+E.


Count Rows icon

Counts the number of rows the active query will return.  You can also choose Query [image] Count Rows.


Define Query Parameters icon

Displays the Define Query Parameters dialog box.  You can also choose Query [image] Define Query Parameters or press CTRL+P.


Show SQL icon

Displays the Show SQL dialog box and the SQL for the active query.  You can also choose Query [image] Show SQL.


Column Sequence icon

Displays the Display dialog box.  You can also choose Results [image] Display or press CTRL+L.


Define Column icon

Displays the Define Column dialog box.  You can also choose Results [image] Define Column or press CTRL+D.


Sort icon

Displays the Sort dialog box.  You can also choose Results [image] Sort or press CTRL+T.


Group By icon

Displays the Group dialog box.  You can also choose Results [image] Group or press CTRL+G.


And icon

Inserts the AND operator into your condition.  This icon is only available when the Condition field is active.  You can also type AND directly into the Condition field, or choose Data [image] AND.


Or icon

Inserts the OR operator into your condition.  This icon is only available when the Condition field is active.  You can also type OR directly into the Condition field, or choose Data [image] OR.


Not icon

Inserts the NOT operator into your condition to negate your condition.  This icon is only available when the Condition field is active.  You can also type NOT directly into the Condition field, or choose Data [image] NOT


Ungroup Conditions icon

Ungroups conditions that are combined with logical operators, such as AND, OR, NOT.  This icon is available only when you have selected a logical operator in the Conditions panel.


Accept icon

Validates and accepts the active condition.  This icon is available only when the Condition field is active.  You can also press ENTER when the Condition field is active to accept and validate your condition.


Cancel icon

Cancels the current condition.  This icon is available only when the Condition field is active.


Paste Column icon

Displays the Paste Column dialog box.  This icon is available only when the Condition field is active.  You can also choose Edit [image] Paste Column.


Paste Function icon

Displays the Paste Function dialog box.  This icon is available only when the Condition field is active.  You can also choose Edit [image] Paste Function.


Help icon

Displays the online help system contents.  You can also choose Help [image] Query Builder Help Topics or press F1.


Sort Ascending icon

Arranges the query results into ascending order, according to the data in the selected column.  You can also choose Results [image] Sort or press CTRL+T to access the Sort dialog box, and select the ascending option for the desired column.


Sort Descending icon

Arranges the query results into descending order, according to the data in the selected column.  You can also choose Results [image] Sort or press CTRL+T to access the Sort dialog box, and select the descending option for the desired column.


Break icon

Defines the selected column in the Results window as a break column.  You can also choose Results [image] Break to display the Break dialog box, and select the appropriate options.


Clear Sort/Break icon

Removes any sorts or breaks that you have placed on the selected column.  You can also remove the sort or break by accessing the Sort dialog box or the Break dialog box and choosing the desired options.


Switch to Totals Tools icon

Changes the Icon Bar to the Totals Icon Bar.  You can access the summary operations and Totals operators separately through the Totals Icon Bar.  You can also choose Results [image] Totals to access the same operators.


Bold icon

Bolds the selected text.  You can also access this option by choosing Results [image] Format.


Italics icon

Italicizes the selected text.  You can also access this option by choosing Results [image] Format.


Underline icon

Underlines the selected text.  You can also access this option by choosing Results [image] Format.


Align Left icon

Justifies the selected text to the left side of the column. You can also access this option by choosing Results [image] Format.


Align Center icon

Justifies the selected text to the center of the column. You can also access this option by choosing Results [image] Format.


Align Right icon

Justifies the selected text to the right side of the column.  You can also access this option by choosing Results [image] Format.


Word Wrap icon

Wraps the text in the selected column around.  You may want to use this option when not all of the data displays in each row of your column. You can also access this option by choosing Results [image] Format.


Switch to Format Tools icon

Changes the Icon Bar to the Format Icon Bar.  You can access text formatting options through this Icon Bar, or select Results [image] Format.


Count icon

Counts the number of rows in the selected column and places the result in the last row of the table.  You can also choose Results [image] Totals and choose the desired options.


Total icon

Creates a sum of all the data in the selected column and places the sum in the last row of the table.  Note that this option will only work for data of the number datatype. You can also choose Results [image] Totals and choose the desired options.


Average icon

Averages the data in the selected column and places the average in the last row of the table.  Note that you can only use this option for data of the number datatype.  You can also choose Results [image] Totals and choose the desired options.


Minimum icon

Locates the data with the lowest value.  If a number datatype, then the smallest number in that column is displayed in the last row of the table.  If a character datatype, then the row beginning with "A", then "B," and so on, is displayed in the last row of the table.  You can also choose Results [image] Totals and choose the desired options.


Maximum icon

Locates the data with the greatest value.  If a number datatype, then the largest number in that column is displayed in the last row of the table.  If a character datatype, then the row beginning with "Z",  then "Y" and so on, is displayed in the last row of the table.  You can also choose Results [image] Totals and choose the desired options.


Standard Deviation icon

Calculates the standard deviation of the data in the selected column and places the number in the last row of the table.  Note that the data must be of the number datatype in order for this operator to work.  You can also choose Results [image] Totals and choose the desired options.


Variance icon

Calculates the square of the standard deviation of the data in the selected column.  Note that the data must be of the number datatype in order for this operator to work.  You can also choose Results [image] Totals and choose the desired options.


Help topic not found.  Check the online help for related topics.


Glossary

A

    action
    active window/view
    ActiveX
    Ada
    aggregation
    alert
    alignment
    alias
    All tool
    ancestor
    ancestor group
    anchor
    anchor point
    anonymous block
    antecedent
    applet
    application
    argument
    ASCII
    attached library
    attribute
    attribute columns
    attribute mask
    Attribute tool
    attribute value
    axis chart

 B

    background element
    background process
    baseline value
    Batch
    batch compile
    bind reference
    bind variable
    block
    body
    boilerplate
    border
    bounding box
    break chart
    break column
    break group
    break report
    break trigger
    breakpoint
    builder
    built-in datatype
    built-ins
    built-in macro
    buffer
    button
    button procedure

C

    cache (memory)
    call interface
    call stack
    canvas
    canvas view
    cartridge
    cascading style sheet
    category
    category axis
    cell
    CGI
    CGM
    CHAR datatype
    chart element
    chart item
    chart template
    check box
    checksum
    child
    child query
    classification
    clause
    client
    client/server architecture
    Clipboard
    CMDFILE
    collection type
    color palette
    column
    column object
    command line
    comment
    commit
    comparison operators
    compile
    component
    concurrency
    condition
    confine mode
    connect
    connect string
    constraint
    construct
    context area
    continuous axis
    control point
    coordinate plane
    coordinate system
    coordinates (x, y)
    copy
    correlated subquery
    cross product
    crosstab
    cursor
    custom measure
    cut

D

    damage
    data dictionary
    data link
    data model
    data structure node
    database
    database trigger
    datasource
    datatype
    DATE
    date axis
    debug
    debug action
    debug trigger
    Debugger
    deconstructed object
    default
    deliver
    dependency tree
    dependency view
    dependent data
    descendant
    designer
    Designer (Oracle)
    detail query
    dialog box
    DIANA
    dimension
    dimension hierarchy
    dimension value
    disabled
    discrete axis
    display
    display item
    downward-growing
    drag
    drill
    duplicate
    dynamic saved selection

E

    EBCDIC
    edge
    Edge/Fill/Text Display
    editor
    element
    element type
    Ellipsis button
    enabled
    enclosing object
    encryption
    environment
    exception
    Exception tool
    executable argument
    execute
    export
    export file
    expression
    external PL/SQL library
    external query

F

    family
    Family tool
    field
    field template
    file node
    fill area
    filter
    firewall
    flattened object
    flex mode
    focus
    foreground element
    foreign key
    form layout
    form letter layout
    format mask
    format trigger
    formula
    formula column
    frame
    function

G

    generate
    Generate
    Global Registry
    global variable
    grant
    gridlines
    group
    group filter
    group tree
    GUI

H

    handle
    header
    heading node
    hidden
    hierarchy
    HTML
    HTTP
    hyperlink
    hypertext

I

    ICD
    icon
    image
    implied item
    import
    independent data
    index
    inherit
    inner bounding box
    input item
    insert mode
    internet
    Interpreter script
    intranet
    IP (Internet Protocol) Address
    item
    item node

J

    JAR
    Java
    join
    justification

K

    keyword

L

    label
    Launcher
    level
    Level tool
    layout
    layout unit
    lexical reference
    library
    link
    link file
    list box
    list item
    list of values
    List tool
    local database
    local registry
    local variable
    logical operators
    logical page
    loop

M

    macro
    mailing label layout
    margin
    master/detail layout
    Match tool
    matrix cell
    matrix layout
    matrix report
    measure
    megabyte (Mb)
    message box
    method
    modal window
    modeless window
    module
    multi-line field

N

    NA value
    nested matrix
    NOCACHE
    node
    non-caching column
    NULL value
    numeric operators

O

    object
    object column
    object group
    object handle
    Object Navigator
    object table
    object type
    ODBC
    OLE
    OLE container
    OLE Server
    Oracle datatype
    Oracle Designer
    ORACLE_HOME
    organization node
    origin
    outer bounding box
    overflow

P

    packaged function
    package
    package body
    package specification
    page
    page break
    palette
    pane
    panel
    parameter
    parameter list
    parent
    paste
    pattern
    Pcode
    PDF
    physical page
    placeholder username
    plot type
    PL/SQL
    pld file
    pll file
    pls file
    plx file
    pop-up list
    port
    pre-defined action
    pre-defined type
    preference
    preview
    Previewer
    primary key
    primitive
    printer control code
    prt file
    procedure
    program unit
    project
    project file
    project item
    Project Navigator
    project node
    project registry
    project view
    Project Wizard
    property
    property class
    protocol

Q

    qualifying dimension
    query
    query filter function
    quit

R

    radio button
    radio group
    Range tool
    RC4
    RDBMS
    rdf file
    rep file
    read consistency
    record
    record group
    REF column
    reference line
    refresh
    register
    registry
    relational operator
    remote database
    repeating frame
    replace mode
    report-level objects
    Reports Background Engine (RWRBE60)
    Reports Queue Manager (RWRQM60)
    Reports Server (RWMTS60)
    Reports Web Cartridge
    Reports Web CGI (RWCGI60)
    rex file
    RGB
    root
    root node
    row
    row object
    RTF
    run
    runfile
    Runtime
    Runtime Parameter Form

S

    saved selection
    Saved Selection tool
    schema
    scope
    script
    section
    SELECT statement
    selection
    selection script
    Selector
    Selector tool
    session
    sibling
    socket
    Sort tool
    source
    specification
    split bar
    SQL
    sql file
    SQL script
    SQL statement
    stack
    statement
    static saved selection
    stored program unit
    stored subprogram
    style sheet
    subprogram
    subproject
    subproject node
    summary column
    SYLK
    syntax
    system variable

T

    table
    table type
    tabular
    target
    target node
    TCP
    tear-off menu
    text item
    title bar
    title page
    toggle
    tool
    toolbar
    tool palette
    Top/Bottom tool
    trailer
    transaction
    trigger
    type

U

    update
    URL
    user exit
    user-defined action
    user-defined type
    user-defined macro
    user registry

V

    valid XML
    value axis
    value construct
    variable
    varying array type
    VBX
    VBX control
    view
    virtual directory
    virtual file system
    visual attribute

W

    Web
    Web browser
    Web cartridge
    Web link
    Web server
    well-formed XML
    wildcard
    window
    WKS
    WRB
    WWW

X

    XML

Y

Z

    zoom


In Project Builder, a command string supplied either by Project Builder or by the user that applies to files of a given type.  An action is not restricted to a single file type; for example, if the action "Compile" is defined for both forms and C source files, selecting "Compile Project" will compile all .fmb and .c files using the appropriate tools.  See also:  pre-defined action, user-defined action.


A high-level programming language developed by the U.S. Department of Defense for use in embedded computer systems.


A window or view that can receive user input.


(Oracle Express) The consolidation of data for several dimension values into a single value, such as the total units sold for all the cities, or into a smaller set of values, such as the average units sold for cities in each region. Data is often collected at the lowest level of detail and is aggregated into higher level totals for analysis.


A modal window that notifies you of a condition that occurred because of your last action.  You must respond to an alert.


The way in which data is positioned in a field.  It may be positioned to the left, right, center, flush/left, flush/right, or flush/center of the defined width of a field.


A secondary name used in a SQL statement to reference a table, view, or column.


(Oracle Express) A Selector dialog box tool that allows you to select all of the values in a dimension, or all of the values in a dimension for a specific hierarchy.


(Oracle Express) A dimension value at any level above a particular value in a hierarchy. The ancestor value is the aggregated total of the values of its descendants.  The following example shows the dimension value "Europe" as the ancestor of "France," "Lyon," and "Paris" in the GEOGRAPHY dimension.


A group that owns another group, either directly or indirectly.


A layout object used to fix a spot on one object to a spot on another object, ensuring the position of the first object in relation to the second object.


(Oracle Express) A specific dimension value that is used when selecting dimension values based on their family relationship or when navigating through a hierarchy.


A PL/SQL program unit that has no name and does not require the explicit presence of the BEGIN and END keywords to enclose the executable statements.  Since they are unnamed, anonymous blocks cannot be referenced by other program units.


An object that appears in another object's group tree at any position above that other object.  Every object is an antecedent of its descendants.


A Java term for small programs that can be dynamically imported into Web pages or applications as needed.


One or more program modules used to achieve a specific result.  Applications can be nested within other applications.  For example, an application to control a company's inventory could consist of various Form Builder applications for input of data, and various Graphics Builder applications to display the data visually.


1. An optional expression passed to a program unit for the program unit to operate on.
2. Clauses containing keywords and their specified values.


Acronym for American National Standard Code for Information Interchange.


A read only collection of PL/SQL subprograms, usually provided with an application to supply necessary PL/SQL source.  Program units in an attached library are implicitly loaded when they are first called by another program unit at compile-time or runtime.  They remain loaded for quick reuse, which reduces runtime memory requirements and improves the performance of an application.  See also:  library .


1. See property.
2.  A part or component of an Oracle8 object type definition.  Each attribute has a name and an Oracle8 datatype.  When an object table is created based on the object type, the columns in the table correspond to the attributes in the object type definition.  When a column object in a relational table is created based on the object type, the column object contains the equivalent of sub-columns, each of which corresponds to an attribute in the object type definition.  Each Oracle8 object attribute in a table is treated as a separate data item, as if it were a separate column.  This is true for both object tables and column objects in relational tables.
3. (Oracle Express) A descriptive characteristic that is shared by dimension values. Attributes represent logical groupings that allow users to select data based on like characteristics. For example, in a database representing footwear, you can use a shoe color attribute to select all boots, sneakers, and slippers that share the same color.


The columns that compose a column object (Oracle8).


In Graphics Builder, the first element in an attribute descriptor.  It is a 32-bit mask that informs the toolkit of the attributes that will be set or retrieved by a subsequent function call.  Only those elements that have their corresponding bits set in the attribute mask are set or retrieved.


(Oracle Express) A Selector dialog box tool that allows you to select dimension values based on attributes and attribute values. For example, in a database representing soft drinks, you might select all products whose size is 1 liter. In this example, Size is an attribute and 1 liter is an attribute value.


(Oracle Express) The values in the database that make up an attribute. For example, in a database representing ice cream products, the following attribute values might belong to a flavor attribute in a Product dimension: vanilla, chocolate, and strawberry. Dimension values at various levels in the Product dimension -- brand, form, and size, for instance -- might share the same value for flavor.


A type of chart in which the data is plotted according to where it lies along the chart's edges, called axes.  An axis chart may contain two or three axes, each of which may be one of the following types:  discrete, continuous, or date.


The white part of a pattern.  You apply color to the background element using the Fill Color palette.


A non-interactive process that runs in an operating system environment and performs some service or action.  Certain Oracle products use background processes for different tasks, such as performing and coordinating tasks on behalf of concurrent users of the database, processing and delivering electronic messages, and managing printing services.


The value used as the starting point for plotting fields on the continuous axis of a chart.


The executable that executes an application in batch mode, without using a windowing system.


To translate, at once, several PL/SQL program units into a machine executable form.


A reference to a parameter used to replace a single literal value (e.g., a character string, number, or date) appearing anywhere in a PL/SQL construct or a SQL SELECT statement.  For a bind reference, you must precede the parameter name with a colon (:).


A global variable you can create and use outside of a PL/SQL package.


1. The basic program unit in PL/SQL, defined by the keywords DECLARE, BEGIN, EXCEPTION, and END.
2. In Form Builder, a logical container that relates to a database table or view.


1. Contains the same information as the program unit specification, and also includes the actual implementation of the subprogram (i.e., its sequence of actions).  In most cases, only a body must be defined for a subprogram.
2. In Report Builder, a region that contains the bulk of the report layout (text, graphics, data, and computations).  The report body appears between the report header and trailer pages.


Text and/or graphics that appear in a report every time it is run.  In some products this is called "constant" text or graphics.


A user-defined outline around a layout object, usually used to highlight its contents.  Borders are drawn such that half the width of the border lies outside the edge of the object, and half inside the edge.


See inner bounding box, outer bounding box.


An axis chart that displays two categories of data along one axis.


A query column assigned to a break group.


A group used to break or categorize report data.  All data in query columns assigned to a break group appear above the data in columns belonging to other groups.  Each record fetched for a break group appears only once, while multiple related records in other groups can be displayed.


A report that contains categories that break the report data into sets.


A PL/SQL block that executes each time a breakpoint is reached.  See also:  breakpoint.


A debug action you can place at any executable source line in a PL/SQL subprogram.  A breakpoint interrupts execution of the program unit and passes control to the PL/SQL Interpreter.  Breakpoints allow you to incrementally execute a subprogram and search for runtime errors.  See also:  break trigger.


Refers to a component used to create a specific type of document. For example, Form Builder is used to create forms, while Report Builder is used to create reports.


A datatype defined and supported by the Oracle Server.


A collection of extensions to the PL/SQL language that are designed to be used with the software.  Built-ins include datatypes, procedures, functions, exceptions, and constants.


In Project Builder, macro shipped with Project Builder.  See also:  macro.


A temporary storage area for data during the transfer of that data between the computer and a peripheral, or between parts of a computer.  A buffer prevents loss of information due to differences in the speed or timing of the transfer and speeds up certain functions.


1. An interface element used to select an action item, display a dialog box, or acknowledge the current condition.
2. An object in an application that has a PL/SQL procedure associated with it.  The procedure is generally executed upon a user mouse event.


A PL/SQL procedure associated with an application object, that is executed when a specific mouse event occurs on the item at runtime (e.g. clicking the button).  It does this by establishing a hierarchy for the fetched data containing a break group.


A temporary storage place for database data that is currently being accessed or changed by users, or for data that Oracle Server requires to support users. The terms are often used interchangeably.


An interface that enables you to invoke Forms Developer or Reports Developer component executables from other Oracle products, or a 3GL program.


A chain of subprogram calls, from the initial entry point down to the currently executing subprogram.  Each subprogram call is represented by a frame on the downward-growing call stack, in which newly entered subprograms are added to the bottom of the stack.


   See editor.


In Form Builder, the background object on which you place the interface objects that users will interact with in the runtime application.  A canvas view is usually associated with a window.


Manageable objects that plug in to the client, application server, or database through an open API.


See style sheet.


A label component that allows differentiation between information on the basis of non-hierarchical delineation or compartments rather than sensitivity levels.  With classification, they are used as a means of restricting access to information.


See discrete axis.


An instance of data in a spreadsheet (e.g., Microsoft Excel).


Acronym for Common Gateway Interface. The industry-standard technique for running applications on a Web server.  Whereas standard HTML documents retrieved from a Web server are static (the exact same text is retrieved every time) CGI enables a program running on the Web server to communicate with another computer to generate "dynamic" HTML documents in response to user-entered information.


Acronym for Computer Graphics Metafile.  A file format recognized internationally and used to store and transport object-oriented graphics from one system or program to another.


An Oracle datatype provided for ANSI/ISO compatibility.  A CHAR column is a fixed-length column and can contain any printable characters, such as A, 3, &, or blanks, and can have from 1 to 255 characters or can be null.


A bar, line, symbol, or other graphical object that represents a single value for a field.


A bordered rectangle of any size used to embed Graphics Builder charts in a form or report.


A collection of attributes and properties that defines the format of a chart.


A interface element, appearing as a small square, that a user can toggle on or off.


An error-checking algorithm.


1. An object that is a member of a group, and is immediately below that group object in its group tree.  The objects that compose a group object are children of that group.  Every object is a child of its parent.
2. (Oracle Express) A dimension value at the level immediately below a particular value in a hierarchy. Values of children are included in the calculation that produces the aggregated total for a parent.  A dimension value may be a child for more than one parent if the dimension has more than one hierarchy.  The following example shows the dimension values "Lyon" and "Paris" as the children of "France" in the GEOGRAPHY dimension.


See detail query.


A hierarchical level that denotes the sensitivity of the information that is labeled.  Classifications are used as a means of restricting access to information.


A part of a SQL statement that does not constitute the full statement; for example, a "WHERE clause."


A user, software application, or computer that requests the services, data, or processing of another application or computer (the "server").  In a two-task environment, the client is the user process.  In a network environment, the client is the local user process and the server may be local or remote.


Separation of processing between two CPUs, one acting as the server that provides services in a transaction and the other as the client in the transaction, requesting and receiving services.  All responsibilities of shared data management can be processed by the computer running the database management system while the workstations running the database application concentrate on the interpretation and display of data.


A memory buffer.  An object remains on the Clipboard until you cut or copy another object, or until you quit the application.


A user-defined datatype that describes a data unit made up of an indefinite number of elements, all of the same datatype.  Varying array types and table types are collection types.  (Oracle8)


An argument that allows you to specify a file that contains a set of command line arguments for Forms Developer executables.


Contains all the colors available to the windowing system, the drawing surface, or a window and its views.


1. A vertical space in a database table that represents a particular domain of data.  A column has a column name (e.g., ENAME) and a specific datatype (e.g., CHAR).  For example, in a table of employee information, all of the employees' names would constitute one column.  A record group column represents a database column.
2. In Report Builder, a data model object created automatically for each column expression in a query's SELECT list, or created manually to perform summaries, formulas, or act as a placeholder.


In an Oracle8 relational table or object table, a column whose definition is based on an object type.  The column is a composite, the parts of which correspond to the attributes of the object type.


An operating-system command line.  Most Oracle products can be invoked from a command line using a number of executable arguments.


A language construct for the inclusion of explanatory text in a program, the execution of which remains unaffected.


To make changes to data in the database permanent.  Before changes are stored, both the old and new data exist so that changes can be stored or the data can be restored to its prior state.


(Oracle Express)

>	Greater than

<	Less than

=	Equal to

<=	Less than or equal to

>=	Greater than or equal to

<>	Not equal to

For numbers, the operands are widened to the least common type (Integer is preferred over Long, which is preferred over Single, which is preferred over Double). For Strings, the comparison is case-sensitive and is based on the collating sequence used by the language specified in the Windows Control Panel. The result is 0 for FALSE and -1 for TRUE.


To translate a source program into a binary, executable format.  PL/SQL program units must be compiled before they can be executed.


A specific target object that is a part of a larger application; e.g. a form in an order entry system, or a library that is shared among many other elements.  See also:  target.


The simultaneous access of the same data by multiple users executing different database applications or the same application.  In database software concurrency requires additional logic to ensure that all users see consistent data and that all changes are made in the proper order.


An expression whose value is either true or false, such as X> 100.  For example, you can set a condition in a query when you want the query to display only those rows that evaluate as true in your expression.


Layout editor mode that prevents child objects from being moved or resized on or outside their parent objects.


To log on to a database.  You must connect if you want to create or modify queries or access an application stored in a database.


The set of parameters, including a protocol, that SQL*Net uses to connect to a specific Oracle instance on the network.


A rule or restriction concerning a piece of data (such as a NOT NULL restriction on a column) that is enforced at the database level, rather than the object or application level.


A PL/SQL code structure.  There are two types of constructs: non-value constructs do not return a value; value constructs return a value.


A work area in which Oracle Server stores the current SQL statement, and if the statement is a query, the result's column headings and one row of the result.  Also referred to as cursor.


An axis that plots values that begin with one value and continue until they reach another value.  Thus, these values are mathematically related.  Dependent data is usually plotted along this axis, also called the value axis.


The four corner points of the virtual rectangle that constitutes an object's ideal shape.  When you select an object on the layout, its control points appear.  Depending on the object selected, you may be able to modify it by dragging a control point.


The two-dimensional grid on which drawing takes place.


The type of x, y units used to place and size objects in the Layout Editor view.  The coordinate systems available are 1) character, which uses the platform's native character cell size, and 2) real, which can be in units of pixels, centimeters, inches, or points.


The location points for objects appearing in the layout.  x and y coordinates determine the horizontal (x) and vertical (y) placement of objects.


To store a replica of a selected object on the Clipboard, so that it may be pasted elsewhere in an editor if desired.


A subquery based on the same tables as the parent query (i.e., references columns in the tables included in its parent query).


Group that owns two or more other groups and performs a mathematical cross product (i.e., correlates values between them).


See matrix cell.


1. A small icon representing the position of the mouse.  The shape of the cursor varies, depending on the selected tool.
2. An internal pointer to data retrieved by a query.  A cursor points only to one row of data at a time; however, you can use built-in subprograms to move the cursor to any row in the data set.


(Oracle Express) A formula created by a user, which allows fine-tuning of data analysis. You create custom measures using base measures in the database. For example, if the database contains the existing measure Volume Share, you can use this measure to create a custom measure named Volume Share Year Ago. This custom measure would allow you to look at a product's absolute share from the previous year.


To delete one or more objects and store them in the Clipboard, so that they may be pasted elsewhere in an editor, if desired.


A view is damaged when it is totally or partially occluded by another view.


A set of tables and views owned by the database administrator.  It is a central source of information for Oracle Server and other relational databases.


A data model object used to define a master/detail (parent-child) relationship between a group and a query.


A relational model that defines what data should be fetched from the database, what values should be computed, and how data should be ordered in a report.  Report Builder objects that define the data model are queries, groups, columns, parameters, and links.


A source for data returned by a query, including database objects such as tables, views, synonyms, snapshots, and queries stored as views.


In Project Builder, a node in the Project Navigator denoting a project item that governs the structure of an open project.  For example, the Global Registry node denotes the Global Registry, which contains a list of local registries and a list of global type definitions.


A set of dictionary tables and user tables that are treated as a unit.


A stored PL/SQL block that is implicitly executed when a triggering INSERT, UPDATE, or DELETE statement is issued against the associated table in the database.  See also:  debug trigger.


A standard form of data.  Some common Oracle datatypes are CHAR, VARCHAR2, DATE, NUMBER, LONG, RAW, and LONG RAW.


An Oracle Server datatype.  A date column may contain a date and time between January 1, 9999 BC and December 31, 9999 AD.


An axis that plots date values that begin at one date and continue until they reach another date.


To detect, diagnose, and eliminate runtime errors in programs.


A user-specified event placed in a PL/SQL subprogram, usually for the purpose of troubleshooting runtime errors.


A conditional debug action you can place at any executable source line in a PL/SQL subprogram.  If the specified condition is met, the specified action is taken before execution of the subprogram resumes.  See also:  database trigger.


The Procedure Builder functionality within the software component used for testing and debugging.


In Oracle8, object types and objects are composites, built of parts or attributes.  Forms Developer or Reports Developer treats each part/attribute as an independent, individually-selectable data item.  This treatment results in a deconstructed object (sometimes also known as a flattened object).  The object is not an homogeneous entity within the Developer environment.


A piece of data that depends on other data for its value.  For example, an employee's  years of service may have the value 10, which is dependent upon the value of the employee's name.  Also called value data.


A value supplied by the system when a user does not specify a required command parameter or attribute.


To prepare and provide a completed application for distribution and deployment.


In Project Builder, the mechanism used by Project Builder to determine how to compile files that are referenced in projects.  Items toward the top left of the tree depend on items toward the lower right; so, for example, if a header file is either out of date or more recent than the executable file it is used to build, then the Project Builder will rebuild the executable file, given a Build command.  Dependencies also extend to subprojects.  For example, if a subproject consisting of a function library has been rebuilt, the Project Builder "knows" the dependent project will need to be rebuilt in order to use the updated library functions.  See also:  dependency view.


In Project Builder, shows files in the Project Navigator in the order in which they depend on each other, with project nodes at the highest point in the hierarchy, followed by target nodes, which are followed by buildable components of those targets.  For example, an executable form depends on and will be followed by an .fmb file, which may depend on and be followed by a library used for a USEREXIT procedure, and so on.  See also: project view, target.


1. An object that appears in another object's group tree at any position below that other object.  Every object is a descendant of its antecedents.
2. (Oracle Express) A dimension value at any level below a particular value in a hierarchy. Values of descendants are included in the calculation that produces the aggregated total for an ancestor.  The following example shows the dimension values "France," "Lyon," and "Paris" as descendants of "Europe" in the GEOGRAPHY dimension.


The interface used to design different components of a Forms Developer or Reports Developer application.


See Oracle Designer.


An application modeling, design and generation toolset.  Using Oracle Designer, you can generate forms and menus from module definitions recorded in the Oracle Repository.


When defining a master/detail report, the detail query retrieves all related records for each record retrieved by the master, or parent, query.


A partial screen or window that prompts you to enter information necessary to complete an operation.


An intermediate representation of a program unit, generated by the compiler.  The DIANA includes all of the syntactic and semantic information for a compiled program unit.  In order to compile a reference to a program unit, the compiler must have access to the referenced program unit's DIANA.  For example, if program unit A calls program unit B, the DIANA for B must be available before A can be compiled.


(Oracle Express) A type of database object that is a list of values that provide categories for data. A dimension acts as an index for identifying values of a variable. For example, if you have sales data with a separate sales figure for each month, then the data has a MONTH dimension; that is, the data is organized by month. A dimension is similar to a key in a relational database.

Any item of data within a multidimensional variable can be uniquely and completely selected by specifying one member from each of the variable's dimensions. For example, if a sales variable is dimensioned by MONTH, PRODUCT, and MARKET, specifying "January" for the MONTH dimension, "Stereos" for the PRODUCT dimension, and "Eastern Region" for the MARKET dimension uniquely specifies a single cell in the variable. Thus, dimensions offer a concise and intuitive way of organizing and selecting data for retrieval, updating, and performing calculations.

A dimension can be simple, with values that are single text or integer values, or it can be conjoint, with values that are combinations of values in other dimensions. A composite is not a dimension, but it is a conjoint-like internal object that Express treats like a dimension for the purpose of handling sparse data.


(Oracle Express) One element in the list that makes up a dimension. For example, a computer company might have dimension values in the PRODUCT dimension called "LAPPC" and "DESKPC." Values in the GEOGRAPHY dimension might include "Boston" and "Paris." Values in the TIME dimension might include "MAY96" and "JAN97."


An interface element state that means a menu item, button, etc., cannot be used in the current context; i.e., it does not respond to keyboard or mouse input.


An axis that plots distinct values at fixed intervals.  These values are not mathematically related.  Independent data is usually plotted along this axis, also called the category axis.


A Graphics Builder document containing any combination of charts, images, text objects and shapes.


In Form Builder, an item that stores or displays information that must be fetched or assigned programmatically with PL/SQL.


As it pertains to the call stack, in which newly entered subprograms are added to the bottom of the stack.  The earliest frame, corresponding to the initial program entry point, is at the top of the stack, while the latest frame, associated with the most deeply nested subprogram call, is at the bottom of the stack.


Press and hold down a mouse button while you slide the mouse pointer to a particular location in a window.


(Oracle Express) To navigate up and down through the levels of aggregation in a dimension that has a hierarchy. When selecting dimension values or viewing data, you can expand or collapse a dimension hierarchy by drilling down or up in it. Drilling down expands the view to include child values that are associated with parent values in the dimension hierarchy. Drilling up collapses the list of descendant values associated with a parent value in the dimension hierarchy.

You can click on a plus icon ( [image]) to drill down on (expand) the hierarchy or on a minus icon ( [image]) to drill up on (collapse) the hierarchy.

In the Selector, you can click on the drill icon ( [image]) to display the Drill dialog box so you can select a level of detail for the entire dimension.


An option that allows you to copy objects directly on the layout without affecting the contents of the Clipboard.


(Oracle Express) A saved selection that can have different dimension values each time it is evaluated. The selected dimension values might differ from use to use. In contrast, a static saved selection is an unchanging list of dimension values. A dynamic saved selection script might include "Select the last three Months", while the script for a static saved selection might have "Select February 1996, March 1996, and April 1996".


Acronym for Extended Binary-Coded Decimal Interchange Code.


The boundary of an object.


A display box on the Layout Editor tool palette that shows the current settings for the edge, fill, and text patterns and colors.


A work area in which you perform a specific set of tasks, such as creating a program unit or designing an application.


A thing of significance about which you may need to record information.  Elements are described by attributes and associations to other elements.


Any element held in the dictionary is classified as being of a particular type.  Examples of element type are entity, attribute, program module, table, diagram, text, softbox.  Occurrences or instances of these are called elements.


(Oracle Express) Displays a dialog box related to the task you are performing, usually so you can select one or more values that you need.


An interface element state that means that a menu item, button, etc., can be used in the current context, that is, it responds to keyboard or cursor/mouse input.


An object that contains another object.  An object is considered to be enclosed by another object only if all of the following are true:  both objects belong to the same region (Body, Margin, Header, or Trailer);  the outermost of the two objects is a frame or repeating frame;  the outermost of the two objects is behind the other object;  the innermost of the two objects lies entirely within the borders of the other object.


The practice of scrambling (encrypting) data in such a way that only an intended recipient can unscramble (decrypt) and read the data.


The user's computer-based workplace, including the tools typically used and their configurations. For Project Builder, this refers to the subset of tools that are used to complete various tasks related to developing Forms Developer applications.


A warming or error condition generated from a PL/SQL subprogram.


(Oracle Express) A Selector dialog box tool that allows the user to select dimension values associated with data that meets cutoff terms that the user specifies. For example, the user can select all products whose dollar sales were greater than $10,000 for the week ending December 23, 1995 in the Boston market.


An argument you can pass to a product executable, such as 'OPENFILE=my_disp' and 'PRINT=YES'.  These arguments usually are specified when the executable is invoked from a command line.


See run.


To store a copy of an object, module, selected text or image to a file or database.  In Project Builder, the process of writing out a file containing project, type, action, and/or macro definitions in a portable format for distribution to others who may work on heterogeneous platforms.  See also:  export file, import.


In Project Builder, the shareable, portable file created by exporting a project.


1. A PL/SQL construct combining variables, constants, literals, and operations on their values.
2. (Oracle Express) One or more data values that are specified in an Express language program. For example, you can specify expressions as arguments to programs or functions. An expression can be any of the following:

•	A single, literal value (for example, 10)

•	A variable or formula that contains one or more values

•	A function that returns one or more values

•	A calculation that combines values, such as variables, formulas, or functions, with arithmetic or Boolean operators


See library.


An ANSI-standard SQL SELECT statement that can be referenced by other Oracle products.


(Oracle Express) A group of related dimension values that correspond to the levels of the dimension hierarchy. Dimension values within a family can relate to each other as ancestor, parent, child, and descendant.  The following example shows family relationships for the dimension value "France" in the GEOGRAPHY dimension.


(Oracle Express) A Selector dialog box tool that allows the user to select a family of dimension values for a particular hierarchy. A family includes a parent and all of its descendant dimension values. For example, the user can select all of the family members below the Chicago market level in the Geography dimension.


1. An interface element in which you enter, edit, or delete data.  2. In Graphics Builder or Report Builder, a layout object that define how the data for a specific query column appears.


In Graphics Builder, a collection of attributes and properties that determines how dependent data (values) are represented as chart fields, such as bars or lines.


In Project Builder, a node in the Project Navigator denoting a file stored in the file system.  For example, the global and local registry nodes correspond to global and local registry files.


The area within an object to which a fill (color or pattern) should be applied.


A PL/SQL function or anonymous function that restricts the data displayed by a group.


A computer that regulates access to computers on a local area network from outside, and regulates access to outside computers from within the local area network.


See deconstructed object.


Layout Editor mode that automatically resizes or shrinks parent objects when child objects are moved or resized, and adjusts the positions of other layout objects that might otherwise be affected by the repositioning or resizing of the objects.


The state of an entity that is able to respond to input from the user or the client.  If an entity has the keyboard focus, it can receive events when the user presses a key.  If a drawn view has the drawing focus, it can respond to client routines that affect drawing.


The black part of a pattern.  You apply color to the foreground element using the Edge Color palette.


A value or column in one table that refers to a primary key in another table.


In Report Builder, a default layout style in which labels appear to the left of fields, and each record prints on a separate logical page.


In Report Builder, a default layout style that intermixes boilerplate and fields, wrapping lines at "word" breaks.


(Field Properties)  A setting that defines the appearance of the value of a field.  For example, a format mask is used to specify the display of currency amounts and dates.


A PL/SQL function that allows you to dynamically change the formatting attributes of an object.


(Oracle Express) A type of database object that represents a stored calculation, expression, or procedure that produces a value. A formula provides a way to define and save complex or frequently used relationships within the data without resaving the data itself. Each time you use a formula, Express performs the calculation or procedure that is required to produce the value.


A user-created column that gets its data from a PL/SQL function or expression, a user exit, a SQL statement, or any combination of these.


In Graphics Builder, the part of a chart template that defines the basic structure of a chart, but does not determine the way the data is plotted.
2. In Report Builder, a layout object used to enclose other layout objects and control the formatting, frequency, and positioning of several objects simultaneously.


A PL/SQL subprogram that performs a specified sequence of actions, and then returns a value.  Can reside and execute on the client and the database server.


To save an application to a file or database in a binary format that can be executed by Runtime and Batch executables.


The Form Builder executable used to save an application to a file or database in a binary format.


A Project Builder registry that stores information common to an entire Forms Developer or Reports Developer installation.  This information is restricted to type definitions, and their associated actions and pre-defined or user-defined properties.  The use of the Global Registry is optional; its functions can be performed by individual user registries.  See also: registry, user registry.


A logical container that exists across an application.  When an application uses a global variable, the application maintains the variable until the application is exited, or until another object explicitly removes it.


To give a user access to a module.  Only a module's creator can grant its access to other users.


In Graphics Builder, the lines that separate rows and columns in the Layout Editor.


1. An object that is composed of several other objects.
2. In Report Builder, a data model object that is created automatically to contain all the columns selected by a query, or created by the user to modify the hierarchy of the data appearing in a report;  it is used primarily for creating breaks in a report, as well as for resetting computations.
3. In Project Builder, collections of related items available via submenus off the Launcher.  Groups allow users to set up the Launcher much like the Windows 95 Start menu, with arbitrary "groups" as entries that pop up to reveal other items and/or groups.  For example, a user could define a group named "Project Docs", containing a project schedule, to-do list, component specifications, etc.; some files may be .TXT files, other .DOC files.


A PL/SQL function that restricts the data fetched by a group.


A hierarchical model-similar to a "family tree"-that describes the relationships between group objects and the objects that compose them.


Acronym for graphical user interface. The use of pictures rather than just words to represent the input and output of a program.  Programs with GUIs run under a windowing system (such as  X Windows, Microsoft Windows, Apple Macintosh, and so on).  GUI programs display icons, buttons, and so on, in windows on the screen;  users control the GUI programs mainly by moving a pointer on the screen (typically controlled by a mouse).  Also known as a bitmapped interface.


An internal pointer to an application object.  In order to reference an object in a PL/SQL program unit, you must pass its handle as an argument to the appropriate subprogram.


An optional report region that can contain introductory material for the report, including text, graphics, data, and computations.  The report header appears first, before the body and trailer.


A node in the Object Navigator denoting a type of object which can be contained within or associated with a report.  Selecting a heading node does not select an object in the report.


(Field Properties)  A property that specifies whether Report Builder will suppress the indicated field at its location in the layout.  If Hidden is specified, the field's value will appear only where referenced in text objects (using &fieldname).


(Oracle Express) A means of organizing and structuring data. A hierarchy exists when values within a dimension are arranged in levels, with each level representing the aggregated total of the data from the level below. Some dimensions have multiple hierarchies based on them.

The following example shows a hierarchy based on the GEOGRAPHY dimension, in which dimension values are arranged in five levels. Data at the Customers level is aggregated into the Cities level, which, in turn, is aggregated into the Countries/Areas, Continents/Regions, and Global levels.


Acronym for Hypertext Markup Language.  A tag-based ASCII language used to specify the content and links to other documents on Web servers on the Internet.  End users with Web browsers view HTML documents and follow links to display other documents.


Acronym for Hypertext Transfer Protocol. The protocol used to carry WWW traffic between a WWW browser computer and the WWW server being accessed.


A reference (link) from some point in one document to (some point in) another document or another place in the same document.  A Web browser usually displays a hyperlink in some distinguishing way (in a different color, font or style).  When users activate hyperlinks (by clicking on them with a mouse) the browser displays the target of the link.


A collection of documents containing cross-references which, with the aid of a Web browser, allow readers to move easily from one document to another.


Cross-references which, with the aid of a Web browser, allow readers to move easily from one document to another.


Acronym for interface C definition-a PL/SQL application programming interface to functionality implemented in C.


A graphic representation of a window or tool.


A bitmapped object that can be stored and loaded into an application.  The client cannot modify an imported image.


In Project Builder, a project item, usually the result of automatic generation, that Project Builder recognizes and for which it automatically creates entries in the Project Navigator.  For example, Project Builder can recognize .OBJ files, generated as an intermediate step during the compilation of C source files, and create entries for them in the Project Navigator.  Although resetting the properties of an implied item is of limited use (the next compilation will destroy changes) such items can be useful, as they can be examined via actions such as Edit, View, and Print.  See also: item, action.


1. To read a module from the file system or database, and incorporate it into an application.
2. To read in a file containing project information.


A piece of data that does not depend on other data for its value.  For example, an employee's name may have the value Jones, which is independent of the values of other employee's names or associated data.  Also called category data.


An optional structure associated with a table that is used by Oracle Server to locate rows of the table quickly, and (optionally) to guarantee that every row is unique.


In Project Builder, to obtain information for an action, type, macro, or property definition from an ancestor node in the dependency tree.  If related attributes exist in an ancestor node, they may be inherited.  Thus, filesystem items like forms and documents may inherit action definitions from subprojects, projects, the user registry, or the Global Registry; projects may inherit type definitions from the local or the Global Registry; and so on.


The virtual rectangle that constitutes an object's ideal shape and connects its four control points, regardless of edge thickness or other attribute settings.


The file used to build a target: for instance, an .FMB is the input for an .FMX.  Also called source.


A mode in which each character you enter is inserted at the cursor, pushing the following characters to the right.  The opposite of replace mode.


A worldwide TCP/IP-based network of computers.


A file containing any mixture of PL/SQL source, Procedure Builder commands, and SQL statements.


An internal TCP/IP network, access to which is restricted (via a firewall) to individuals inside the company or organization.  An intranet provides similar services within an organization to those provided by the Internet, but is not necessarily connected to the Internet.  A common example of an intranet is when a company sets up one or more Web servers on an internal network for distribution of information or applications within the company.


A four-part number with no more than three digits in each part that uniquely identifies a computer on the Internet.


In Project Builder, an object in the file system associated with the project, such as a form or a document, pointed to or represented by a node in the Project Navigator.


In Project Builder, an entry in the Project Navigator window, consisting of an icon and a descriptive title.  Nodes point to actual items in the filesystem, such as Form Builder files and documents.  See also: node, item.


Acronym for Java ARchive.  A file used for aggregating many files (Java class files, images, and so on)  into one file.


A computer language that supports programming for the Internet in the form of platform-independent "applets".


Combining data from two (or more) tables in a single SELECT statement.


  See alignment.


1. Part of a command line syntax that must be supplied with a corresponding argument.  2. A required part of a PL/SQL construct.


Text identifying the values or meaning of an application's visual objects.


(Oracle Express) A position in a dimension hierarchy. Each level above the base level represents the aggregated total of the data from the level below. For example, the TIME dimension might have ascending levels such as Month, Quarter, and Year. Within a dimension hierarchy, a dimension value at one level has a family relationship with the dimension values at the levels above and below that level.


(Oracle Express) A Selector dialog box tool that allows the user to select dimension values based on their level in a particular hierarchy. For example, the user can select all of the dimension values at the Key Account level in the Geography dimension.


The area of an editor in which you can create, modify, position, or delete objects.


A unit of measurement for ensuring a consistent measurement system across different display devices.  Used to specify a position on the layout, or the dimensions of an object.


A reference to a parameter used to represent a string of text in a SQL SELECT statement.  For a lexical reference, you must precede the parameter name with an ampersand (&).


A collection of one or more PL/SQL program units that are stored together in a file on the client file system or in the database.  Program units stored in a library can be referenced by several applications at once.  Unlike stored program units, which reside and execute in the database, program units in libraries always load and execute within the PL/SQL engine on the client side.  To reference program units that are stored in a library, you must first attach the library.  See also:  attached library.


A data model object used to define a master/detail (parent-child) relationship between a group and a query.


The name of a file to which a boilerplate object is associated.  At runtime, Report Builder imports the file's contents into your report layout.


A list of items within a dialog box.  Usually, the list box offers choices for selection, and it is scrollable.


In Form Builder, an item that displays a list of choices.


Also called LOV. A scrollable popup window that provides the user with a single or multi-column selection list.


(Oracle Express) A Selector dialog box tool that allows you to select dimension values from a list of the currently selected values in the dimension.


1. The database on the computer running the application.  2. The database to which an application is connected.  This database parses and executes all SQL statements generated by the application.


See user registry.


A PL/SQL variable declared only within the scope of the current program unit.


(Oracle Express)

Not	Unary Not -- Operand can be integer or long. The operation is performed bitwise (one's complement).

And	And -- Operands can be integer or long. The operation is performed bitwise.

Or	Inclusive Or -- Operands can be integer or long. The operation is performed bitwise.

Xor	Exclusive Or -- Operands can be integer or long. The operation is performed bitwise.

Eqv	Equivalence -- Operands can be integer or long. The operation is performed bitwise. (A Eqv B) is the same as (Not (A Xor B)).

Imp	Implication -- Operands can be integer or long. The operation is performed bitwise. (A Imp B) is the same as ((Not A) OR B).


One page of your actual report (it may be displayed on one or more physical pages).


In a PL/SQL subprogram, a conditional statement that is repeated until the condition is no longer met.


In Project Builder, a type-specific variable that may be used to customize and extend actions.  A macro may be either a constant or a simple expression (which, in turn, may contain other constants and/or expressions).  The use of macros adds great flexibility in issuing command options, and in allowing the user to modify sets of commands by changing one property definition.


A default layout that displays records in one or more columns on the page.  Fields appear directly beneath each other in a single repeating frame.


An optional report region that appears at the top and bottom of each logical page in the body region.  The margin may include any layout object, but typically contains boilerplate and fields (for page numbers, page totals, grand totals, and current date and time).


A default layout in which the detail records appear below the master records.  Master records appear in form layout style, while detail records appear in tabular layout style.


(Oracle Express) A Selector dialog box tool that allows the user to select dimension values by matching characters in dimension value names to characters that the user specifies. For example, the user can select all dimension values in the Product dimension at the Brand level that contain the characters "new."


A repeat direction that displays each record of a repeating frame as a cell of a matrix.


A default layout, resembling a grid, that formats the four sets of data necessary for a matrix report; one set of data displays as a row of labels, one as a column of labels, one set creates the grid, and one set fills the cells of the grid.


A cross-tabulation of four sets of data:  one set of data displays across the page; one set of data displays down the page; one set of data is the cross-product, determining all possible locations in which the across and down data relate and placing a cell in those locations; and one set of data displays as the "filler" of the cells.


(Oracle Express) Data that can be examined and analyzed, such as sales or cost data. You can select and display the data in a measure. Measures can be stored as variables or relations, or measures can be calculated by means of formulas.


A unit of memory equal to 1,048,576 bytes (1024 x 1024). Often rounded to one million bytes.


A modal window that notifies you of a condition that occurred because of your last action.  You must respond to a message box.


Executable code attached to an Oracle8 object through its object type definition.


A window that elicits a response from the operator before the application can continue.


An unrestricted window that can have as much functionality as the runtime window manager can support.


1. An object that can be shared by multiple applications.  External queries and PL/SQL libraries are examples of modules.  2. In Form Builder, a major component of an application (i.e., a menu module).


A field in a window or dialog that extends for several lines, allowing you to scroll vertically through the information.


(Oracle Express) A special data value that indicates that data is "not available" (NA). NA is the value of any cell to which a specific data value has not been assigned or for which data cannot be calculated.


Matrix report in which either the across and/or down dimension contains one or more master/detail relationships.  There is no limit to the amount of nesting allowed.


A datatype indicating that the data will be fetched when the page on which it appears is formatted (instead of fetched and cached until formatted).


1.  A specific location in the Object Navigator object hierarchy.  Nodes can be expanded to reveal information about an object, such as cross references to other objects.  2.  In Project Builder, an entry in the Project Navigator.  See also:  item, item node, organization node.


A report column that references a database column of datatype NOCACHE.


The absence of a value.


(Oracle Express)

^	Exponentiation,

-,+	Unary minus and plus,

*, /	Numeric multiplication or division. For division, the result is a double.

\	Integer division. The operands can be integer or long.

Mod	Modulus or Remainder. The operands can be integer or long,

-, +	Numeric addition and subtraction. The + operator can also be used for string concatenation.


1.  An item that can be placed on the layout.  The following are examples of objects:  rectangle, line, ellipse, arc, polygon, polyline, rounded rectangle, freehand, chart, text, symbol, and text field.  2.  In Oracle8, an instance of an object type.  An object can be a row in an object table, or the portion of a row contained in a column object in a relational table.


See column object.


A container for a group of objects, used to provide the ability to copy or reference related objects.


See handle.


The window containing a hierarchical list of objects for the current session.  The list appears in outline form, and enables the user to accomplish several tasks such as creating, editing, renaming, and deleting objects.


In Oracle8, a table whose definition is based on an object type.  Each attribute in the object type becomes a column in the object table.  Each row in the table is an individual Oracle8 object.


In Oracle8, a user-supplied definition or template, upon which objects can be built.  An object type is a composite, whose parts are attributes.  Each attribute has a name and a datatype, and the object type as a whole has a name.  An object type may also include one or more methods.


Acronym for Open Database Connectivity.  A standard for accessing different database systems.  An application can submit statements to ODBC using the ODBC flavor of SQL.  ODBC then translates these to whatever flavor the database understands.  Using the Oracle Open Client Adapter (OCA), an application can access different database management systems in one consistent manner.  This allows an application developer to develop, compile, and ship an application without targeting a specific DBMS.


Object Linking and Embedding.


An application that can store and display OLE objects.


An application that creates OLE objects.


See built-in datatype.


An environment variable that indicates the root directory of Oracle products.


An entry in the Project Navigator, such as "Types", "Projects", "Form Files", etc., used to help organize the Navigator and indicate the filetypes of child items.  It may also correspond to a data structure within certain types of filesystem objects.


Represented in x and y coordinates.  The origin of a view indicates the location of the view on its parent's canvas.  The origin of a canvas indicates the position of drawing on a drawn view.


The smallest virtual rectangle that completely surrounds an object.


When an object's contents cannot fit on the current logical page.  In the event of overflow, some of the contents are moved to the next logical page.


A PL/SQL function provided with the software that can be referenced anywhere within a program unit.


A method of encapsulating and storing related procedures, functions, variables, and other package constructs together as a unit in the database or on the client.  While packages provide the database administrator or application developer organizational benefits, they also offer increased functionality and database performance.  The software provides several built-in packages which contain PL/SQL constructs that can be referenced by client-side program units (e.g., TEXT_IO package).  Several packages are also supplied with the Oracle database server for referencing by stored program units (e.g., DBMS_OUTPUT package).


Includes the actual implementation of the package, which may include private subprograms and datatypes.  The body is optional if the package consists only of declarations.


Declares the public interface to the package-that is, the datatypes and subprograms that can be referenced by other program units.


(Oracle Express) 1. A slice of data included in a report. Additional pages display additional slices of data.

The following figure shows three pages of data.

2. In Express, a page can also refer to a unit of storage in an Express database.


(Oracle Express) A representation of how data selected for a query will appear in your spreadsheet. The page layout shows three edges -- Across, Down, and Page. The edges indicate how the multi-dimensional datacube defined by the query will appear in the two-dimensional spreadsheet. Dimension tiles display the names and positions of the dimensions. A dimension tile on the Across edge indicates that this dimension will appear as a column. A dimension tile on the Down edge indicates that this dimension will appear as a row. A dimension on the Page edge indicates that this dimension will appear in a page box at the top of the results.


A setting that controls whether to move the current object and its children to the next logical page.  You can set page breaks to occur before or after the current object appears in the report output.


An interface element that displays tools, colors, or patterns available in an editor.


A distinct portion of an editor where work is performed, usually resizeable.


The number of physical pages needed to print one logical page.


1. A PL/SQL construct used to pass information to a subprogram.  For example, in the subprogram call MYPROC (x),x is a parameter.  2. In Forms Developer, parameters are used to pass information between components such as Form Builder and Graphics Builder.  3. A variable you can change at runtime.


A group of parameter names and associated values used to pass information between Forms Developer components.


1. An object that is immediately above another object in its group tree.  A group object is the parent to each of the objects that compose it.  Every object is the parent of its children.
2. (Oracle Express) A dimension value at the level immediately above a particular value in a hierarchy. The parent value is the aggregated total of the values of its children.  The following example shows the dimension value "France" as the parent of "Lyon" and "Paris" in the GEOGRAPHY dimension.


To place the contents of the Clipboard (cut or copied objects) at the current cursor location.


A graphical property you can apply to the edge or fill of most objects.


The machine code or instruction list for a compiled program unit.


Acronym for Portable Document Format.  A file format (native for Adobe Acrobat) for representing documents in a manner that is independent of the original application software, hardware, and operating system used to create the documents.  A PDF file can describe documents containing any combination of text, graphics, and images in a device-independent and resolution independent format.


The size of a page that is output by your printer.


The type of element used to plot a field on a chart, such as bar, line, or symbol.


Oracle's proprietary extension to the SQL language.  Adds procedural and other constructs to SQL that make it suitable for writing applications.


A Procedure Builder Interpreter script file.


A PL/SQL library file that contains source code and compiled code. The file can be opened in Procedure Builder for editing.


A text file that contains the PL/SQL source of a single program unit.


A runtime PL/SQL library file that contains executable code only.  The file cannot be opened in Procedure Builder for editing.


A list that pops up when the user performs a particular action.


A number that TCP uses to route transmitted data to and from a particular program.


An action shipped with Project Builder and automatically available to the user via a menu item and/or a toolbar button.  Pre-defined actions include Build, Deliver, and several source control options.  When a pre-defined action is defined for a supported file type, the action is invoked for any selected item of that type when the user calls the action from Project Builder.  See also:  action, user-defined action.


See built-in datatype.


A setting that affects the behavior of the application's interface.


(Oracle Express) View the script that would be generated if you accept the current criteria. In the dialog box for any Selector tool you can preview your selection by choosing the Preview button.


The interface with which you view and scroll through report output online.


A column in a database table whose members consist of unique values that can be used to identify a row in a table.


See built-in datatype.


In a printer definition file, an escape sequence to which you assign a printer code that can be referenced in a report.


A file that contains a printer definition in text (e.g., ASCII or EBCDIC) format.


A PL/SQL subprogram that performs a specified sequence of actions. .  Can reside and execute on the client and the database server.


A PL/SQL code structure that can be independently recognized and processed by the PL/SQL compiler.  Anonymous blocks, subprogram specifications and bodies (procedures and functions), and package specifications and bodies are types of program units.  Program units can reside and execute on the client and the database server.  See also:  stored program unit.


The basic data structure created by Project Builder.  Projects are collections of pointers to all the files you consider part of your application, or otherwise want to group together conceptually.  You can define the editors, compilers, and other tools you use to work with the items contained within a project.  All project information is stored within a project registry.  See also:  project item, project file, export.


In Project Builder, project files are all the files associated with an application.  Project files can be of any type, including forms, reports, displays, 3GL modules, documentation, and test scripts. See also:  project, project item.


In Project Builder, anything for which properties may be defined, including all entities stored under the Projects node, types, actions, and macros. See also: project, project file.


The window containing a hierarchical list of project items for the current session.  The list appears in outline form, and enables the user to accomplish several tasks such as creating, editing, renaming, and deleting objects.  Although only one is visible at any time, the user can choose from two different schema by which to organize the objects.


The top-level node for a project.  It represents or points to the project file.  See also: project, project file.


In Project Builder, the file that stores all the information associated with a project:  files belonging to the project, types, actions, and macros defined for the project, and so on.


The project view shows objects organized by their type, and by their project/subproject relationships.  The projects are organized alphabetically by project file, then alphabetically by category.  See also:  dependency view, project, project file, view.


A dialog that assists the user in accomplishing the steps necessary to create a new project or sub project.


A characteristic of an object that determines the behavior or appearance of that object.


A named object that contains a list of properties and their settings used to globally control the appearance and functionality of objects based on that class.


A string you can enter when connecting to a database or server that allows you to connect to a remote database or server.


(Oracle Express) When you select values for one of the dimensions in a measure, the other dimensions that make up that measure are known as qualifying dimensions.

Suppose your Sales measure is dimensioned by Product, Month, and Geography. If you select the top ten cities based on Sales, you must also specify which product and month you want to consider when you compare Sales values. In this case, Product and Month are the qualifying dimensions.


A SQL SELECT statement that specifies the data you wish to retrieve from one or more tables or views of a database.


A PL/SQL function that, when executed, filters data retrieved by a query.  Only the data that meet the conditions in the function are charted.


An option that terminates the current session and returns the user to the operating system.  On some systems, Quit is Exit.


A control (similar to a check box), appearing in sets of two or more, only one of which may be either "on" or "off" at any given time.


A set of two or more radio buttons, only one of which may be either "on" or "off" at any given time.


(Oracle Express) A Selector dialog box tool that allows the user to select consecutive time periods as dimension values. These time periods can be based on any of the following:

•	A specific number of the most recent time periods

•	A beginning time period and a most recent time period

•	A beginning and ending time period

For example, the user can select dimension values for the range from the week ending November 25, 1996 through the week ending December 30, 1996.


A cipher designed by RSA Data Security, Inc. which can accept keys of arbitrary length, and is essentially a pseudo random number generator with the output of the generator being XORed with the data stream to produce the encrypted data.  For this reason, it is very important that the same RC4 key never be used to encrypt two different data streams.


Acronym for Relational Database Management System.  A database that allows the definition of data structures, storage and retrieval operations, and integrity constraints.  In such a database, data and relations between them are organized in tables.


A file that contains a single report definition in binary format.  .RDF files are used to both run and edit reports.


A file that contains a single report definition in binary format.  .rep files are used solely to run reports; you cannot edit a .rep file.


The ability to query the data throughout a database as of a single point in time.


One row fetched by a SQL SELECT statement.


An internal data structure object with a column/row framework, that belong to the form module in which they are defined.


A type of column that points to data in other column objects or object tables (Oracle8).


A line that appears at a specified value on a chart.


(Oracle Express) Fetch the most recent data from an Express database after query results are displayed in your report. When you open a saved Express query, the results are refreshed automatically.


To add the definition of a type or a new project to either the global or local registry.  Upon being registered, a new type can have actions defined for it, while a new project can have its referenced files identified as specific types, so that actions can be invoked against them.  See also:  registry.


A global and/or user-specific configuration file used to store project definitions and environment information.  See also:  Global Registry, user registry.


A symbol used in search criteria to indicate a comparison between two values, such as the equal sign in "WHERE DEPTNO = 10." Rows in which the comparison results in "true" are returned in the result (fetched), while rows in which the comparison returns "false" are rejected from the result.


A database on a computer other than the local database.  Usually a computer on the same network, but at a different node (i.e., a database that you use through a database link).


A layout object used to display rows of data that are fetched for a group.


A mode in which each character you type replaces the current character at the cursor.  The opposite of insert mode.


The objects that are used to define a report's data model and layout.  Report-level objects are assigned to a single report, and cannot be shared by other reports or other Oracle products. Objects include reports, queries, external queries, external PL/SQL libraries, groups, columns, parameters, links, frames, repeating frames, fields, boilerplate, and anchors.


Available on Windows NT and Windows 95 only, waits for any command lines to run a report, then places that report in its queue to run in the background on the local server.  Typically command lines are received from Form Builder or Graphics Builder via RUN_PRODUCT, or from another report via SRW.RUN_REPORT.  You can also use RWISV60 to submit a report to the Reports Background Engine.


Maintains timestamp and status information about reports managed by the Reports Server.


Available on Windows NT, Windows 95, and UNIX, enables running reports on a remote server.  Queues job requests and allows you to schedule when to run reports.  Use RWCLI60 to send a report to the Reports Server.


Provides an interface between your Web Server and Reports Runtime, enabling you to run reports dynamically from your Web browser.


Provides a connection between a Web Server and the Reports Server.


A file that contains a report definition stored in text (e.g., ASCII or EBCDIC) format.  .REX files are used to port reports from one platform to another. Note that .REX files are no longer required. They are available for backward compatibility.


Refers to the intensities of the red, green, and blue components of a color.


The group object to which all other objects in an application belong.


Top-level node for a project representing user's environment.  May correspond to the user registry or the Global Registry, depending on which registry has been chosen to set defaults.


1. One set of field values in a table; for example, the fields representing one employee in the example table EMP.  2.  To generate a runtime version of an application.


An object in an Oracle8 object table.  Each row in the table is an object.  However, in Forms Developer an object table is deconstructed into individual columns, so that the row object no longer exists as a single entity (only as its separate attributes).


To execute a runtime version of an application or program unit.


Acronym for Rich Text Format.  A non-platform-specific file format based on control words used to represent unformatted text and images in various applications and operating systems.  RTF uses the ANSI, PC-8, Macintosh, or IBM PC character set to control the representation and formatting of a document, both on the screen and in print.


See rep file.


The interface in which users view and interact with a runtime application.


A screen or window appearing optionally at runtime in which a user can modify print options and parameters prior to report execution.


(Oracle Express) A set of dimension values that has been saved either as a list of values or as a script. You can save selections that you build, or your database administrator can create saved selections and make them available to you.

Saved selections are a time-saving feature: each time that you need to re-use a selection, you can simply retrieve a saved selection without having to recreate it.


(Oracle Express) A Selector tool that allows you to retrieve a selection of dimension values that has been saved.


A collection of related database objects, usually grouped by database userid.  Schema objects includes tables, views, sequences, stored program units, synonyms, indexes, clusters, and database links.


The level at, or range in which, an object operates.  For Project Builder:  1.  Describes the range of files affected by Compile and Compile All commands invoked against compilable project items.  2:  Describes the range of files influenced by inheritable definitions.


See SQL script, Interpreter script.


In Report Builder, one of multiple layouts in the same report, each with a different target audience, output format, page layout, page size, or orientation.  For example, a single report can include an executive summary for senior management in one section and also a detailed breakdown for  individual managers in another section.


A SQL statement that specifies which rows and columns to fetch from one or more tables or views.


(Oracle Express) The set of dimension values currently chosen for a dimension, or the script that contains conditions or criteria to specify those values.


(Oracle Express) As shown on various dialog boxes in the Selector, a description of the choices that a user has made in selecting dimension values. It is a translation, in ordinary language, of the Express Language script.


(Oracle Express) A set of tools that you use to choose the dimension values to include, for instance, in a report, table, graph, or dimension list box. For example, you can use the Selector to choose the top 10 cities based on Sales or those cities whose Sales exceeded Quota by 15 percent. The Selector also allows you to sort dimension values and to create and retrieve saved selections.


(Oracle Express) A dialog box or group of dialog boxes that provides a particular way of selecting or sorting dimension values. Each Selector tool is part of the Selector. You can use Selector tools to select dimension values based on their level in the hierarchy, their attributes, the characters in the value name, and so on. You can also use Selector tools to sort the dimension values.


The period between invoking and quitting the executable.


One of two or more objects that have the same parent object in its group tree.  All objects that belong to one group object are siblings.


The combination of an IP address and a port number.


(Oracle Express) A Selector dialog box tool that allows you to rearrange the order of dimension values in a displayed selection. You can sort a selection in ascending or descending order by the following:

•	Alphabet

•	Order in the database

•	Data value

•	Order or level in the hierarchy

•	Time period

You can also sort a selection manually, by dragging and dropping dimension values.


In PL/SQL:  Statements provided in a subprogram or package.  You must have the source code for a program unit before you can compile it into executable form.  In Project Builder:  The input types required to build target types.


Defines only the names, parameters, and return type (applies to functions only) of the subprogram.


A small rectangle at the bottom of the vertical scrollbar and to the left of the horizontal scrollbar in an editor.  Dragging and releasing the mouse button creates a new pane, and divides the scroll bar into separate scroll bars for each pane.


A standard interface for storing and retrieving information in a relational database.  SQL is an acronym for Structured Query Language.


A that contains a query stored in text (e.g., ASCII or EBCDIC) format.


A file containing SQL statements that you can run to perform database administration quickly and easily.  Several SQL scripts are shipped with Oracle products.


A SQL instruction to Oracle.  A SELECT statement is one type of SQL statement.


See call stack.


A PL/SQL construct used for conditional, iterative, and sequential control, and for error handling.  A semi-colon (;) must terminate every PL/SQL statement.


(Oracle Express) A saved selection that contains the same dimension values every time it is evaluated. Static saved selections list the dimension values that are to be selected, rather than the selection criteria. For example, a static saved selection script might include "Select February 1996, March 1996, and April 1996", while the script for a dynamic saved selection might have "Select the last three Months".


A procedure, function, or package that resides and executes in the Oracle database server.


A procedure or function that resides and executes in the Oracle database server.


HTML extensions that provide powerful formatting flexibility in HTML documents.  To view an HTML document that takes advantage of style sheets, display it in a browser that supports style sheets.


A PL/SQL procedure or function.


In Project Builder, a project contained within another project file, rather than being stored in its own file.  See also:  project.


In Project Builder, a node denoting a subproject.  See also:  project, project node, subproject node.


A column, with a type of summary, which computes subtotals, grand totals, running totals, and other summaries of the data in a report, using one of the packaged summary functions provided by Report Builder.


The file format used by the Microsoft Excel program.


The orderly system by which commands, qualifiers, and parameters are combined to form valid command strings.


One of the variables that are provided with Reports Developer (e.g., &Logical Page, DESTYPE).


A named collection of related information, stored in a relational database or server, in a two-dimensional grid that is made up of rows and columns.


A collection type, which defines a data unit made up of an unordered set of elements, all of the same data type.  The corresponding data unit is a single column nested table, the column type of which is a built-in type or an object type.  (Oracle8)


A default layout displaying labels at the top of the page and rows of data underneath the labels.


In Project Builder, any item in the middle of the dependency tree; for example, an executable is a (compile) target for a library; while a library is a target for a group of objects; and an object is a target for a source file.


A node denoting a target.  See also: target.


Acronym for Transmission Control Protocol. The underlying communication protocol for exchanging HTTP requests between clients and Web servers.


A submenu that the user can remove from its source using a mouse or other pointing device and drag to another part of the display screen.


In Form Builder, an item that displays a character string value.


The horizontal area at the top of a window that displays the name of the application or interface element in that window.


See header.


To turn a setting alternately on or off.  For example, you can hide or show the Object Navigator.


An iconic button used to create and manipulate objects in an application.


Collection of iconic buttons that perform product commands.  Usually aligned horizontally along the top, or vertically down the side of a window.


A collection of tools.


(Oracle Express) A Selector dialog box tool that allows the user to select dimension values based on the top x, bottom y, or top x and bottom y data values. For example, the user can produce a dimension selection that contains products whose Volume Sales were among the top 10 percent of products in the Total U.S. for the four-week time period ending December 30, 1995.


A optional report region that can contain closing material for the report, including text, graphics, data, and computations.  The report trailer appears last, following the header and body.


A sequence of SQL statements treated as a single unit.


A PL/SQL procedure that is executed, or "fired," upon a specific event.


A description of a file type, such as a form, a document, etc., containing such information as type name and description. Each description applies to a single file type.  In addition, there are four meta-types: "updtext", which applies to all text files, “updproj” which applies to all project files, “updlink”, which applies to all project links, and "all" which applies to any file type.  Types are the foundation for defining actions and macros.


To change the values for table data, in particular, by altering data values using the SQL command UPDATE, but also by deleting values using the SQL command DELETE or by inserting values using the command INSERT.


Acronym for Uniform Resource Locator. The "address" used to specify a WWW server and home page.  For example:
http://www.acme.com/
which indicates that the host's address is www.acme.com. An URL most often is a filename (possibly with a long path to it and usually with an extension of .HTML, or .HTM (for PC-DOS filenames).


A way in which to pass control (and possibly arguments) from Forms Developer to another Oracle product or 3GL, and then return control (and possibly arguments) back to Forms Developer.


A custom action defined by a Project Builder user.  Such actions may apply to any single file type, or to all file types.  See also:  action, pre-defined action.


See object type.


A custom macro defined by a Project Builder user.  Such macros may be used to modify both built-in actions and user-defined actions.  See also:: macro, built-in macro, action, pre-defined action.


A project file storing configuration information on a per-user basis.  This enables users to customize their individual development environments.  The user registry inherits type information from the Global Registry, and may define new types as well as override aspects of type definitions stored in the Global Registry.  It also stores environment and preference information, such as the user's preferred connection string, UI settings such as "Display Toolbar" and certain general project information such as "Author."  See also:  registry, Global Registry.


See continuous axis.


A PL/SQL code structure that returns a value.  Value constructs include functions and expressions.


A named object that can be assigned a value and whose assigned value may change over time.


A collection type, which defines a data unit made up of an ordered set of elements, all of the same datatype.  The corresponding data unit is a varying array or VARRAY.  The number of elements in a VARRAY (size) is variable.  However, you must specify a maximum size when you declare a varying array type.


Visual Basic extension.  Used as the file extension for special dynamic link libraries distributed on files that contain predefined VBX controls.


A widget, built with the Microsoft Visual Basic language, used to enhance the user interface, obtain user input, and display output  (e.g. sliders, grids, and control knobs).


A virtual table whose rows do not actually exist in the database, but which is based on a table that is physically stored in the database.


A directory mnemonic (essentially a shortcut or synonym) that the virtual file system uses to map files stored in the file system on the host machine.


A mapping that associates the pathnames used in URL to the file system maintained by the host machine's operating system.


The font, color, and pattern properties that you set for objects that appear in your application interface.


See WWW.


A program that end users utilize to read HTML documents and programs stored on a computer (serviced by a Web server).


A program executed on a Web server via the Web Request Broker.


A server process (HTTP daemon) running at a Web site which sends out Web pages in response to HTTP requests from remote Web browsers.


A character used to mean 'any one character' or 'a contiguous set of characters' within a word or phase.


1. A rectangular area of the desktop that contains an application.  Each window has an area where you can interact with the application.  Windows can be opened, resized, moved, reduced to an icon, or enlarged to fill the entire desktop.  2. In Form Builder, an object type you can associate with a canvas view.


The file format used by some spreadsheet programs, such as SQL*Calc, or by Lotus 1-2-3 programs.


Acronym for Oracle Web Request Broker.  Provides a powerful distributed runtime environment for developing and deploying applications for the Web.  The WRB runtime platform enables application developers to write applications that are independent of, and work with a number of, Web servers.


Acronym for World Wide Web.  The network of servers on the Internet, each of which has one or more home pages, which provide information and links to other documents on that and (usually) other servers.


To expand an object to allow more room for editing the contents of the field.


The secondary toolbar docked (by default) to the left of the Project Navigator.  It provides simple organization and application launching abilities.


 the latest version of OLE controls (OCX)


XML output complying with general XML principles such as proper tagging, nesting, and the inclusion of an XML declaration, e.g. <?xml version="1.0" encoding="UTF-8"?>.


A placeholder username enables users to log on to the database using their personal username rather than the Oracle database username (e.g., $user_name@database) when running reports from a Web browser.


Extensible Markup Language -- a metalanguage using SGML to define and structure data. Report Builder supports XML output to enable Web publishing as well as electronic data exchange with third-party applications. You can also use XML to build report definitions that can be merged with other report definitions at runtime or run separately


XML output with structure and element content conforming to a document type definition (DTD). Valid XML is also well-formed.


Oracle8 Server support

  Related Topics

The Oracle8 Server provides a number of new features.  In general, the features are in these categories:

•	Greater scalability.  For example, Oracle8 databases can contain tens of terabytes of data, and can support simultaneous access by up to 10000 users.  Tables themselves can now contain up to 1000 columns.  CHAR and VARCHAR2 datatypes can now be up to 2000 and 4000 bytes, respectively.

•	Greater performance and efficiency.  Use of these Oracle8 features is handled for you and requires no actions on your part.  You automatically receive the speed and reliability benefits of having your data on the Oracle8 Server.

•	Large objects (sometimes called LOBs)

•	User-defined objects.

Continued support for Oracle7

Oracle7 Server features are still supported (Release 7.3.4 and later).


Oracle8 datatypes supported

  Related Topics

This release continues to support the following Oracle8 datatypes:

Large objects (LOBs)

In Oracle8, programmers can define large objects, often called LOBs.  These can store such things as text, images, sound, or video.

This release supports the following Oracle8 large object datatypes:

•	BLOB, or binary large object

•	CLOB, or single-byte character large object

•	BFILE, or binary file (sometimes referred to as an external file).

User-defined objects

In Oracle8, programmers can define object types (also called user-defined types), which are composites based on the existing single-element datatypes supported by Oracle8.  Using these object definitions, programmers can then create various kinds of tables.

Currently supported Oracle8 objects include:

•	An object table, where the entire table is based on a single object type.

•	A column object in a relational table, where that column is based on an object type.

•	A REF column in either an object or relational table, where that column's values are pointers to rows in another object table.

New scalar datatypes

This release supports the new Oracle8 FLOAT datatype.  It maps this datatype to its own NUMBER datatype.


About Oracle8 large objects (LOBs)

You can access the Oracle8 large object datatypes: binary large objects (BLOBs), character large objects (CLOBs), and binary files (BFILEs).

On an Oracle8 server, these datatypes can store objects of up to 4 gigabytes in size.  They are similar in purpose to the familiar Oracle7 LONG and LONG RAW datatypes, but offer a number of additional advantages.

Comparison of LOBs with LONGs

LOBs	LONGs
Up to 4 gigabytes	Up to 2 gigabytes
Multiple LOB columns allowed in a single table	Only one LONG column allowed per table
Replication supported	No replication
Random data access supported	Only sequential access supported
Better table space management (only LOB locator is stored)	Less efficient (data is stored directly in table)
Allowed in user-defined object types	Not allowed
Easily passed to procedures and external calls	Not easily passed
LOBs in detail:

BLOBs are used to store large objects in binary format.  CLOBs are used to store character data.  These two types are also known as internal LOBs, because their data is stored within the server.  The data participates in transactions and integrity control, including commits and rollbacks.

BFILEs, in contrast, are stored outside of the server. The server stores only the operating system address of the file.  As a consequence, BFILEs can not participate in database transactions.

Compatibility

Because of the various advantages offered by LOBs (see the table above), you should used LOBs instead of LONGs in any new application.

However, for backward compatibility, LONGs are still fully supported.

Forms Types

Within a form, the LOB would usually be given a control type of Text, Image, or Sound, depending on its nature and usage.

Restrictions

•	BFILEs are read-only.

•	Although Forms will attempt to load the entire BFILE or BLOB when it is read at runtime, it is limited by the amount of runtime virtual memory available.

•	For CLOBs, Forms has a size limitation of 65534 bytes.


About Oracle8 user-defined objects

  Related Topics  [image]  Examples

In its support for the Oracle8 Server, this release provides you with access to Oracle8 user-defined objects.  These include object tables, column objects in relational tables, and the special kind of column object known as a REF column.

Object tables

In Oracle8, an object table is based on a user's object type definition.  This definition is itself a composite, each attribute or component of which is based on a familiar Oracle8 built-in datatype.  (An object type definition can be based on another object type definition, but ultimately the definitions rest on known Oracle8 built-in datatypes.)

Because of this, each column in an object table is treated as an individual data item, in a manner similar to the way it treats the columns of a relational table.

Column objects in relational tables

A database programmer can also base a column in a relational table on an object type definition.  This has the effect of embedding a composite object in the relational table.

The embedded column object is expanded into its composite parts, and each part is treated as a column in itself.

For example, if the Oracle8 Server held a 3-column relational table, and one of those columns was a column object, and that column object was based on an object definition having four parts or attributes, then the table would be treated as a 6-column table (the 2 relational columns plus the 4 columns that comprise the object).

In the event one of the individual attributes of the column object's definition was based on yet another object type, then that part is further expanded and treated as a set of individual columns.

REF columns

A column in either an object or relational table can be defined as having a type of REF.  A REF column does not hold data directly, but rather has pointers (references) to another object table that holds the actual data.

The reference can be resolved at runtime, so that the user sees the actual referenced data.


Oracle8 user-defined objects: examples

  Related Topics

To create objects in the Oracle8 Server, a database programmer first defines an object type, and then creates a specific object based on that type definition.  The resulting object can be either an object table or a column object in a relational table.

Examples

Consider the following definitions.  The first creates an object type.  The second creates a table of that type.

         CREATE TYPE  emp_type  AS OBJECT

                      (name     CHAR(30),

                       title    CHAR(20),

                       idnum    NUMBER);

         CREATE TABLE  emp  OF emp_type;

In the above example, we create an object table named emp.  It has three columns, and those columns have the names and datatypes that are established in the attributes of the emp_type object type definition.

The following is an example of a column object in a relational table:

         CREATE TYPE  address_type  AS OBJECT

                      (street    VARCHAR2(40),

                       city      VARCHAR2(25),

                       state     VARCHAR2(2)

                       zip       NUMBER);

         CREATE TABLE  customers

                       (cust_id  NUMBER,

                        name     VARCHAR(40),

                        address  address_type);

In this example, we create a relational table named customers.  The table has three columns.  The first two columns (cust_id and name) are the usual relational columns.  The third column (named address) is a column object.  It is built on the object type definition named address_type.  This address column has the structure and datatypes established by that object definition.  The address column can be thought of as having four parts or components (street, city, state, and zip).  Each of these components is of the datatype that was established in the address_type object type definition.  (In this case, all are of VARCHAR2 except for zip, which is of datatype NUMBER).

In the following illustration, the third column of a three-column relational table is based on an object type that itself has three parts or components.

Note that an Oracle8 object is a composite, built from parts or components.  Each of its parts has a name and is of a known Oracle8 datatype.  If the object is a table (actually, a row in an object table), it has as many columns or parts as there are attributes in the object type definition.  If a column in a relational table is based on an object, then each of the object's parts can be thought of an additional column in that table.  Because each of these parts is based on an attribute in the object type definition, these parts are sometimes called attribute columns.

How Developer treats objects

An object table is treated very much like a relational table.  Each column is individually selectable.  Although all columns may be selected, each is treated as a separate item within the application.  The table itself does not have any particular identity as an object within the environment.

Similarly, a column object in a relational table is not treated as a single, homogeneous entity.  Instead, it treats each of the column object's components as an individual, independent data item.  Each component of the column object is selectable.  Once selected, each is treated as an independent data item.  Even if all of the column object's components are selected, the column object itself does not have any particular identity as an object within the development environment.

Data items selected from an object are given the default name ColumnObjectName_ItemName.  Thus, if we select the component named zip from the column object named address in the above example, the data item will have the name address_zip within the application.


About Oracle8 REFs

  Related Topics

REF is a new datatype in Oracle8.  It establishes a column object as a pointer to data in another object table.  The REF column itself does not hold the data.

For example:

           CREATE TYPE  dept_type  AS OBJECT

                        (deptname     VARCHAR2(24),

                         deptnumber   NUMBER);

           CREATE TABLE  dept_table OF dept_type;

           CREATE TABLE  emp

                         (empname    VARCHAR2(50),

                          empnumber  NUMBER,

                          empdept    REF  dept_type);

In the relational table named emp, the column empdept is a REF column.  It points to an object of type dept_type.

Types versus tables

When a REF column is defined, it identifies an object type -- not a particular object table.  It is possible for a database to contain more than one object table based on a single object type definition.  Thus, the REF column definition itself does not identify the target table(s).

Pointer resolution

When the table holding the REF column is initialized, the values placed in the REF column will be pointers to rows in the specific table or tables that have the desired data.  Later at runtime, when users look at the REF column, they will see the pointed-to data (not the pointers).

How Developer displays REFs at design time

At design time, when you ask to see a table that contains a REF column, the REF column displays in its expanded form.  That is, it shows the attributes in the object type pointed to by the original REF definition.

In the above example, the empdept REF column would show as holding two components or attribute columns:  deptname and deptnumber.  These were the two attributes in the object type (dept_type) identified in the REF definition.

Just as with a regular column object, you can select any or all of the referred-to components/attribute columns to become data items in the application.  However, in contrast to regular column objects, you can also select the REF name itself to become a manipulatable item within your application.  That is, you can use the REF in your PL/SQL coding.  By default, the REF becomes part of the null canvas.

How you can display REF data to your users

As an application designer, you have a choice in how the runtime user will see the referenced data (the resolved REF):

•	data displayed in the data block in which the REF exists (sometimes called the look-up method)

•	data displayed in the data block pointed to by the REF (the master-detail method)

The first choice (displaying the data as part of the data block in which the REF exists) is the simplest.  You select the desired REF components as items into your data block, and at runtime Developer and Oracle8 will resolve the references and place the actual referred-to data in the display items.  The user need see only the one block.

The other choice (displaying the data in the pointed-to data block) requires setting up a master-detail link.  This is very similar to setting up a master-detail link for two blocks having a primary-key foreign-key relationship.  In this case, the REF creates the link between the two blocks.  The user will see both blocks, but will  see the REF data only in the referred-to block.

The Form Builder Data Block Wizard can be used to set up this master-detail relationship.


Icons for Oracle8 objects

The object navigator and wizards use icons to help you see what type of Oracle8 data you are accessing.   These icons, and their meanings, are as follows:

             normal table

             object type

             object view

             object table

             nested object

             nested table

             referenced object

             varying array

             method

             unknown type

             server-side stack mode

             server-side program unit

             file

             datatype of Date

             datatype of Number

             datatype of Character

             datatype of Raw

             datatype of BLOB

             datatype of BFILE

             datatype of CLOB

             rowid

             MLS label


Scalability and performance with Oracle8

Oracle8 Server gives you a number of scalability and performance improvements.  For the most part, you obtain these advantages automatically.

For example, you can access Oracle8's:

•	Larger sizes.  The database itself, the number of concurrent users, and its table column limits (now up to 1000 columns) have all increased.

•	Table and index partitioning

•	Client-side object caching

You can also make direct use of several other Oracle8 functions that provide it with improved performance:

•	Prefetch.  Forms uses this Oracle8 feature (along with the existing Array Size mechanism) to reduce both database round-trips and memory usage.  This is done automatically, without any need for user involvement.

•	Returning values clause.  Forms uses this new DML feature when doing inserts and updates.  The Returning clause eliminates the need for a second database query (to obtain any server-side trigger changes to the data), thus speeding up processing.  Users can control it with the DML Returning Value property.

•	INSTEAD-OF trigger for updating views.  This server-side trigger provides a mechanism for updating both relational and object views that would not otherwise be updatable.


Support for PL/SQL 8

  Related topics

This release uses PL/SQL 8 on the client and the server.  Client-side program units currently cannot support Oracle8 object-related functionality.  However, stored program units can use all the new PL/SQL 8 features.

Appendix A in the PL/SQL User's Guide and Reference Release 8 manual describes the new PL/SQL 8 features.  This manual is available online.  Choose Help -> Manuals, and navigate to Reference Manuals under Online Manuals.


Compatibility with earlier releases of PL/SQL

If you have client-side program units written in PL/SQL 1 or PL/SQL 2, you must convert the code to the new level.  The PL/SQL 1 conversion utility is provided to help automate the upgrading process.

Stored program units created with previous releases will run against the PL/SQL 8 engine on the database server, but may compile with errors.  These errors arise in some situations where there is syntax incompatibility between PL/SQL 8 and PL/SQL 2.  A compatibility flag in PL/SQL 8 allows you to choose a PL/SQL 2 compatibility mode, so that the PL/SQL 8 engine does not issue such errors when it compiles PL/SQL 2 code in the server.  Chapter 6 in the Oracle8 Server Migration manual describes the situations that are covered by this compatibility flag.


Oracle8 features not yet supported

  Related Topics

Not all Oracle8 features are currently supported across this release.  The major unsupported features are:

•	Collection types (nested tables and varying arrays, or varrays)

•	Stored procedures that return object values

•	Scalar datatypes NCHAR and NVARCHAR2.

You should also be aware that, although this release does support PL/SQL 8, client-side PL/SQL 8 does not yet fully support all the features in the Oracle8 Server.  (Server-side PL/SQL 8 support is complete.)


Oracle8 objects examples

  Related Topics 	   All Examples

Object Type

Table Type

Varying Array Type

Column Object

Object Table

Object View


Oracle8 Object Support in the Object Navigator

Oracle8 functionality in the Object Navigator provides access to object datatypes, enabling you to program against Oracle8 data more easily. The added Oracle8 object support allows you to view user-defined datatypes in a hierarchical format, and to find and display tables, views, columns, triggers, attributes and methods associated with the user-defined datatypes.

The Database Objects node contains the schemas and their objects that you can access in the currently connected database. When connected to an Oracle8 database, the Object Navigator displays available user-defined datatypes (with their attributes and methods) in the Types node within each schema. Tables, views, columns and triggers associated with the user-defined datatypes are displayed in the Tables and Views nodes.

Specifically, Oracle8 functionality in the Object Navigator enables you to display and find:

•	object tables and column objects in the Tables node

•	object views in the Views node

•	Instead Of triggers for views in the Views node

•	object types, table types and varying array types in the Types node

•	attributes and methods of object types in the Types node

Icons are used to differentiate relational objects and Oracle8 objects in the Object Navigator. For example, separate icons are used to represent normal, relational tables and object tables, and normal views and object views.

For information about Oracle8 user-defined datatypes, see your Oracle8 documentation.

Learn more about

Object Type nodes

Table Type nodes

Varying Array Type nodes

Column Object nodes

Object Table nodes

Object View nodes

Expanding All

Oracle8 objects icons


Object Type nodes

  Example	  Related Topics

Object types appear within the Types node in the Object Navigator. An object type node represents a type created by the SQL statement, CREATE TYPE AS OBJECT. The object type is displayed in the Object Navigator as:

object-type-name ( Object Type [ * ] & Body [ * ] )

The Object Type label appears when a CREATE TYPE AS OBJECT statement has been issued to the database. An asterisk ( * ) appears when the type specification is invalid or if the type is incomplete. An incomplete type is a placeholder for defining types recursively.

The Object Body label appears when a type body has been created using the CREATE TYPE BODY statement. An asterisk ( * ) appears when the type body is invalid.

When both object type and object body have been successfully created, then the label appears as Object Type & Body.

Object type nodes are composite nodes; they have two subnodes, Attributes and Methods. An object type node can be expanded only if a valid type specification exists.

The attributes of an object type are represented as nodes under the Attributes subnode.

The methods of an object type are represented as nodes under the Methods subnode. The methods appear as procedures and functions. The method labels are displayed in the Object Navigator as:

method-name (Procedure)

and

method-name (Function)

If a map method or order method is specified for an object type, then the method label is displayed as:

method-name (Map Function)

or

method-name (Order Function)

Note:  The argument list of a method is not displayed.


Object Type node example

  Related Topics 	   All Examples

The following SQL statement creates the object type emp_typ.

CREATE TYPE emp_typ AS OBJECT
  (ename VARCHAR2(30), esalary NUMBER(5,2),
  MEMBER PROCEDURE raise(amount NUMBER),
  MEMBER FUNCTION get_salary RETURN NUMBER,
  MAP MEMBER FUNCTION compare RETURN NUMBER);

The subtree for emp_typ is:


Table Type nodes

  Example	  Related Topics

Table types appear within the Types node in the Object Navigator. A table type node represents a type created by the SQL statement, CREATE TYPE AS TABLE. The table type is displayed in the Object Navigator as:

table-type-name (TABLE OF table-subtype-name)

The table type node is not expandable if the subtype is a built-in type. If the subtype is an object type or REF object, the attributes of the object appear as subnodes.


Table Type node example

  Related Topics 	   All Examples

The following SQL statements create the object type emp_typ and the table type emp_tbl.

CREATE TYPE emp_typ AS OBJECT
  (ename VARCHAR2(30), esalary NUMBER(5,2),
  MEMBER PROCEDURE raise(amount NUMBER),
  MEMBER FUNCTION get_salary RETURN NUMBER,
  MAP MEMBER FUNCTION compare RETURN NUMBER);

CREATE TYPE emp_tbl AS TABLE OF emp_typ;

The subtree for emp_tbl is:


Varying Array Type nodes

  Example	  Related Topics

Varying array types appear within the Types node in the Object Navigator. A varying array type node represents a type created by the SQL statement CREATE TYPE AS VARYING ARRAY. The varying array type is displayed in the Object Navigator as:

varying-array-type-name (VARRAY (size) varying-array-subtype-name)

The varying array type node is not expandable if the subtype is a built-in type. If the subtype is an object type or REF object, the attributes of the object appear as subnodes.


Varying Array Type node example

  Related Topics 	   All Examples

The following SQL statements create the object type emp_typ and the varying array type emp_ary.

CREATE TYPE emp_typ AS OBJECT
  (ename VARCHAR2(30), esalary NUMBER(5,2),
  MEMBER PROCEDURE raise(amount NUMBER),
  MEMBER FUNCTION get_salary RETURN NUMBER,
  MAP MEMBER FUNCTION compare RETURN NUMBER);

CREATE TYPE emp_ary AS VARRAY(7) OF emp_typ;

The subtree for emp_ary is:


Column Object nodes

  Examples	  Related Topics

Column objects appear as nodes within the Columns node. Each object is labeled with a name and its type in parentheses. Column object nodes are composite nodes, with the attributes of the type appearing as subnodes.

There can be four subtypes of column objects-nested object type, table type, varying array type and REF object type. Column objects are labeled in the Object Navigator as follows:

column-name (nested-object-type)

column-name (table-type-name:TABLE OF table-subtype-name)

column-name (varying-array-type-name: VARRAY(size) OF varying-array-subtype-name)

column-name (REF reference-type-name)


Column Object node examples

  Related Topics 	   All Examples

In the following example, the emp_col column of table emp_tab is a nested object of type emp_typ.

CREATE TYPE emp_typ AS OBJECT (ename VARCHAR2(30), esalary NUMBER(5,2));
CREATE TABLE emp_tab (emp_col emp_typ, dept VARCHAR2(2));

The subtree for emp_tab is:

In the next example, the emps column of table dept_tab is a nested table with subtype emp_tbl. The nested table contains rows of type emp_typ.

CREATE TYPE emp_typ AS OBJECT (ename VARCHAR2(30), esalary NUMBER(5,2));
CREATE TYPE emp_tbl AS TABLE OF emp_typ;
CREATE TABLE dept_tab (dname VARCHAR2(20), emps emp_tbl)
  NESTED TABLE emps STORE AS emps_tab;

The subtree for dept_tab is:

In the following example, the emps column of table dept_tab_ary is a varying array with subtype emp_ary. The varying array contains rows of type emp_typ.

CREATE TYPE emp_typ AS OBJECT (ename VARCHAR2(30), esalary NUMBER(5,2));
CREATE TYPE emp_ary AS VARRAY(7) OF emp_typ;
CREATE TABLE dept_tab_ary (dname VARCHAR2(20), emps emp_ary);

The subtree for dept_tab_ary is:

In the next example, the manager column of table dept_ref is a referenced object with type emp_typ.

CREATE TYPE emp_typ AS OBJECT (ename VARCHAR2(30), esalary NUMBER(5,2));
CREATE TABLE dept_ref (dname VARCHAR2(20), manager REF emp_typ);

The subtree for dept_ref is:


Object Table nodes

  Example	  Related Topics

Object tables appear within the Tables node in the Object Navigator. An object table node represents a table created by the SQL statement CREATE TABLE OF. Object table nodes are labeled in the Object Navigator as:

object-table-name (object-type-name)

Each object table node has two subnodes, Triggers and Columns. The Triggers subnode contains all triggers associated with the object table. These triggers are displayed in the same manner as triggers associated with normal, relational tables. The Columns node contains all relational columns and column objects associated with a given object table. Each column is labeled with a name and its type in parentheses.


Object Table node example

  Related Topics 	   All Examples

The following SQL statements create the object type person_typ and the object table person_tab.

CREATE TYPE person_typ AS OBJECT
  (name VARCHAR2(30), address VARCHAR2(60));

CREATE TABLE person_tab OF person_typ;

The subtree for person_tab is:


Object View nodes

  Example	  Related Topics

Object views appear within the Views node in the Object Navigator. An object view node represents a view created by using the OF object_type clause. Object view nodes are labeled with a name and its type in parentheses. The attributes of the object are not displayed.

Each object view node has two subnodes, Triggers and Columns. The Triggers subnode contains all triggers associated with the object view, including Instead Of triggers. When an Instead Of trigger is associated with a view, the label is displayed as:

trigger-name (INSTEAD OF trigger-statements)


Object View node example

  Related Topics 	   All Examples

The following SQL statements create an object view.

CREATE TYPE emps_typ AS OBJECT (ename VARCHAR2(30), esalary number(5,2), deptno NUMBER(2));
CREATE TABLE emps (ename VARCHAR2(30), esalary number(5,2), deptno NUMBER(2));
CREATE VIEW dev_emps OF emps_typ
  WITH OBJECT OID (ename)
  AS SELECT ename, esalary, deptno FROM emps WHERE deptno = 10;

The subtree for the object view dev_emps is:


Expanding all Oracle8 object nodes

  Related Topics

The Expand All operation in the Object Navigator does not expand Oracle8 datatype and attribute nodes recursively.

For example, if an attribute called manager with type emp_typ appears as a descendent from another type emp_typ, then the manager attribute would not be expanded by Expand All.