Re: Coding for ORA-02291 in Pl/SQL

dfosgate_at_llbean.com wrote:

> Hi,
>
> I want to trap an execption from a table insert where the Oracle
> exception code will be ORA-02291 . Can I code the exception as follows:
>
> --
> -- INSERT INTO PRICE_LIST_DTL for CATG within CLASS
> --
> BEGIN <<insert_prlstdtl>>
> INSERT INTO PRICE_LIST_DTL
> (
> PRICE_LIST_ID
> ,SEQ_NO
> ,TYPE_CD
> ,DEPTGRP_NO
> ,DEPT_NO
> ,CLASS_NO
> ,LINE_NO
> ,SKU_NO
> ,PRICE_LEVEL
> ,PRICE_AMOUNT
> ,PRICE_PCT
> ,START_DATE
> ,END_DATE
> ,DATE_CREATED
> ,USER_CREATED
> ,DATE_MODIFIED
> ,USER_MODIFIED
> )
> VALUES (
> lv_plid
> ,lv_seq
> ,'DFR'
> ,NULL
> ,lv_dept
> ,lv_class
> ,lv_line
> ,lv_sku_no
> ,1
> ,NULL
> ,lv_pct
> ,NULL
> ,NULL
> ,sysdate
> ,'ROD046'
> ,sysdate
> ,'ROD046'
> );
> EXCEPTION
> WHEN ORA-02291
> THEN
> DBMS_OUTPUT.PUT_LINE('ERROR - RI
> DATA NOT FOUND for UPC = ' || lv_upc_id);
> lv_wng:=lv_wng+1;
> rc:=04;
> GOTO continue;
> WHEN OTHERS THEN
> line_out := '** ERROR: '||
> QLCODE || ':'||SQLERRM||'**';
> DBMS_OUTPUT.PUT_LINE(line_out);
> rc:=12;
> GOTO abend;
>
> END insert_prlstdtl;
> will the above code WHEN ORA-02291 trap the error?

Sybrand's answser is correct. For a full page of demos of the various ways to trap errors in Oracle go to: http://www.psoug.org
click on Morgan's Library
click on Exception Handling

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)