Oracle Row Returning Database Procedure Example
The following example shows how to create, register and invoke a database procedure that returns rows 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 that returns rows
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 PACKAGE rowproc_pkg AS
TYPE emptabtype IS RECORD (
NAME CHAR(10),
SALARY FLOAT(126),
DEPT CHAR(8),
DIV CHAR(3),
MGR CHAR(10),
BIRTHDATE CHAR(25),
NUM_DEP NUMBER(5)
);
TYPE emptabtab IS TABLE OF emptabtype;
FUNCTION emp_rowproc(insalary NUMBER, multiplier NUMBER)
RETURN emptabtab PIPELINED;
END rowproc_pkg;';
\p\g
DIRECT EXECUTE IMMEDIATE '
CREATE OR REPLACE PACKAGE BODY rowproc_pkg AS
FUNCTION emp_rowproc(insalary NUMBER, multiplier NUMBER)
RETURN emptabtab PIPELINED
IS
emp_rowproc_rec emptabtype;
CURSOR c1(sal NUMBER) IS
SELECT * FROM emp_rowproc_table WHERE salary <= sal;
BEGIN
OPEN c1(insalary);
LOOP
FETCH c1 INTO emp_rowproc_rec;
EXIT WHEN c1%NOTFOUND;
emp_rowproc_rec.salary := emp_rowproc_rec.salary * multiplier;
PIPE ROW(emp_rowproc_rec);
END LOOP;
CLOSE c1;
RETURN;
END;
END rowproc_pkg;';
\p\g
2. Register the stored procedure as a gateway database procedure:
REGISTER PROCEDURE emp_rowproc
(sal float,
multiplier integer)
AS IMPORT FROM 'rowproc_pkg.emp_rowproc'
RESULT ROW
(char(10), float, char(8), char(3), char(10), char(25), integer);
\p\g
3. Invoke the gateway database procedure - Embedded SQL:
printf( "name Salary Dept Div Manager Hire date Department#\n");
EXEC SQL execute procedure emp_rowproc(multiplier = :multi,sal = 30000)
result row (:name, :fsalary, :cdept, :div, :mgr, :bdate,:num_dep);
EXEC SQL begin;
printf( "%s %8.2f %s %s %s %s %d\n",
name, fsalary, cdept, div, mgr, bdate, num_dep);
EXEC SQL end;
Last modified date: 08/22/2022