If you are creating queries using the Oracle database server, you can take advantage of features specific to that server. The following guidelines outline differences you will notice in the Query Designer when you work with the Oracle Server, and provide information about Oracle-specific features that you can use.
Below you will find information about:
It is recommended that you install and use the Oracle ODBC driver provided with the Microsoft® Visual Database Tools. This driver has been tested extensively to ensure that it works properly with the Query Designer.
Using the Query Designer, you can create queries using not only tables and views, but using synonyms as well. You can query any synonym for which you are the owner or which you have been given permission to.
If you do not see column names in the input source window for a synonym, synonym column information may not have been enabled for the ODBC driver you are using. In that case, you can use the synonym columns if you know their names by entering them in the SQL or Grid panes. For details about enabling synonym column names, see the documentation for the Oracle ODBC driver you are using.
Database objects (tables, views, columns, and synonyms) in Oracle databases are identified with unique names that consist of two parts (for tables and views) or three parts (for columns):
owner.table
owner.table.column
If you are the owner of a table or view, you do not need to include the owner qualifier when you reference the table or view. For example, if you are working with a column called PRICE in the TITLES table in the current database, you can simply reference the column by name, as in this SQL statement:
SELECT PRICE
FROM TITLES
If you are working in the Diagram and Grid panes, the Query Designer automatically adds the owner name to the table or view if necessary.
If you are not the owner of a table or view, you can reference the table and view in the SQL pane (if you have permission) by including the owner name. For example, to display information from an EMP table owned by the user HR, you can include the owner name. If the query contains more than one table, you must also include table name qualifiers. The following query illustrates the use of the owner and table name qualifiers:
SELECT HR.EMP.LAST_NAME, HR.EMP.FIRST_NAME, MyEmp.NOTES
FROM HR.EMP, EMP MyEmp
WHERE HR.EMP.EMPID = MyEmp.EMPID
The names of database objects — tables, views, columns, and synonyms — are case-sensitive by default in the Oracle database server. For example, it is possible to have two tables whose names are distinguished only by uppercase and lowercase letters, such as EMP and Emp.
When a query is executed, the Oracle database server, by default, converts all database object names to uppercase characters. If the actual object name uses lowercase or mixed-case letters, an error results because the converted uppercase name (for example, EMP) would not match the actual name (Emp).
However, the Oracle database server does not convert to uppercase letters an object name that is enclosed in double quotation marks. Therefore, you can specify lowercase or mixed-case database object names by putting the names in double quotation marks. For example, in the following SQL statement, because the mixed-case table name Emp is in double quotation marks, the Oracle database server does not convert it to the name EMP when the query is run:
SELECT "Emp".ENAME
FROM "Emp"
In the Query Designer, database object names are displayed in Data View of the project workspace with the actual combination of uppercase and lowercase letters. When you drag tables or views onto the Diagram pane, and when you choose names in the Grid pane, the Query Designer automatically places double quotation marks around the name if the name is not all uppercase.
If you enter lowercase or mixed-case database object names in the SQL pane, place double quotation marks around any names that contain lowercase letters. If you do not place quotation marks around the lowercase or mixed case names, the Query Designer follows this strategy in resolving the name:
In many cases the Query Designer catches invalid syntax and displays an appropriate message. However, in some cases — usually involving syntax specific to Oracle databases — the Query Designer does not detect all errors. When Oracle attempts to run the query, it detects an error, and it returns an error number and string to the Query Designer, which displays the information in a "raw" state.
For example, if the column list in the SQL statement contains an invalid reference, Oracle might return error 00923, with the phrase "FROM keyword not found." This error indirectly indicates a problem with the column list.
If you have access to Oracle documentation, you can look up the errors returned in this fashion. If not, you might have to experiment with the syntax of your query to determine where the error lies.
In some other cases, it is the Microsoft Oracle ODBC driver that is returning the error. For example, the driver does not support the MLSLABEL data type. If you attempt to create a query using this data type, the driver (not Oracle) reports an "unknown error" or an error indicating that the query contains an "unsupported network data type or representation."
If you create an outer join between input sources, it is represented in the SQL pane using the Oracle-specific (+) syntax. For example, the following query creates a left outer join between the EMP and DEPT tables:
SELECT EMP.EMPNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO (+) = DEPT.DEPTNO
If you are creating an outer join in the SQL pane, you can use the (+) syntax. If you attempt to use the LEFT OUTER JOIN or RIGHT OUTER JOIN syntax, the Query Designer will display the Query Definitions Differ Dialog Box dialog box and report an error when you execute your query or change panes. You cannot use the (+) operator in the Grid pane.
You can also use the ODBC { oj } syntax. If you do, the Query Designer might convert it to (+) syntax, depending on the ODBC driver you are using.
Oracle does not support full outer joins. When you are working with an Oracle database, the Query Designer will prevent you from creating a full outer join by setting the properties of the Join Line.
Similarly, Oracle also has restrictions on creating outer joins involving three or more tables. In general, the Query Designer will prevent you from creating a query that would not be allowed in Oracle. For more information about restrictions for outer joins, see the Oracle documentation.
In the list of columns to return, Oracle does not permit you to combine column names with * (All Columns). For example, the following syntax will result in an error:
SELECT pub_id, pr_info, *
FROM pub_info
Because this syntax is allowed in some databases, the Query Designer does not prevent you from entering it in the SQL pane. However, when the SQL statement is processed, the Query Designer will display an error that might include one of the following phrases:
(00923) FROM keyword not found where expected
(00926) Missing Expression
When you work with Oracle databases, the Query Designer allows you to use the + operator when you are using the Grid pane to create expressions that concatenate text. However, the Query Designer will convert the + operator to the Oracle-standard || operator. If you are entering expressions directly in the SQL pane, you must use the || operator.
When you enter a valid date in the Grid pane, the Query Designer replaces the date you entered with a call to the TO_DATE( ) function, using your date as one of the parameters. This assures that Oracle will correctly interpret that data you have entered.
You can enter dates directly in the SQL pane, but you must enter them in exactly the format specified for your database, as specified by the current session's NLS_DATE_FORMAT value. Because there might not be an easy way to determine the correct format, it is advisable to use the TO_DATE( ) format in the SQL pane. For example, a query might look like this:
SELECT EMP_ID, HIRE_DATE
FROM EMPLOYEE
WHERE HIRE_DATE > TO_DATE('01/01/90', 'MM/DD/YY')
For details about the TO_DATE( ) function, refer to the Oracle documentation.
In addition to the standard aggregate functions (AVG, SUM, and on), Oracle allows you to use the aggregate functions STDDEV( ), which calculates the standard deviation of a summary value, and VARIANCE( ), which returns a variance on the summary values. When you are working with an Oracle database, both of these aggregate functions, along with variants that include their optional DISTINCT qualifier, appear in the drop-down list under Group By in the Grid pane. You can also enter them directly in the SQL pane.
When you create an Insert Values query using an Oracle database, you can use named sequences in the New Values clause. For example, you can create the following named sequence:
CREATE SEQUENCE empseq
INCREMENT BY 10
You can then use this named sequence in an Insert Values query, as in the following example:
INSERT INTO emp
VALUES (empseq.nextval, 'Ann', 'E', 'Smith', 7902, 11, '0877', SYSDATE)
In Oracle databases, a Make Table query is implemented with the Create Table as Select command. In all other respects a Make Table query functions the same as with any other database.
Oracle allows you to create comparisons using distributive syntax, as in the following example:
SELECT EMP_ID, LNAME
FROM EMP
WHERE (JOB_ID, DEPT_ID) in (6669,21)
You can enter this syntax directly in the SQL pane. When you change panes or execute the query, the Query Designer reformats the SQL statement by redistributing the elements in the comparisons. The above statement would be reformatted as follows:
SELECT EMP_ID, LNAME
FROM EMP
WHERE (JOB_ID = 6669) AND (DEPT_ID = 21)
Any similar distributive syntax will be reformatted in the same way. In complex statements, especially those involving NOT, the resulting statement can become quite long. However, it is always guaranteed to be logically identical to the distributive syntax that you originally entered.
Oracle does not support AS in table alias clauses. However, you can create an alias without the AS, as in the following example:
SELECT *
FROM DEPT D
WHERE D.LOC = 'NEW YORK'
If you are entering a query directly in the SQL pane, you can add optimizer hints before the SELECT, UPDATE, or DELETE command to specify how the query should be parsed and compiled. When reformatting the contents of the SQL pane, the Query Designer will maintain these comments according to the following conventions:
For more details about optimizer hints, refer to the Oracle documentation.
Because the MLSLABEL data type is designed for use primarily with high-security applications, it is not supported by the Microsoft Oracle ODBC driver. MLSLABEL is a legal data type in Oracle, so the Query Designer does not immediately detect an error when you use an input source that includes a column of that data type. However, when you run the query, the Query Designer displays an error message that indicates an "unknown error" or one that indicates that the query contains an "unsupported network data type or representation."
The identifiers LEVEL, ROWID, and ROWNUM are pseudo-columns in Oracle that you can use in queries you create with the Query Designer. You enter the pseudo-columns into the Grid pane or directly in the SQL pane. They do not appear in the input source window in the Diagram pane.
Some types of legal Oracle queries cannot be represented graphically in the Query Designer. You can still enter them in the SQL pane, and they will execute correctly. However, the Query Designer will display the Query Definitions Differ dialog box and report an error when you execute your query or change panes.
The following types of Oracle queries are not supported graphically: