| To log the row in error while selecting from database [message #623242] |
Sun, 07 September 2014 05:16  |
 |
gopal.biswal
Messages: 11
Registered: October 2013
Location: Pune
|
Junior Member |
|
|
|
With dml error logging, we can add a clause to our bulk DML statements (INSERT, UPDATE, MERGE and DELETE) to prevent the statement failing on hitting exceptions (i.e. "bad data"). Exceptional rows are added to a specifically-created errors table for investigation and/or intervention. As per my understanding there is no such feature in Oracle in which case a long running query(bulk select) returning more than one row to continue in stead of failing whenever it finds some error in one row. In this case the whole query fails though we have one row in error. Per example a query returning thousands rows fail whenever one row fails due to bad data in one date field. So in this case, the only way to handle this is to process query row by row. Wondering is there any built in approach to log the row in error while fetching rows from database just like we have DML error logging to log the rows in exception.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: To log the row in error while selecting from database [message #623303 is a reply to message #623250] |
Mon, 08 September 2014 03:10  |
|
gopal.biswal
Messages: 11
Registered: October 2013
Location: Pune
|
Junior Member |
|
|
Yes you are correct, we should have filtered out the data before loading.However,due to poor design of application, the data was not filtered loaded in to table. The XMLTYPE column MESSAGE in CONTRACT_MSG_DETAIL contains the XML data.
Now, we are doing a one time migration activity of already loaded data in CONTRACT_MSG_DETAIL
We are using XMLTABLE to query the XML data. For some records for any of column (COL1,COL2,COL3), data length exceeds more than 4000 and we receive this error ORA-01706: user function result value was too large. We could use CLOB in stead of VARCHAR2 but this might affects the performance.
SELECT A.MSG_ID, B.PRODUCT_ID, C.*
FROM CONTRACT_MSG_DETAIL A,
CONTRACT_MSG_INFO B,
XMLTABLE (
'//MxML/trades/trade[2]'
PASSING MESSAGE
COLUMNS COL1 VARCHAR2 (4000)
PATH 'tradeBody/interestRateSwap/stream[1]/capital/initialCapitalAmount/text()',
COL21 VARCHAR2 (4000)
PATH 'tradeBody/interestRateSwap/stream[1]/capital/initialCapitalCurrency/text()',
COL31 VARCHAR2 (4000)
PATH 'tradeBody/interestRateSwap/stream[1]/floatingRateStream/margin/text()') C
WHERE a.MSG_ID = B.MSG_ID
AND B.PRODUCT_ID IN
(1014)
AND B.EVENT_ID IN (6123, 6111)
AND B.IS_LATEST_VERSION = 'Y';
Regards;
Gopal
Lalit : Added code tags.
Please do it yourself in future, read How to use [code] tags
[Updated on: Mon, 08 September 2014 03:30] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
