Monday, 16 January 2012 00:00
For a basic Oracle function, visit my article Basic Oracle Function Structure. For a more advanced version which uses cursors to work with SSRS, see my article Oracle Stored Procedures in SSRS.
What?
I asked someone for a simple, easy and basic Oracle stored procedure as my MySQL and T-SQL stored procedures work slightly differently and are easier to pull off. After much umming and aah-ing, I have written this article as a note for me to demo a working stored procedure and how to use cursors. I'm told I need to use this for SQL Server Reporting Services (SSRS) which is why I'm going down this route. For an even simpler stored procedure, I'd imagine you just get rid of the cursor.
Why
A standalone query is faster in the eyes of SSRS, however within SSRS we don't have the capability to run standard PL/SQL commands... unless they're run from within a stored procedure. Our aim is to run a small query first to return the ID of the student, and then to use this number in a second query which we're hoping will be quicker than a straightforward table join.
Sunday, 15 January 2012 00:00
I need to refer to how to create a function in Oracle PL/SQL and sites on the net just attempt to overcomplicate everything and have forgotten how it is to be new to Oracle. I need a function in it's simplest form and if I want to torture my successors, I'll complicate the function myself.
How?
Functions are supposed to return a single value, which is all I need in this case. In my example, I need to submit an Active Directory (AD) username and receive a student ID number instead, all with the aim to improve performance on some SSRS queries which accept either an ID number or an AD name as user parameters.
Syntax:
CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [function_name];
