Fetch rowid caused exception`
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Fetch rowid caused exception`

  1. #1
    Join Date
    Dec 2003
    Location
    Mehamadabad
    Posts
    14

    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

  2. #2
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    If you need help post the hole code so we can try to analyze it

  3. #3
    Join Date
    Dec 2003
    Location
    Mehamadabad
    Posts
    14
    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

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 2003
    Location
    Mehamadabad
    Posts
    14
    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
  •  



Click Here to Expand Forum to Full Width