Running Stored Procedures

About Running Stored Procedures in BRM

You can access data in the BRM database by using the BRM base opcodes, such as PCM_OP_SEARCH, PCM_OP_READ_FLDS, PCM_OP_WRITE_FLDS, and so on. While these opcodes will meet most of your business needs, you may occasionally need to perform complex joins, aggregations, and updates on the BRM data. To do so, you can run a custom stored procedure against the BRM database.

Adding Custom Stored Procedures to BRM

If you want to run a custom stored procedure against the BRM database, you must add the stored procedure's name to the /config/stored_procedure object. BRM allows only the stored procedures listed in /config/stored_procedure to be run in BRM.

To add custom stored procedures to BRM, you edit the config_stored_procedure.xml file and then load it into the database by using the load_config utility.

To allow BRM to run custom stored procedures:

Open the BRM_home/sys/data/config/config_stored_procedure.xml file.
Add a <RESULTS> array element for each custom stored procedure:
```
<RESULTS elem="x"> 
   <NAME>storedProcedure</NAME>
   <DESCR>description</DESCR>
</RESULTS>
```
where storedProcedure is the name of your custom stored procedure, and description is a short description of what the stored procedure does.
Save and close the XML file.
Load the file into the database by running the load_config utility:
```
load_config config_stored_procedure.xml
```
If the XML file is in a different directory from which you run the utility, include the entire path to the file.

See "load_config" in BRM Developer's Guide for information about the utility's syntax and parameters.

To verify that your custom stored procedures were loaded into the database, display the /config/stored_procedure object by using the Object Browser or by using the testnap utility. See "Reading an Object and Writing Its Contents to a File" in BRM Developer's Guide.

For more information about the /config/stored_procedure object, see BRM Storable Class Reference.

Running Stored Procedures

To run a stored procedure against the BRM database, you use the PCM_OP_EXEC_SPROC opcode. Configure your custom client application to call the opcode, or run the opcode by using testnap. See "Using the testnap Utility to Test BRM" in BRM Developer's Guide for information about using testnap.

Caution:

Running stored procedures using PCM_OP_EXEC_SPROC should be done with caution as there are chances for data corruption or undesired results if not used with the utmost care.
Stored procedures have fixed input and output parameters, which may inhibit the extensibility of a feature if not used carefully.

The input flist that you pass into the PCM_OP_EXEC_SPROC opcode must specify the stored procedure to run and include an array of PIN_FLD_ARGS elements for defining the parameters required by the stored procedure. You can also include an optional PIN_FLD_RESULTS element for defining the results to return in the output flist. For example:

0 PIN_FLD_POID              POID [0] 0.0.0.1 /procedure -1 0
0 PIN_FLD_PROC_NAME          STR [0] "Procedure_Name"
0 PIN_FLD_ARGS             ARRAY [1] 
1     PIN_FLD_ACCOUNT_OBJ   POID [0] 0.0.0.1 /account 1536772 0
1     PIN_FLD_ARG_TYPE      ENUM [0] 0
0 PIN_FLD_RESULTS          ARRAY [1]

These fields are used for simple input and output parameters:

PIN_FLD_POID: Set this to a type-only POID for the /procedure object.
PIN_FLD_PROC_NAME: Set this to the name of the stored procedure to run. The name of the stored procedure must be listed in the BRM_home/sys/data/config/config_stored_procedure.xml file.
PIN_FLD_ARGS: An array of input and output parameters. These must be defined in the order in which the parameters are defined in the stored procedure. There is no support for named parameters.
- PIN_FLD_ARG_TYPE: Specify whether the field is an input (0) or an output (1) parameter.
- PIN_FLD_ACCOUNT_OBJ: This represents any BRM field type. For POID fields, the individual component values are split out and passed as separate parameters. Thus, a single POID would map to four distinct parameters in the stored procedure.
PIN_FLD_RESULTS: An array of parameters for the opcode to return in its output flist.

Sample of Running a Custom Stored Procedure

The following shows an example of how to run a custom stored procedure. This example includes the following high-level steps:

