-
Fetch rowid caused exception`
Hi All,
I am having problem in getting the rowid which has caused exception while inserting
data. I am doing the following :
1) Insert/*+ append */
into
test t1
select
a,b,c
from
test2 ts
where
not exists
(
t1.a = t2.a
)
If an exception occurs in the above case,I am handling the exception by way of inserting sqlcode, sqlerrm, the code block which caused exception in my exception table. But also I want to insert the rowid which caused the exception so that I can easily trace down the data. I know that Oracle provides a table called exceptions which can contain these data for contrainst,if enabled.
But I want to know is there any other way round to get this information to insert this data in my exception table?
Ramchandra Jetwani
-
If you need help post the hole code so we can try to analyze it
-
Hi All,
The following are the examples.
example :1
-----------
INSERT/*+ APPEND */ INTO
t_endbankdep_uhm NOLOGGING
SELECT
ebd_vpk_country_code ,
ebd_vpk_company_code ,
ebd_vpk_routeid ,
ebd_vpk_route_key ,
ebd_vpk_visit_key ,
ebd_vnm_branch_code ,
ebd_vnm_sub_bank_code ,
ebd_vnm_control_dgt ,
ebd_vnm_account_nbr ,
ebd_vnm_refnbr ,
trim(putDefault(ebd_vnm_tot_dep)),
ebd_vnm_document_nbr ,
ebd_vnm_document_dt ,
ebd_vnm_document_tm ,
ebd_vnm_void_indic ,
ebd_vnm_empid ,
ebd_vpk_cyclenbr ,
ebd_vnm_tcomm_dt ,
ebd_vnm_tcomm_tm ,
ebd_vnm_input_srce ,
ebd_vnm_business_type ,
ebd_vnm_dc_nbr ,
FROM
(
SELECT
ebd_vpk_country_code ,
ebd_vpk_company_code ,
ebd_vpk_routeid ,
ebd_vpk_route_key ,
ebd_vpk_visit_key ,
ebd_vnm_branch_code ,
ebd_vnm_sub_bank_code ,
ebd_vnm_control_dgt ,
ebd_vnm_account_nbr ,
ebd_vnm_refnbr ,
ebd_vnm_tot_dep ,
ebd_vnm_document_nbr ,
ebd_vnm_document_dt ,
ebd_vnm_document_tm ,
ebd_vnm_void_indic ,
ebd_vnm_empid ,
ebd_vpk_cyclenbr ,
ebd_vnm_tcomm_dt ,
ebd_vnm_tcomm_tm ,
ebd_vnm_input_srce ,
ebd_vnm_business_type ,
ebd_vnm_dc_nbr ,
COUNT(*) OVER (PARTITION BY
ebd_vpk_country_code,
ebd_vpk_company_code,
ebd_vpk_cyclenbr ,
ebd_vpk_route_key ,
ebd_vpk_visit_key ,
ebd_vnm_document_nbr,
ebd_vnm_tcomm_dt ,
ebd_vnm_tcomm_tm ,
ebd_vnm_dc_nbr ,
ebd_vpk_routeid ,
ebd_vnm_empid
ORDER BY ebd_vnm_file_id ROWS UNBOUNDED PRECEDING
) REC
FROM
t_endbankchqdep_uhh_external
) ebd_ext
WHERE
NOT EXISTS
(
SELECT
'X'
FROM
t_endbankdep_uhm ebd
WHERE
ebd.ebd_vpk_country_code = ebd_ext.ebd_vpk_country_code AND
ebd.ebd_vpk_company_code = ebd_ext.ebd_vpk_company_code AND
ebd.ebd_vpk_routeid = ebd_ext.ebd_vpk_routeid AND
ebd.ebd_vpk_route_key = ebd_ext.ebd_vpk_route_key AND
ebd.ebd_vnm_tcomm_dt = ebd_ext.ebd_vnm_tcomm_dt AND
ebd.ebd_vnm_tcomm_tm = ebd_ext.ebd_vnm_tcomm_tm AND
ebd_vnm_type_code = '08'
)
AND
REC = 1;
example :2
-----------
UPDATE
t_loadunload_hdr_uhh NOLOGGING
SET
luh_vnm_totalqty
=
(
SELECT
NVL(SUM(DECODE(lud_vpk_product_code,lud_vnm_casenbr,lud_vnm_case_total/NVL(lud_vnm_units_per_case,1),lud_vnm_case_total)),0)
FROM
t_loadunload_dtl_uhh
WHERE
lud_vpk_country_code = '138' AND
lud_vpk_company_code = '002' AND
lud_vpk_docseq_nbr = luh_vpk_docseq_nbr
GROUP BY
lud_vpk_country_code,
lud_vpk_company_code,
lud_vpk_docseq_nbr
)
WHERE
luh_vpk_country_code = '138' AND
luh_vpk_company_code = '002' AND
luh_vnm_void_indic = 0 AND
luh_nnm_status_f != 7;
In case of insert statement example,
the exception which can occur is of Primary Key violation
The primary key is on document number, transaction date and time,
route and employee id on the t_endbankdep_uhm table.
Table t_endbankdep_uhm_external has no constraints.
In case of update statment example,
The exception which can occur is inserted value is too
large for column.
I want to know which row has caused this exception in both the cases.
Ramchandra Jetwani
-
This thread at Ask Tom might help ... http://asktom.oracle.com/pls/ask/f?p...:8784259916366
By the way, there's no such thing as a nologging update.
-
Hi,
Thank you very much for pointing out my error on NOLOGGING on update statement.
I have gone through the link given by you on ASK TOM. What I came to know is that in case of "insert into with select statement", there is no way we can get the rowid which has caused exception because the above "insert with select" will either insert all rows or none so there is no way to get the rowid which has caused the exception. The other way round is that to bulk collect the data in collections and then insert the data by way of using FORALL statement to insert data. In collections, there is facility availablle where one can continue to process of DML in case of even exceptions are raised. This can be done by using BULK EXCEPTIONS. The example of the same is available on the link provided by Slimdave.
Ramchandra Jetwani
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|