Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Converting Access SQL to Oracle and Vice Versa
Other
There are numerous incompatibilities between Access/Jet SQL and Oracle SQL, including the following:
1. The date delimiter is "#" in Access and " ' " (single quote) in Oracle.
2. In CHAR Datatype, Oracle returns trailing spaces. Access does not return trailing spaces in String data type. In Oracle, use VarChar2 Datatype.
3. Jet allows use of the "Join" keyword. Oracle uses "(+)" following the joined field(s). Join is not a recognized word in Oracle.
4. IIF() is allowed in Jet but in Oracle, you must use the DECODE function. See Oracle documentation.
5. The following functions in Jet must be translated in Oracle.
Mid(fld, 2, 2) - Substr(fld, 2, 2)
Left(2) - Substr(fld, 1, 2)
Right
- Substr( fld, Instr(...),n)
Len() - LENGTH()
Year(Fld) = 1997 - to_char(Fld, 'yyyy') = '1997'
Month(Fld) = 6 - to_char(fld, 'mm') = '06'
Trim(Fld) - LTrim() or RTrim()
6. Generally, you need to use "to_date('01/23/1978', 'mm/dd/yyyy')" to format a date for comparison in Oracle SQL whereas in Jet Sql you could simply use "#01/23/1978#".
7. "RENAME" is a reserved word in Oracle and is therefore an illegal field or table name, but not in Jet.
8. If a memory variable is used in a SQL statement, and the variable is a string with trailing spaces, Jet will trim the value before comparing to the database field. Oracle will not! Therefore, the following will work in Jet but not Oracle, assuming the Oracle field in VARCHAR[2].
Sql = " ... where fldname = 'abc ' ...."
9. Oracle SQL will not find uppercase data in the database if the value in the SQL statement is lowercase. Assume that the LastName field contains the value 'SMITH' in the Oracle table Policy. Access will make a find on the following SQL and Oracle will not (Like character would be "*" for Access.
select * from Policy where LastName Like 'smith%'
A good workaround for oracle would be to do this:
select * from Policy where upper(LastName) Like upper('smith%')"
10. Conversion of Memo (Jet) fields to Oracle can be a problem. The only data type in Oracle that is analogous to the Memo in Jet is the Long. Although it can hold up to 2gb of string data, you cannot append more than approximately 5,000 characters to the existing Long field because you exceed the length of the maximum string length in an Oracle SQL Statement.
Additionally, you cannot use such expressions, as the following, usable in Jet, in Oracle. They result in the error, "Invalid use of Long Datatype."
select distinct LONGFLD
Length(LONGFLD)
It seems impossible to use a query to determine rows where there is data in the Long field since a Long field that has ' in it is not Null.
Oracle 8 has Append and Get Chunk commands, but the documentation in the Oracle 7.2 Book does not mention these functions, nor are there any examples of how to update a Long field that exceeds 5,000+ characters.
11. Use of apostrophes (') in names and abbreviations are a problem in SQL. In Access, it may be circumvented by use of the double quote (") to encapsulate string data in the SQL statement instead of the apostrophe, thus allowing the use of the (') in the string. In Oracle, you can use two single quotes (') to denote an apostrophe. The string below is valid in Oracle Sql.
... Set Lastname = 'O'Toole',...
It is a good idea to use the Replace() function to prepare any string field that could have a single quote in it, to insert two single quotes. For example,
...SQL = SQL & "set LastName = '" & Replace(lsNameVar, "'", "'") & "', "
12. In Access, an empty string ("") is not Null. Oracle's default parameter for Null is set to ' (double single quotes or empty string). In other words, Oracle, by default, treats an empty string field as Null. Therefore, the following code which works in Access, does not work in Oracle.
...Where Trim(Fld) <> '.....
In Oracle, change the SQL to:
... Where Fld is not null....
Also, the following sql will not work if a field is Null.
... Where STATE <> 'Z'...
Even though the field is null, and therefore <> 'Z', the row containing the Null field will not be returned. In order to get the row, you must enhance the SQL as:
...where (STATE <> 'Z' or STATE is Null)...
WHATEVER YOU THINK NULL IS, IT ISN'T!
13. Access allows an Order By clause at the end of a query that uses a Union, but if you try this in Oracle, it will not work because Oracle ignores the column names in the result set from the Union. Not to worry! You can Order By column positions from the select statement. In other words, if Name is returning in column 3 of the result set, and you want to order by Name, then use "Order By 3..."
1. The date delimiter is "#" in Access and " ' " (single quote) in Oracle.
2. In CHAR Datatype, Oracle returns trailing spaces. Access does not return trailing spaces in String data type. In Oracle, use VarChar2 Datatype.
3. Jet allows use of the "Join" keyword. Oracle uses "(+)" following the joined field(s). Join is not a recognized word in Oracle.
4. IIF() is allowed in Jet but in Oracle, you must use the DECODE function. See Oracle documentation.
5. The following functions in Jet must be translated in Oracle.
Mid(fld, 2, 2) - Substr(fld, 2, 2)
Left(2) - Substr(fld, 1, 2)
Right
- Substr( fld, Instr(...),n)Len() - LENGTH()
Year(Fld) = 1997 - to_char(Fld, 'yyyy') = '1997'
Month(Fld) = 6 - to_char(fld, 'mm') = '06'
Trim(Fld) - LTrim() or RTrim()
6. Generally, you need to use "to_date('01/23/1978', 'mm/dd/yyyy')" to format a date for comparison in Oracle SQL whereas in Jet Sql you could simply use "#01/23/1978#".
7. "RENAME" is a reserved word in Oracle and is therefore an illegal field or table name, but not in Jet.
8. If a memory variable is used in a SQL statement, and the variable is a string with trailing spaces, Jet will trim the value before comparing to the database field. Oracle will not! Therefore, the following will work in Jet but not Oracle, assuming the Oracle field in VARCHAR[2].
Sql = " ... where fldname = 'abc ' ...."
9. Oracle SQL will not find uppercase data in the database if the value in the SQL statement is lowercase. Assume that the LastName field contains the value 'SMITH' in the Oracle table Policy. Access will make a find on the following SQL and Oracle will not (Like character would be "*" for Access.
select * from Policy where LastName Like 'smith%'
A good workaround for oracle would be to do this:
select * from Policy where upper(LastName) Like upper('smith%')"
10. Conversion of Memo (Jet) fields to Oracle can be a problem. The only data type in Oracle that is analogous to the Memo in Jet is the Long. Although it can hold up to 2gb of string data, you cannot append more than approximately 5,000 characters to the existing Long field because you exceed the length of the maximum string length in an Oracle SQL Statement.
Additionally, you cannot use such expressions, as the following, usable in Jet, in Oracle. They result in the error, "Invalid use of Long Datatype."
select distinct LONGFLD
Length(LONGFLD)
It seems impossible to use a query to determine rows where there is data in the Long field since a Long field that has ' in it is not Null.
Oracle 8 has Append and Get Chunk commands, but the documentation in the Oracle 7.2 Book does not mention these functions, nor are there any examples of how to update a Long field that exceeds 5,000+ characters.
11. Use of apostrophes (') in names and abbreviations are a problem in SQL. In Access, it may be circumvented by use of the double quote (") to encapsulate string data in the SQL statement instead of the apostrophe, thus allowing the use of the (') in the string. In Oracle, you can use two single quotes (') to denote an apostrophe. The string below is valid in Oracle Sql.
... Set Lastname = 'O'Toole',...
It is a good idea to use the Replace() function to prepare any string field that could have a single quote in it, to insert two single quotes. For example,
...SQL = SQL & "set LastName = '" & Replace(lsNameVar, "'", "'") & "', "
12. In Access, an empty string ("") is not Null. Oracle's default parameter for Null is set to ' (double single quotes or empty string). In other words, Oracle, by default, treats an empty string field as Null. Therefore, the following code which works in Access, does not work in Oracle.
...Where Trim(Fld) <> '.....
In Oracle, change the SQL to:
... Where Fld is not null....
Also, the following sql will not work if a field is Null.
... Where STATE <> 'Z'...
Even though the field is null, and therefore <> 'Z', the row containing the Null field will not be returned. In order to get the row, you must enhance the SQL as:
...where (STATE <> 'Z' or STATE is Null)...
WHATEVER YOU THINK NULL IS, IT ISN'T!
13. Access allows an Order By clause at the end of a query that uses a Union, but if you try this in Oracle, it will not work because Oracle ignores the column names in the result set from the Union. Not to worry! You can Order By column positions from the select statement. In other words, if Name is returning in column 3 of the result set, and you want to order by Name, then use "Order By 3..."