Oracle Stored Procedure Example
The following example shows how to create, register and invoke a database procedure using the Oracle gateway:
Note: The user creating the Oracle stored procedure must have create procedure permissions. The register procedure requires insert permissions on the gateway procedure catalogs. The execute procedure statement requires execute permissions for the Oracle stored procedure and select permissions on the gateway procedure catalogs.
To create, register and invoke a database procedure
1. Create the Oracle stored procedure from the terminal monitor. The trailing spaces after each line are required to successfully create the procedure.
DIRECT EXECUTE IMMEDIATE
'CREATE OR REPLACE PROCEDURE ora_credit_update
(acc_no IN NUMBER,
amount IN NUMBER)
AS BEGIN
UPDATE accounts
SET balance = balance + amount
WHERE account_id = acc_no;
END;';
2. Register the stored procedure as a gateway database procedure:
REMOVE PROCEDURE gwcredit_update;
REGISTER PROCEDURE gwcredit_update
(acc_no INTEGER4,
amount FLOAT8)
AS IMPORT
FROM 'ora_credit_update';
3. Invoke the gateway database procedure:
EXECUTE PROCEDURE gwcredit_update
(acc_no = 14567,
amount = 1678.93);
Last modified date: 08/22/2022