Oracle databases

Oracle has the following database products available (prices as at Feb 2011 are for perpetual licence per Named user Plus):

• Oracle XE free limited version – version 10G, single database, max. 4Gb storage and 1Gb RAM usage, limited to 1 CPU; 32bit version only;
• Oracle Database Personal Edition – $A552 but minimum is 1 copy ($A110 for 1 year instead of perpetual)
• Oracle Database Standard Edition One – $A216 each but minimum purchase of 5 copies, plus $A48 per year support; limited to 2 CPU;
• Oracle Database Standard Edition – $A420 each but minimum purchase of 5 copies, plus $A93 per year support; limited to 4 CPU;
• Oracle Database Enterprise Edition – $A1141 but minimum is 1 copy and 1 year support is $A251

Current version of Oracle database is 11G but XE version is 10G.

Oracle also provide for free download (but you do need to create an Oracle account and log in):

• SQL developer – 32 bit or 64 bit version – both require Java to be installed; 32 bit version does not seem to install on 64 bit Windows 7

Installing Oracle XE:

• you will be prompted for a password for the main system accounts: SYS and SYSTEM
• the server will be automatically started – to start or stop manually – go to Start Menu, Programs, Oracle Express Edition…
• once installed, you can manage the database via the web-based administration system by logging on with the SYSTEM account
• you can connect to the database from SQL Developer by selecting create new connection and enter:
• connection name (any name you feel like creating)
• username = SYS
• password you created initially
• on the Oracle tab, set role to SYSDBA, connection type to Basic, hostname to localhost, port will be automatically entered as will SID = xe which is the databasename
• test connection and it should work, and then you can save this connection.
• clicking on the connection displays the tables, etc in the database, and just as in MS SQL Developer, you can create new tables, etc.

Connection strings:

MS OLE DB provider (msdaora.dll):

• standard security:
  Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;
• trusted connection:
  Provider=msdaora;Data Source=MyOracleDB;Persist Security Info=False;Integrated Security=Yes;

Oracle OLE DB provider (OraOLEDB.Oracle):

• standard security:
  Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;
• trusted connection:
  Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;
• XE, VB6 ADO:
  Provider=OraOLEDB.Oracle;dbq=localhost:1521/XE;Database=myDataBase;User Id=myUsername;Password=myPassword;
• XE, C++ ADO:
  Provider=OraOLEDB.Oracle;Data Source=localhost:1521/XE;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
• TNL-less:
  Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=myPort)))(CONNECT_DATA=(SID=MyOracleSID)(SERVER=DEDICATED)));User Id=myUsername;Password=myPassword;

MS ODBC (Driver={Microsoft ODBC for Oracle}):

• new version:
  Driver={Microsoft ODBC for Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword;
• connect directly with no TSN or DSN required:
  Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=199.199.199.199)(PORT=1523))(CONNECT_DATA=(SID=dbName)));Uid=myUsername;Pwd=myPassword;
• without TSN alternative:
  Driver={Microsoft ODBC for Oracle};CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server)(PORT=7001))(CONNECT_DATA=(SERVICE_NAME=myDb)));Uid=myUsername;Pwd=myPassword;

Oracle in XE Client ODBC driver:

• Driver=(Oracle in XEClient);dbq=111.21.31.99:1521/XE;Uid=myUsername;Pwd=myPassword;

Delphi 2007 DBX:

• if you get “error finding oci.dll in path”, open Tools:Options in Delphi 2007 IDE, go to Environmental Variables add C:\oraclexe\app\oracle\product\10.2.0\server\bin; to the Path variable in User Overrides.
• in TSQLConnection, create new connection and choose Oracle thenset connection properties: Database = localhost User_Name = user name and you should be OK to test connection which will prompt for password.
• NB. seems when you try to connect, Delphi 2007 tries to determine location of oracle client dlls via looking up HKLM\Software\Wow6432Node\Microsoft\CTF\KnownClasses but this key does not exist on 64 bit Windows 7 – nevertheless, connection works if you add the path as above.

BDE SQL Explorer:

• create new alias of type Oracle, set Net Protocol = TCP/IP,  Server Name = localhost and User Name then connect
• alternatively can set Net Protocol = TNS and Server Name = XE or whatever is in the tnsnames.ora file in your Oracle folders.

ODBC on 64 bit Windows 7:

• make sure you use the 32 bit ODBC manager on windows\sysWOW64\odbc32.exe to set up your datasources and not the default 64 bit ODBC manager.

MS SQL Server Management Studio v10.50.1600.1:

• no luck connecting as yet on my 64bit Win 7 machine.
• in attempting to create a connection to Oracle XE via the Export data functionality, neither the MS OLEDB driver for Oracle or the Oracle OLE DB driver was able to return a connection – strangely the list of available drivers differed to those available in either 32bit or 64bit ODBC32.exe, and did not include the 32 bit Oracle XE driver.

Migrating data:

• see Oracle’s page on migrating data
• see Oracle’s page on migrating from MS SQL Server.
• unfortunately Oracle SQL Developer v2.x does not support migrating from MS SQL Server 2008 but only v2000 or v2005 – if you try running the migration batch file with v2008, it fails with errors such as Invalid Object Name dbname.dbo.sysproperties.
• the beta version of Oracle SQL Developer v3.0 has a much better migration tool which does work with MS SQL Server 2008 BUT does not seem to migrate the data, only the schema.
• migrating databases does result in mapping of datatypes such as CLOB instead of text, BLOB instead of image, Number(x) instead of integer and bit types, and varChar2 instead of varchar.
• SQL Server datetime is precise to 1/300th sec, while Oracle’s date is precise to 1 sec but its timestamp is precise to 1/100 millionth sec, but there is no Time datatype.
• there are different year ranges catered for in date types
• Oracle’s varchar2 can hold up to 4000 bytes and may be an option for SQL Server Text datatype instead of resorting to CLOB.
• Unlike SQL Server, there is no Database Devices or Dump Devices in Oracle.

Migrating SQL statements from SQL Server to Oracle:

• Oracle does not support SELECTs without FROM clauses. However, Oracle provides the DUAL table which always contains one row.
• instead of getdate() you must use sysdate from dual
• insert requires into as it is no longer optional
  
• cannot use * for outer joins, instead can use (+) on opposite side – perhaps better to use proper SQL syntax for joins
• cannot use = in column aliases
• cannot use CONVERT(), datediff, datepart, etc must use Oracle equivalents such as to_number, to_char(date, format)

• use of DECODE instead of subqueries?
• cannot use COMPUTE or BROWSE
• while SQL Server allows multiple databases on a server, Oracle has one database but multiple tablespaces, schemas (equivalent to dbo) and users, hence, unfortunately with Oracle you do need to specify the schema table owner in the SQL statements ie. you must use Select * from dbo_dbname.tablename as Select * from tablename will not work. Maybe there is a way around this but I am a newbie to Oracle and haven’t found it yet.
• instead of Transact-SQL stored procedura syntax, you, must use PL/SQL for stored procedures
• Oracle has many unique features such as Clusters, Packages, triggers for each row, Synonyms, Snapshots and regular expressions.
• see details of difference here.
• in summary, if you need to run two versions of a complex database application on both SQL Server and Oracle, it is a nightmare to manage the SQL code differences, especially if you have use a lot of conversion or calculated column functions.
• if these seems too hard, maybe it’s time to consider use of a middle-tier schema architecture such as remObjects DA Schema – but not all SQL is supported.