Exception: DBD, ORA-01722: invalid number

building reports desktopintelligence
1

Hi,

We have a weird situation.
The report ran fine last month for same conditions and this month
when we run for the date range 1/1/2008 to 06/30/2008
it gives Connection or sQL sentence error DA0005 and Exception: DBD, ORA-01722: invalid number State: N/A error

But if we run the same report for values
2/1/2008 to 06/30/2008 it runs fine.
3/1/2008 to 06/30/2008 it runs fine.
4/1/2008 to 06/30/2008 it runs fine.

And there is no limitation to return the data/rows nor thee is a time limit.

couple of reports which contain huge data run fine.and couple of them are giving this error.
Where as all these reports ran fine 1 month back.

We are on BOXI SP3 on oracle 9i/windows

Thanks
Anupama

mailanuv (BOB member since 2005-11-27)

2

Hi,

How about if you run the report for the period of 1/1/2008 to 1/31/2008 - does it run fine too?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Check the database data for the date column…
It looks like there is some dirty data in the date column which is not converting correctly.

Reasons:
– Null value
– Date Invalid in the database.

BO_Chief :us: (BOB member since 2004-06-06)

4

The report runs fine for 01/01/2008 to 01/31/2008 and also
in all combinations 01/01/2008 to 03/30/2008.

Today the report ran fine again wihtout any problem.
but again after some days when we run the report again
it gives ORA-01722 error

This happened couple of times for couple of canned reports.
sometimes it is giving this problem.

Thanks
Anupama

mailanuv (BOB member since 2005-11-27)

5

Hi

Its true that your data columns storing some junk character or invalide format, this causing you the error. You can easily find this to take the length of the column value and verify with your column’s defined value length.

Regards

Siva.M

looksmee :uk: (BOB member since 2006-02-08)

6

Do you have any other prompts in the report other than date range ?
Can you post your SQL here and also let us know what is the data type of the column(s)… ?

BO_Chief :us: (BOB member since 2004-06-06)

7

Yes we have 8 prompts total in which date is one.
The data type of date object is date and others are character.

Here is the sql