Create a custom stored procedure named get_gl_total.
Add get_gl_total to the /config/stored_procedure object.
Create an input flist for PCM_OP_EXEC_SPROC.
Use testnap to run get_gl_total through the PCM_OP_EXEC_SPROC opcode.

Creating Sample Stored Procedure

The following creates a simple stored procedure named get_gl_total that returns the sum of /journal data for an account, grouped by resource_id and gl_id.

CREATE OR REPLACE PROCEDURE get_gl_total (
   i_account_obj_db   IN NUMBER,
   i_account_obj_type IN VARCHAR2,
   i_account_obj_id0  IN NUMBER,
   i_account_obj_rev  IN NUMBER,
   out_cv             IN OUT SYS_REFCURSOR) AS
BEGIN
   OPEN out_cv FOR
       SELECT account_obj_db,
              account_obj_type,
              account_obj_id0,
              0 account_obj_rev,
              resource_id,
              gl_id,
              SUM(db_ar_net_amt) AS amount
         FROM journal_t
        WHERE gl_id > 0
          AND DECODE(i_account_obj_id0, 0, account_obj_id0, i_account_obj_id0) = account_obj_id0
     GROUP BY account_obj_db,
              account_obj_type,
              account_obj_id0,
              resource_id,
              gl_id;
END;

Adding Sample Stored Procedure to BRM

The following procedure shows how to add get_gl_total to the list of stored procedures that can be run against the BRM database:

Add the following <RESULTS> array element to the BRM_home/sys/data/config/config_stored_procedure.xml file:

<RESULTS elem="80"> 
   <NAME>get_gl_total</NAME>
   <DESCR>This stored procedure sums journal data for an account</DESCR>
</RESULTS>

Load the file into the database by running the load_config utility:
```
load_config config_stored_procedure.xml
```
Stop and restart the Connection Manager (CM).

Creating Sample Input Flist

The following shows a sample input flist for PCM_OP_EXEC_SPROC for running the get_gl_total stored procedure. It specifies to retrieve journal data for account 1536772 and return the /account object POID, resource ID, G/L ID, and amount for each journal entry.

0 PIN_FLD_POID               POID [0] 0.0.0.1 /procedure -1 0
0 PIN_FLD_PROC_NAME           STR [0] "get_gl_total"
0 PIN_FLD_ARGS              ARRAY [1]
1     PIN_FLD_ACCOUNT_OBJ    POID [0] 0.0.0.1 /account 1536772 0
1     PIN_FLD_ARG_TYPE       ENUM [0] 0
0 PIN_FLD_RESULTS           ARRAY [0]
1     PIN_FLD_ACCOUNT_OBJ    POID [0] 0.0.0.1 /account -1 0
1     PIN_FLD_RESOURCE_ID     INT [0] 0
1     PIN_FLD_GL_ID           INT [0] 0
1     PIN_FLD_AMOUNT      DECIMAL [0] NULL

Using testnap to Run Sample Stored Procedure

The following shows how to run the sample get_gl_total stored procedure by using the testnap utility and the sample input flist:

Start the testnap utility:
```
testnap
```
Load the sample input flist (in a file named sample_input) into buffer 1:
```
r <<token sample_input 1
```

Run the PCM_OP_EXEC_SPROC opcode using the input flist from buffer 1:

xop PCM_OP_EXEC_SPROC 0 1

The utility returns the opcode's output flist. For example:

0 PIN_FLD_POID                 POID [0] 0.0.0.1 /procedure -1 0
0 PIN_FLD_RESULTS             ARRAY [0]
1     PIN_FLD_ACCOUNT_OBJ      POID [0] 0.0.0.1 /account 1536772 0
1     PIN_FLD_RESOURCE_ID       INT [0] 978
1     PIN_FLD_GL_ID             INT [0] 102
1     PIN_FLD_AMOUNT        DECIMAL [0] 13.548
0 PIN_FLD_RESULTS             ARRAY [1]
1     PIN_FLD_ACCOUNT_OBJ      POID [0] 0.0.0.1 /account 1536772 0
1     PIN_FLD_RESOURCE_ID       INT [0] 1000502
1     PIN_FLD_GL_ID             INT [0] 102
1     PIN_FLD_AMOUNT        DECIMAL [0] 0