Introduction to the Binding method (Binding Style) in Oracle EBS OAF (ADF) VO (View Object) Development ____oracle

Introduction to the Binding method (Binding Style) in Oracle EBS OAF VO (View Object) Development

Wei Hongsheng

In the development of oaf Vo, Binding style is primarily used to dynamically transfer values to VO's WHERE clause, which has a total of three ways

1. Oracle Named

2. Oracle positional

3. JDBC Positional

Oracle Positional

Oracle positional is one of the most common ways to introduce Oracle documentation

This style represents parameters as colons followed by numbers, as in the following code fragment:where Bar =: 1 and foo = : 2 in this style, the numbers are just for easier readability:parameters are passed to the View object in which the numbers occur. The above fragment, for example, are equivalent to WHERE bar =: and Foo =: 3

In simple terms, the arguments in this way are declared with ': ' and a number, for example: WHERE bar =: 1 and foo =: 2. In this way, numbers are used only to increase readability, and parameters are passed into the View object only in the order in which the numbers appear in VO SQL statement, not in the size of the numbers. For example, the above code fragment is the same effect as the following one where bar =: 3, described below in the implementation.

How to use the Oracle positional binding method in Vo

First, we need to set up on the VO definition interface,

1. Binding Style set to Oracle positional

2. Use ': ' Plus numbers as parameters in the WHERE clause in Vo SQL Statement, for example: 1.

After the VO has been set up, we need to execute the VO query using vo.setwhereclauseparam (int index, Object value) to assign values to VO's binding parameters, such as the following code.

public void Initshiptolocation (String org_id) { Rcvshiptolocationvoimpl vo =this.getrcvshiptolocationvo1 (); Vo.setwhereclause (NULL); Vo.setwhereclauseparam (0, new number (Integer.parseint (org_id))); }

 

 

issues to be aware of using Oracle positional bindings

1. When using Vo.setwhereclauseparam (int index, Object value) to assign value, index must start from 0, then 1,2,3, ... However, in VO SQL statement, the parameter does not have to start at 0, and the value of the subsequent argument does not have to be larger than the preceding. For example, the following is also possible,

However, you must use 0 and 1 as index when using Vo.setwhereclauseparam () to bind parameter values to VO, and the index must be the same type as the binding parameter in the order in VO SQL statement, for example

public void Initshiptolocation (String org_id) { Rcvshiptolocationvoimpl vo =this.getrcvshiptolocationvo1 (); Vo.setwhereclause (NULL); Vo.setwhereclauseparam (0, new number (Integer.parseint (org_id))); Vo.setwhereclauseparam (1, "Hello"); }

If any other index is used, the following exception is thrown (Invalid column index)

Oracle.apps.fnd.framework.oaexception:oracle.jbo.sqlstmtexception:jbo-27122:invalid column index .....

2. If you use the number as a binding parameter in VO SQL statement, and you use Vo.setwhereclauseparam () when setting the binding value for VO, make sure that the way Vo is bound (Binding Style) is Orac Le positional, otherwise the following exception may appear (invalid column type)

Oracle.apps.fnd.framework.oaexception:oracle.jbo.sqlstmtexception:jbo-27122:invalid column type .....

3. If VO SQL statement two or more binding parameters must be the same value passed in each time, in VO SQL statement although you can use the same number as the binding parameter, but when you use Vo.setwhereclauseparam () to set the bound value You also set values separately, and it is recommended that you do not use the same number as the binding parameter.

public void Initshiptolocation (String org_id) { Rcvshiptolocationvoimpl vo =this.getrcvshiptolocationvo1 (); Vo.setwhereclause (NULL); Vo.setwhereclauseparam (1, "Hello"); Vo.setwhereclauseparam (2, "Hello"); Vo.setwhereclauseparam (0, new number (Integer.parseint (org_id))); }

Otherwise VO will report the following error when executing the query

Java.sql.SQLException: Missing in or out parameter in index:: 8

Oracle Named

Oracle Named binding is the default in VO definition and is a very common way to introduce Oracle documentation as

This style represents parameters as colons followed by identifiers, as in the following code fragment:where Bar =: Barpara M and foo =: Fooparam Unlike the positional styles, the names of the parameters are actually the used when the application fil ls the parameter values.

As the name implies, the Oracle named Way is ': ' and the name of the way to represent parameters, not like Oracle Positional with numbers. The advantage of this approach is that the same parameter can be used with the same name, and the parameters of the same name can be initialized only once for VO initialization parameters, which is described in the implementation below.

How to use the Oracle named Binding method in Vo

First, we need to set up on the VO definition interface,

1. Define the name, type, and default value of the binding parameters to be used in VO SQL statement on the Bind Variable page.

2. Set binding style to Oracle Named on the VO SQL statement page.

3. In the WHERE clause in VO SQL Statement use ': ' plus the bind variable defined in bind variable as parameters, such as: inv_org_id, and we can use these parameters multiple times.

After the VO definition is set up, we need to perform a VO query using Vo.setnamedwhereclauseparam (String name, Object value) to assign values to the binding parameters of Vo, such as the following code.

public void Initrcvsubinventory (string orgid, String itemId, String receiptsourcecode) { Rcvsubinventorylovimpl vo =this.getrcvsubinventorylov1 (); Vo.setwhereclause (NULL); Vo.setwhereclauseparams (NULL); Vo.setnamedwhereclauseparam ("inv_org_id", OrgID); Vo.setnamedwhereclauseparam ("rcv_item_id", itemId); Vo.setnamedwhereclauseparam ("Receipt_source_code", Receiptsourcecode); }

issues to be aware of using Oracle Named bindings

1. Be sure to declare bind Variable when using Oracle named Bindings, and if it is used in VO SQL statement without a declaration, the following error occurs at run time.

(oracle.jbo.SQLStmtException) Jbo-27122:sql error during statement preparation.

# # Detail 0 # #

(java.sql.SQLException) Missing in or out parameter at index:: 1

2. Conversely, if bind Variable is defined but not used in VO SQL statement, or a defined type error, the following error is thrown at run time

Oracle.jbo.sqlstmtexception:jbo-27122:sql error during statement preparation.

# # Detail 0 # #

Java.sql.SQLException:Attempt to set a parameter name this does not occur in the

sql:inv_org_id

3. If you do not specify a default value when you define bind Variable, and the bind Variable is not initialized with Vo.setnamedwhereclauseparam () at run time, then bind Variable will use null values by default . But in general, NULL is not what the user needs, remember to set the default value or to specify a value for bind variable at run time.

JDBC Positional

This binding approach is not common in EBS OAF development, as described in the Oracle documentation

This style represents parameters as question marks, as in the following code fragment:where foo =? and bar =?. This should is used only if your are working with Non-oracle database.

Because EBS should not be using a non-Oracle database, it should not be used in OAF development, but it should be used in ADF development, but this is not within the scope of our discussion today.