SELECT
HEADER.TRACKING_NO,
DECODE(HEADER.TDC_ACTUAL_DATE,NULL,DECODE(HEADER.TDC_REVISED_DATE,NULL,HEADER.TDC_ESTIMATED_DATE,HEADER.TDC_REVISED_DATE),HEADER.TDC_ACTUAL_DATE),
DECODE(HEADER.TDC_ACTUAL_DATE,NULL,DECODE(HEADER.TDC_REVISED_DATE,NULL,DECODE(HEADER.TDC_ESTIMATED_DATE,NULL,NULL,‘E’),‘R’),‘A’),
DECODE(HEADER.ETA_ACTUAL_DATE,NULL,DECODE(HEADER.ETA_REVISED_DATE,NULL,HEADER.ETA_ESTIMATED_DATE,HEADER.ETA_REVISED_DATE),HEADER.ETA_ACTUAL_DATE),
DECODE(HEADER.ETA_ACTUAL_DATE,NULL,DECODE(HEADER.ETA_REVISED_DATE,NULL,DECODE(HEADER.ETA_ESTIMATED_DATE,NULL,NULL,‘E’),‘R’),‘A’),
DECODE(HEADER.CEN_ACTUAL_DATE,NULL,DECODE(HEADER.CEN_REVISED_DATE,NULL,HEADER.CEN_ESTIMATED_DATE,HEADER.CEN_REVISED_DATE),HEADER.CEN_ACTUAL_DATE),
DECODE(HEADER.CEN_ACTUAL_DATE,NULL,DECODE(HEADER.CEN_REVISED_DATE,NULL,DECODE(HEADER.CEN_ESTIMATED_DATE,NULL,NULL,‘E’),‘R’),‘A’),
WORKSHEET_PO.WORKSHEET_NO,
WORKSHEET_PO.WORKSHEET_SEQ_NO,
WORKSHEET_PO.HDR_WORKSHEET_STATUS,
DECODE(HEADER.WRK_ACTUAL_DATE,NULL,DECODE(HEADER.WRK_REVISED_DATE,NULL,HEADER.WRK_ESTIMATED_DATE,HEADER.WRK_REVISED_DATE),HEADER.WRK_ACTUAL_DATE),
DECODE(HEADER.WRK_ACTUAL_DATE,NULL,DECODE(HEADER.WRK_REVISED_DATE,NULL,DECODE(HEADER.WRK_ESTIMATED_DATE,NULL,NULL,‘E’),‘R’),‘A’),
HEADER.POD
FROM
WORKSHEET_PO,
HEADER,
DETAIL
WHERE
( DETAIL.CREATE_DATE=HEADER.CREATE_DATE and DETAIL.PHASE_NO=HEADER.PHASE_NO and DETAIL.TRACKING_NO=HEADER.TRACKING_NO )
AND ( DETAIL.COMPANY=WORKSHEET_PO.COMPANY and DETAIL.CREATE_DATE=WORKSHEET_PO.CREATE_DATE and DETAIL.DIVISION=WORKSHEET_PO.DIVISION and to_number(DETAIL.PO_LINE_NO)=WORKSHEET_PO.PO_LINE_NO and DETAIL.PO_NO=RTRIM(WORKSHEET_PO.PO_NO) and DETAIL.TRACKING_NO=WORKSHEET_PO.SHIPMENT_NO )
AND ( HEADER.PHASE_TYPE=‘S’ )
AND
( HEADER.SHIP_VIA_GROUP IN @Prompt(‘25c. Enter Ship-Via Group’,‘A’,‘Header\Ship Via Group’,multi,free)
OR (‘all’ IN @Prompt(‘25c. Enter Ship-Via Group’,‘A’,‘Header\Ship Via Group’,multi,free)) )
AND ( HEADER.POD IN @Prompt(‘26. Enter POD’,‘A’,‘Header\Port of Departure’,multi,free) OR (‘all’ IN @Prompt(‘26. Enter POD’,‘A’,‘Header\Port of Departure’,multi,free)) )
AND ( DETAIL.ORDER_TYPE IN @Prompt(‘02. Enter Order Type’,‘A’,‘Detail\Order Type’,multi,free) OR (‘all’ IN @Prompt(‘02. Enter Order Type’,‘A’,‘Detail\Order Type’,multi,free)) )
AND ( DECODE(HEADER.CEN_ACTUAL_DATE,NULL,DECODE(HEADER.CEN_REVISED_DATE,NULL,HEADER.CEN_ESTIMATED_DATE,HEADER.CEN_REVISED_DATE),HEADER.CEN_ACTUAL_DATE) BETWEEN
@Prompt(‘53 a. Customs Entry Start Date’,‘D’,‘Events\CEN - Date’,mono,free) AND @Prompt(‘53 b. Customs Entry End Date’,‘D’,‘Events\CEN - Date’,mono,free) )

mailanuv (BOB member since 2005-11-27)

8

Can you check whether decode is returning values always… :?:

BO_Chief :us: (BOB member since 2004-06-06)

9

I ran the report for various parameters ran ;but for the date range 01/1/2008 to 03/30/2008 and 03/01/2008 to 06/30/2008

i got error when the range is 01/01/2008 to 06/30/2008 and for combinations of various values (which has huge data )

So may be it is not able to handle huge data.

Notes:
All single one’s run fine with that date range…01/01/2008 to 06/30/2008

OCN,OCT run fine.
OCN,OCT,ACT - bad.(huge data)
but all run fine individually.

So i guess it can’t be a specific record.

Even i changed the parameters for data fetch rows and in tools->options also.

Can i know what is the max number of records/data BO can handle
is ther any limitation from Business Objects sied.

mailanuv (BOB member since 2005-11-27)

10

As far as I know there is not limit from BO side. :!:

BO_Chief :us: (BOB member since 2004-06-06)