1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Exception handling in oracle

Discussion in 'Training and Certification' started by TechQueryPond, Apr 5, 2016.

  1. TechQueryPond

    TechQueryPond Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    INDIA


    Exception Handling In Oracle

    Code (SQL):
    CREATE OR REPLACE PROCEDURE ORA_EXCE_HANDLING
    IS
    MYQUERY VARCHAR2(100);
    STR_ID VARCHAR2(10);
    BEGIN
    MYQUERY:='SELECT ID FROM MYDEV.EMPLOYEE111 WHERE ID=''1''';
    DBMS_OUTPUT.PUT_LINE('QUERY '||MYQUERY);
    BEGIN
    EXECUTE IMMEDIATE MYQUERY INTO STR_ID;
    EXCEPTION WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('EXCEPTION CAPTURE.');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('OTHER EXCEPTION IS CAPTURE HERE :'||SQLERRM);
    END;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID '||STR_ID);
    END
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation

    What do you want to make?

    simple example :

    Code (SQL):
    SET serveroutput ON
    DROP TABLE test_exp;
    CREATE TABLE test_exp
    (
        id INT PRIMARY KEY,
        name varchar2(4)
    );

    INSERT INTO test_exp
    SELECT
        level,
        'lvl'||to_char(level)
    FROM dual
    CONNECT BY level <=7;

    commit;

    DECLARE
        l_name test_exp.name%TYPE;
    BEGIN
        SELECT
            name
        INTO
            l_name
        FROM
            test_exp e
        WHERE
            e.id = 8;
    exception
        WHEN no_data_found THEN
            dbms_output.put_line(sqlerrm);  
    END;  
    /



    Recomendtaion , see on link : PL/SQL Error Handling
     
    Last edited: Apr 5, 2016