Kbase 18439: How to Create an Oracle Stored Procedure
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
15/10/2008 |
|
Status: Verified
GOAL:
How to create an Oracle stored procedure.
FIX:
A stored procedure is created via the create procedure command. The stored procedure will contain a block of code which is stored in the ORACLE database data dictionary. This block of code can be executed as a unit by invoking the stored procedure.
The following steps will produce an example that can be run against a sports database that has been migrated to ORACLE.
1) Log into Oracle's SQLPLUS as particular user that already has
the sports table migrated as that user's objects.
2) run the following code in SQLPLUS to create the stored
procedure 'pcust':
create or replace procedure pcust (num in int, orders out int,
states out int)
as begin
if num is null then
raise_application_error (-20101, 'cust num is missing');
else
select count(*) into orders from customer, order_
where customer.cust_num = order_.cust_num and
customer.cust_num > num;
select count(*) into states from customer where cust_num > num;
end if;
end;
3) In Progress, connect to the holder that was created after
migrating the sports database to ORACLE.
4) Update the schema holder using the menu option Dataserver->Oracle
Utilities->Add/Update schema. For the pre-selection criteria,
specify the username that used when creating the stored
procedure in ORACLE. Select 'pcust' from the list of objects.
NOTE: the stored procedure must be put into the schema holder
in order to be able to run this stored procedure from the 4GL.
5) Now, while connected to this newly-updated schema holder, run
the following 4gl routine that will in turn run the ORACLE stored
procedure. Type this into the procedure editor and run it:
define var h1 as int.
run stored-proc pcust h1 = proc-handle (20, output 0, output 0).
close stored-proc pcust where proc-handle = h1.
display pcust.orders pcust.states.
NOTE: Close the stored procedure after running it before successfully accessing the stored procedure buffer fields. The stored procedure buffer in this example is 'pcust' and it's fields are 'pcust.orders' and 'pcust.states'.