 | |
Select not returning value some times
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Select not returning value some times -
02-18-2008
, 04:27 PM
#2
| |||
| |||
|
|
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 |
v_n_currcd := vsd_currency; /* This is the numeric currency code */
So both values are now the same, and appearantly either vsd_currrency was
holding the numeric value, or the numeric value is assigned an ISO code?
Then you try to find a record with the isocode equal to vsd_currency, which
leads to the conclusion that vsd_currency WAS holding the iso-code, and you
HOPE to find a corresponding numeric value. If you don't find it (so no
values have changed in the select) you assign
vsd_currency := v_n_currcd;
but both values were already the same...... furthermore, you assign a
varchar2(30) to a varchar2(3)..... but fortunately it will never be longer
than 3, because of the previous assignment.
I don't get it.
Shakespeare
#3
| |||
| |||
|
|
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 |
v_n_currcd := vsd_currency; /* This is the numeric currency code */
So both values are now the same, and appearantly either vsd_currrency was
holding the numeric value, or the numeric value is assigned an ISO code?
Then you try to find a record with the isocode equal to vsd_currency, which
leads to the conclusion that vsd_currency WAS holding the iso-code, and you
HOPE to find a corresponding numeric value. If you don't find it (so no
values have changed in the select) you assign
vsd_currency := v_n_currcd;
but both values were already the same...... furthermore, you assign a
varchar2(30) to a varchar2(3)..... but fortunately it will never be longer
than 3, because of the previous assignment.
I don't get it.
Shakespeare
#4
| |||
| |||
|
|
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 |
v_n_currcd := vsd_currency; /* This is the numeric currency code */
So both values are now the same, and appearantly either vsd_currrency was
holding the numeric value, or the numeric value is assigned an ISO code?
Then you try to find a record with the isocode equal to vsd_currency, which
leads to the conclusion that vsd_currency WAS holding the iso-code, and you
HOPE to find a corresponding numeric value. If you don't find it (so no
values have changed in the select) you assign
vsd_currency := v_n_currcd;
but both values were already the same...... furthermore, you assign a
varchar2(30) to a varchar2(3)..... but fortunately it will never be longer
than 3, because of the previous assignment.
I don't get it.
Shakespeare
#5
| |||
| |||
|
|
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 |
v_n_currcd := vsd_currency; /* This is the numeric currency code */
So both values are now the same, and appearantly either vsd_currrency was
holding the numeric value, or the numeric value is assigned an ISO code?
Then you try to find a record with the isocode equal to vsd_currency, which
leads to the conclusion that vsd_currency WAS holding the iso-code, and you
HOPE to find a corresponding numeric value. If you don't find it (so no
values have changed in the select) you assign
vsd_currency := v_n_currcd;
but both values were already the same...... furthermore, you assign a
varchar2(30) to a varchar2(3)..... but fortunately it will never be longer
than 3, because of the previous assignment.
I don't get it.
Shakespeare
#6
| |||
| |||
|
|
joshian... (AT) gmail (DOT) com> schreef in berichtnews:413d4cbe-4ea0-42da-9d1e-665db1d0e666 (AT) s8g2000prg (DOT) 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 Could you pleas explain this piece of code? First you assign v_n_currcd := vsd_currency; * /* This is the numeric currency code */ So both values are now the same, and appearantly either vsd_currrency was holding the numeric value, or the numeric value is assigned an ISO code? Then you try to find a record with the isocode equal to vsd_currency, which leads to the conclusion that vsd_currency WAS holding the iso-code, and you HOPE to find a corresponding numeric value. If you don't find it (so no values have changed in the select) you assign vsd_currency := v_n_currcd; but both values were already the same...... furthermore, you assign a varchar2(30) to a varchar2(3)..... but fortunately it will never be longer than 3, because of the previous assignment. I don't get it. Shakespeare- Hide quoted text - - Show quoted text - |
table.
With the select statement, it is expected that ISO currency code
(column ISO_CCY_CD) gets overwritten
on previous numeric value of vsd_currency.
"WHERE CURRENCY_CD = v_n_currcd;" will fetch a row that has numeric
CURRENCY_CD equal to value of "v_n_currcd"
The numeric code is never more than 3 characters even though the
variable is varchar(30).
Thanks for taking time.
#7
| |||
| |||
|
|
joshian... (AT) gmail (DOT) com> schreef in berichtnews:413d4cbe-4ea0-42da-9d1e-665db1d0e666 (AT) s8g2000prg (DOT) 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 Could you pleas explain this piece of code? First you assign v_n_currcd := vsd_currency; * /* This is the numeric currency code */ So both values are now the same, and appearantly either vsd_currrency was holding the numeric value, or the numeric value is assigned an ISO code? Then you try to find a record with the isocode equal to vsd_currency, which leads to the conclusion that vsd_currency WAS holding the iso-code, and you HOPE to find a corresponding numeric value. If you don't find it (so no values have changed in the select) you assign vsd_currency := v_n_currcd; but both values were already the same...... furthermore, you assign a varchar2(30) to a varchar2(3)..... but fortunately it will never be longer than 3, because of the previous assignment. I don't get it. Shakespeare- Hide quoted text - - Show quoted text - |
table.
With the select statement, it is expected that ISO currency code
(column ISO_CCY_CD) gets overwritten
on previous numeric value of vsd_currency.
"WHERE CURRENCY_CD = v_n_currcd;" will fetch a row that has numeric
CURRENCY_CD equal to value of "v_n_currcd"
The numeric code is never more than 3 characters even though the
variable is varchar(30).
Thanks for taking time.
#8
| |||
| |||
|
|
joshian... (AT) gmail (DOT) com> schreef in berichtnews:413d4cbe-4ea0-42da-9d1e-665db1d0e666 (AT) s8g2000prg (DOT) 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 Could you pleas explain this piece of code? First you assign v_n_currcd := vsd_currency; * /* This is the numeric currency code */ So both values are now the same, and appearantly either vsd_currrency was holding the numeric value, or the numeric value is assigned an ISO code? Then you try to find a record with the isocode equal to vsd_currency, which leads to the conclusion that vsd_currency WAS holding the iso-code, and you HOPE to find a corresponding numeric value. If you don't find it (so no values have changed in the select) you assign vsd_currency := v_n_currcd; but both values were already the same...... furthermore, you assign a varchar2(30) to a varchar2(3)..... but fortunately it will never be longer than 3, because of the previous assignment. I don't get it. Shakespeare- Hide quoted text - - Show quoted text - |
table.
With the select statement, it is expected that ISO currency code
(column ISO_CCY_CD) gets overwritten
on previous numeric value of vsd_currency.
"WHERE CURRENCY_CD = v_n_currcd;" will fetch a row that has numeric
CURRENCY_CD equal to value of "v_n_currcd"
The numeric code is never more than 3 characters even though the
variable is varchar(30).
Thanks for taking time.
#9
| |||
| |||
|
|
joshian... (AT) gmail (DOT) com> schreef in berichtnews:413d4cbe-4ea0-42da-9d1e-665db1d0e666 (AT) s8g2000prg (DOT) 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 Could you pleas explain this piece of code? First you assign v_n_currcd := vsd_currency; * /* This is the numeric currency code */ So both values are now the same, and appearantly either vsd_currrency was holding the numeric value, or the numeric value is assigned an ISO code? Then you try to find a record with the isocode equal to vsd_currency, which leads to the conclusion that vsd_currency WAS holding the iso-code, and you HOPE to find a corresponding numeric value. If you don't find it (so no values have changed in the select) you assign vsd_currency := v_n_currcd; but both values were already the same...... furthermore, you assign a varchar2(30) to a varchar2(3)..... but fortunately it will never be longer than 3, because of the previous assignment. I don't get it. Shakespeare- Hide quoted text - - Show quoted text - |
table.
With the select statement, it is expected that ISO currency code
(column ISO_CCY_CD) gets overwritten
on previous numeric value of vsd_currency.
"WHERE CURRENCY_CD = v_n_currcd;" will fetch a row that has numeric
CURRENCY_CD equal to value of "v_n_currcd"
The numeric code is never more than 3 characters even though the
variable is varchar(30).
Thanks for taking time.
#10
| |||
| |||
|
|
joshian... (AT) gmail (DOT) com> schreef in berichtnews:413d4cbe-4ea0-42da-9d1e-665db1d0e666 (AT) s8g2000prg (DOT) 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 Could you pleas explain this piece of code? First you assign v_n_currcd := vsd_currency; /* This is the numeric currency code */ So both values are now the same, and appearantly either vsd_currrency was holding the numeric value, or the numeric value is assigned an ISO code? Then you try to find a record with the isocode equal to vsd_currency, which leads to the conclusion that vsd_currency WAS holding the iso-code, and you HOPE to find a corresponding numeric value. If you don't find it (so no values have changed in the select) you assign vsd_currency := v_n_currcd; but both values were already the same...... furthermore, you assign a varchar2(30) to a varchar2(3)..... but fortunately it will never be longer than 3, because of the previous assignment. I don't get it. Shakespeare- Hide quoted text - - Show quoted text - |
table.
With the select statement, it is expected that ISO currency code
(column ISO_CCY_CD) gets overwritten
on previous numeric value of vsd_currency.
"WHERE CURRENCY_CD = v_n_currcd;" will fetch a row that has numeric
CURRENCY_CD equal to value of "v_n_currcd"
The numeric code is never more than 3 characters even though the
variable is varchar(30).
Thanks for taking time.
--------------------
Ok, but I never see you UPDATE any table... and still the assignment at the
end seems useless. Yor proc wil continue with the numeric code if no data is
found.
Shakespeare
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.