Execute PL/SQL Methods

This page describes how a form window can interact with the database by executing SQL or PL/SQL.

Contents

• Bind variables
• Qualifying bind variables
• Parameter direction
• Make a database lookup
• Make a database transaction

Bind variables

Working with database calls, it is convenient and sometimes even necessary to use bind variables. A bind variable can be either a variable or a field, and they are used as a parameter to a database expression.

Bind variables are used both to provide data to the server execution as well as retrieve values back to the client. In the situation where data is retrieved to the client, it is quite obvious that we need to make use of a bind variable. Not as obvious though, is that making use of bind variables for supplying data is not only convenient but can also enhance the overall performance. When building a statement it is possible to concatenate client values directly into the statement. However, oracle as well as other database managers try to optimize performance by caching parsed statements. To utilize this optimization the developer should try to keep a consistent statement signature for recurring calls.

Figure 1 illustrates the difference between using concatenation and bind variables. When concatenating, the value that dfsEmpNo holds will be part of the statement sent to the server. If we assume that this statement will be called repeatedly, then we can see that concatenating will result in a different statement signature for each unique employee, while the bind variable construct will stay the same. So for statements where the input value will change, it is recommended to use bind variables. 

// Create the database statement with concatenated value.
DbImmediate("SELECT name INTO :i_hWndFrame.frmEmployee.dfsName FROM &AO.EMP WHERE emp_no = " + dfsEmpNo.Text);

// Call the database with bind variable
DbImmediate("SELECT name INTO :i_hWndFrame.frmEmployee.dfsName FROM &AO.EMP WHERE emp_no = :i_hWndFrame.frmEmployee.dfsEmpNo");

Figure 1: Equivalent statements shown using concatenated value as well as bind variable.

Qualifying bind variables

A bind variable needs to be fully qualified. To qualify means to define the context in which the variable resides. The format for a fully qualified reference is <window handle>.<window class>.variable or <window handle>.<window class>.field respectively for variables and fields. You must qualify variables because the actual database call is not made from within the current execution context, and without the qualification the referenced variables would not be reachable.

Parameter direction

When using bind variables, we need to instruct the client framework how the variable will be used in the server. This is done by setting the Parameter Direction property of the bind variable object. This is important because the client framework will handle the value of the bind variable differently depending on how the parameter direction is set. When a data field is used as a bind variable the edit flag of the field is affected differently.  

Table 1: List of parameter direction values together with their inline hint equivalent

The framework will provide correct parameter directions for all occurrences of bind variables where it is possible to resolve the usage. Framework will detect assignment statements and function calls. The developer will normally need to manually handle procedure parameters, string concatenation and calculations.

Table 2: List default direction given by the framework depending on usage.

Developing with Application Forms we do not work with bind variable objects directly in the way that is common with for example ADO.NET. In Application Forms the variables are defined directly in the statement as shown in Figure 1. The framework will parse and create the bind variable objects for us. This process is completely transparent to the developer. This means that we will not have a bind variable for which we can set the Parameter Direction property. Instead we hint the PL/SQL parser directly in the statement in combination with the variable reference. Table 1 lists the inline parameter direction hint name for the directions and Figure 3 illustrates the usage of a hint where the parameter to Add_To_Imp_Table__ is hinted as Input.

DbPLSQLBlock("&AO.Create_Company_Log_API.Add_To_Imp_Table__( :i_hWndFrame.dlgCreateCompany.dfsNewCompanyId IN);");

Figure 3: Shows a DbPLSQLBlock call with a hinted procedure parameter.

Calculations and Concatenations

The framework logic for setting default values for directions is limited. It will detect a if a bind variable is used to assign simply by validating that it is preceded by the assignment operator :=. So if you are using a bind variable in combination with other variables you should think about the order.

Note: Inline hints can only be applied to function and procedure parameters. To control the directional hint outside functions and procedures you choose order of assignments and use local variables as shown in Figure 4.

DECLARE
    price NUMBER;
