Home » SQL & PL/SQL » SQL & PL/SQL » Passing warning collection from PL/SQL (11.0.2.10 )
Passing warning collection from PL/SQL [message #666648] Sun, 19 November 2017 03:38 Go to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi all,

In the example below I am trying to control the way warnings, and error message are returned from PL SQL layer to the called application (a .net application).
I have the following questions that need expert opinion:
1- I thought that collecting all warnings in a single refcursor would be the right way. What can be a better approach from performance,
code readability and code efficiency points of view?
2- If this approach is fine, how can it be used in a more efficient way? Is there a way to collect several varchar variables in a ref cursor in a better way?
3- I tried to create a function that would be called using warning varchar variables and this function encapsulates the code to return a single refcursor with all the warnings, but the problem is that the number of warnings is variable. Thought of using array of varchar2 and then pass the array to a function:
-- something like:
    TYPE array_warn IS TABLE OF varchar2 INDEX BY number;
    function append_warning(arWarning in array_warn) return refcursor;
but not sure if this is too much or worth the hassle of collecting all warnings in an array and then loop inside the function and then return a refcursor. What do you think knowing that returning warnings will be very common pl sql layer and in more than one package?
4- the no_data_found exception returns the error varchar, no need for a collection of errors as there can only be 1. Is there any comments such as refusing this technique and leaving the error reporting to normal oracle exception? or maybe raise an application error?
5- finally, I know this is a .net (windows forum) question, but can array be passed instead of refcursor to .net?
Any other general comments or suggestions are highly welcomed.

create table test_table ( id number primary key, name varchar2(50));

CREATE OR REPLACE Package Proce_Params_Array As
    Type T_FieldValue Is Table Of Proce_Params_Table.FieldValue%Type Index By Pls_Integer;
End Proce_Params_Array;

CREATE OR REPLACE PACKAGE test_pckg AS

   -- Add a Withdrawal in follow up stage
   PROCEDURE P_ADD_test(
                              ARRAY_INPARAMS  PROCE_PARAMS_ARRAY.T_FIELDVALUE,
                              S_APP_USER  VARCHAR2,
                              S_MODULE_NAME VARCHAR2,
                              CUR_WARNING OUT SYS_REFCURSOR,
                              S_ERROR out varchar2,
                              I_SERIAL_OUT OUT NUMBER
                            );

END test_pckg;

CREATE OR REPLACE PACKAGE BODY test_pckg AS

    PROCEDURE P_ADD_test(ARRAY_inParams        Proce_Params_Array.T_FieldValue,
                             S_APP_USER  varchar2,
                             S_MODULE_NAME varchar2,
                             CUR_WARNING OUT SYS_REFCURSOR,
                             S_ERROR out varchar2,
                             I_SERIAL_OUT OUT NUMBER
      )
    /* 
    This Stored Procedure is designed to add a new record and return a cursor with possible warnings in addition to 
      the serial number of created record in case of success
      */           
    AS

        S_EXIST         VARCHAR2(100);
        S_AGR_EFFECTIVE VARCHAR2(100);
        S_CNT_SUSPEND   VARCHAR2(100);
    BEGIN
    
        --0.1- save function parameters for logging and variable initiation
        -- removed
       -- 0.2- warning check and warning messages array formation 
          --0.2.1- If same amount and currency previously added to the same agreement
          select decode(count(*), 0, '', count(*)) into S_EXIST  from dual where sysdate = sysdate -1;

          IF S_EXIST IS NOT NULL THEN 
            S_EXIST := 'warning 1';
          END IF;        

          --0.2.2- If AGREEMENT effectiveness date is  null
          select decode(count(*), 0, '', count(*)) into S_AGR_EFFECTIVE from dual where sysdate = sysdate ;
    
          IF S_AGR_EFFECTIVE IS NOT NULL THEN 
            S_AGR_EFFECTIVE := 'warning2.' ;
          END IF;

          --0.2.3- If country suspended, user should see a warning    
          select decode(count(*), 0, '', count(*)) into S_CNT_SUSPEND from dual where 1 = 2 ;
    

          IF S_CNT_SUSPEND is not null THEN 
            S_CNT_SUSPEND := 'warning 3' ;
          END IF;

          --0.2.4- return the warning cursor
          OPEN CUR_WARNING FOR SELECT S_EXIST WARNING FROM DUAL UNION SELECT S_AGR_EFFECTIVE WARNING FROM DUAL UNION SELECT S_CNT_SUSPEND WARNING FROM DUAL;
                                   
       -- 0.3- Calling an SQL statement that adds a new  RECORD and returns the ID .
       INSERT INTO test_table
                (ID, name)
       VALUES(TO_NUMBER(ARRAY_INPARAMS(1)),TO_DATE(ARRAY_INPARAMS(2)))
    	 RETURNING NVL(id,-1) INTO I_SERIAL_OUT
      ;
       
       -- 0.5- Log the event
    
       EXCEPTION 
        WHEN NO_DATA_FOUND THEN
          S_ERROR := 'Invalid inserted data, no data found to insert';
          raise;
        WHEN OTHERS THEN
            --fill error varchar
            -- removed
            -- call P_LOG_ERROR to log the exception error to event log table
            -- removed
            -- raise exception for caller
        RAISE;	
    END P_ADD_test;



END test_pckg;

Re: Passing warning collection from PL/SQL [message #666654 is a reply to message #666648] Sun, 19 November 2017 09:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Have you tried the dbms_utility.format_error_stack function?
Re: Passing warning collection from PL/SQL [message #666657 is a reply to message #666654] Sun, 19 November 2017 22:27 Go to previous message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Dear Barbara,

Thanks for your reply. Regarding format error stack, I use functions (the removed part) to access v$session to get the last executed SQL statement and the user and log them into DB log and error table.
I am very much concerned about the warning technique I am suggesting so it will be great if you can give me your feedback about that as well.

Thanks,
Ferro
Previous Topic: ora 01882
Next Topic: Query Analysis
Goto Forum:
  


Current Time: Thu Mar 28 16:40:13 CDT 2024