DbPLSQLTransaction

public bool DbPLSQLTransaction(string stmt)
public bool DbPLSQLTransaction(string stmt, params string[] args)
public bool DbPLSQLTransaction(string stmt, IDictionary<string, string> namedBindVariables)
public bool DbPLSQLTransaction(string stmt, FndPLSQLContext plSQLContext)
public SalBoolean DbPLSQLTransaction(SalSqlHandle hSql, string stmt)

The DbPLSQLTransaction executes a set of PL/SQL statements inside a transaction. The entire transaction is executed on the server.

Parameters

Name Description
string stmt String that contains PL/SQL statements. The keywords BEGIN and END should not be specified, since these are automatically added.
params string[] args The bind variables to use with the PL/SQL statement. First bindvariable will be replace the "{0}" placeholder, next one will replace "{1}" etc.
IDictionary<string, string> namedBindVariables Dictionary with named bind variables to use with the PL/SQL statement. Each bindvariable value will replace its corresponding "{key}" placeholder.
FndPLSQLContext plSQLContext The PLSQL Context passed within this database call.
NOTE! FndPLSQLContext should be avoided and only be used in order to solve legacy problems, having used PL/SQL globals with values passed from the client to the server without having the option to pass these as simple procedure arguments.
SalSqlHandle hSql Handle that identifies the database connection
NOTE! Should only be used if the hSql is other then cSessionManager.c_hSql. In pratice, 99.9% of all calls use cSessionManager.c_hSql.

Returns

The return value is TRUE all the statements are executed and the transaction committed, FALSE otherwise.

Comments

Using DbPLSQLTransaction instead of a combination of DbTransactionBegin, DbPLSQLBlock, DbTransactionEnd, gives better performance since less interaction across the network is needed.

The DbPLSQLTransaction wraps the statements inside an anonymous PL/SQL block and then executes the entire block on the server. The wrapping is done according to the following:

BEGIN
   <sStmt>
   commit;
EXCEPTION
   WHEN Error_SYS.Err_Security_Checkpoint THEN
      raise;
   WHEN OTHERS THEN
      rollback;
      raise;
END;

Try avoid to pass static values as arguments. Instead, keep using bind variables and assign static values to these. The reason for that is having the Oracle database being able to cache and re-use parts of the execution statement, if it's unchanged.

Example

DbPLSQLTransaction("&AO.Company_Org_Structure_API.Modify_Pos(:i_hWndFrame.frmOrganizationStructure.sAttr IN);");
DbPLSQLTransaction("&AO.Company_Org_Structure_Api.Modify_Pos({0} IN);", this.QualifiedVarBindName("sAttr"));
DbPLSQLTransaction("&AO.Customer_Order_Line_API.Modify_Blocked_For_Invoicing( {0} IN, {1} IN, {2} IN)",
		tblInvoicableLines_colsOrderNo.QualifiedBindName,
		tblInvoicableLines_colsLineNo.QualifiedBindName,
		tblInvoicableLines_colsRelNo.QualifiedBindName);
IDictionary<string, string> namedBindVariables = new Dictionary<string, string>();
namedBindVariables.Add("OrderNo", tblInvoicableLines_colsOrderNo.QualifiedBindName);
namedBindVariables.Add("LineNo", tblInvoicableLines_colsLineNo.QualifiedBindName);
namedBindVariables.Add("RelNo", tblInvoicableLines_colsRelNo.QualifiedBindName);

DbPLSQLTransaction("&AO.Customer_Order_Line_API.Modify_Blocked_For_Invoicing( {OrderNo} IN, {LineNo} IN, {RelNo} IN)", namedBindVariables);
FndPLSQLContext plSqlContext = new FndPLSQLContext();
plSqlContext.Set("RULES_API.PROCEDURE_NAME", this.dfsProcedureName.Text);

DbPLSQLTransaction("&AO.Rules_API.Run_Procedure(:i_hWndFrame.frmRules.sParams IN);", plSqlContext);


PL/SQL procedure extracting the value sent using FndPLSQLContext above:
PROCEDURE Run_Procedure( params_ IN VARCHAR2 )
IS
   procedure_name_ VARCHAR2(2000);
BEGIN
   procedure_name_ := App_Context_SYS.Find_Number_Value('RULES_API.PROCEDURE_NAME', NULL);
   ...
END Get_Parent_Inv_Id;