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.

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:

1. Open the BRM_home/sys/data/config/config_stored_procedure.xml file.

2. 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.

3. Save and close the XML file.

4. 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 Storable Class Reference.

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.

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.

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

1. Create a custom stored procedure named get_gl_total.

2. Add get_gl_total to the /config/stored_procedure object.

3. Create an input flist for PCM_OP_EXEC_SPROC.

4. Use testnap to run get_gl_total through the PCM_OP_EXEC_SPROC opcode.

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;

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