Symptom

How do you call Oracle Stored Procedures and Functions from PowerBuilder?

Environment

Microsoft Windows 7 x64

Resolution

Calling Oracle Stored Procs/Functions from Powerbuilder whenever you want to make a call to an Oracle stored procedure or stored function, a good approach is to first declare it as an external function and then invoke it based on that declaration.

  1. Declaring an Oracle Stored Procedure so that PowerBuilder Knows About it

    This function/procedure declaration is done in the transaction user object (e.g. n_tr for a PFC App). Once inside the transaction user object, choose "Declare-Local External Functions" and follow the syntax below.
    1. Stored Procedure (no package)

      The declaration syntax for a stored procedure (on its own, outside package) is:

      SUBROUTINE SubRtnName(args) RPCFUNC

      In example 1.1, the declaration passes a string by value (i.e. IN) and a string by reference (i.e. IN OUT or OUT).

      SUBROUTINE CalcAmount(string LS_In1, ref string LS_Out2) RPCFUNC

      Notes:
      - if the procedure is not in a package and does not take any array parameters, then you can click the procedures button to paste in the procedure declaration directly from the database.
      - an optional alias clause can be added to allow PowerBuilder to use a different function name from Oracle (see alias format used with package declarations).
    2. Procedure inside an Oracle package

      The declaration syntax for a stored procedure inside a package is:

      SUBROUTINE SubRtnName(args) RPCFUNC ALIAS FOR "PackageName.ProcName"

      In example 1.2, the declaration passes a string by value (i.e. IN) and a string array by reference
      (i.e. IN OUT or OUT).

      SUBROUTINE CalcPenaltyAmt(string LS_In1, ref string LS_Out2[]) RPCFUNC ALIAS FOR "Penalty.P_Calc_Amount"
    3. Stored Function (no package)

      The declaration syntax for a stored function (on its own, outside package) is:

      FUNCTION ReturnTypeFcnName(args) RPCFUNC

      In example 1.3, the declaration passes a string by value (i.e. IN) and a string array by reference (i.e. IN OUT or OUT) and it returns a long.

      FUNCTION long CalcAmount(string LS_In1, ref string LS_Out2[]) RPCFUNC

      Note: the same notes given for stored procedure declarations apply to stored functions.
    4. Function inside an Oracle package

      The declaration syntax for a stored function inside a package is:

      FUNCTION ReturnTypeFcnName(args) RPCFUNC ALIAS FOR "PackageName.FunctionName"

      In example 1.4, the declaration passes a string by value (i.e. IN) and a string array by reference (i.e. IN OUT or OUT) and returns a long.

      FUNCTION long CalcPenaltyAmt(string LS_In1, ref string LS_Out2[])) RPCFUNC ALIAS FOR "Penalty.f_Calc_Amount"
  2. Invoking an Oracle Stored Procedure/Function

    This is the invocation syntax for a stored procedure/function that has been declared in the transaction object is shown below.

    Notes on Variables passed by Reference

    Dynamically-sized output variables (i.e. strings and arrays) must be preallocated up to the size needed. When using this invocation method, PowerBuilder does not dynamically allocate the space needed for them.

    Array Size Limitation: number of array elements times maximum element size cannot exceed 32K.
    1. Invoking a Stored Procedure

      The invocation syntax for a stored procedure is:

      TransactionObjectName.ProcName(args)

      Sample invocation:

      string in_parm1
      string out_parm2

      in_parm1 = "input value"
      out_parm2 = space(50) // preallocating space for string

      SQLCA.CalcAmount(in_parm1, out_parm2)
    2. Invoking a Stored Function (shown using an array variable)

      The invocation syntax is:

      ReturnValue = TransactionObjectName.FcnName(args)

      Sample invocation:

      string in_parm1
      string out_parm2[5] // defining fixed sized array
      long ll_return

      in_parm1 = "input value"

      // preallocating space for 500 chars for whole string array.
      // Each element will effectively be 500 bytes by allocating
      // the first.
      out_parm2[1] = space(500)

      ll_Return = SQLCA.CalcAmount(in_parm1, out_parm2[])
1
0