This article documents my initial effort to port SWAT to Oracle. Now the actual source code modification to SWAT are not included. What is included is changing the SQL from SQL Server to Oracle and some test code to verify the operations of the various Oracle stored procedures are understood.
It should be obvious the real work that is a usable end product is Al Alberto's SWAT series. It was an excellent series to work through. SWAT Part 9[^]
Now a little on my background needs to be understood so the extent on the usefulness of this article to you can be understood. I would hardly consider myself a "database person". Other than some basic Access/Jet apps and some very basic general SQL work to Oracle instances that others created I have done little. Now I have worked with database experts for years so believe I understand the concepts but that is all. So this has been several weeks of learning what I am sure is basic understanding of those that do this class of work as a day to day activity.
With that in mind it maybe just more of an introduction to newbie’s in general than specific to the SWAT code.
The samples provided are a simple set of working code that executes Oracle Functions, Procedures, and Packages. I have used the code against developer instances of Oracle 8i and 9i. They are provided to give you a working starting point and some code that you can rearrange just to see what happens.
To myself the tools provided with Oracle just did not flow like those with SQL Server. This is not a complaint just my observation of what I found more comfortable. I did come across one free utility to work with Oracle that I used extensively while learning what the heck am I doing. That was SQL TOOLS[^] by Aleksey Kochetov. It allowed me to work with the entire SQL file or individual lines as need. It also allowed me to clean up (DROP) the Function/Procedures/Packages in single steps, which was very nice.
I hope this first is obvious. T/SQL is not the same as PL/SQL! Just as one application has features that set it apart from others in the database world the ability to store operations for reliable and consistent results is a major one.
IDENTITY vs SEQUENCE
Oracle does not have a direct equivalent of the IDENTITY type as SQL Server. This is formed by having a SEQUENCE function that is used to "get" the next value.
There was not a direct equivalent to the TOP select option. I just deleted that and have not assessed what the impact is.
Does not support the Functions and Procedures as other options do. Yes it is more portable but that is becasue it works with the least common denominator. Is that what you want?
Oracle does have a .NET package that works directly with it's native API. As such it is faster than other options. It was a rather large download 82 Meg but did install easily and worked well with C#. Look for OraWin9204.exe on Oracles site. You do have to join to download it. I have not had undo SPAM so I will say you can trust joining. You do have to add the reference to the project. I used the 9i version with both 8i and 9i databases.
Parameter Ordering vs Names
Now the one item I found here that may very well be common knowledge and I just never thought of it is parameter names only apply to each side of the call to the stored procedure. I.E. On the client side you may reference a parameter by it's name and on the database side you can reference the parameter by it's name. However the parameter list is sent as an ordered list from the client to the database. The names are meaningless between the two.
Seamed very consistent with the ODP. I created the second client by just copying the first and doing a substitution for Oracle and OleDb. Then correcting the data types in the parameters.
Parameter Types and Direction
It appears that any default type and direction were not consistent. Now I will state I came to this conclusion while I was learning the ordering issue so this may just be my miss understanding. However for reliability I suggest that Direction and Type be specified. Also note that in Oracle functions you have the RETURN direction as well as the IN and OUT.
Cursors and Packages
In returning sets I find the usage of Oracle Packages to be hands down more intuitive than anything else. You can return cursors in via other means but you have to create definitions of what each is prior to usage. With the Package usage the ability to OPEN a cursor which is just then used by the following SQL was just simpler and more understandable. IMhO.
Resolution of the Procedures
To make sure I knew what was going on I deliberately broke each procedure to do one segment. If this was a production implementation at least some would be merged. Such as creating the next ID in the sequence and entering the new item would just be new item.
CREATE TABLE [dbo].[USERS] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemName] [nvarchar] (64) NOT NULL ,
[Password] [nvarchar] (128) NOT NULL ,
[Email] [nvarchar] (50) NULL ,
[Roles] [tinyint] NULL ,
[Active] [tinyint] NOT NULL
) ON [PRIMARY]
CREATE TABLE USERS (
ID NUMBER(10,0) NOT NULL ,
ItemName varchar2(24) NOT NULL ,
TeamName VARCHAR2(16) NULL ,
Password varchar2(16) NOT NULL ,
Email varchar2(50) NULL ,
UserRoles NUMBER(2,0) NULL ,
Active NUMBER(2,0) NOT NULL
create sequence userid increment by 1
start with 100 maxvalue 9999999 nocycle;
CREATE OR REPLACE FUNCTION GetNextUserID
RETURN NUMBER IS ret NUMBER(10,0);
SELECT userid.NEXTVAL INTO ret FROM dual;
CREATE OR REPLACE PROCEDURE InsertUser
id IN NUMBER,
itemname IN VARCHAR2,
team IN VARCHAR2,
password IN varchar2,
email IN varchar2,
userroles IN NUMBER,
active IN NUMBER
INSERT INTO USERS(ID, ItemName, TeamName,
Password, Email, UserRoles, Active) VALUES
(id, itemname, team, password, email, userroles, active);
Why Part 1
I have elected to call this Part One because the work is clearly not finished and I felt it was timely to at least share the effort to date. Now with my position I have to get permission to share my work done at home as well as under paid time. There are valid reasons for this so no whining, I am not. I am likely not going to finish the work (or if I do can never say that I have done so.) With that I open the rights up to create Part 2 to whom ever wishes to do so. Thanks to Alberto to sharing the original and allowing me to at least do this part.
- 27 March 2004 - initial article.