Data type conversion between databases and pages in OAFFlexField

Welcome to the Oracle community forum and interact with 2 million technical staff. There is FlexField in OracleApplication. In FlexFieldtables, data is stored in one or more columns of the VARCHAR2 type. Whether it is a number, date, text, etc., it will eventually be stored in the table with VARCHAR2. In OAPage

Welcome to the Oracle community forum and interact with 2 million technical staff> There is FlexField in Oracle Application. In FlexField tables, data is stored in one or more columns of the VARCHAR2 type. Whether it is a number, date, text, etc., it will eventually be stored in the table with VARCHAR2. In the OA Page

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Oracle Application has FlexField (elastic field. In FlexField tables, data is stored in one or more columns of the VARCHAR2 type. Whether it is a number, date, text, etc., it will eventually be stored in the table with VARCHAR2. In the OA Page, VARCHAR2 generally needs to use the corresponding format, such as date and number, to use the corresponding verification mechanism, or convert to different formats according to different regions. The problem is, how do I convert the data type in the front and back consoles? One of the simplest ways is to use the FlexField Bean of OAF. However, in some cases, using FlexField Bean is not easy to control. Even though the method follows the OAF Developer Guide, this FlexField Bean is never used. At this moment, you can only do this manually.

The basic principle of data type conversion is simple: read/write splitting, or separate data streams in different directions in the front and back.

1. Traditional situations

In traditional cases, or in general, the column names of databases are actually meaningful and clear. For example, the column name is paitive_date, and the attribute names in EO and VO also use inclutivedate.

We will define an SQL statement in VO. During the operation, OAF encapsulates the SQL statement and then reads and writes it. For example:

[SQL]

SELECT ExampleEO. example_id AS EXAMPLE_ID

, ExampleEO. inclutive_date AS inclutive_date

, ExampleEO. employee_name AS EMPLOYEE_NAME

FROM ExampleEO

WHERE ExampleEO. example_id> 10

When you query records whose example_id = 100, OAF encapsulates them as follows:

[SQL]

SELECT *

FROM (SELECT ExampleEO. example_id AS EXAMPLE_ID

, ExampleEO. inclutive_date AS inclutive_date

, ExampleEO. employee_name AS EMPLOYEE_NAME

FROM ExampleEO

WHERE ExampleEO. example_id> 10) QRSLT

Where qrslt. EXAMPLE_ID = 100

The insert operation is similar:

[SQL]

UPDATE (SELECT ExampleEO. example_id AS EXAMPLE_ID

, ExampleEO. inclutive_date AS inclutive_date

, ExampleEO. employee_name AS EMPLOYEE_NAME

FROM ExampleEO

WHERE ExampleEO. example_id> 10) QRSLT

Set qrslt. interval tive_date = -- OK

, QRSLT. EMPLOYEE_NAME = -- OK

Where qrslt. EXAMPLE_ID = 100

In general, it is to encapsulate the SQL statement in VO into a table, and then wrap the corresponding operation outside.

2. FlexField Problems

In FlexField, the column name is meaningless or ambiguous. FlexField columns for storing data are generally ORG_INFORMATION1 and PER_INFORMATION5. In VO, these names may be replaced with actual purposes. For example, ORG_INFORMATION1 may be a valid Date, so the attribute in VO will be named 1_tivedate. this mapping is set in VO properties.

To simplify the column name, we call ORG_INFORMATION1 and PER_ATTRIBUTE1 information1. therefore, in a FlexField table, the VO statement will look like this:

[SQL]

SELECT ExampleEO. example_id AS EXAMPLE_ID

, ExampleEO. information1 AS inclutive_date

, ExampleEO. information2 AS EMPLOYEE_NAME

FROM ExampleEO

WHERE ExampleEO. example_id> 10

To query records whose example_id is 100, the following statement is generated:

[SQL]

SELECT *

FROM (SELECT ExampleEO. example_id AS EXAMPLE_ID

, ExampleEO. information1 AS inclutive_date -- information1 is a VARCHAR2

, ExampleEO. information2 AS EMPLOYEE_NAME

FROM ExampleEO

WHERE ExampleEO. example_id> 10) QRSLT

Where qrslt. EXAMPLE_ID = 100

The query statement is correct. However, in the OA Page, the type of the item displaying the DATE is generally set to DATE, and the type of the query result of the preceding SQL statement is VARCHAR2. therefore, no matter how you set the attribute types of VO and PG, an error will be reported at the top of the page. This is Question 1.

[1] [2]