Customize PL/SQL exceptions in Oracle

Question

Frequently I found myself doing some functions to insert/delete/update in one or more tables and I've seen some expected exceptions been taken care of, like no_data_found, dupl_val_on_index, etc. For an insert like this:

create or replace FUNCTION          "INSERT_PRODUCTS" (

  a_supplier_id IN FORNECEDOR.ID_FORNECEDOR%TYPE,

  a_prodArray IN OUT PRODTABLE

  )

 RETURN NUMBER IS

    v_error_code    NUMBER;
    v_error_message VARCHAR2(255);
    v_result        NUMBER:= 0;
    v_prod_id       PRODUTO.ID_PROD%TYPE;
    v_supplier      FORNECEDOR%ROWTYPE;
    v_prodInserted  PROD_OBJ;
    newList prodtable := prodtable();

 BEGIN

 SELECT  FORNEC_OBJ(ID_FORNECEDOR,NOME_FORNECEDOR,MORADA,ARMAZEM,EMAIL,TLF,TLM,FAX) into v_supplier from fornecedor where id_fornecedor = a_supplier_id;

  FOR i IN a_prodArray.FIRST .. a_prodArray.LAST LOOP

      INSERT INTO PRODUTO (PRODUTO.ID_PROD,PRODUTO.NOME_PROD,PRODUTO.PREC_COMPRA_PROD,PRODUTO.IVA_PROD,PRODUTO.PREC_VENDA_PROD,PRODUTO.QTD_STOCK_PROD,PRODUTO.QTD_STOCK_MIN_PROD)
      VALUES (S_PRODUTO.nextval,a_prodArray(i).NOME_PROD,a_prodArray(i).PREC_COMPRA_PROD,a_prodArray(i).IVA_PROD,NULL,NULL,NULL);

      /* If the above insert didn't failed, we can insert in weak entity PROD_FORNECIDO. */
      SELECT ID_PROD into v_prod_id from PRODUTO where NOME_PROD = a_prodArray(i).NOME_PROD;

      INSERT INTO PROD_FORNECIDO VALUES (a_supplier_id, v_prod_id,a_prodArray(i).PREC_COMPRA_PROD);

      SELECT PROD_OBJ(ID_PROD,NOME_PROD,PREC_COMPRA_PROD,PREC_VENDA_PROD,QTD_STOCK_PROD,QTD_STOCK_MIN_PROD,IVA_PROD) into v_prodInserted from PRODUTO where ID_PROD= v_prod_id;
      a_prodarray(i).ID_PROD := v_prod_id;

    END LOOP;

    INSERT INTO FORNECPRODS VALUES (a_supplier_id,v_supplier, a_prodarray);
    v_result:= 1;
    RETURN v_result;
    COMMIT;

Exception

When no_data_found then
    v_error_code := 0;
    v_error_message:= 'Insert Products: One of selects returned nothing';
    Insert Into errors Values (v_error_code,v_error_message, systimestamp);
    RETURN  v_result;

 When others Then
    ROLLBACK;
    v_error_code := SQLCODE;
    v_error_message:=substr(SQLERRM,1,50);
    Insert Into errors Values (v_error_code,'Error inserting products list',systimestamp);
    RETURN  v_result;

END;

I would like to customize more of my exceptions or do an exception block for each select/insert. Is that possible or correct?

If so, could please show me some code with important exceptions being throwed by this function?

Answer 1

If you just want to substitute your own error message, there is RAISE_APPLICATION_ERROR...

When no_data_found then
    RAISE_APPLICATION_ERROR(-20000
                             , 'Insert Products: One of selects returned nothing';
                             , true);

The third parameter returns the original error as well as your custom one.

Oracle also gives us the option to define our exceptions. This can be useful if we want to pass the exception to a calling program...

Declare
    no_product_found exception;
Begin
    ....
When no_data_found then
    raise no_product_found;

This would be most effective if we defined the NO_PRODUCT_FOUND exception in a package specification where it could be referenced by external program units.

In addition, Oracle provides the INIT_EXCEPTION pragma which allows us to associate Oracle error numbers with our custom exceptions. Unfortunately we cannot overload error numbers which Oracle has already defined (for instance, we cannot create our own exceptions for ORA-1403 which is already covered by the NO_DATA_FOUND exception). Find out more.

Answer 2

1- I saw that raise application error, but for my case, is simpler and better to return 0 if the functions fails to insert, or fornecimento_id (i.e.,suppliement_id) otherwise. Then, I pass this result to JAVA and there I'll handle as I want. So, in this code, I'm doing something wrong? For example, if the first insert fails, I think it skips all the other instructions, because I always catch some exception, right? In others words, I don't want it to execute the next instruction if a prior fails.

create or replace function insert_supplierOrder (a_employee_id IN FORNECIMENTO.ID_PESSOA%TYPE, 
    a_supplier_id IN FORNECIMENTO.ID_FORNECEDOR%TYPE,
    a_prodId IN ITEM_FORNECIMENTO.ID_PROD%TYPE,
    a_prodQtd IN ITEM_FORNECIMENTO.QTD_IF%TYPE, 
    a_total_price IN ITEM_FORNECIMENTO.PREC_TOTAL_IF%TYPE) RETURN FORNECIMENTO.ID_FORNECIMENTO%TYPE
        IS 
          v_order_id    FORNECIMENTO.ID_FORNECIMENTO%TYPE := 0;
          v_error_code    NUMBER;
          v_error_message VARCHAR2(255);


        BEGIN
            INSERT INTO fornecimento(id_fornecimento, id_fornecedor, id_pessoa, data_encomenda, data_entrega, total_enc_fornec)
            values (v_order_id, a_supplier_id, a_employee_id, to_date(sysdate,'dd-MM-YYYY'),null,null);
            /*if it has inserted, retrieve the last inserted id from table fornecimento*/
            select id_fornecimento into v_order_id from fornecimento where id_fornecimento = (select max(id_fornecimento) from fornecimento);  
            /*if select doesn't fail, we can go on inserting to the weak entity*/
            INSERT INTO item_fornecimento values (v_order_id, a_prodId, a_prodQtd, a_total_price); 
            return v_order_id;
            COMMIT;

          EXCEPTION
            When dup_val_on_index Then
             Insert INTO errors Values (v_error_code,'Insert Order failed: Duplicated supplier name',systimestamp);
             RETURN  v_order_id;

             When no_data_found Then
             INSERT INTO errors Values (v_error_code,'SELECT: Failed to retrieve fornecimento ID from Fornecimento',systimestamp);
             RETURN v_order_id;

              When others Then 
              v_error_code := SQLCODE;
              v_error_message:=substr(SQLERRM,1,50);
              Insert Into errors Values (v_error_code,v_error_message,systimestamp);
              RETURN  v_order_id;
        END;

2- I also stay in doubt in what concerns to doing or not rollback on those exceptions; when I did a rollback on dupl_val_on_index, the function deleted my previous inserted supllier order! I noticed that, in java side, I was doing something like this:

//CallabbleStatement executing an insert, delete or update, set in parameters, out and
(...)
            this.dbConn.commit(); //oracleConnection= dbConn
            this.callstmt.close();
            this.dbConn.close();
            return true;
        } catch (SQLException x) {
            try {
                this.dbConn.rollback();
                return false;
            } catch (SQLException ex) {
                return false;
            }

So, When I have my own plsql function doing some begin-commit transaction (but not rollback, because I don't kwnow If I need it) I think it's redundant making the same in Java! If I set autocommit to false, in java, maybe it won't be any inconsistency. What advice to you have for me? I'm all ears :)

Answer 3

• In the exception section; you can raise application error or return 0 with error code explanation. It is about your choice.

• If you want to log your errors in the exception section (or in main section), write your own logging procedure with AUTONOMOUS TRANSACTION. so, your logging mechanism is not affected by your main transaction's COMMIT or ROLLBACK. (see: http://www.dba-oracle.com/t_autonomous_transaction.htm)

• Another logging mechanism (DML Error Log) in Oracle 10gR2 (and above) is LOG ERRORS clause (see: http://www.oracle-base.com/articles/10g/DmlErrorLogging_10gR2.php).