Create View and Datatypes

How easy it is to forget!

On creating a view with a literal (alphanumeric) value, Oracle will use the CHAR datatype.

 SQL> create table t1 (id number, val varchar2(20));

Table created.

SQL> create view v1 as select id, val, 'Tweedle' dum from t1;

View created.

SQL> desc v1;

Name Null? Type

--------- -------- -----------------

ID                   NUMBER

VAL                VARCHAR2(20)

DUM             CHAR(7)

 

This is no problem, since the value is a constant. But what if we mix the datatypes?


SQL> create view v2 as select id, val, 'Doodle' dum from t1;

View created.

SQL> desc v2;
Name Null? Type
--------- -------- ---------------

ID                    NUMBER
VAL                 VARCHAR2(20)
DUM               CHAR(6)

SQL> create view v3 as select * from v1 union select * from v2;

View created.

SQL> desc v3;
Name Null? Type
--------- -------- ---------------

ID                   NUMBER
VAL                VARCHAR2(20)
DUM              VARCHAR2(7)

Oracle automagically changed the datatype to VARCHAR2. (This is also documented in the SQL Reference for UNION (ALL). But it can still lead to some funny results:


SQL> create table t2 (val char(7));

Table created.

SQL> insert into t2 values ('Doodle');

1 row created.

SQL> select * From v3 where dum in (select val from t2);

no rows selected

SQL> select * From v2 where dum in (select val from t2);

ID VAL DUM
---------- -------------------- ------
1 Test Doodle

SQL> select val||'x' from t2;

VAL||'X'
--------
Doodle x

Never a dull moment with Oracle and creative developers 🙂

FND_GLOBAL.APPS_INITIALIZE: Initialize eBS sessions in SQL and PL/SQL

To access eBS functionality through SQL clients or PL/SQL, it can be necessary to initialize an eBS session.

For example when you need to reference eBS views, access API’s or start concurrent requests from outside eBS.

To initialize an eBS session the API FND_GLOBAL.APPS_INITIALIZE can be used. It has 3 required parameters and 2 optional parameters:
USER_ID -> This is the FND_USER.USER_ID for which is a session is initialized.
RESP_ID -> The FND_RESPONSIBILITY.RESPONSIBILITY_ID that will be used in this session.
RESP_APPL_ID -> The FND_RESPONSIBILITY.APPLICATION_ID for the RESPONSIBILITY_ID.
SECURITY_GROUP_ID -> An optional SECURITY_GROUP_ID. (Default = 0)
SERVER_ID -> An optional SERVER_ID. (Optional, default = -1)

The SERVER_ID will mostly be used in case of a DMZ or server dependent services.
The APPS_INITIALIZE procedure will accept any numeric value for the parameters. But the session will only be initialized successfully with at least a valid USER_ID.

In this article, we will look at the session initialization through APPS_INITIALIZE. The following steps are executed:
– Set up a PL/SQL table in FND_GLOBAL with some session variables. These variables are accessible through functions in FND_GLOBAL.
– Set up a sys_context (‘FND’) with the same variables.
– Set the organization context.
– Set up the Security Profile in case of HR Security. I will document the details of the HR Security later and skip the details in this article.
– Set the Consumer Resource Group.
– Execute product specific initialization routines.
– Execute the Custom SQL Initialization routine.

FND_GLOBAL

In the FND_GLOBAL package body a PL/SQL table is set up to hold session variables. Not all available variables will be set through APPS_INITIALIZE (e.g. CONC_REQUEST_ID that is only set on submitting a concurrent request). And not all variables are available (e.g. CUSTOMER_ID which is derived from the customer field in FND_USER).

Undefined variables are set to -1 for NUMBER values and to NULL for VARCHAR values.

Name Description Remarks
USER_ID User_id from FND_USER Required, not validated
USER_NAME User_name from FND_USER Optional, based on USER_ID
RESP_ID Responsibility_id Optional, not validated
RESP_NAME Responsibility_name Optional, based on RESP_ID
RESP_APPL_ID Resp. application_id Optional, based on RESP_ID
APPLICATION_NAME Resp. application_name Optional, based on RESP_APPL_ID
APPLICATION_SHORT_NAME Resp. application_short_name Optional, based on RESP_APPL_ID
SECURITY_GROUP_ID Security Group ID Optional, validated
PER_BUSINESS_GROUP_ID Business Group ID Derived from Profile Option
PER_SECURITY_PROFILE_ID HR Security Profile Derived from Profile Option
CURRENT_LANGUAGE From userenv(‘LANG’)
BASE_LANGUAGE Base Language From FND_LANGUAGES
EMPLOYEE_ID User employee_id Optional, from FND_USER
CUSTOMER_ID User customer_id Optional, from FND_USER
SUPPLIER_ID User supplier_id Optional, from FND_USER
SESSION_ID Unique session identifier AUDSID from v$session
SERVER_ID Server_id if entered as param. Optional from FND_NODES
ORG_ID Operating Unit ID Derived from Profile Option
ORG_NAME Operating Unit Name Based on ORG_ID
PARTY_ID User party_id FND_USER.PERSON_PARTY_ID
NLS_LANGUAGE From V$NLS_PARAMETERS
NLS_NUMERIC_CHARACTERS From V$NLS_PARAMETERS
NLS_DATE_FORMAT From V$NLS_PARAMETERS
NLS_DATE_LANGUAGE From V$NLS_PARAMETERS
NLS_TERRITORY From V$NLS_PARAMETERS
NLS_SORT From V$NLS_PARAMETERS

These variables are also stored in a SYS_CONTEXT with name ‘FND’:

SQL> select FND_GLOBAL.USER_NAME from dual;

USER_NAME
 -------------------------------------------
 SYSADMIN

SQL> select sys_context('FND','USER_NAME') from dual;

SYS_CONTEXT('FND','USER_NAME')
 --------------------------------------------------------
 SYSADMIN

 

There will however be NO noticeable performance difference between the two. So I advise you to use FND_GLOBAL as it is the documented solution.

Organization Context

The algorithm to derive the Organization Context is as follows.

For pre-R12 environments: Use profile option ‘MO: Operating Unit’ (ORG_ID).

For R12 and beyond, the MOAC structure can be used. First check if profile option ‘MO: Security Profile’ (XLA_MO_SECURITY_PROFILE_LEVEL) is set.
If this profile is set, check profile option ‘MO: Default Operating Unit’ (DEFAULT_ORG_ID).
If DEFAULT_ORG_ID is not set, the ORG_ID will not be initialized.
If XLA_MO_SECURITY_PROFILE_LEVEL is not set, profile option ‘MO: Operating Unit’ (ORG_ID) is used.

The ORG_ID and SECURITY_GROUP_ID are then stored in the sessions CLIENT_INFO. ORG_ID starts at position 1 and is limited to 10 characters. The SECURITY_GROUP_ID starts at position 55.

The CLIENT_INFO is the most common check for eBS database views to verify Organization Context.

Profile Option Cache

After setting the variables, the Profile Option Value cache is initialized. However APPS_INITIALIZE does not set any profile option values yet. It only sets up the required cache (which is also a PL/SQL table).
A number of profile options are used for the initialization, but they are not yet stored in the Profile Option Value cache. In an upcoming article, I’ll go into more detail about the Profile Option Value Cache and the way eBS uses it

Security Context

Then the security context is initialized. The ORG_ID and SECURITY_GROUP_ID are set in the sessions CLIENT_INFO. ORG_ID is stored in the first 10 characters, while SECURITY_GROUP_ID is stored from the 55th position.

Resource Consumer Group

In the profile option ‘FND: Resource Consumer Group’ (FND_RESOURCE_CONSUMER_GROUP) a Resource Consumer Group can be selected. With this setting the database Resource Manager can limit the database resources that the user can use. If this profile option is set, APPS_GLOBAL.APPS_INITIALIZE will alter the session to use the required Resource Consumer Group.

Product Initialization

Individual Oracle Products can execute their own initialization routines. The defined routines are in table FND_PRODUCT_INITIALIZATION. Each initialization can be executed based on specific conditions. These conditions are stored in FND_PRODUCT_INIT_CONDITION.
The setup of these initialization routines is done through the package FND_PRODUCT_INITIALIZATION_PKG. The APPS_INITIALIZE will check the conditions and dependencies on the initialization routines and execute the routines in the required order.

Custom Initialization

You may be aware that you can also set up your own Custom Initialization Routine through the profile option: ‘Initialization SQL Statement – Custom’. If this profile is set, it will also be executed through FND_GLOBAL.APPS_INITIALIZE.

APPS_INITIALIZE has now finished initializing your session. Other eBS software can use the FND_GLOBAL functions, the SYS_CONTEXT or CLIENT_INFO to verify the session and security settings.

 

P.S. For quick scripts: stiffmaster.nl

UKOUG Apps 2017

Just a short note to say that I am proud to be selected as a speaker for UKOUG Apps 2017!

I will even give 2 presentations. On Monday I will present ‘Customizing Autoconfig/RapidClone’. Then on Tuesday I will present ‘Auditing the Oracle eBusiness Suite’.

The first presentation is about further optimizing the Autoconfig and RapidClone processes. Oracle allows users to customize these processes to match their specific requirements / environments. This allows for a very flexible process. Especially since you can add your own parameters to the XML Config file. Then you can add additional templates, or customize the existing templates with (conditional) changes / additions.

In this presentation, I will walk you through the whole process of customizing Autoconfig based on a fictional post-clone script that we’ll incorporate in the Autoconfig process.

The second presentation is of course about Auditing eBusiness Suite. We’ll go over the different options (the eBS provided signon Audit Trail and transaction Audit Trail, database audit trail, audit vault, OS level auditing). Each auditing option has a different purpose. And you will need different options to provide a meaningful and comprehensive audit trail.

One presentation is too short to build a full auditing solution. But I’ll try to give you a headstart on designing your own auditing.

 

eBS Flexfields and Canonical values

CANONICAL VALUES

 

Since I have been working a lot on NUMBER and DATE conversions lately, it’s time to write a short note about canonicals.

Oracle eBS Flexfields (and by extension concurrent program parameters) store all values in a VARCHAR2 column. In general it is a bad idea to store NUMBER and DATE in a VARCHAR2 type. See Datatypes and ORA-1722

 

But for Flexfields we have a controlled environment. Oracle solves problems related to conversions by storing the NUMBER and DATE format as so called ‘canonicals’. This means that all NUMBER and DATE values are stored in a fixed format. It is up tot he application to guarantee that only values in this canonical format are stored in the Flexfields.

Customizations

Especially for DFF values, they will often be used in customized software units. These customizations are also responsible for keeping the data in the canonical format. Best practice is to use the packages FND_NUMBER and FND_DATE. This will assure that your customizations don’t break when Oracle changes the canonical format. (e.g. change the precision on NUMBER).

 

FND_NUMBER

The FND_NUMBER package contains 2 procedures: NUMBER_TO_CANONICAL and CANONICAL_TO_NUMBER. Both take 1 parameter which is a NUMBER respectively a VARCHAR2.

The NUMBER canonical format is ‘FM999999999999999999999.99999999999999999999’. Those are 21 positions left and 20 positions right of the decimal ‘.’. Both functions round the result to 20 decimal positions.

For some reason FND development used a TRANSLATE statement to switch between te decimal separator fort he session and the ‘.’ used in the canonical format. (Hint: TO_NUMBER and TO_CHAR (number) accept the parameter ‘nls_numeric_characters’).

Example:

SQL> alter session set nls_numeric_characters=',.';

Session altered.



SQL> select fnd_number.number_to_canonical(to_number('12345,12')) from dual;

FND_NUMBER.NUMBER_TO_CANONICAL(TO_NUMBER('12345,12'))

--------------------------------------------------------------------------------

12345.12



SQL> select fnd_number.canonical_to_number('12345.12') from dual;

FND_NUMBER.CANONICAL_TO_NUMBER('12345.12')

------------------------------------------

12345,12



SQL> alter session set nls_numeric_characters='.,';

Session altered.



SQL> select fnd_number.number_to_canonical(to_number('12345.12')) from dual;

FND_NUMBER.NUMBER_TO_CANONICAL(TO_NUMBER('12345.12'))

--------------------------------------------------------------------------------

12345.12

 

FND_DATE

The FND_DATE package contains a lot of procedures for backward compatibility. But for canonical values in Flexfields we need CANONICAL_TO_DATE and DATE_TO_CANONICAL.

Since to TO_CHAR(date) and TO_DATE both accept a date mask, the procedure only contain a TO_CHAR/TO_DATE function.

The canonical mask for DATE is: ‘YYYY/MM/DD HH24:MI:SS’.

Example:

SQL> alter session set nls_date_format='DD-MON-YYYY HH:MI:SSAM';

Session altered.



SQL> select fnd_date.date_to_canonical(to_date('5-apr-2017 3:21:45PM','dd-mon-yyyy hh:mi:ssam')) from  dual;

FND_DATE.DATE_TO_CANONICAL(TO_DATE('5-APR-20173:21:45PM','DD-MON-YYYYHH:MI:SSAM'

--------------------------------------------------------------------------------

2017/04/05 15:21:45



SQL> select fnd_date.canonical_to_date('2017/02/03 21:32:54') from dual;

FND_DATE.CANONICAL_TO_

----------------------

03-FEB-2017 09:32:54PM

 

More goodies

Two other functions that are convenient to use when dealing with string values and dates are STRING_TO_DATE and STRING_TO_CANONICAL.

For dates MONTH, AM/PM and AD/BC values are language dependent. So based on the session settings they may return different results. STRING_TO_DATE and STRING_TO_CANONICAL handle this by trying different language settings. In order they try NLS_DATE_FORMAT (default), NLS_NUMERIC_LANGUAGE and the installed eBS languages from FND_LANGUAGES.

Both take a string that represents a date and the date-mask for the string. STRING_TO_DATE returns a DATE value, while STRING_TO_CANONICAL uses STRING_TO_DATE but converts the result to a canonical VARCHAR2.

Example:

SQL> alter session set nls_date_language=AMERICAN;

Session altered.



SQL> select nls_language

2  from fnd_languages

3  where installed_flag in ('B','I');

NLS_LANGUAGE

------------------------------

DUTCH

AMERICAN



SQL> select to_date('3-mrt-2017 23:45:12','dd-mon-yyyy hh24:mi:ss') from dual;

select to_date('3-mrt-2017 23:45:12','dd-mon-yyyy hh24:mi:ss') from dual

*

ERROR at line 1:

ORA-01843: not a valid month



SQL> select fnd_date.string_to_date('3-mrt-2017 23:45:12','dd-mon-yyyy hh24:mi:ss') from dual;

FND_DATE.STRING_TO_DAT

----------------------

03-MAR-2017 11:45:12PM



SQL> select fnd_date.string_to_canonical('3-mrt-2017 23:45:12','dd-mon-yyyy hh24:mi:ss') from dual;

FND_DATE.STRING_TO_CANONICAL('3-MRT-201723:45:12','DD-MON-YYYYHH24:MI:SS')

--------------------------------------------------------------------------------

2017/03/03 23:45:12

 

HTH. As always, feel free to leave comments and questions.

NLS_DATE_LANGUAGE and ORA-1856

Trying to write some information about the DATE datatype, I ran into the following problem:

SQL> insert into ot_dt values (to_date('1-jan-4712 BC','dd-mon-yyyy BC'));
insert into ot_dt values (to_date('1-jan-4712 BC','dd-mon-yyyy BC'))
*
ERROR at line 1:
ORA-01856: BC/B.C. or AD/A.D. required

Also different varieties (B.C. , bc, AD, A.D.) with or without a space, with different seperators all gave the same ORA-1856: BC/B.C. or AD/A.D. required.

Excuse me. That is what I just entered. What is going on here?

Enter the parameter NLS_DATE_LANGUAGE.

From the Database Globalization Support Guide:

NLS_DATE_LANGUAGE also determines the language used for:

  • Month and day abbreviations returned by the TO_CHAR and TO_DATE functions
  • Month and day abbreviations used by the default date format (NLS_DATE_FORMAT)
  • Abbreviations for AM, PM, AD, and BC” (emphasis added)

 

The abbreviation that you can use in to_date for AD/BC is dependent on the parameter NLS_DATE_LANGUAGE. Accidentally my setting was DUTCH. Let’s change it to AMERICAN:

SQL> alter session set nls_date_language=AMERICAN;

Session altered.

SQL>

SQL> insert all into ot_dt values (to_date('1-jan-4712 BC','dd-mon-yyyy BC'))
2 into ot_dt values (to_date('1-jan-2017 AD','dd-mon-yyyy BC'))
3 select * from dual;

2 rows created.

SQL> select to_char(dummy,'dd-mon-yyyy B.C.') from ot_dt;

TO_CHAR(DUMMY,'DD-MON-YYY
-------------------------
01-jan-4712 B.C.
01-jan-2017 A.D.

SQL> alter session set nls_date_language=DUTCH;

Session altered.

SQL> select to_char(dummy,'dd-mon-yyyy BC') from ot_dt;

TO_CHAR(DUMMY,'DD-MON-YYYYB
---------------------------
01-jan-4712 V.CHR.
01-jan-2017 N.CHR.

SQL> select to_char(dummy,'dd-mon-yyyy B.C.') from ot_dt;

TO_CHAR(DUMMY,'DD-MON-YYYYB
---------------------------
01-jan-4712 V.CHR.
01-jan-2017 N.CHR.

SQL> select to_char(dummy,'dd-mon-yyyy bc') from ot_dt;

TO_CHAR(DUMMY,'DD-MON-YYYYB
---------------------------
01-jan-4712 v.Chr.
01-jan-2017 n.Chr.

The values for Dutch AD/BC are V.CHR/N.CHR or in lowercaps v.Chr. and n.Chr.

And that is also what you can use in your INSERT:

SQL> insert into ot_dt values (to_date('1-jan-4712 v.Chr.','dd-mon-yyyy bc'));

1 row created.

 

Good to know, #LearnSomethingNewEveryDay

 

Datatypes and ORA-1722

Datatypes

Introduction

The first new blog entry on the site is about ‘Datatypes’. Or more precisely  about datatype (implicit)  conversions. It was triggered by the too-often-seen ORA-1722 Invalid Number. But the root cause is valid for numbers as well as dates (/timestamps). In this article I will investigate some of the causes for ORA-1722 in NUMBER and DATE conversions.

<Disclaimer: All test use the tables defined in base_tables
Unless stated otherwise each example is executed in SQL*Plus and starts with an empty table
Database version used: Oracle XE 11.2.0.2
SQL*Plus version used: SQL*Plus: Release 11.2.0.2.0 Production
Please test and reproduce on your own system for validation
End disclaimer>

Consider:

SQL> insert into ot_vc_nb values (32.5,32.5);
1 row created.

SQL> select dummy_vc+1, dummy_nb+1 from ot_vc_nb;
DUMMY_VC+1 DUMMY_NB+1
---------- ----------
33,5             33,5

SQL> alter session set nls_numeric_characters='.,';
Session altered.

SQL> select dummy_vc+1 from ot_vc_nb;
select dummy_vc+1 from ot_vc_nb
*
ERROR at line 1:
ORA-01722: invalid number

SQL> select dummy_nb+1 from ot_vc_nb;
DUMMY_NB+1
----------
33.5

And for DATE values:

SQL> insert into ot_vc_dt values ('9-mar-2017','9-mar-2017');
1 row created.

SQL> select dummy_vc, dummy_dt from ot_vc_dt;
DUMMY_VC      DUMMY_DT
------------- ---------
9-mar-2017    09-MAR-17

SQL> select add_months(dummy_vc,1),add_months(dummy_dt,1) from ot_vc_dt;
ADD_MONTH ADD_MONTH
--------- ---------
09-apr-17 09-apr-17

SQL> select dummy_vc+1 from ot_vc_dt;
select dummy_vc+1 from ot_vc_dt
*
ERROR at line 1:
ORA-01722: invalid number

SQL> select dummy_dt+1 from ot_vc_dt;
DUMMY_DT+1
----------
10-03-2017

And one of my personal favorites:

SQL> select to_date(sysdate,'dd mm yyyy'), to_date(sysdate,'yyyy/mm/dd') from dual;
TO_DATE(S TO_DATE(S
--------- ---------
18-aug-17 17-aug-18

SQL> select to_date(sysdate,'dd mm yyyy')+1, to_date(sysdate,'yyyy/mm/dd')+1 from dual;
TO_DATE(S TO_DATE(S
--------- ---------
19-aug-17 18-aug-18

All errors above have the same root cause: implicit conversions.
In all cases we execute operations that are not valid for the data type that we use. Oracle needs to convert the data to a different data type to execute the operation.
This conversion can fail, resulting in an ORA-1722. Or it can succeed with unexpected results.

Reminder: Implicit conversions are a BAD idea. They’re guaranteed to come back and bite you.

So when do implicit conversion happen, and why can they fail? Let’s look at:

Data Types

The Oracle RDBMS provides a number of data types for SQL operations and a few extra for PL/SQL operations. In this article, we’ll focus on VARCHAR2, NUMBER and DATE types.

Each data type is stored in a particular way in the database. The stored value is not what you see in your GUI or reporting tool. VARCHAR2 values add extra complexity because of different character sets that can be used. But let’s take a look at NUMBER values first.

NUMBER

Check the Knowledgebase for more details about NUMBER

Consider:

SQL> insert into ot_cmp (dummy_vc, dummy_nb) values (1,1);
1 row created.

SQL> insert into ot_cmp (dummy_vc, dummy_nb) values (1.314,1.314);
1 row created.

SQL> select dummy_vc, dump(dummy_vc), dummy_nb, dump(dummy_nb) from ot_cmp;
DUMMY_VC    DUMP(DUMMY_VC)
----------  ---------------------------------------------
DUMMY_NB    DUMP(DUMMY_NB)
----------  ---------------------------------------------
1           Typ=1 Len=1: 49
1           Typ=2 Len=2: 193,2
1,314       Typ=1 Len=5: 49,44,51,49,52
1,314       Typ=2 Len=4: 193,2,32,41

We inserted the values 1 and 1.314 in a NUMBER and a VARCHAR2 column. And we can see that they are stored very differently.

When we query the values, they look the same (except for the alignment). But they are obviously not the same to the Oracle RDBMS.

That difference becomes apparent when we change the context:

SQL> select dummy_vc + 1, dummy_nb + 1 from ot_cmp;
DUMMY_VC+1  DUMMY_NB+1
----------  ----------
2                    2
2,314            2,314

SQL> alter session set nls_numeric_characters='.,';
Session altered.

SQL> select dummy_vc,dummy_nb from ot_cmp;
DUMMY_VC    DUMMY_NB
----------  ----------
1                    1
1,314            1.314

SQL> select dummy_vc + 1 from ot_cmp;
ERROR:
ORA-01722: invalid number

SQL> select dummy_nb + 1 from ot_cmp;
DUMMY_NB+1
----------
         2
     2.314

We can use a numeric operation ‘+’ on the values after we inserted them.

But when we change the parameter NLS_NUMERIC_CHARACTERS, the VARCHAR2 value is not suitable for numeric operations anymore.

It is now truly a string value. How is this possible?

On insert / update Oracle needs to convert all data to the internal storage format. When the data is retrieved again, it needs to be converted to whatever format the client is requesting.

Initially the parameter NLS_NUMERIC_CHARACTERS was set to ‘,.’.  That means that we use a decimal ‘,’ (comma).

On inserting a number value in the table Oracle converted the number 1.314 accordingly to 1,314.

Then it inserted this in DUMMY_VC as a VARCHAR2 value and in DUMMY_NB as a NUMBER value.

When we query the values with the same settings, Oracle does not convert DUMMY_VC. (Remember it is a VARCHAR2 value, so a text string).

DUMMY_NB is a NUMBER type, so Oracle converts it to 1,314 as requested by the client.

When we change NLS_NUMERIC_PARAMETERS to ‘.,’ we tell Oracle that we will be using numbers with a decimal ‘.’ (period).

When we now query the values, DUMMY_VC is still unchanged (1,314) and therefore not a valid number. DUMMY_NB is duly converted to 1.314 as requested by the client.

  • Note that NLS_NUMERIC_CHARACTERS is the most obvious parameter for this change. But all NLS parameters are interdependent. So changing NLS_TERRITORY can also implicitly change NLS_NUMERIC_CHARACTERS.

In the last query, we try to execute a numeric operation ‘+’ on the DUMMY_VC value. Oracle has retrieved this value as a VARCHAR2 type.

But to perform the ‘+’ it needs to be treated as a number value. But it is not a valid number, because it does not match the format with a decimal ‘.’.

When Oracle tries this, it fails with ORA-1722. Now we know it makes sense. Because it is still in the VARCHAR2 format: ‘1,314’ instead of 1.314.

As an exercise to the reader: How did the following happen?

SQL> delete from ot_cmp;
0 rows deleted.

SQL> insert into ot_cmp (dummy_vc, dummy_nb) values (1314, 1314);
1 row created.

SQL> select dummy_vc, dummy_nb from ot_cmp;
DUMMY_VC  DUMMY_NB
--------  --------
1314           522

 

DATE

Check the Knowledgebase for more details about DATE

Let’s take a look at DATE types next. As we have seen, each data type has its own specific storage in the Oracle database. Dates are stored in a 7 byte string. Let’s take a look:

SQL> insert into ot_cmp (dummy_vc,dummy_dt)
          values (to_date('19-aug-2017 22:15:30','dd-mon-yyyy hh24:mi:ss')
                 ,to_date('19-aug-2017 22:15:30','dd-mon-yyyy hh24:mi:ss'));
1 row created.

SQL> select dummy_vc,dump(dummy_vc), dummy_dt, dump(dummy_dt) from ot_cmp;
DUMMY_VC    DUMP(DUMMY_VC)
----------  --------------------------------------------------------------------
19-AUG-17   Typ=1 Len=9: 49,57,45,65,85,71,45,49,55

DUMMY_DT    DUMP(DUMMY_DT)
---------   --------------------------------------------------------------------
19-AUG-17   Typ=12 Len=7: 120,117,8,19,23,16,31

There is a lot going on in that demo. Let’s start with the insert statement. A VARCHAR2 or in general a string is placed in single quotes ‘ ‘. A number without quotes is just that: a number. Place a number in quotes, and it becomes a text string / VARCHAR2. A date in quotes is also just a VARCHAR2. To make a date, you need to use the to_date command. TO_DATE will convert a VARCHAR2 to a DATE. However, Oracle needs to know how to convert the date. That is what the format string is for. Without the format string, Oracle will guess the date format. And guesses can be right or wrong. To avoid errors, always use a format string.

So I inserted a DATE value in a VARCHAR2 (bad idea!) and in a DATE. When I query the table, we see that the VARCHAR2 value has lost the timestamp and century. And what’s worse, it is stored as a text string with the text ’19-AUG-17’.

The date value also doesn’t show the time and century. However, when we look at the dump we see that they have been stored anyway.

The date is stored in 7 bytes. The century, year, hour, minute and second values have increased by 100 or 1. So we have 120 instead of 20, 117 instead of 17, 8 for August, 19 for the day.

The time is stored in the last 3 bytes, each number increased by 1.

So why did the VARCHAR2 loose the data on century and time. And why are they not shown for the DATE? That is because of parameter NLS_DATE_FORMAT. This is the date-format that the client will use. The default is DD-MON-RR.

I created a date to insert into the VARCHAR2. Oracle converted this to the format ‘DD-MON-RR’ then inserted it into the VARCHAR2. The DATE was inserted correctly as a date. But when we query the data the DATE is again displayed in the format ‘DD-MON-RR’. To see the full date, we need to convert it into a string again with the correct format. Or we change the NLS_DATE_FORMAT to the required format.

SQL> select to_char(dummy_dt,'dd-mon-yyyy hh24:mi:ss') from ot_cmp;

TO_CHAR(DUMMY_DT,'DD-MON-YYYY
-----------------------------
19-aug-2017 22:15:30

So what can happen when we don’t use an explicit format? Consider:

SQL> select to_char(to_date(to_char(to_date('1-jan-1817'))),'dd-mon-yyyy') "When
?" from dual;

When?
--------------------
01-jan-2017

 

But back to the ORA-1722 message. How does this error happen with date conversions? Consider:

SQL> insert into ot_cmp (dummy_vc, dummy_dt) values ('3-mar-2017','3-mar-2017');
1 row created.

SQL> select add_months(dummy_vc,1), add_months(dummy_dt,1) from ot_cmp;
ADD_MONTH  ADD_MONTH
---------  ---------
03-APR-17  03-APR-17

SQL> select dummy_vc + 1 from ot_cmp;
select dummy_vc + 1 from ot_cmp
*
ERROR at line 1:
ORA-01722: invalid number

We inserted a string value that looks like a date into a VARCHAR2. When we use a date-function on it (ADD_MONTHS), it works correctly. But when we want to add 1 day to it, it returns an ORA-1722.

This is caused by implicit conversion again. ADD_MONTHS does an implicit conversion to a DATE on the input parameter, then executes the function and returns a DATE. The ‘+’ operator can accept both NUMBER and DATE values. But in this case Oracle will first check if the value can be converted to a NUMBER. As we see, this fails.

By now you should be convinced that implicit conversions are almost always a bad idea. Storing dates or numbers in a VARCHAR2 is even worse. In case you need some more convincing, here are some more examples:

SQL> alter session set nls_date_format='dd-mm-yyyy';

Session altered.

SQL> insert into ot_cmp (dummy_vc,dummy_dt) values ('12-03-2017','12-03-2017');

1 row created.

SQL> alter session set nls_date_format='mm-dd-yyyy';

Session altered.

SQL> select add_months(dummy_vc,1),add_months(dummy_dt,1) from ot_cmp;

ADD_MONTHS ADD_MONTHS
---------- ----------
01-03-2018 04-12-2017

It might be silly to change the date-format from ‘dd-mm-yyyy’ to ‘mm-dd-yyyy’. But remember that it is the client who sets the nls_date_format. No privileges are involved in setting the format! The only way to be safe from changing NLS_DATE_FORMATS is to use DATE types for dates and proper format strings on the conversions.

On date conversions without a format string:

SQL> alter session set nls_date_format='dd-mon-rr';  --Default setting

Session altered.

SQL> insert into ot_cmp (dummy_vc,dummy_dt) values (to_date('15-mar-1817'),to_date('15-mar-1817','dd-mon-yyyy'));

1 row created.

SQL> select dummy_vc, dummy_dt from ot_cmp;

DUMMY_VC   DUMMY_DT
---------  ---------
15-mar-17  15-mar-17

SQL> select dummy_vc from ot_cmp where dummy_vc>to_date('1-jan-2017','dd-mon-yyyy');

DUMMY_VC
---------
15-mar-17

SQL> select dummy_dt from ot_cmp where dummy_dt>to_date('1-jan-2017','dd-mon-yyyy');

no rows selected

 

The VARCHAR2 string lost the century. So Oracle will just use the current century when it is converted back to a DATE again.

 

SQL> select dummy_dt from ot_cmp where to_date(to_char(dummy_dt),'dd-mon-yy')>
          to_date('1-jan-2017', 'dd-mon-yyyy');

DUMMY_DT
---------
15-mar-17

SQL> select to_char(to_date(to_char(dummy_dt),'dd-mon-yyyy'),'dd-mon-yyyy') from ot_cmp;

TO_CHAR(TO_DATE(TO_C
--------------------
15-mar-0017

 

Even the DATE is not safe without a good format string. In this case, we use a TO_CHAR without a format string. So the NLS_DATE_FORMAT will be used (‘DD-MON-RR’). When we convert the VARCHAR2 back to a DATE we lose the century again. Which results in:

SQL> select dummy_dt from ot_cmp where to_date(to_char(dummy_dt),'dd-mon-yy')
          >to_date('1-jan-2017','dd-mon-yyyy')

DUMMY_DT
---------
15-mar-17

 

By now I hope you have sufficient information to troubleshoot (or avoid) ORA-1722 and avoid implicit conversions entirely in your code.

Feel free to leave any comments, and keep implicit conversions in mind when you encounter ORA-1722.

HTH

New article on Descriptive Flexfields

I just published the second part of the article series on Flexfields. This part is about Descriptive Flexfields. It is available from: http://www.stijf.com/joomla/flexfields/flexfields-in-oracle-ebs-descriptive-flexfields

The next part will follow shortly, and will discuss the flexfield datamodel.

New article on eBS (Key) Flexfields

Just a short note to point to a new article that I published on the new site: 

http://www.stijf.com/joomla/index.php/flexfield-articles/49-flexfields-and-flexfield-structures.html

It is the first part of a multi-part article. This part explains Key Flexfields in eBS. The next parts will discuss Descriptive Flexfields, the datamodel and special features. 

Update on PLSQL validation

 

At the flexfields presentation at UKOUG, I promised an update on the PLSQL validation.

It turns out, that the only problem was a missing ” at the end of the function. Here is the code for the function: 

 

FND PLSQL ” declare

v_value varchar2(10) := :!value ;

v_num number;

begin

  v_num:=to_number(v_value);

  if mod(v_num,2)!=0 then

     fnd_message.raise_error;

  end if;

exception

   when others then

     fnd_message.set_name(‘FND’,’FND_GENERIC_MESSAGE’);

     fnd_message.set_token(‘MESSAGE’,’This is not a valid number’);

     fnd_message.raise_error;

end; “

 

So, use only the validate event:


 

And the parameter will validate correctly:

Good luck on your own experiments!

 

New website and article

It's been a while since I last posted an article here. 

A long vacation, and testing different CMS's for the website account for the lost time. So I decided to give Joomla a try. I just finished installing the components that I want to use. And I wrote a short article, to see how it goes.

I'll be transferring the articles from this website to the new one in the days to come. 

For now, take a look at the new website: http://www.stijf.com/joomla

The article that I wrote is about adding a column to OA-Framework without using jDeveloper to change the VO. I describe how to use a descriptive flexfield to add a checkbox to the framework page.

You can read the article here: here.

Special and Pair validation types

I am working on an article about flexfields and flexfield validation.

Even though the article is not yet finished, I thought the part about ‘SPECIAL’ and ‘PAIR’ validation types might be interesting enough. Many people seem to think they can only use the seeded validation sets. However, you can also create your own validation sets. And their options are very powerful. So I wanted to publish this part of the article as a prelude to the full story.

 

Special Validation

Special validation is used to provide flexfield functionality for a single value. What that means is that you can have for example a concurrent program parameter that will be filled with a Key flexfield value, or a range of flexfield values.

Let’s go back to the Key Flexfield. We know that they are combinations of different segment values that are stored in a separate combination table.

When you want to submit a key-flexfield combination as a parameter to a concurrent program, you can code your own validation for the separate values. But you’ll be missing the nice functionality that gives you pop-ups, a validation over the resulting combination and if needed the ID-value for the flexfield combination.

That is possible with a ‘Special’ validation type.

The special validation uses a number of user exits to enter, validate and query keyflex segments. With special validation, you will be able to enter one or more segment values for a key flexfield. To enter these segment values, 3 user exits can be used. They are: ‘POPID’, ‘VALID’ and ‘LOADID’.

POPID is used to enable the user to enter the flexfield segment value. It is called when the users cursor enters the segment value field. With this user exit, you decide which segment values should be shown, and how they should be shown.

 VALID is called when the user exits the segment value, or confirms the chosen flexfield combination. It validates the entered value against the values existing in the key flexfield table.

LOADID is optional, and it can be used to choose which information will be returned as flexfield value. This can be the concatenated segments, or the id-value for the flexfield combination or segment values.

These 3 user exits can be assigned to 3 ‘events’. There are more events possible, but they are either not yet in use, or their use is not yet supported. So we will only use ‘Validate’, ‘Edit’ and ‘Load’.

Sounds complicated, so far? Don’t worry; this is not an easy validation. But we’ll build some examples to give you an idea. First we start with building a very easy special validation. This will be built on our Code Combination key flexfield. We’ll be using a concurrent program ‘Test Flex Validation’ program to see our different options.

This program is based on the following procedure:

CREATE OR REPLACE PROCEDURE XXX_TEST_FLEXFIELD_PARAMS

( errbuf   out varchar2

, retcode  out varchar2

, p_flex   in  varchar2

, p_flex2  in  varchar2 := ‘XXX’

, p_flex3  in  varchar2 := ‘XXX’

, p_flex4  in  varchar2 := ‘XXX’

, p_flex5  in  varchar2 := ‘XXX’

) IS

BEGIN

   FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex);

   FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex2);

   FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex3);

   FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex4);

   FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex5);

END;

 

This will only write the parameter value to the output of the request. To use flexfields as parameters for concurrent programs, we need to define a value set based on them.

We will start with the barest setup to enter a key-flexfield combination. For this article, we use the accounting flexfield, with code ‘GL#’  and id-num ‘101’.

In this case, we have the following definition:

 

So what does this mean?

The first box is for the edit event. This will be triggered when the user enters the cursor into the field with this value set.

FND POPID         This is the user exit to pop up a flexfield screen, and let the user enter the flexfield values.

CODE="GL#"     This is the flexfield code for the key flexfield that we will be using.

APPL_SHORT_NAME="SQLGL" The short name for the application the flexfield belongs too. Together with ‘Code’, this will identify the flexfield itself.

NUM="101"       The id-number for the flexfield structure. If you have only a single structure flexfield, it is optional. For flexfields enabled for multiple structures, you need to enter the id-number.

VALIDATE="PARTIAL"   Validate can be ‘None’, ‘Partial’ or ‘Full’. None means the combination is not validated. Partial means that the separate segments are validated, there is no validation if the combination exists. Full means that segments and combination will be checked, and if a new value is entered, this will be inserted into the key flexfield table.

SEG=":!VALUE"                This is the forms field that will be used to store the value of the segments.

The second box is for the ‘Validation’ event. This code will be called when the user navigates out of the field, or submits the entire combination.

Now when we set this value set as a parameter for our concurrent program, we can see how the validation works:

 

Now when we run the program, we get this pop-up:

 

We have all the functionality of the key flexfield. We can use the ‘Combinations’ button to search for existing combinations, and all separate segments will be validated, as will be the final combination.

When we submit a value to our program, it will show the concatenated segments as the value of our parameter:

 

Now let’s see some more features of this validation. For example, we’d like to have the value of the combination id. (CODE_COMBINATION_ID in our case, since we use the Accounting Flexfield).

To get that, we need to add the LOADID user exit:

 

The ‘Load’ event will get the combination-id from the flexfield table. This is only possible for the ‘VALIDATE=”FULL”, since it will validate the whole combination. Also we need to set the ID=”:!ID”. This will populate the :!ID column with the ID value of the combination.

Finally, I added the ‘DINSERT=”NO” ‘, because we don’t want to allow insertion of new code combinations from this value set. (And Validation=”FULL” by default inserts new combinations into the flexfield column).

Now when we run the concurrent request, we see that the parameter value is the code_combination_id instead of the concatenated segments:


With these user exits it is also possible to select just a number of segments, instead of the whole combination. For this we remove the ‘Load’ / ‘LOADID’ part again.

Then we add a ‘DISPLAY=”x” ‘ to the ‘Edit’ and ‘Validate’ user exits. The “display” parameter is defaulting to ‘ALL’. But you can also specify separate segments by their sequence number or names. In our case, we display the first 2 segments:

 

Now when we run the concurrent program, we get a pop-up for only the first 2 values:

 

A very nice feature (at least as far as I’m concerned) is the use of a where clause on the combination values. Consider the following ‘Enter’ code:

FND POPID

CODE="GL#"

NUM="101"

APPL_SHORT_NAME="SQLGL"

VALIDATE="FULL"

TITLE="Special Validation Key"

ID=":!ID"

SEG=":!VALUE"

DESC=":!MEANING"

WHERE="segment2 not like '1%' "

 

The “WHERE” clause prevents us from choosing combinations that have a segment2 starting with ‘1’. When we run our concurrent program with this, and choose the combinations:

 

 

There is no Dpt starting with 1.

 

When we add the “WHERE”-clause to the validation event too, it will prevent us from entering the values manually:

 

 

 

The last feature that we’ll look into is the use of a pl/sql validation through the special validation routines. By using the user-exit PLSQL, we can call an anonymous PL/SQL block in our ‘Validation’ event. I created a value set with the following function for the ‘Validation’ event:

 

FND PLSQL " declare

  v_value varchar2( 10 ) := :!value ;

  v_sum number;

  v_valid boolean;

begin

   v_sum:=0;

   for i in 1..length(v_value) loop

    v_sum :=v_sum+(length(v_value)+1-i)*substr(v_value,i,1);

  end loop;

  if mod(v_sum,11)=0 then

     v_valid := TRUE;

  else

     v_valid:=FALSE;

  end if;

  if not v_valid then

      fnd_message.set_name('FND','FND_GENERIC_MESSAGE' );

      fnd_message.set_token('MESSAGE','This is not a valid bank account');

      fnd_message.raise_error;

  end if;

END; "

 

 

This PL/SQL procedure validates a (Dutch) bank account number. If it does need pass the test, a message will be displayed. This gives you almost unlimited possibilities for validating entered data.

As you can see, it is only a ‘Validate’ event. Because we don’t need any special functionality for entering the data. We can limit the entry to numbers only on the ‘Validation Set’ main page.

 

Now when we use this value set for our concurrent program, we can only enter valid dutch bank accounts:

 

 

And

 


The list of parameters for the user exits is longer than this. So we won’t be going through all the possibilities. You can check the Developers Guide and the Flexfield guide for a complete listing of options. (Did you notice the flexfield title that I sneaked into the pop-up? Try and find the option for that!)

Please try the different options for yourself, and realize the possibilities of the special validation.

 

Pair Validation

Meanwhile, we’ll continue to the ‘Pair’ validation. The pair validation is very much like the ‘special’ validation. It uses the same kind of user exits, but this time, a range of segment values or combinations is selected.

Let’s first create a range of the account segment. Instead of using POPID and VALID, we use POPIDR and VALIDR. The R-version of the user-exits automatically create a range.

Of course we need 2 parameters to set the range. However, we need only one validation set.

I created the validation set ‘XXX_PAIR_VAL’. I entered only the edit and validate events:

 

The next step is to set the parameters for both the low and high value. Both parameters have the validation set ‘XXX_PAIR_VAL’.

 

Now when we run the program, we can enter a range. This includes validation that the high value is indeed higher or equal to the low value.

 

Of course the concurrent program will receive the values for 2 parameters.

 

When we use the full validation we can enter a range of the whole account combination. Note that we cannot use the FULL validation for pair-validation. Because that would mean the use of the combination-id from the flexfield table and based on the combination-id’s you cannot build a range.

 

So we can only use PARTIAL and NONE for the validation. For that same reason, I have not yet had a reason to use a LOAD event for PAIR validation. It is however allowed to use one.

 

I created a PAIR validation for the whole accounting range as follows:

 

 

When used in the concurrent program, it will indeed allow us to enter a range of all segments:

 

 

That completes the chapter on PAIR validation too.