Calling Oracle Stored Procs/Functions from PB

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.

A. Declaring an Oracle Stored Procedure so that PowerBuilder Knows About it
This function/procedure declaration is done in the transaction user object. First create transaction object. Select File > New then choose Standard Class under Object Tab. Then select Transaction Type on Select Standard Class Type. 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, 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, 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 ReturnType FcnName(args) RPCFUNC

   
   
   In example, 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
   Function inside an Oracle package
   
   The declaration syntax for a stored function inside a package is:
   
   

       FUNCTION ReturnType FcnName(args) RPCFUNC ALIAS FOR "PackageName.FunctionName"

   
   
   In example, 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"

   
   

B. 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[])