How-To Use the Oracle Stored Procedures Fetcher

This article is a step-by-step guide to create and invoke stored procedures using the Oracle Stored Procedures Fetcher.

Step-by-Step Guide

Configure SDK Project:

1. Create a new Stored Procedure SDK project
   

2. Set the Database Type to Oracle JDBC

   
   

3. Populate the backend credentials and press Finish
   

   Note: the backend user must have a DBA Role / `GRANT SELECT ANY DICTIONARY TO user`, in addition to other privileges needed to create new Schema Objects , Stored Procedures, packages, etc.

Create Stored Procedure Entity Using Stored Procedure Fetcher - Simple Use Case

This use case describes a stored procedure that returns output parameters supported by JDBC SQL types, such as VHARCHAR, INT, BLOB, FLOAT, Schema Objects etc.
In this case we can invoke the stored procedure directly without the need for a wrapper

1. Right-Click on SDK Project→ OpenLegacy → Import Oracle Stored Procedure
   
   
   

2. Click "Fetch metadata".
   
3. Use the Database Schema combo-box to select the requested schema
   
   

4. Set the Database Package and choose Stored Procedure
   
   
   
   

5. Check "Generate JUnit test", if needed, and press OK
   

Create Stored Procedure Entity Using Stored Procedure Fetcher - Complex Use Case

This use case describes a stored procedure that returns output parameters that are not supported by JDBC SQL types, such as Record.
In this case, a wrapper is generated automatically and needs to be added to the database.
Our entity will invoke the Stored Procedure wrapper

1. Right-Click on SDK Project→ OpenLegacy → Import Oracle Stored Procedure
   
   
   

2. Click "Fetch metadata" .
   
3. Use the Database Schema combo-box to select the requested schema
   
   

4. Set the Database Package and choose Stored Procedure
   
   
   
   

5. Check "Generate JUnit test", if needed, and press OK
   An entity is generated together with a wrapper SQL script located in the SdkProject/src/main/resources/wrappers folder
   
   

6. Copy the wrapper code to the target package and compile it (in the database)
   
   

   Wrapper Code Example

   /*Compile types*/
   
       CREATE TYPE DEMO.REC_EMPLOYEE_OBJ AS OBJECT (
           EMPLOYEE_ID NUMBER,
           FIRST_NAME VARCHAR2(20),
           SALARY NUMBER,
           CONSTRUCTOR FUNCTION REC_EMPLOYEE_OBJ RETURN SELF AS RESULT
       );
       /
   
   /*Compile types*/
   
   /*Add procedure signature to package and compile*/
   
       PROCEDURE GET_EMPLOYEE_BY_ID_WR(EMP_ID IN NUMBER, STATUS_CODE OUT VARCHAR2, EMPLOYEE OUT DEMO.REC_EMPLOYEE_OBJ);
   
   /*Add procedure signature to package and compile*/
   
   /*Add procedure wrapper & init functions to package body and compile*/
   
       FUNCTION INIT(PARAM IN DEMO.TEST.REC_EMPLOYEE) RETURN DEMO.REC_EMPLOYEE_OBJ
       IS
           RES DEMO.REC_EMPLOYEE_OBJ;
       BEGIN
           RES := DEMO.REC_EMPLOYEE_OBJ(
               PARAM.EMPLOYEE_ID,
               PARAM.FIRST_NAME,
               PARAM.SALARY
           );
           RETURN RES;
       END;
   
       PROCEDURE GET_EMPLOYEE_BY_ID_WR(EMP_ID IN NUMBER, STATUS_CODE OUT VARCHAR2, EMPLOYEE OUT DEMO.REC_EMPLOYEE_OBJ)
       IS
           EMPLOYEE_ORIG DEMO.TEST.REC_EMPLOYEE;
       BEGIN
           DEMO.TEST.GET_EMPLOYEE_BY_ID(EMP_ID, STATUS_CODE, EMPLOYEE_ORIG);
           EMPLOYEE := INIT(EMPLOYEE_ORIG);
       END;
   
   /*Add procedure wrapper & init functions to package body and compile*/
   
   
   

   
   

7. Set the Execution action path to the target package for which the wrapper was implemented

   
   

   
   

   Set Path as follows : SCHEMA_NAME.PACKAGE_NAME.STORED_PROCEDURE_NAME