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.
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. |
The return value is TRUE all the statements are executed and the transaction committed, FALSE otherwise.
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.
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;