From SQL to Oracle Convertion

For converting sql server schema to Oracle

SQL Ways Wizard

Observations:
1. Oracle Procedure name supports only 32 characters.
2. Return type is number or string then it was converted as Function.
3. Return type is List (Ref Cursors) then it was converted as Procedure.
4. All the identity columns will have a sequence created in Oracle.
5. Sequence of paramters in procedure should be in same order while calling in DAL layer.
6. The Oracle User should have permissions on views and create sequence.
7. For data comparisons in plsql, TO_TIMESTAMP('1753-01-01', 'YYYY/MM/DD') is used.
8. For Crystal reports
..Logon parameters, the database will be blank and server will be data source.
..Location doesn't have to be passed to tables while retrieving the data from procedures.

Reference in bin - Oracle.DataAccess
Replace
System.Data.SqlClient to Oracle.DataAccess.Client
SqlDataAdapter  to  OracleDataAdapter
SqlCommand to OracleCommand
SqlDataReader  to OracleDataReader
SqlConnection  to OracleConnection
SqlParameter  to  OracleParameter
SqlDbType.Int  to  OracleDbType.Int32
SqlDbType.NVarChar  to OracleDbType.NVarchar2
SqlDbType.DateTime  to OracleDbType.TimeStamp
SqlDbType.Image  to  OracleDbType.Blob
SqlDbType.Char  to  OracleDbType.Char
SqlDbType.VarChar  to OracleDbType.VarChar2
SqlDbType  to  OracleDbType
SqlDbType.bit  TO  OracleDbType.Int32

WEB.CONFIG - DBACCESS
Cursor for all the return multiple rows methods...
Dim prmOut As New OracleParameter("v_ref_cur", OracleDbType.RefCursor)
prmOut.Direction = ParameterDirection.Output
db.Parameters.Add(prmOut)

Remove dr.GetInt32(
dr.GetInt32(dr.GetOrdinal("AuditID"))
dr("AuditID")

dalATSUsers - access privileges...
MenuList - replace xml file for uppercase field names...

can you pls suggest the dbtype for 64 bit os err msg
Conversion from type 'OracleDecimal' to type 'Integer' is not valid.
in the DAL layer
search for
db.AddParamToSQLCmd(sqlCmd, "@ReturnValue", OracleDbType.Int32, 0, ParameterDirection.ReturnValue, Nothing)
replace it with
db.AddParamToSQLCmd(sqlCmd, "@ReturnValue", DbType.Int32, 0, ParameterDirection.ReturnValue, Nothing)


drop user IEPS cascade;
CREATE USER IEPS IDENTIFIED BY IEPS;
GRANT CONNECT,RESOURCE TO IEPS;
GRANT CREATE VIEW TO IEPS;

To get the first monday in coming month...
SELECT TO_CHAR(NEXT_DAY(LAST_DAY(sysdate)+1,'Monday'),'MM/DD/YYYY') "Next_Day" from DUAL;

To Support Arabic... for Client Tools
HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLE \ HOMExx \ NLS_LANG
Change the registry value from
AMERICAN_AMERICA.AR8MSWIN1256
to
American_America.UTF8