BEGIN
    -- In this first statement the bind variable will be recognized as a assignment and its variable direction is defaulted to Input.
    salesPrice := :i_hWndFrame.frmSales.dfnTax * price;

    -- In these examples where the bind variable is not placed directly after the assignment operator the framework will not recognize 
    -- the assignment and default to InputOutput.
    salesPrice := price * :i_hWndFrame.frmSales.dfnTax;

    salesPrice := (:i_hWndFrame.frmSales.dfnTax * (price - rebate ));

    -- In a case where you have two bind variables only the first will be recognized. So for this example the tax bind will default to 
    -- Input while the price bind variable default to InputOutput
    salesPrice := :i_hWndFrame.frmSales.dfnTax * :i_hWndFrame.frmSales.dfnPrice;

    -- Fix above problem by using local variable 
    price := :i_hWndFrame.frmSales.dfnPrice;
    salesPrice := :i_hWndFrame.frmSales.dfnTax * price;
END;

 Figure 4: Examples of variable directions outside procedure and function calls.

Note: In source code that was ported using IcePorter another hint concept is used, called Signature hints or SQL hints. This concept was optimized for an automated porting procedure and is less efficient for manual development. For manual development it is strongly recommended that inline parameter direction hints are used. In a situation where an existing Signature hint needs to be altered because of new development it is recommended to rewrite the whole statement to make use of inline hints. This will enhance readability.

Make a database lookup

There are methods in the framework that can be used to fetch data from the database to the client. To execute an SQL statement to fetch data you use the method DbImmediate or DbPrepareAndExecute in the window class. Only the first row returned by the select statement is fetched by the method DbImmediate. Use DbPrepareAndExecute and DbFetchNext when you want to process several rows from a select statement. To execute multiple PL/SQL function calls or both SQL select statements and function calls to fetch data, you use the method DbPLSQLBlock in the window class. 

The method DbImmediate takes an SQL statement as parameter. The SQL statement used as parameter in method DbImmediate is using the SQL keyword INTO to fetch data into bind variables. Example:

this.DbImmediate("SELECT part_no, serial_no, superior_part_no, superior_serial_no, latest_transaction," +
    " &AO.Part_Catalog_API.Get_Description(PART_NO) INTO" +
    " :i_hWndFrame.frmSerialStructureTree.sPartNo, :i_hWndFrame.frmSerialStructureTree.sSerialNo," +
    " :i_hWndFrame.frmSerialStructureTree.sSuperiorPartNo, :i_hWndFrame.frmSerialStructureTree.sSuperiorSerialNo," +
    " :i_hWndFrame.frmSerialStructureTree.sLatestTransaction," +
    " :i_hWndFrame.frmSerialStructureTree.sPartDescription" +
    " FROM" +
    " &AO.PART_SERIAL_CATALOG" +
    " WHERE" +
    " PART_NO = :i_hWndFrame.frmSerialStructureTree.sParentPartNo" +
    " AND SERIAL_NO = :i_hWndFrame.frmSerialStructureTree.sParentSerialNo");

Figure 3: Example of a DbImmediate call

The methods DbPrepareAndExecute and DbFetchNext are used to process several rows returned from a select statement. The SQL statement used as parameter in method DbPrepareAndExecute is using the SQL keyword INTO to fetch data into bind variables. Example:

sStmt = "SELECT language_code, &AO.Iso_Language_API.Decode(language_code) description into " + 
    sFullName + ".sTemplateLng, " + 
    sFullName + ".sTemplateLngDesc" +
    " FROM &AO.Company_Translation_Lng" +
    " WHERE key_name = 'CompanyKeyLu'" +
    " AND key_value = " + sFullName + ".dfCompany" +
    " group by language_code" +
    " order by 2";

DbPrepareAndExecute(sStmt);
while (DbFetchNext(ref nInd)) 
{
    sExistingLngs[i] = sTemplateLng;
    sExistingLngDescs[i] = sTemplateLngDesc;
    i++;
}

Figure 4: Example of processing several rows using DbPrepareAndExecute and DbFetchNext

The method DbPLSQLBlock executes a complete anonymous PL/SQL block in a single call to the server. The PL/SQL block to execute can consist of one or several PL/SQL statements. When executing several statements you should use one call to DbPLSQLBlock instead of multiple calls to DbPLSQLBlock and DbImmediate. Minimizing server calls will improve the performance of the application.

