Select not returning value some times

From: <joshianant_at_gmail.com>
Date: Mon, 18 Feb 2008 14:27:28 -0800 (PST)
Message-ID: <413d4cbe-4ea0-42da-9d1e-665db1d0e666@s8g2000prg.googlegroups.com>


Hi,

We have a stored procedure in which we replace numeric internal currency code to ISO currency code using a lookup table. The procedure handles about 5K records and for few of them the currency remains numeric when inserted to target table. This causes exceptions in downstream system as it expects ISO codes.

Here's section of the code:



/* Get the ISO Currency Code if Currency code came in the file is number */
v_n_currcd := vsd_currency; /* This is the numeric currency code */ BEGIN
SELECT ISO_CCY_CD into vsd_currency FROM CD_MAP_TBL WHERE CURRENCY_CD = v_n_currcd;
EXCEPTION
WHEN NO_DATA_FOUND THEN
vsd_currency := v_n_currcd;
END;

We were unable to duplicate this issue in test environment even while processing 100K records.

Table CD_MAP_TBL is defined as:

SQL> desc CD_MAP_TBL;
Name Null? Type

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

CURRENCY_CD NOT NULL VARCHAR2(3)
EFFDT NOT NULL DATE
EFF_STATUS NOT NULL VARCHAR2(1)
ISO_CCY_CD NOT NULL VARCHAR2(3)
LOAD_DTTM DATE
LASTUPDDTTM DATE
ERROR_FLAG NOT NULL VARCHAR2(1) Variables are declared as:

v_n_currcd VARCHAR2(3);
vsd_currency ST_DET.CURRENCY%Type; (This is VARCHAR2(30))

Any ideas ?

Thanks in advance Received on Mon Feb 18 2008 - 16:27:28 CST

Original text of this message