Oracle BYREF Stored Procedure Example
The following example shows how to create, register and invoke a database procedure with BYREF parameters 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 with BYREF parameters
1. Create the Oracle stored procedure from the terminal monitor. The trailing spaces after each line are required to successfully create the procedure.
remove procedure order_byref;
\p\g
direct execute immediate '
CREATE OR REPLACE PROCEDURE order_byref
( id_number IN OUT NUMBER,
name IN OUT VARCHAR,
status IN OUT CHAR,
order_date IN OUT DATE)
AS
BEGIN
INSERT INTO orders values (id_number, name, status, order_date);
SELECT id_number, name, status, order_date
INTO id_number, name, status, order_date
FROM orders
WHERE orders.id_number=0;
DELETE FROM orders WHERE orders.id_number=0;
END;';
\p\g
2. Register the stored procedure as a gateway database procedure:
register procedure order_byref
( id_number int byref,
name varchar(20) byref,
status char(20) byref,
order_date date byref)
as import
from 'order_byref';
\p\g
3. Invoke the gateway database procedure. (Embedded SQL code fragment):
col1=10;
sprintf(col2, "UpdateVarChar" );
sprintf(col3, "UpdateVarChar" );
sprintf(col4, "23-feb-2001" );
printf( "values before:\n col1: %d\n col2: %s\n col3: %s\n col4: %s\n\n", col1, col2, col3, col4 );
EXEC SQL execute procedure order_byref
( id_number = BYREF(:col1),
name = BYREF(:col2),
status = BYREF(:col3),
order_date = BYREF(:col4));
printf( "values after:\n col1: %d\n col2: %s\n col3: %s\n col4: %s\n\n", col1, col2, col3, col4 );
After the execution of the procedure, the content of each parameter is different.
Last modified date: 08/22/2022