this.DbPLSQLBlock(cSessionManager.c_hSql, " BEGIN" +
" :i_hWndFrame.frmPartCatalog.sSerialTrack := &AO.PART_SERIAL_TRACKING_API.Get_Db_Value(0);" +
" :i_hWndFrame.frmPartCatalog.sNoSerialTrack := &AO.PART_SERIAL_TRACKING_API.Get_Db_Value(1);" +
" :i_hWndFrame.frmPartCatalog.sSerialRule := &AO.PART_SERIAL_RULE_API.Get_Client_Value(0);" +
" :i_hWndFrame.frmPartCatalog.sConfigured := &AO.PART_CONFIGURATION_API.Get_Db_Value(0);" +
" :i_hWndFrame.frmPartCatalog.sNotConfigured := &AO.PART_CONFIGURATION_API.Get_Db_Value(1);" +
" :i_hWndFrame.frmPartCatalog.sAlowCondCode := &AO.CONDITION_CODE_USAGE_API.Get_Db_Value(0);" +
" :i_hWndFrame.frmPartCatalog.sNotAllowCondCode := &AO.CONDITION_CODE_USAGE_API.Get_Db_Value(1);" +
" :i_hWndFrame.frmPartCatalog.sTrackingOn := &AO.MULTILEVEL_TRACKING_API.Get_Db_Value(0);" +
" :i_hWndFrame.frmPartCatalog.sTrackingOff := &AO.MULTILEVEL_TRACKING_API.Get_Db_Value(1);" +
" :i_hWndFrame.frmPartCatalog.sNotLotTracking := &AO.Part_Lot_Tracking_API.Decode(" + "'NOT LOT TRACKING');" +
" END;");
  

Figure 5: Example of executing several PL/SQL function calls in one server call using DbPLSQLBlock

Make a database transaction

To call one or more PL/SQL procedures that make database transactions you can use the method DbPLSQLTransaction.

DbPLSQLTransaction(cSessionManager.c_hSql, 
" &AO.Create_Company_Log_API.Add_To_Imp_Table__( :i_hWndFrame.dlgCreateCompany.dfsNewCompanyId);" +
" &AO.Create_Company_Log_API.Update_Log_Tab_To_Comments__( :i_hWndFrame.dlgCreateCompany.dfsNewCompanyId);");

Figure 6: Example of calls to multiple PL/SQL procedures in one transaction using DbPLSQLTransaction

When you want to call the same procedure for multiple records in the client you first start a transaction by calling the method DbTransactionBegin. Then you make your transactions with method DbPLSQLBlock. Finally you call DbTransactionEnd to end the transaction. If you do not want the transaction to be committed you call the method DbTransactionClear.

lsStmt = "&AO.Accounting_Period_API.Open_Period(" +
" :i_hWndFrame.frmAccPer.tblAccPerDetail.__colObjid," +
" :i_hWndFrame.frmAccPer.tblAccPerDetail.__colObjversion)";

nCurrentRow = Sys.TBL_MinRow;
nOldRow = Sal.TblQueryContext(this);

DbTransactionBegin(ref hSql);

while (Sal.TblFindNextRow(this, ref nCurrentRow, Sys.ROW_Selected, 0)) 
{
    Sal.TblSetContext(this, nCurrentRow);
    if (!(DbPLSQLBlock(cSessionManager.c_hSql, lsStmt))) 
    {
        DbTransactionClear(cSessionManager.c_hSql);
        if (p_sAction == "CLOSE_FIN") 
        {
            Sal.TblSetContext(this, nOldRow);
        }
        else
        {
            Sal.SendMsg(Sys.hWndForm, Ifs.Fnd.ApplicationForms.Const.PM_DataSourcePopulate, 
                Ifs.Fnd.ApplicationForms.Const.METHOD_Execute, 0);
        }
        Sal.WaitCursor(false);
        return false;
    }
    DataRecordRefresh(Ifs.Fnd.ApplicationForms.Const.METHOD_Execute);
}

DbTransactionEnd(cSessionManager.c_hSql);
Sal.TblSetContext(this, nOldRow);

Figure 7: Example of using DbTransactionBegin, DbTransactionEnd and DbTransactionClear to execute a PL/SQL procedure on several records

Note: You can read more about transactions at Transactions.