Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

 

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:

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

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

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:

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.