Calling Oracle stored procedures and functions
Symptom
How do you call Oracle Stored Procedures and Functions from
PowerBuilder?
Environment
Microsoft Windows 7 x64
Solution
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.
-
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.-
Stored Procedure (no package)
The declaration syntax for a stored procedure (on its own,
outside package) is:1SUBROUTINE SubRtnName(args) RPCFUNCIn example 1.1, the declaration passes a string by value
(i.e. IN) and a string by reference (i.e. IN OUT or OUT).1SUBROUTINE CalcAmount(string LS_In1, ref string LS_Out2) RPCFUNCNotes:
1) 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.2) 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). -
Procedure inside an Oracle package
The declaration syntax for a stored procedure inside a
package is:1SUBROUTINE 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).1SUBROUTINE CalcPenaltyAmt(string LS_In1, ref string LS_Out2[]) RPCFUNC ALIAS FOR "Penalty.P_Calc_Amount" -
Stored Function (no package)
The declaration syntax for a stored function (on its own,
outside package) is:1FUNCTION ReturnTypeFcnName(args) RPCFUNCIn 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.1FUNCTION long CalcAmount(string LS_In1, ref string LS_Out2[]) RPCFUNCNote: the same notes given for stored procedure
declarations apply to stored functions. -
Function inside an Oracle package
The declaration syntax for a stored function inside a
package is:1FUNCTION 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.1FUNCTION long CalcPenaltyAmt(string LS_In1, ref string LS_Out2[])) RPCFUNC ALIAS FOR "Penalty.f_Calc_Amount"
-
-
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.-
Invoking a Stored Procedure
The invocation syntax for a stored procedure is:
1TransactionObjectName.ProcName(args)Sample invocation:
1234567string in_parm1string out_parm2in_parm1 = "input value"out_parm2 = space(50) // preallocating space for stringSQLCA.CalcAmount(in_parm1, out_parm2) -
SInvoking a Stored Function (shown using an array
variable)The invocation syntax is:
1ReturnValue = TransactionObjectName.FcnName(args)Sample invocation:
123456789101112string in_parm1string out_parm2[5] // defining fixed sized arraylong ll_returnin_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[])
-