How to write a Stored Procedure in Oracle

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

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: