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!

When no exceptions are thrown, execute statement?

Discussion in 'SQL PL/SQL' started by pditty8811, Feb 10, 2014.

  1. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    How do I execute a statement if no exceptions are thrown? Perhaps in an if statement?

    I need to print the line "success" if the script finishes with no exceptions. I have the rest of the script written.
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    This is very crazy...
    ... So why can't
    Code (SQL):
    dbms_output.put_line('Success...');
    before exception block begins....
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi ,

    We can try as following to achieve this.

    Code (Text):

    declare
    a number;
    ............
    begin
    ............
    a := 1/5;
    ............
    ............
    dbms_output.put_line('Success');
    exception when others then
    null;
    end;
     
    The dbms_output.put_line('Success'); line in the above code will execute once all the statements get executed well.
     
  4. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    But if the exceptions are above the output.put_line, then wont the exceptions get thrown before the output.put_line and then I'll have the exception output.put_lines printed before the "success"? That is what I'm trying to prevent. I have individual exceptions nested above the success statement.
     
  5. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi ,

    Got your point, Can you please post your code here. By that we can try to modify it accordingly.
     
  6. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi pditty8811,

    I am unable to understand the requirement of yours.. Please share your script here ..
     
  7. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Code (Text):

    SET SERVEROUTPUT ON;
         
    DECLARE
      vCustid NUMBER(4,0);
      vOrderid NUMBER(4,0);
      vPartid NUMBER(4,0);
      vQuantity Number(4,0);
      vCname VARCHAR2(100);
      vSalesdate DATE;
      vMatch NUMBER(4,0);
      vDescription VARCHAR2(100);
      vDetail NUMBER(2,0);
      vStockQuantity NUMBER(4,0);
     
      quantity_not_greater_than_zero EXCEPTION;
      stock_quantity_equals_zero EXCEPTION;

     
    BEGIN
      vCustid := &1;
      vOrderid := &2;
      vPartid := &3;
      vQuantity := &4;
     
      DBMS_OUTPUT.put_line(vCustid || ' ' || vOrderid || ' ' || vPartid || ' ' || vQuantity);
      BEGIN
        --Verify customer exists
        SELECT CUSTOMERS.CNAME
        INTO vCname
        FROM CUSTOMERS
        WHERE CUSTOMERS.CUSTID = vCustid;
     
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.put_line('An error was encountered verifying if customer exists - '||SQLERRM||'-'||SQLCODE);
      END;
     
      BEGIN
        --Verify order exists
        SELECT ORDERS.SALESDATE
        INTO vSalesdate
        FROM ORDERS
        WHERE ORDERS.ORDERID = vOrderid;
     
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.put_line('An error was encountered verifying if order exists - '||SQLERRM||'-'||SQLCODE);
      END;
     
      BEGIN
     
        --Verify custid matches with orderid
        SELECT ORDERS.ORDERID
        INTO vMatch
        FROM ORDERS    
        WHERE ORDERS.ORDERID = vOrderid AND ORDERS.CUSTID = vCustid;
     
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.put_line('An error was encountered matching CUSTID with ORDERID - '||SQLERRM||'-'||SQLCODE);
      END;
     
      BEGIN
     
        --Verify partid exists
        SELECT INVENTORY.DESCRIPTION
        INTO vDescription
        FROM INVENTORY    
        WHERE INVENTORY.PARTID = vPartid;
     
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.put_line('An error was encountered verifying if part exists - '||SQLERRM||'-'||SQLCODE);
      END;
     
      BEGIN
     
        --Test to ensure quantity is greater than zero
        IF vQuantity < 1 THEN
          RAISE quantity_not_greater_than_zero;
        END IF;
     
        EXCEPTION
          WHEN quantity_not_greater_than_zero THEN
            DBMS_OUTPUT.put_line('An error was encountered testing if quantity was greater than zero - '||SQLERRM||'-'||SQLCODE);
      END;
     
     
      --Insert data into ORDERITEMS table
      BEGIN
        SELECT MAX(ORDERITEMS.DETAIL) + 1
        INTO vDetail
        FROM ORDERITEMS
        WHERE ORDERITEMS.ORDERID = vOrderid;
       
        IF vDetail IS NULL THEN
            vDetail := 1;
        END IF;
      END;
     
      INSERT INTO ORDERITEMS
      (ORDERID, DETAIL, PARTID, QTY)
      VALUES
      (vOrderid, vDetail, vPartid, vQuantity);

     
     
      --Update stock quantity in INVENTORY table
      UPDATE INVENTORY
      SET STOCKQTY = STOCKQTY - vQuantity
      WHERE PARTID = vPartid;
     
     
      --Check stock quantity to ensure it is not less than or equal to 0
      BEGIN
        SELECT INVENTORY.STOCKQTY
        INTO vStockQuantity
        FROM INVENTORY
        WHERE INVENTORY.PARTID = vPartid;
     
        IF vStockQuantity <= 0 THEN
          RAISE stock_quantity_equals_zero;
        END IF;
     
        EXCEPTION
          WHEN stock_quantity_equals_zero THEN
            DBMS_OUTPUT.put_line('An error was encountered - stock quantity is less than or equal to zero - '||SQLERRM||'-'||SQLCODE);
            DBMS_OUTPUT.put_line('Rolling back action... ');
            ROLLBACK;
      END;
     
      --If successful display message and COMMIT
      DBMS_OUTPUT.put_line('Successful input!!!');
      DBMS_OUTPUT.put_line('Input data: ' || vCustid || ' - ' || vCname || ' - ' || vOrderid || ' - ' || vSalesdate || ' - ' || vPartid || ' - ' || vQuantity);
      COMMIT;
     
    END;

     
     
  8. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Double post
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The code that you posted should report success if all of the BEGIN..END blocks complete without exception. If it doesn't post the output you DO receive so we can see where the fault might lie.
     
  10. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Perhaps you're not understanding me.

    I can make any of the exceptions fire on command. I can keep the exceptions from firing. However, I want the "success" to fire ONLY IF NO EXCEPTIONS ARE RAISED. ONLY THEN DO I WANT SUCCESS TO BE PRINTED.

    Does this make sense?
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yell at me again and you will receive no further help.


    What you have done is 'encapsulate' your exceptions into individual blocks, and you have made NO provision to halt the execution of FURTHER blocks once an exception is raised, which is why your code 'falls through' the exception that should cause it to terminate. Modifications to your code to make it work are shown below:


    Code (SQL):

    SQL>
    SQL> CREATE TABLE customers(
      2  custid  NUMBER,
      3  cname   varchar2(40));
    TABLE created.
    SQL>
    SQL> CREATE TABLE orders(
      2  orderid NUMBER,
      3  custid  NUMBER,
      4  salesdate      DATE);
    TABLE created.
    SQL>
    SQL> CREATE TABLE orderitems(
      2  orderid NUMBER,
      3  detail  NUMBER,
      4  partid  NUMBER,
      5  qty     NUMBER);
    TABLE created.
    SQL>
    SQL> CREATE TABLE inventory(
      2  partid  NUMBER,
      3  description varchar2(40),
      4  stockqty      NUMBER);
    TABLE created.
    SQL>
    SQL> BEGIN
      2        FOR i IN 1..100 loop
      3         INSERT INTO customers
      4         VALUES(i, 'CUSTOMER '||i);
      5         INSERT INTO orders
      6         VALUES(i, i, sysdate-i);
      7         INSERT INTO orderitems
      8         VALUES(i, i, i, MOD(i, 17)+1);
      9         INSERT INTO inventory
     10         VALUES(i, 'PART '||i, MOD(i, 17)+1);
     11        END loop;
     12  
     13        commit;
     14  
     15  END;
     16  /
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> SET SERVEROUTPUT ON;
    SQL>
    SQL> DECLARE
      2    vCustid NUMBER(4,0);
      3    vOrderid NUMBER(4,0);
      4    vPartid NUMBER(4,0);
      5    vQuantity NUMBER(4,0);
      6    vCname VARCHAR2(100);
      7    vSalesdate DATE;
      8    vMatch NUMBER(4,0);
      9    vDescription VARCHAR2(100);
     10    vDetail NUMBER(2,0);
     11    vStockQuantity NUMBER(4,0);
     12  
     13    quantity_not_greater_than_zero EXCEPTION;
     14    stock_quantity_equals_zero EXCEPTION;
     15  
     16  
     17  BEGIN
     18    vCustid := &1;
     19    vOrderid := &2;
     20    vPartid := &3;
     21    vQuantity := &4;
     22  
     23    DBMS_OUTPUT.put_line(vCustid || ' ' || vOrderid || ' ' || vPartid || ' ' || vQuantity);
     24    BEGIN
     25    --Verify customer exists
     26    SELECT CUSTOMERS.CNAME
     27    INTO vCname
     28    FROM CUSTOMERS
     29    WHERE CUSTOMERS.CUSTID = vCustid;
     30  
     31    EXCEPTION
     32      WHEN NO_DATA_FOUND THEN
     33        DBMS_OUTPUT.put_line('An error was encountered verifying if customer exists - '||SQLERRM||'-'||SQLCODE);
     34        raise;
     35    END;
     36  
     37    BEGIN
     38    --Verify order exists
     39    SELECT ORDERS.SALESDATE
     40    INTO vSalesdate
     41    FROM ORDERS
     42    WHERE ORDERS.ORDERID = vOrderid;
     43  
     44    EXCEPTION
     45      WHEN NO_DATA_FOUND THEN
     46        DBMS_OUTPUT.put_line('An error was encountered verifying if order exists - '||SQLERRM||'-'||SQLCODE);
     47        raise;
     48    END;
     49  
     50    BEGIN
     51  
     52    --Verify custid matches with orderid
     53    SELECT ORDERS.ORDERID
     54    INTO vMatch
     55    FROM ORDERS
     56    WHERE ORDERS.ORDERID = vOrderid AND ORDERS.CUSTID = vCustid;
     57  
     58    EXCEPTION
     59      WHEN NO_DATA_FOUND THEN
     60        DBMS_OUTPUT.put_line('An error was encountered matching CUSTID with ORDERID - '||SQLERRM||'-'||SQLCODE);
     61        raise;
     62    END;
     63  
     64    BEGIN
     65  
     66    --Verify partid exists
     67    SELECT INVENTORY.DESCRIPTION
     68    INTO vDescription
     69    FROM INVENTORY
     70    WHERE INVENTORY.PARTID = vPartid;
     71  
     72    EXCEPTION
     73      WHEN NO_DATA_FOUND THEN
     74        DBMS_OUTPUT.put_line('An error was encountered verifying if part exists - '||SQLERRM||'-'||SQLCODE);
     75        raise;
     76    END;
     77  
     78    BEGIN
     79  
     80    --Test to ensure quantity is greater than zero
     81    IF vQuantity < 1 THEN
     82      RAISE quantity_not_greater_than_zero;
     83    END IF;
     84  
     85    EXCEPTION
     86      WHEN quantity_not_greater_than_zero THEN
     87        DBMS_OUTPUT.put_line('An error was encountered testing if quantity was greater than zero - '||SQLERRM||'-'||SQLCODE);
     88        raise;
     89    END;
     90  
     91  
     92    --Insert data into ORDERITEMS table
     93    BEGIN
     94    SELECT MAX(ORDERITEMS.DETAIL) + 1
     95    INTO vDetail
     96    FROM ORDERITEMS
     97    WHERE ORDERITEMS.ORDERID = vOrderid;
     98  
     99    IF vDetail IS NULL THEN
    100        vDetail := 1;
    101    END IF;
    102    END;
    103  
    104    INSERT INTO ORDERITEMS
    105    (ORDERID, DETAIL, PARTID, QTY)
    106    VALUES
    107    (vOrderid, vDetail, vPartid, vQuantity);
    108  
    109  
    110  
    111    --Update stock quantity in INVENTORY table
    112    UPDATE INVENTORY
    113    SET STOCKQTY = STOCKQTY - vQuantity
    114    WHERE PARTID = vPartid;
    115  
    116  
    117    --Check stock quantity to ensure it is not less than or equal to 0
    118    BEGIN
    119    SELECT INVENTORY.STOCKQTY
    120    INTO vStockQuantity
    121    FROM INVENTORY
    122    WHERE INVENTORY.PARTID = vPartid;
    123  
    124    IF vStockQuantity <= 0 THEN
    125      RAISE stock_quantity_equals_zero;
    126    END IF;
    127  
    128    EXCEPTION
    129      WHEN stock_quantity_equals_zero THEN
    130        DBMS_OUTPUT.put_line('An error was encountered - stock quantity is less than or equal to zero - '||SQLERRM||'-'||SQLCODE);
    131        DBMS_OUTPUT.put_line('Rolling back action... ');
    132        ROLLBACK;
    133        raise;
    134    END;
    135  
    136    --If successful display message and COMMIT
    137    DBMS_OUTPUT.put_line('Successful input!!!');
    138    DBMS_OUTPUT.put_line('Input data: ' || vCustid || ' - ' || vCname || ' - ' || vOrderid || ' - ' || vSalesdate || ' - ' || vPartid || ' - ' || vQuantity);
    139    COMMIT;
    140  
    141  END;
    142  /
    Enter VALUE FOR 1: 1
    OLD  18:   vCustid := &1;
    NEW  18:   vCustid := 1;
    Enter VALUE FOR 2: 1
    OLD  19:   vOrderid := &2;
    NEW  19:   vOrderid := 1;
    Enter VALUE FOR 3: 1
    OLD  20:   vPartid := &3;
    NEW  20:   vPartid := 1;
    Enter VALUE FOR 4: 1
    OLD  21:   vQuantity := &4;
    NEW  21:   vQuantity := 1;
    1 1 1 1                                                                        
    Successful INPUT!!!                                                            
    INPUT DATA: 1 - CUSTOMER 1 - 1 - 09-FEB-14 - 1 - 1                              
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> spool off
    SQL>
    SQL> CREATE TABLE customers(
      2  custid  NUMBER,
      3  cname   varchar2(40));
    TABLE created.
    SQL>
    SQL> CREATE TABLE orders(
      2  orderid NUMBER,
      3  custid  NUMBER,
      4  salesdate      DATE);
    TABLE created.
    SQL>
    SQL> CREATE TABLE orderitems(
      2  orderid NUMBER,
      3  detail  NUMBER,
      4  partid  NUMBER,
      5  qty     NUMBER);
    TABLE created.
    SQL>
    SQL> CREATE TABLE inventory(
      2  partid  NUMBER,
      3  description varchar2(40),
      4  stockqty      NUMBER);
    TABLE created.
    SQL>
    SQL> BEGIN
      2        FOR i IN 1..100 loop
      3         INSERT INTO customers
      4         VALUES(i, 'CUSTOMER '||i);
      5         INSERT INTO orders
      6         VALUES(i, i, sysdate-i);
      7         INSERT INTO orderitems
      8         VALUES(i, i, i, MOD(i, 17)+1);
      9         INSERT INTO inventory
     10         VALUES(i, 'PART '||i, MOD(i, 17)+1);
     11        END loop;
     12  
     13        commit;
     14  
     15  END;
     16  /
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> SET SERVEROUTPUT ON;
    SQL>
    SQL> DECLARE
      2    vCustid NUMBER(4,0);
      3    vOrderid NUMBER(4,0);
      4    vPartid NUMBER(4,0);
      5    vQuantity NUMBER(4,0);
      6    vCname VARCHAR2(100);
      7    vSalesdate DATE;
      8    vMatch NUMBER(4,0);
      9    vDescription VARCHAR2(100);
     10    vDetail NUMBER(2,0);
     11    vStockQuantity NUMBER(4,0);
     12  
     13    quantity_not_greater_than_zero EXCEPTION;
     14    stock_quantity_equals_zero EXCEPTION;
     15  
     16  
     17  BEGIN
     18    vCustid := &1;
     19    vOrderid := &2;
     20    vPartid := &3;
     21    vQuantity := &4;
     22  
     23    DBMS_OUTPUT.put_line(vCustid || ' ' || vOrderid || ' ' || vPartid || ' ' || vQuantity);
     24    BEGIN
     25    --Verify customer exists
     26    SELECT CUSTOMERS.CNAME
     27    INTO vCname
     28    FROM CUSTOMERS
     29    WHERE CUSTOMERS.CUSTID = vCustid;
     30  
     31    EXCEPTION
     32      WHEN NO_DATA_FOUND THEN
     33        DBMS_OUTPUT.put_line('An error was encountered verifying if customer exists - '||SQLERRM||'-'||SQLCODE);
     34        raise;
     35    END;
     36  
     37    BEGIN
     38    --Verify order exists
     39    SELECT ORDERS.SALESDATE
     40    INTO vSalesdate
     41    FROM ORDERS
     42    WHERE ORDERS.ORDERID = vOrderid;
     43  
     44    EXCEPTION
     45      WHEN NO_DATA_FOUND THEN
     46        DBMS_OUTPUT.put_line('An error was encountered verifying if order exists - '||SQLERRM||'-'||SQLCODE);
     47        raise;
     48    END;
     49  
     50    BEGIN
     51  
     52    --Verify custid matches with orderid
     53    SELECT ORDERS.ORDERID
     54    INTO vMatch
     55    FROM ORDERS
     56    WHERE ORDERS.ORDERID = vOrderid AND ORDERS.CUSTID = vCustid;
     57  
     58    EXCEPTION
     59      WHEN NO_DATA_FOUND THEN
     60        DBMS_OUTPUT.put_line('An error was encountered matching CUSTID with ORDERID - '||SQLERRM||'-'||SQLCODE);
     61        raise;
     62    END;
     63  
     64    BEGIN
     65  
     66    --Verify partid exists
     67    SELECT INVENTORY.DESCRIPTION
     68    INTO vDescription
     69    FROM INVENTORY
     70    WHERE INVENTORY.PARTID = vPartid;
     71  
     72    EXCEPTION
     73      WHEN NO_DATA_FOUND THEN
     74        DBMS_OUTPUT.put_line('An error was encountered verifying if part exists - '||SQLERRM||'-'||SQLCODE);
     75        raise;
     76    END;
     77  
     78    BEGIN
     79  
     80    --Test to ensure quantity is greater than zero
     81    IF vQuantity < 1 THEN
     82      RAISE quantity_not_greater_than_zero;
     83    END IF;
     84  
     85    EXCEPTION
     86      WHEN quantity_not_greater_than_zero THEN
     87        DBMS_OUTPUT.put_line('An error was encountered testing if quantity was greater than zero - '||SQLERRM||'-'||SQLCODE);
     88        raise;
     89    END;
     90  
     91  
     92    --Insert data into ORDERITEMS table
     93    BEGIN
     94    SELECT MAX(ORDERITEMS.DETAIL) + 1
     95    INTO vDetail
     96    FROM ORDERITEMS
     97    WHERE ORDERITEMS.ORDERID = vOrderid;
     98  
     99    IF vDetail IS NULL THEN
    100        vDetail := 1;
    101    END IF;
    102    END;
    103  
    104    INSERT INTO ORDERITEMS
    105    (ORDERID, DETAIL, PARTID, QTY)
    106    VALUES
    107    (vOrderid, vDetail, vPartid, vQuantity);
    108  
    109  
    110  
    111    --Update stock quantity in INVENTORY table
    112    UPDATE INVENTORY
    113    SET STOCKQTY = STOCKQTY - vQuantity
    114    WHERE PARTID = vPartid;
    115  
    116  
    117    --Check stock quantity to ensure it is not less than or equal to 0
    118    BEGIN
    119    SELECT INVENTORY.STOCKQTY
    120    INTO vStockQuantity
    121    FROM INVENTORY
    122    WHERE INVENTORY.PARTID = vPartid;
    123  
    124    IF vStockQuantity <= 0 THEN
    125      RAISE stock_quantity_equals_zero;
    126    END IF;
    127  
    128    EXCEPTION
    129      WHEN stock_quantity_equals_zero THEN
    130        DBMS_OUTPUT.put_line('An error was encountered - stock quantity is less than or equal to zero - '||SQLERRM||'-'||SQLCODE);
    131        DBMS_OUTPUT.put_line('Rolling back action... ');
    132        ROLLBACK;
    133        raise;
    134    END;
    135  
    136    --If successful display message and COMMIT
    137    DBMS_OUTPUT.put_line('Successful input!!!');
    138    DBMS_OUTPUT.put_line('Input data: ' || vCustid || ' - ' || vCname || ' - ' || vOrderid || ' - ' || vSalesdate || ' - ' || vPartid || ' - ' || vQuantity);
    139    COMMIT;
    140  
    141  END;
    142  /
    Enter VALUE FOR 1: 1
    OLD  18:   vCustid := &1;
    NEW  18:   vCustid := 1;
    Enter VALUE FOR 2: 1
    OLD  19:   vOrderid := &2;
    NEW  19:   vOrderid := 1;
    Enter VALUE FOR 3: 0
    OLD  20:   vPartid := &3;
    NEW  20:   vPartid := 0;
    Enter VALUE FOR 4: 1
    OLD  21:   vQuantity := &4;
    NEW  21:   vQuantity := 1;
    1 1 0 1                                                                        
    An error was encountered verifying IF part EXISTS - ORA-01403: no DATA found-100
    DECLARE
    *
    ERROR at line 1:
    ORA-01403: no DATA found
    ORA-06512: at line 75

    SQL>
     

    Yelling gets you nowhere here. If this doesn't work for you (as it did work for me) post your output, without yelling, and someone may assist you.
     
  12. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    sample output, it takes 4 parameters

    Code (Text):
    199 6099 1001 1
    An error was encountered verifying if customer exists - ORA-01403: no data found-100
    An error was encountered matching CUSTID with ORDERID - ORA-01403: no data found-100
    Successful input!!!
    Input data: 199 -  - 6099 - 15-DEC-95 - 1001 - 1
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Obviously you choose to ignore the assistance you've already been provided (as in my examples prior to this last post of yours). Let me run my modified version of your code, generating an error, so you can see it does NOT report success:


    Code (SQL):

    SQL>
    SQL> CREATE TABLE customers(
      2  custid  NUMBER,
      3  cname   varchar2(40));


    TABLE created.


    SQL>
    SQL> CREATE TABLE orders(
      2  orderid NUMBER,
      3  custid  NUMBER,
      4  salesdate      DATE);


    TABLE created.


    SQL>
    SQL> CREATE TABLE orderitems(
      2  orderid NUMBER,
      3  detail  NUMBER,
      4  partid  NUMBER,
      5  qty     NUMBER);


    TABLE created.


    SQL>
    SQL> CREATE TABLE inventory(
      2  partid  NUMBER,
      3  description varchar2(40),
      4  stockqty      NUMBER);


    TABLE created.


    SQL>
    SQL> BEGIN
      2        FOR i IN 1..100 loop
      3         INSERT INTO customers
      4         VALUES(i, 'CUSTOMER '||i);
      5         INSERT INTO orders
      6         VALUES(i, i, sysdate-i);
      7         INSERT INTO orderitems
      8         VALUES(i, MOD(i,11)+1, i, MOD(i, 17)+1);
      9         INSERT INTO inventory
     10         VALUES(i, 'PART '||i, MOD(i, 17)+1);
     11        END loop;
     12  
     13        commit;
     14  
     15  END;
     16  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> UPDATE inventory
      2  SET stockqty=0 WHERE partid=99;


    1 ROW updated.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> SET SERVEROUTPUT ON;
    SQL>
    SQL> DECLARE
      2    vCustid NUMBER(4,0);
      3    vOrderid NUMBER(4,0);
      4    vPartid NUMBER(4,0);
      5    vQuantity NUMBER(4,0);
      6    vCname VARCHAR2(100);
      7    vSalesdate DATE;
      8    vMatch NUMBER(4,0);
      9    vDescription VARCHAR2(100);
     10    vDetail NUMBER(2,0);
     11    vStockQuantity NUMBER(4,0);
     12    vExceptionCt NUMBER:=0;
     13  
     14    quantity_not_greater_than_zero EXCEPTION;
     15    stock_quantity_equals_zero EXCEPTION;
     16  
     17  
     18  BEGIN
     19    vCustid := &1;
     20    vOrderid := &2;
     21    vPartid := &3;
     22    vQuantity := &4;
     23  
     24    DBMS_OUTPUT.put_line(vCustid || ' ' || vOrderid || ' ' || vPartid || ' ' || vQuantity);
     25    BEGIN
     26    --Verify customer exists
     27    SELECT CUSTOMERS.CNAME
     28    INTO vCname
     29    FROM CUSTOMERS
     30    WHERE CUSTOMERS.CUSTID = vCustid;
     31  
     32    EXCEPTION
     33      WHEN NO_DATA_FOUND THEN
     34        DBMS_OUTPUT.put_line('An error was encountered verifying if customer exists - '||SQLERRM||'-'||SQLCODE);
     35        vExceptionCt:=vExceptionCt + 1;
     36    END;
     37  
     38    BEGIN
     39    --Verify order exists
     40    SELECT ORDERS.SALESDATE
     41    INTO vSalesdate
     42    FROM ORDERS
     43    WHERE ORDERS.ORDERID = vOrderid;
     44  
     45    EXCEPTION
     46      WHEN NO_DATA_FOUND THEN
     47        DBMS_OUTPUT.put_line('An error was encountered verifying if order exists - '||SQLERRM||'-'||SQLCODE);
     48        vExceptionCt:=vExceptionCt + 1;
     49    END;
     50  
     51    BEGIN
     52  
     53    --Verify custid matches with orderid
     54    SELECT ORDERS.ORDERID
     55    INTO vMatch
     56    FROM ORDERS
     57    WHERE ORDERS.ORDERID = vOrderid AND ORDERS.CUSTID = vCustid;
     58  
     59    EXCEPTION
     60      WHEN NO_DATA_FOUND THEN
     61        DBMS_OUTPUT.put_line('An error was encountered matching CUSTID with ORDERID - '||SQLERRM||'-'||SQLCODE);
     62        vExceptionCt:=vExceptionCt + 1;
     63    END;
     64  
     65    BEGIN
     66  
     67    --Verify partid exists
     68    SELECT INVENTORY.DESCRIPTION
     69    INTO vDescription
     70    FROM INVENTORY
     71    WHERE INVENTORY.PARTID = vPartid;
     72  
     73    EXCEPTION
     74      WHEN NO_DATA_FOUND THEN
     75        DBMS_OUTPUT.put_line('An error was encountered verifying if part exists - '||SQLERRM||'-'||SQLCODE);
     76        vExceptionCt:=vExceptionCt + 1;
     77    END;
     78  
     79    BEGIN
     80  
     81    --Test to ensure quantity is greater than zero
     82    IF vQuantity < 1 THEN
     83      RAISE quantity_not_greater_than_zero;
     84    END IF;
     85  
     86    EXCEPTION
     87      WHEN quantity_not_greater_than_zero THEN
     88        DBMS_OUTPUT.put_line('An error was encountered testing if quantity was greater than zero - '||SQLERRM||'-'||SQLCODE);
     89        vExceptionCt:=vExceptionCt + 1;
     90    END;
     91  
     92  
     93    --Insert data into ORDERITEMS table
     94    BEGIN
     95    SELECT MAX(ORDERITEMS.DETAIL) + 1
     96    INTO vDetail
     97    FROM ORDERITEMS
     98    WHERE ORDERITEMS.ORDERID = vOrderid;
     99  
    100    IF vDetail IS NULL THEN
    101        vDetail := 1;
    102    END IF;
    103    END;
    104  
    105    INSERT INTO ORDERITEMS
    106    (ORDERID, DETAIL, PARTID, QTY)
    107    VALUES
    108    (vOrderid, vDetail, vPartid, vQuantity);
    109  
    110  
    111  
    112    --Update stock quantity in INVENTORY table
    113    UPDATE INVENTORY
    114    SET STOCKQTY = STOCKQTY - vQuantity
    115    WHERE PARTID = vPartid;
    116  
    117  
    118    --Check stock quantity to ensure it is not less than or equal to 0
    119    BEGIN
    120    SELECT INVENTORY.STOCKQTY
    121    INTO vStockQuantity
    122    FROM INVENTORY
    123    WHERE INVENTORY.PARTID = vPartid;
    124  
    125    IF vStockQuantity <= 0 THEN
    126      RAISE stock_quantity_equals_zero;
    127    END IF;
    128  
    129    EXCEPTION
    130      WHEN stock_quantity_equals_zero THEN
    131        DBMS_OUTPUT.put_line('An error was encountered - stock quantity is less than or equal to zero - '||SQLERRM||'-'||SQLCODE);
    132        DBMS_OUTPUT.put_line('Rolling back action... ');
    133        ROLLBACK;
    134        vExceptionCt:=vExceptionCt + 1;
    135    END;
    136  
    137    --If successful display message and COMMIT
    138    IF vExceptionCt = 0 THEN
    139        DBMS_OUTPUT.put_line('Successful input!!!');
    140        DBMS_OUTPUT.put_line('Input data: ' || vCustid || ' - ' || vCname || ' - ' || vOrderid || ' - ' || vSalesdate || ' - ' || vPartid || ' - ' || vQuantity);
    141        COMMIT;
    142    END IF;
    143  
    144  END;
    145  /
    Enter VALUE FOR 1: 99
    Enter VALUE FOR 2: 99
    Enter VALUE FOR 3: 99
    Enter VALUE FOR 4: 99
    99 99 99 99                                                                    
    An error was encountered - stock quantity IS less than OR equal TO zero -      
    User-Defined Exception-1                                                        
    Rolling back action...                                                          


    PL/SQL PROCEDURE successfully completed.


    SQL>
     

    Note that an exception was thrown and no 'success' message appeared. Let's just see if it works for a successful run:


    Code (SQL):

    SQL>
    SQL> SET SERVEROUTPUT ON;
    SQL>
    SQL> DECLARE
      2    vCustid NUMBER(4,0);
      3    vOrderid NUMBER(4,0);
      4    vPartid NUMBER(4,0);
      5    vQuantity NUMBER(4,0);
      6    vCname VARCHAR2(100);
      7    vSalesdate DATE;
      8    vMatch NUMBER(4,0);
      9    vDescription VARCHAR2(100);
     10    vDetail NUMBER(2,0);
     11    vStockQuantity NUMBER(4,0);
     12    vExceptionCt NUMBER:=0;
     13  
     14    quantity_not_greater_than_zero EXCEPTION;
     15    stock_quantity_equals_zero EXCEPTION;
     16  
     17  
     18  BEGIN
     19    vCustid := &1;
     20    vOrderid := &2;
     21    vPartid := &3;
     22    vQuantity := &4;
     23  
     24    DBMS_OUTPUT.put_line(vCustid || ' ' || vOrderid || ' ' || vPartid || ' ' || vQuantity);
     25    BEGIN
     26    --Verify customer exists
     27    SELECT CUSTOMERS.CNAME
     28    INTO vCname
     29    FROM CUSTOMERS
     30    WHERE CUSTOMERS.CUSTID = vCustid;
     31  
     32    EXCEPTION
     33      WHEN NO_DATA_FOUND THEN
     34        DBMS_OUTPUT.put_line('An error was encountered verifying if customer exists - '||SQLERRM||'-'||SQLCODE);
     35        vExceptionCt:=vExceptionCt + 1;
     36    END;
     37  
     38    BEGIN
     39    --Verify order exists
     40    SELECT ORDERS.SALESDATE
     41    INTO vSalesdate
     42    FROM ORDERS
     43    WHERE ORDERS.ORDERID = vOrderid;
     44  
     45    EXCEPTION
     46      WHEN NO_DATA_FOUND THEN
     47        DBMS_OUTPUT.put_line('An error was encountered verifying if order exists - '||SQLERRM||'-'||SQLCODE);
     48        vExceptionCt:=vExceptionCt + 1;
     49    END;
     50  
     51    BEGIN
     52  
     53    --Verify custid matches with orderid
     54    SELECT ORDERS.ORDERID
     55    INTO vMatch
     56    FROM ORDERS
     57    WHERE ORDERS.ORDERID = vOrderid AND ORDERS.CUSTID = vCustid;
     58  
     59    EXCEPTION
     60      WHEN NO_DATA_FOUND THEN
     61        DBMS_OUTPUT.put_line('An error was encountered matching CUSTID with ORDERID - '||SQLERRM||'-'||SQLCODE);
     62        vExceptionCt:=vExceptionCt + 1;
     63    END;
     64  
     65    BEGIN
     66  
     67    --Verify partid exists
     68    SELECT INVENTORY.DESCRIPTION
     69    INTO vDescription
     70    FROM INVENTORY
     71    WHERE INVENTORY.PARTID = vPartid;
     72  
     73    EXCEPTION
     74      WHEN NO_DATA_FOUND THEN
     75        DBMS_OUTPUT.put_line('An error was encountered verifying if part exists - '||SQLERRM||'-'||SQLCODE);
     76        vExceptionCt:=vExceptionCt + 1;
     77    END;
     78  
     79    BEGIN
     80  
     81    --Test to ensure quantity is greater than zero
     82    IF vQuantity < 1 THEN
     83      RAISE quantity_not_greater_than_zero;
     84    END IF;
     85  
     86    EXCEPTION
     87      WHEN quantity_not_greater_than_zero THEN
     88        DBMS_OUTPUT.put_line('An error was encountered testing if quantity was greater than zero - '||SQLERRM||'-'||SQLCODE);
     89        vExceptionCt:=vExceptionCt + 1;
     90    END;
     91  
     92  
     93    --Insert data into ORDERITEMS table
     94    BEGIN
     95    SELECT MAX(ORDERITEMS.DETAIL) + 1
     96    INTO vDetail
     97    FROM ORDERITEMS
     98    WHERE ORDERITEMS.ORDERID = vOrderid;
     99  
    100    IF vDetail IS NULL THEN
    101        vDetail := 1;
    102    END IF;
    103    END;
    104  
    105    INSERT INTO ORDERITEMS
    106    (ORDERID, DETAIL, PARTID, QTY)
    107    VALUES
    108    (vOrderid, vDetail, vPartid, vQuantity);
    109  
    110  
    111  
    112    --Update stock quantity in INVENTORY table
    113    UPDATE INVENTORY
    114    SET STOCKQTY = STOCKQTY - vQuantity
    115    WHERE PARTID = vPartid;
    116  
    117  
    118    --Check stock quantity to ensure it is not less than or equal to 0
    119    BEGIN
    120    SELECT INVENTORY.STOCKQTY
    121    INTO vStockQuantity
    122    FROM INVENTORY
    123    WHERE INVENTORY.PARTID = vPartid;
    124  
    125    IF vStockQuantity <= 0 THEN
    126      RAISE stock_quantity_equals_zero;
    127    END IF;
    128  
    129    EXCEPTION
    130      WHEN stock_quantity_equals_zero THEN
    131        DBMS_OUTPUT.put_line('An error was encountered - stock quantity is less than or equal to zero - '||SQLERRM||'-'||SQLCODE);
    132        DBMS_OUTPUT.put_line('Rolling back action... ');
    133        ROLLBACK;
    134        vExceptionCt:=vExceptionCt + 1;
    135    END;
    136  
    137    --If successful display message and COMMIT
    138    IF vExceptionCt = 0 THEN
    139        DBMS_OUTPUT.put_line('Successful input!!!');
    140        DBMS_OUTPUT.put_line('Input data: ' || vCustid || ' - ' || vCname || ' - ' || vOrderid || ' - ' || vSalesdate || ' - ' || vPartid || ' - ' || vQuantity);
    141        COMMIT;
    142    END IF;
    143  
    144  END;
    145  /
    Enter VALUE FOR 1: 1
    Enter VALUE FOR 2: 1
    Enter VALUE FOR 3: 1
    Enter VALUE FOR 4: 1
    1 1 1 1                                                                        
    Successful INPUT!!!                                                            
    INPUT DATA: 1 - CUSTOMER 1 - 1 - 10-FEB-14 - 1 - 1                              


    PL/SQL PROCEDURE successfully completed.


    SQL>
     

    And it does. Read through the code I just posted to see what I did to make this happen. Try this again using the example code I just gave you (you didn't with this last post of yours so I'm not surprised it didn't work as you want it to).
     
  14. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

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

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Why the attitude?

    I wasn't yelling as I used no exclamation marks; I was emphasizing my statements so they would be more clearly understood. There IS a difference.

    Thanks for the help???????????
     
  16. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    On-line using all capitals is considered yelling, especially in forums such as this. There is no 'attitude', it is frustration. Your previous post, showing the output from your original code, indicated, to me, anyway, that you either didn't read the previous examples or didn't understand them. I can accept the latter as that case usually generates more questions rather than silence.


    There are different modifications of your originally posted code that do what you want -- provide the output generated by the exceptions without showing success, and successful runs. Remember that the members in this forum take their spare time and contribute.


    If you're sincere with regard to the thanks, you're welcome.