Archive for category Oracle
This is the SQL to get data between two dates, I know lots of people find datediff() confusing so this is a nice cut and paste script to have handy:
select * from tableName where datediff(day,'2011-06-27 12:28:34.480',theDate)>=0 and datediff(day,'2011-06-28 12:28:34.480',theDate)<=0
The first date in that string is the start date and the second is the end date. You can change the ‘day’ parameter to any amount you wish (second, month, year). You should always use datediff() to get the difference between dates in SQL Server as it will handle issues like 30, 31 and 28 day months, leap years, etc. datediff() is odd in that if you run this:
select datediff(day,'2011-06-27 12:28:34.480','2011-06-28 12:28:34.480')
It will give you a whole number, in this case 1. So when the left side is less than the right side we get positive numbers. This seems backwards to the more natural seeming syntax of greater than / less than:
select * where date1 > date2
Unfortunately you can’t use greater than / less than, this is because even though they work, they will compare down to the nanosecond, which is not generally what we want.
If you need to do this in your .Net dataset here are two examples of how to do it. Two because the way you write your query will vary depending on what database driver you’re using.
For SQL Server 2000:
select * from tableName where datediff(day,?,theDate)>=0 and datediff(day,?,theDate)<=0
For SQL Server 2005+:
select * from tableName where datediff(day,@StartDate,theDate)>=0 and datediff(day,@EndDate,theDate)<=0
Remember that if you have issues the default way to use this is with question (?) marks. Question marks were used to denote parameters all the way back to ADO in VB 6. The downside of using question marks is your parameters must be added to your code in the order they are in the query. (In Oracle they always have to be added in the order of parameters in the query, despite being named. Unless they’ve fixed that since I last used the Oracle driver).
Here’s a topic that’s integral to programming, but I doubt there’s a developer out there that has them all memorized, datatypes. Different languages / data storage applications have different datatypes. Often they’re not even compatible with each other so marshaling data from database to user interface and back again can be a challenge.
I’m going to keep a list of links to datatype definitions here, if you have one you would like me to add be sure to leave a message!
I’ve been having quite the time recently getting old code, old Oracle databases and old Oracle database drivers to work alongside new code, new databases and new database drivers. Some of the issues I’ve had are:
1) Microsoft wrote a oracle driver for .Net which they have now deprecated
2) Oracle doesn’t seem willing to allow downloads of version 9.x from their website, if you can figure out where to download it from, let me know.
3) .Net 2010 needs a recent version of the Oracle driver (around ver 22.214.171.124.2) to replace the now deprecated Microsoft one. Unfortunately when installed the new driver won’t connect to old databases. Most of the databases I use are recent, but there is one I can’t connect to with 11.x
4) Oracle used to use a Oracle_Base folder to tell a machine what the default version of Oracle on it was. They seem to be moving away from this approach but the old base dir still haunts. My old VB6 apps for example are stuck in their ways and won’t use anything but the base version.
There’s more, but I’ll leave it at that for now. Finally I found some details on how I can make this nightmare a little less frightening. Firstly make Oracle 9.x the “Base” version, then in your .Net apps you can specify what version to use. To do this modify your web or app config files, add this XML just before the closing </configuration> tag:
<oracle.dataaccess.client> <settings> <add name="DllPath" value="C:\ORACLE\Base\product\126.96.36.199.2\client\bin"/> <add name="FetchSize" value="65536"/> <add name="PromotableTransaction" value="promotable"/> <add name="StatementCacheSize" value="10"/> <add name="TraceFileName" value="C:\ORACLE\Base\product\188.8.131.52.2\client\odpnet2.trc"/> <add name="TraceLevel" value="63"/> <add name="TraceOption" value="1"/> </settings> </oracle.dataaccess.client>
Note: the paths have to be valid for your machine.
To make this process even easier, instead of modifying your web or app config you can modify your machine config. This will affect all .Net apps written for a given .Net version. EG modify this file:
With the same method mentioned above. Now all 4.0 .Net apps will use that Oracle version by default. They can still override that by putting a different one in their app or web config.
More info on managing Oracle installations can be found here:
Here are some links to Oracle resources that you’ll need if you want to use .Net to get data from Oracle (particularly .Net 2010 / 4.0):
Oracle driver download (they’ve deprecated the System.Data.Oracle driver that Microsoft originally developed for the release of .Net, this is Oracle’s .Net driver):
How to write code to fetch data from Oracle (this is without using the XSD dataset that exists in .Net):
FYI: Oracle data parms use colons, like so:
I’ll have to write something more verbose than this at some point. For the time being those links are useful.
If you’ve not guessed yet, I’ve done allot of work in Microsoft technologies. It’s strange but I didn’t realize how much I’d come to like Microsoft apps until I started using the competitions. Here are a few of the differences between Oracle and SQL Server that I’ve found so far. I’ll add more as I stumble on / think of them.
SQL Server: @
If you’re creating paramertized SQL statements in SQL Server the statement looks like this:
select * from table where parm = @inParm
in Oracle its:
select * from table where parm = :inParm
SQL Server: select GetDate()
Oracle: select sysdate from dual
To get the date in SQL Server it’s just a call to the GetDate() function, In Oracle you have to select things like this from ‘dual’, this applies to a number of things. In general if in SQL Server the sql leaves out the ‘from’ statement in Oracle add ‘from dual’
SQL Server: F5
Now this isn’t necessarily an Oracle ism, but that’s because Oracle doesn’t have a reasonably priced query executer like SQL Server does. In SQL Express F5 runs a SQL statement, in Oracle (or Oracle SQL Developer which is what I’m using) it’s F9.
SQL Server: null is null and ” is an empty string
Oracle: null is null and ” is also null
This is one of my biggest beefs with Oracle. Oracle treats an empty string as a null. So if you set a field in Oracle to ” and then read it back (in .Net code) ” will not equal the field. Joy. This is good fun when you’re designing databases and would like to not have to code for nulls. In SQL server you could set the field as ‘not null’ and in code just treat it as a string, avoiding all the aggravation of testing if IsNull(), or = System.DbNull.Value. This isn’t possible in Oracle, instead you have to pad all 0 length strings as ‘ ‘, which would be wrong since a single space isn’t a valid value. Ah magic numbers, you have to love them.
SQL Server: Select column1, column2, * from myTable
Oracle: Select column1 as c1, column2 as c2, myTable.* from myTable
This is a peculiar one. You really shouldn’t ever use * in production code, but when testing it’s very useful. In SQL Server if you want to get a couple of columns first and everything after all you have to do is say the columns and then *. In Oracle that generates a ambiguous column error. Oracle is treating the named column and the same column included by the * as using the same name and thus an error (ambiguous). While this is technically correct, it’s annoying.
More to come, I still need to talk about dates, varchars, stored procedures, the list is loooong.
I created a batch file that checks to see if the .net framework 3.5, 4.0, iSeries and Oracle Client is installed and if not install them. I found it pretty useful, here’s the code:
if not exist %windir%\microsoft.net\framework\v3.5 (
echo Copying installer for .Net 3.5
xcopy “\\somenetworklocation\dotNetInstallers\dotnetfx35.exe” c:\dotNetInstallers\ /c /r /y /q
echo Installing .Net 3.5
c:\dotNetInstallers\dotnetfx35.exe /qb /nopatch /norestart /lang:enu
) Else (
echo .Net 3.5 already installed, skipping
if not exist %windir%\microsoft.net\framework\v4.0.30319 (
echo Copying installer for .Net 4.0
xcopy “\\somenetworklocation\dotNetInstallers\dotNetFx40_Full_x86_x64.exe” c:\dotNetInstallers\ /c /r /y /q
echo Installing .Net 4.0
c:\dotNetInstallers\dotNetFx40_Full_x86_x64.exe /norestart /passive
) Else (
echo .Net 4.0 already installed, skipping
if exist c:\dotNetInstallers (
rmdir c:\dotNetInstallers /S /Q
if not exist C:\ORACLE\Base\product\11.1.0\client (
echo Copying Oracle 11g .Net Data Adapter
xcopy “\\somenetworklocation\Oracle 11g ODAC and Oracle Developer Tools for Visual Studio 184.108.40.206.20\*.*” C:\OraInst\ /E /C /H /R /Y /Q
echo Installing Oracle 11g .Net Data Adapter
setup.exe -responseFile C:\OraInst\OracleInstallPrefrencesNew.rsp
rem setup.exe -silent -responseFile C:\OraInst\OracleInstallPrefrencesNew.rsp
) Else (
echo Oracle 11.1.0 client already installed, skipping
if exist c:\OraInst (
rmdir c:\OraInst /S /Q
if not exist “C:\Program Files\IBM\Client Access\READMESP.TXT” (
echo Installing IBM iSeries Driver
“\\somenetworklocation\iSeries Access Install Image w all Options\setup.exe” -s
) Else (
echo IBM iSeries Driver already installed, skipping
echo Installation Complete
echo NOTE: you may need to restart for the changes to take effect.
Thanks to symantec’s forum for the inspiration for this one:
===== ORACLE NOTES =====
Automating Oracle’s install requires an ini file of sorts. The easiest way to generate this file is to run the install on a machine with the settings you’re interested in, like so:
setup.exe -record -destinationFile C:\OraInst\rec.rsp
Once you have that file you can use it to install like so:
setup.exe -silent -responseFile C:\OraInst\custom.rsp
There are some gotcha’s with this, one of note is the -responseFile option would not work for me when the file path had a space in it, even if I used the standard double quote escaping. So
setup.exe -silent -responseFile "C:\Oracle Install\custom.rsp”
Also you can’t move the installer after generating it, if you do you will get a “Invalid staging area” error. What I did to get around this was create a folder for installing, like:
and generated the file there. Then in the batch file copy the install from a network share to this local dir and then run setup.
Some more info on this is available on informit’s website here:
===== iSeries NOTES =====
Automating iSeries works much the same as automating Oracle. It creates an ini file of sorts when you run this command:
setup -r -f1d:\dir\file.iss
note that the flag "-f1" actually runs into the file and folder path, in this case: "d:\dir\file.iss". More here:
To then install the app on subsequent machines run this:
setup -s -f1d:\dir\file.iss -f2d:\dir\file.log
-s indicates a silent install, -f1 is the ini file and -f2 is where the install logs messages to. If you name the iss file this: setup.iss, and put it in the same dir as the setup app you don’t have to provide the -f1 parm. This is because the setup app will look for setup.iss automatically. More here:
Coming from a Microsoft background I use stored procedures all the time. They are great for many reasons, not the least being the rule of least privileges, for being precompiled (speed), for helping avoid SQL injection attacks etc.
Writing a stored procedure in SQL server is cake, here is an example of a simple stored procedure:
create PROCEDURE myProcedure (@inParm varchar(500)) AS SELECT column1, column2 FROM tTableName where column3 = @inParm
Nothing to it really, so when I had to write a stored procedure for Oracle silly me thought it would be just as easy, after all it is a enterprise class RDBMS right? Well, let’s just say things are allot different in Oracle land. In order for me to accomplish what seems to be a simple task first I had to create a new package. I liken this to C’s header files where you have to declare your method interface. Here is the SQL to create the package:
create or replace PACKAGE mypackage AS TYPE MyPackageType IS REF CURSOR RETURN tTheTableName%ROWTYPE; PROCEDURE GET_TheTableRecord ( MyInParm in VARCHAR2, MyReturnVal IN OUT MyPackageType); END mypackage;
Now that I have the package created with the Varchar2, MyPackageType interface I can modify the procedure: (presumably you can write both this and the following as one statement, I like splitting it out as I find it easier to follow)
create or replace PACKAGE BODY mypackage AS PROCEDURE GET_TheTableRecord ( MyInParm in VARCHAR2, MyReturnVal IN OUT MyPackageType) IS BEGIN OPEN MyReturnVal FOR SELECT * FROM tTheTableName where tTheTableName.column1 = MyInParm; END GET_TheTableRecord; END mypackage;
Now that you have a new procedure the questions is, how do you test it? I know that’s what my first question was. Back in SQL server land the test would look like this:
exec myProcedure('find this')
But not so in Oracle, this is what you have to run to test your new stored proc:
var my_cur refcursor exec mypackage.GET_TheTableRecord('test',:my_cur); print my_cur
I hope this saves you allot of time!
Here’s a link to a good Microsoft Article on this topic: http://support.microsoft.com/kb/321718