Basic Oracle Stored Procedure Structure

Previously
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.

Syntax
copyraw
CREATE [OR REPLACE] PROCEDURE stored_procedure_name 
    [ (parameter [,parameter]) ]
IS | AS
    [declaration_section]

BEGIN
    executable_section

[EXCEPTION
    exception_section]

END [stored_procedure_name ];
  1.  CREATE [OR REPLACE] PROCEDURE stored_procedure_name 
  2.      [ (parameter [,parameter]) ] 
  3.  IS | AS 
  4.      [declaration_section] 
  5.   
  6.  BEGIN 
  7.      executable_section 
  8.   
  9.  [EXCEPTION 
  10.      exception_section] 
  11.   
  12.  END [stored_procedure_name ]

Example
copyraw
CREATE OR REPLACE PROCEDURE sp_get_studentdetails_from_ad (
	c_test out sys_refcursor, 
	p_STUDENT_ADNAME in varchar2
	) 
IS
BEGIN
   OPEN c_test FOR 
   SELECT * FROM student_accounts WHERE student_username = p_STUDENT_ADNAME;
END;
  1.  CREATE OR REPLACE PROCEDURE sp_get_studentdetails_from_ad ( 
  2.      c_test out sys_refcursor, 
  3.      p_STUDENT_ADNAME in varchar2 
  4.      ) 
  5.  IS 
  6.  BEGIN 
  7.     OPEN c_test FOR 
  8.     SELECT * FROM student_accounts WHERE student_username = p_STUDENT_ADNAME; 
  9.  END; 

View the results?
Oracle PL/SQL is a bit of a bugger as you need to do a few other things to see the results of this stored procedure. The following are commands that I would run to view the results of the above example:
copyraw
VARIABLE myResultSet REFCURSOR;
  1.  VARIABLE myResultSet REFCURSOR; 
copyraw
EXEC sp_get_studentdetails_from_ad(:myResultSet,'jsmith2012');
  1.  EXEC sp_get_studentdetails_from_ad(:myResultSet,'jsmith2012')
copyraw
PRINT myResultSet;
  1.  PRINT myResultSet; 
Should yield something like:
copyraw
MYRESULTSET
--------------------------------------------------------------------------------
STUDENTID  STUDENT_USERNAME  STUDENT_CREATION_DATE  STUDENT_FILE              
---------- ----------------- ---------------------- ----------------------------
1234567    jsmith2012        08-MAY-12              sac180786.txt
  1.  MYRESULTSET 
  2.  -------------------------------------------------------------------------------- 
  3.  STUDENTID  STUDENT_USERNAME  STUDENT_CREATION_DATE  STUDENT_FILE 
  4.  ---------- ----------------- ---------------------- ---------------------------- 
  5.  1234567    jsmith2012        08-MAY-12              sac180786.txt 

Permissions
copyraw
CREATE PUBLIC SYNONYM sp_get_studentdetails_from_ad FOR base_table_owner.sp_get_studentdetails_from_ad;
GRANT EXECUTE ON sp_get_studentdetails_from_ad TO a_more_casual_user;
  1.  CREATE PUBLIC SYNONYM sp_get_studentdetails_from_ad FOR base_table_owner.sp_get_studentdetails_from_ad; 
  2.  GRANT EXECUTE ON sp_get_studentdetails_from_ad TO a_more_casual_user; 

Yay for me
So this is the basic Oracle stored procedure. Now to integrate this with SQL Server Reporting Services (SSRS) v2008 R2...

Category: Oracle PL/SQL :: Article: 410

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.