I'm trying to convert an existing SQL Server database and vb.net app to use Oracle and odp.net, i am a complete Oracle novice, but experienced in SQL Server, and so i have encountered several problems along the way which i think i have solved as follows:
1) The existing data uses GUIDs as the primary key for most tables (as the data is being sync'd across unconnected databases). My first problem was that seemingly oracle doesn't support GUIDs directly. I researched and got a solution of using RAW(16), so my tables are created with RAW(16) where ever the original used GUIDs
2) Next there are a lot of BIT values, again oracle doesn't appear to directly support BIT, and i've seen lots of debate over what to use e.g. CHAR(1) or NUMBER(1,0). In the end i opted for NUMBER(1,0)
3) Thirdly the existing database makes extensive use of Stored Procedures and what amounts to parameterised views (SELECT statements), which i seem to have found Oracle doesn't really do in anything like the same way. From looking around on the net it seems most people suggest using a RefCursor as an out parameter in the Stored Procedure. Here is what i ended up with as a query against a simple table, which has a RAW(16) primary "ClockingID", a RAW(16) foreign key "UserID", a date "Clocking", and a Number(1) "Deleted":
create or replace procedure Time_Clocking_GetForDateRange(FromDate in date, ToDate in date, ForUserID in Raw ,rc OUT SYS_REFCURSOR) as
begin
OPEN rc FOR
SELECT *
FROM Time_Clocking
WHERE (Time_Clocking.Clocking>=FromDate And Time_Clocking.Clocking<=ToDate) AND Time_Clocking.UserID=ForUserID AND Time_Clocking.Deleted=0
ORDER BY Time_Clocking.Clocking;
END;
My vb code is as follows (note this is just to test getting info out of the database):
Dim dbCommand As Oracle.ManagedDataAccess.Client.OracleCommand = Nothing
Dim db As Oracle.ManagedDataAccess.Client.OracleConnection = DAL.Utility.GetOracleDB()
db.Open()
Dim cmd As String = "Time_Clocking_GetForDateRange"
dbCommand = New Oracle.ManagedDataAccess.Client.OracleCommand(cmd, db)
dbCommand.CommandType = CommandType.StoredProcedure
dbCommand.BindByName = True
Dim param1 As New Oracle.ManagedDataAccess.Client.OracleParameter
param1.ParameterName = "rc"
param1.OracleDbType = Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor
param1.Direction = ParameterDirection.ReturnValue
dbCommand.Parameters.Add(param1)
dbCommand.Parameters.Add("FromDate", New Date(2014, 6, 1, 0, 0, 0))
dbCommand.Parameters.Add("ToDate", New Date(2014, 6, 1, 23, 59, 59))
dbCommand.Parameters.Add("ForUserID", Oracle.ManagedDataAccess.Client.OracleDbType.Raw, New Guid("367C37E05E26DE4FB625B3663B0CAA15").ToByteArray, ParameterDirection.Input)
Dim dt As New DataTable
dt.Load(dbCommand.ExecuteReader(CommandBehavior.CloseConnection))
Its at the ExecuteReader point that the app crashes with the following:
ORA-06550: line 1, column 15:
PLS-00306: wrong number or types of arguments in call to 'TIME_CLOCKING_GETFORDATERANGE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
There are so many things that i don't know here its hard to figure out which one of the above issues i thought i had solved is actually causing my problem. I would be very grateful for your expert thoughts. Many thanks.
The parameter direction on the REF cursor should be OUT. ReturnValue is for functions.