Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

Navigation  Back up to About Overview 
  
[+] Expand All
[-] Collapse All

Working with Stored Procedures in Oracle

The following notes discuss some considerations specific to Oracle, which uses the term package and package body when referring to stored procedures.

Assume you have a SELECT statement that extracts a user’s name, password, and profile from the table usertable when it receives the user’s name as an input parameter:

SELECT fullname, password, profile FROM usertable WHERE username = %name/63s

To write a package called myPack1 that performs the equivalent function, you would enter the following sequence of commands:

Package myPack1    is(PROCEDURE myProcname IN VARCHAR2,pass OUT VARCHAR2,prof OUT VARCHAR2,fName OUT VARCHAR2);End myPack1;

When referencing the package from radsql.aut, you would point to the package name myPack1 (not the procedure name myProc):

Package Body myPack1    is(PROCEDURE myProcname IN VARCHAR2,pass OUT VARCHAR2,prof OUT VARCHAR2,fName OUT VARCHAR2)ISBEGINSELECT fullname INTO fName, password INTO pass, profile INTO prof FROM usertable WHERE username = name;END myProc;End myPack1;

When you invoke the stored procedure, you would delineate each parameter as an input (!i), output (!o), or input/output (!io) variable. The presence of a !io or !o keyword indicates that the value returned from the database is to be included in the Access-Accept response as if it had been coded in the [Results] section. If a r value is included in the suffix (for example, !ir!r, or !or), the parameter is expected to be an output parameter, and the attribute is to be treated as if it were included in the [FailedSuccessResultAttributes] section. Variables that are not specifically marked are considered input parameters by default.

Note: Do not configure a stored procedure to call the same attribute more than once. Doing so may cause Steel-Belted Radius Carrier to fail.

Correct: SQL= {call joeproc2 ( @class!o)}
Incorrect: SQL= {call joeproc2 ( @class!o, @class!o)}

You can replace the SELECT statement by invoking myProc as follows:

SQL=BEGIN myPack1.myProc(%name!i, %password!o, %profile!o, %fullname!o ); END;

When using input-output parameters with Oracle, you must set the DefaultResults setting to 0. Any other variables that need to be returned (such as Reply-Message) must be identified by the “!o” marker within the SQL statement.

Modified: 2017-03-07