DbPLSQLBlock

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

The DbPLSQLBlock executes a set of PL/SQL statements 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, FALSE otherwise.

Comments

If a DbPLSQLBlock results in starting an transaction, it also needs to be wrapped with a combination of the methods DbTransactionBegin, DbTransactionEnd & DbTransactionClear, which will commit or rollback the ransaction, depending on the result. As an alternative, DbPLSQLTransaction can be often used in order to to avoid writing these combinations.

The DbPLSQLBlock 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>
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

DbPLSQLBlock(":i_hWndFrame.frmOrganizationStructure.sPos := &AO.Company_Org_Structure_API.Get_Pos(:i_hWndFrame.frmOrganizationStructure.sAttr IN);");

DbPLSQLBlock("{1} := &AO.Company_Org_Structure_Api.Get_Pos({0} IN);", this.QualifiedVarBindName("sAttr"), this.QualifiedVarBindName("sPos"));

DbPLSQLBlock("{3} := &AO.Customer_Order_Line_API.Get_Description( {0} IN, {1} IN, {2} IN)",
		this.tblInvoicableLines_colsOrderNo.QualifiedBindName,
		this.tblInvoicableLines_colsLineNo.QualifiedBindName,
		this.tblInvoicableLines_colsRelNo.QualifiedBindName,
		this.QualifiedVarBindName("sDescription"));

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);
namedBindVariables.Add("Description", this.QualifiedVarBindName("sDescription"));

DbPLSQLBlock("{Description} := &AO.Customer_Order_Line_API.Get_Description( {OrderNo} IN, {LineNo} IN, {RelNo} IN)", namedBindVariables);

FndPLSQLContext plSqlContext = new FndPLSQLContext();
plSqlContext.Set("RULES_API.PROCEDURE_NAME", this.dfsProcedureName.Text);

DbPLSQLBlock("&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;