Category Archives: Troubleshooting

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 🙂

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

eBS Troubleshooting

Oracle eBS troubleshooting

 

On this webpage, I’ll make a FAQ on common eBS problems and troubleshooting. Probably the format will change over time. But I’ll just start. I use basic flow diagrams, to give an overview of the process. Under the diagrams is an explanation of all the steps.

Ø My concurrent manager does not start

                                                                                       

1)      Check if the Internal Manager is running. On Unix, you can run: ps –ef | grep ‘FNDLIBR FND CPMGR’. If this returns a process, check the start time for the process to make sure it is the correct process.

2)      If a process is returned, and it started at the time you started the managers, the ICM is running.

3)      The ICM is not running. First check the adcmctl.txt file. It is found in the process_log directory. (Check your autoconfig xml file or the adcmctl.sh script). It will show the logs for the start-up scripts of the ICM.

4)      The ICM is running, but can’t start the other managers. Most common cause is  the APPS Listener is down. Check for it running with: ps –ef  | grep ‘tnslsnr APPS_<SID>’

5)      If no process is returned, start the apps listener with adalnctl.sh. Wait a minute afterwards, for the ICM to retry starting the managers.

6)      The ICM is running, or was started successfully before it died. Check the logfile from the ICM in $APPLCSF/$APPLLOG. By default the file is named <SID>_<proc>.mgr, where proc is the process_id from adcmctl.txt.

 

Ø Notifications are not being sent by the notification mailer

                                                                                    

1)      Look for the notification in ‘wf_notifications’. Check the status, mail_status, recipient_role and notification_id.

2)      If Mail_status = ‘SENT’, the message has been mailed already. If status<>’OPEN’ (Note the difference between status and mail_status), the notification is not eligible to be mailed anymore.

3)      The Notification Mailer has already sent this message. Maybe it was sent to the TEST_ADDRESS  from the Notification Mailer.

4)      Check the mail_preference for the recipient_role from wf_roles.

5)      If the preference is ‘QUERY’, ‘SUMHTML’, ‘SUMMARY’ or ‘DISABLED’ then the recipient will not receive notifications by mail.

6)      Change the preference in the Users preferences. Or test with a different user.

7)  The Notification Mailer reads notifications to send from the queue WF_NOTIFICATION_OUT. Select from the queue-view:

select notification_id,msg_state,msg_id,role,corrid,enq_time,deq_time

from  (select msg_id, o.enq_time, o.deq_time, msg_state

              ,(select str_value

                from   table (o.user_data.header.properties)

                where  name = 'NOTIFICATION_ID') notification_id

              , (select str_value

                 from   table (o.user_data.header.properties)

                 where  name = 'ROLE') role

              , (select str_value

                 from   table (o.user_data.header.properties)

                 where  name = 'Q_CORRELATION_ID') corrid

       from   applsys.aq$wf_notification_out o)         

where notification_id=<notification_id>

and rownum=1;

8)      If you received a result on the query, the notification is in the queue.

9)      The status of the notification in the queue should be ‘READY’. If it is ‘PROCESSED’, or ‘ERROR’, the notification is already dequeued or errored. Otherwise check the status of the queue with:

select name,enqueue_enabled,dequeue_enabled

from dba_queues

where name='WF_NOTIFICATION_OUT';

10)   The status of the message should be ‘READY’. The ‘ENQUEUE_ENABLED’ and ‘DEQUEUE_ENABLED’ columns should be ‘YES’.

11)   Reprocess the message with the conc. Request. Or stop/start the queue with dbms_aqadm.

12)   Check the Notification Mailer logfile. The notification is ready for the mailer, but it is unable to pick it up. Check if the mailer shut down due to too many errors.

13)   The message is not in ‘WF_NOTIFICATION_OUT’. It can still be queued on the ‘WF_DEFERRED’ queue. Check this queue with:

select v.msg_id,v.msg_state,v.enq_time,v.deq_time,v.corr_id

from   applsys.aq$wf_deferred v

,      table(v.user_data.parameter_list) t

where v.corr_id like 'APPS:oracle.apps.wf.notification.%'

and name ='NOTIFICATION_ID'

and value = <Notification_id>;

14)   If the query returned results and the msg_state is ‘READY’, the notification is still being processed. Skip to 16

15)   Unfortunately, your notification seems to have gone missing. Consult Oracle Support. Please inform us of the solution in this case, so we can extend the FAQ.

16)   The notification is still in the deferred queue, waiting to be picked up by the ‘Workflow Deferred Notification Agent Listener’. Check the status of this listener. And if needed, check its logfile.

17)   If the Deferred Notification Agent Listener is not running, or erroring

18)   (re)start it.

19)   Check if the Business Event ‘oracle.apps.wf.notification.denormalize’ is enabled and has a subscription to procedure ‘Wf_Notification_Util.Denormalize_RF’.

20)   If either the Business event is disabled, or the subscription non-existent or disabled, go to 21.

21)   Make sure that the Business event is enabled and the subscription is valid and enabled. You will still have to reprocess the notification with the concurrent program to resubmit notifications.

22)   Unfortunately, we can’t locate the issue yet. If you manage to solve the issue please inform us, so we can update this FAQ.