The following is some information gathered on migrating Cold Fusion apps from MS SQL
Server to Oracle. Has anyone been through this process? Are there any suggestions of
things to watch for or problems with any of the following info?
{~:~}
a) With Oracle native drivers for ColdFusion there are general issues with date
functions like, CreateODBCDate() or date=#CreateODBCDateTime(Now())# Modules-Need to
identify standardized Date format for all code.-Many modules can use the Oracle
equivalent of (getdate()) instead of front end generating the date.
b) Any �Date� Insert or Update to Oracle database.-Standardize how dates are to be
formatted.-Identify all date columns in db and search all modules for each field.
-Make syntax change for each field-Test-
Date Validation in your ColdFusion Code through hidden field "name=formfield_date"
will not work with Oracle. Only client side validation (Javascript validation) would
work.Need to write �datediff� function in Oracle to mimic this functionality. Modify
syntax for front end Oracle does not support the <CFINSERT> and <CFUPDATE> Cold Fusion
tags. As a result, all database interaction between Cold Fusion and Oracle must be
done through the use of the <CFQUERY> tag.
Oracle Stored Procedure execution syntax is different from SQL Server Stored Procedure
syntax:
EXECUTE PROCEDURE:
<CFQUERY Name="SampleProcedure" Datasource="OracleDB">
BEGIN procedureName('Param1','Param2', 'ETC'); END;
</CFQUERY>
While this syntax works perfectly for the execution of any PL/SQL Stored
Procedure, one important thing to note is that Oracle procedures do not have "output".
In other words, once a procedure has been executed, further processing will require
separate queries. As a result, if a procedure is designed to INSERT data into a table,
the only way to view that data after it has been inserted is to write a new query that
SELECTs the newly inserted data from the desired tables. This SELECT query may not be
part of the <CFQUERY> that executed the procedure. For example, if one row of data was
inserted into a table called procTable when the procedure above was executed, the
following query would be necessary to extract that data and use it throughout the
remainder of the Cold Fusion template.Broken down: 10 Crawler stored procedures
rewritten:
Select count(ColumnName)........ would not work, try using:
"SELECT TO_CHAR(COUNT(colName)) AS counterAlias"
Need to determine why this is used and how it will affect functionality.
Right and Left Joins may not be supported by Oracle requiring views to be rewritten.
The MS SQL Server and Sybase SELECT INTO statement can insert rows into a table. This
construct, which is part SELECT and part INSERT, is not supported by ANSI. Replace
these statements with INSERT...SELECT statements in Oracle. If the MS SQL Server or
Sybase construct is similar to the following:
SELECT col1, col2, col3 INTO target_table FROM source_table
WHERE where_clauseyou should
convert it to the following for Oracle:
INSERT into target_table SELECT col1, col2, col3 FROM
source_tableRecommendations for any new code for
ColdFusion/SQL Server:
MS SQL Server and Sybase also allow a SELECT statement in the WHERE clause. For
example, consider the following statement from MS SQL Server or Sybase:
SELECT empname, deptname FROM emp, dept WHERE emp.empno = 100 AND
(SELECT security_code FROM employee_security WHERE empno =
emp.empno) = (SELECT security_code FROM
security_master WHERE sec_level = dept.sec_level)Convert this to
the ANSI-standard statement below for Oracle: SELECT empname, deptname FROM
emp, dept WHERE emp.empno = 100 AND EXISTS (SELECT security_code
FROM employee_security es WHERE es.empno = emp.empno
AND es.security_code = (SELECT
security_code FROM security_master
WHERE sec_level = dept.sec_level));
If you have the following in MS SQL Server or Sybase:
WHERE col1 = NULL
Convert it as follows for Oracle:
WHERE col1 IS NULL.
a) Column Name length limitations
b) Table Name length limitationsc) View Name length LimitationsA
Use of Max( *_id) for retrieving the last PK for insert as a FK. This needs to be
handles with Sequences in Oracle. This will become a problem once traffic gets
heavier.
----
1. SQL Server functions (that I've seen so far in SQL)
datediff � this can be converted as follows:
select * from view where datediff(day, date_posted, getdate()) = 1
this becomes
select * from view where trunc(sysdate - date_posted) = 1
getdate() � this becomes sysdate
2. Outer join syntax - Oracle doesn't support the join syntax in the from clause at
all. It must be converted to Oracle syntax. e.g.:
select * from organization left outer join credit on
organization.parent_organization_id = credit.standard_company_id
this becomes
select * from organization, credit where organization.parent_organization_id =
credit.standard_company_id (+)
3. Case Sensitivity - Character data is not case-consistent and our queries are not
case-consistent either. Data in the status column of Posting ranges from 'available'
to 'Available' to 'AVAILABLE'. Also, queries reference it some places as "status =
'available'", other places as "status = 'Available'".
Oracle cannot be made case-insensitive! Need to change all the queries to
lower(column_name) = 'xxxxx'
4. Text data types � The most literal equivalent of the text datatype in Oracle is
either long or clob. (i.e. it is actually a pointer to a block of memory and as such
can store huge amounts of data). However, in SQL Server you can perform string
operations on a text field, while in Oracle you cannot. If it is necessary to perform
string operations on column data then the field must be varchar2 and it must be
limited to 4000 characters. (2000 if using Unicode).
5. Unicode characters - Cannot store non-us_english characters like the Euro symbol in
most of character fields. I recommend we convertingl the chars and varchars to nchar
and nvarchar. This stores all characters as 2-byte unicode symbols instead of 1-byte
ASCII symbols. The only down side is each character is now 2 bytes instead of 1 - i.e.
the maximum nvarchar size is 2000 instead of 4000 and there will be twice as much
character data to transmit over the network.
-----------------------------------------------
FREE! The World's Best Email Address @email.com
Reserve your name now at http://www.email.com
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebarRsts&bodyRsts/cf_talk or send a message
to [EMAIL PROTECTED] with 'unsubscribe' in the body.
