Microsoft Enterprise Library Data Access Block [DAAB] on Oracle Provider [ODP.NET]

CREATE TABLE DAABCUSTOMER
(
  DAABCUSTOMERID   INTEGER,
  DAABCONTACTNAME  VARCHAR2(100),
  DAABADDRESS      VARCHAR2(100),
  DAABCITY         VARCHAR2(100),
  DAABCOUNTRY      VARCHAR2(100),
  DAABPOSTCODE     VARCHAR2(10),
  CONSTRAINT DAABCUSTOMER_PK
 PRIMARY KEY
 (DAABCUSTOMERID)
)
LOGGING 
NOCACHE
NOPARALLEL;


CREATE OR REPLACE PACKAGE SSM_CS.PKG_DAABCUSTOMER
-- Package Definition Begin
AS

--/ Application                    :DEMO DAAB OF MICROSOFT ENT LIBRARY                  :
--/ Author                          :MARTIN ADAIKALAM

TYPE CURSOR_RESULTS IS REF CURSOR;

PROCEDURE P_DAABCustomerByPostcode  (
                                        I_Reference IN  VARCHAR2, 
                                        O_Results   OUT CURSOR_RESULTS
                                    );
PROCEDURE P_DAABCustomerDetails  (
                                        I_Reference         IN      INT, 
                                        O_CustomerID        OUT     INT,
                                        O_CustomerName      OUT     VARCHAR2                                        
                                    );
                                    
PROCEDURE P_DAABCustomerName     (
                                        I_Reference         IN      INT,
                                        O_CustomerName      OUT     VARCHAR2                                        
                                    );

PROCEDURE P_DAABMultipleCursor     (
                                        I_Reference             IN      VARCHAR2,
                                        O_PostcodePresent       OUT     CURSOR_RESULTS,
                                        O_PostcodeNotPresent    OUT     CURSOR_RESULTS                                                                                
                                    );

END PKG_DAABCUSTOMER;
/





CREATE OR REPLACE PACKAGE BODY SSM_CS.PKG_DAABCUSTOMER
AS

-- Procedure  P_DAABCustomerByPostcode
    PROCEDURE P_DAABCustomerByPostcode (
                                    I_Reference IN VARCHAR2, 
                                    O_Results OUT CURSOR_RESULTS
                                    )  IS
    BEGIN

        OPEN O_Results 
                FOR      
                Select      DAABCUSTOMERID CustomerID, 
                            DAABCONTACTNAME Name, 
                            DAABADDRESS Address, 
                            DAABCITY City, 
                            DAABCOUNTRY Country, 
                            DAABPOSTCODE PostalCode 
                From        DAABCUSTOMER
                WHERE 
                            DAABCUSTOMER.DAABPOSTCODE = I_Reference;
      
        EXCEPTION
         WHEN OTHERS THEN
          raise_application_error(-20000,  'Unexpected error:' || sqlerrm);
      
    END P_DAABCustomerByPostcode;


    -- Procedure  P_DAABCustomerDetails
    PROCEDURE P_DAABCustomerDetails (
                                        I_Reference         IN      INT, 
                                        O_CustomerID        OUT     INT,
                                        O_CustomerName      OUT     VARCHAR2)   IS   

       V_CUSTOMER DAABCUSTOMER%ROWTYPE;
    BEGIN      
       
       Select       *  
                    INTO
                    V_CUSTOMER                                                 
        From        
                    DAABCUSTOMER
        WHERE 
                    DAABCUSTOMER.DAABCUSTOMERID = I_Reference;      
    
        O_CustomerID    := V_CUSTOMER.DAABCUSTOMERID;
        O_CustomerName  := V_CUSTOMER.DAABCONTACTNAME;        
        
        EXCEPTION
         WHEN OTHERS THEN
          raise_application_error(-20000,  'Unexpected error:' || sqlerrm);
      
    END P_DAABCustomerDetails;
    
    
    
    PROCEDURE P_DAABCustomerName     (
                                        I_Reference         IN      INT,
                                        O_CustomerName      OUT     VARCHAR2                                        
                                    ) IS
    V_CUSTOMER DAABCUSTOMER%ROWTYPE;
    BEGIN      
       
       Select       *  
                    INTO
                    V_CUSTOMER                                                  
        From        
                    DAABCUSTOMER
        WHERE 
                    DAABCUSTOMER.DAABCUSTOMERID = I_Reference;
                    
        O_CustomerName  := V_CUSTOMER.DAABCONTACTNAME;            
        
        EXCEPTION
         WHEN OTHERS THEN
          raise_application_error(-20000,  'Unexpected error:' || sqlerrm);
      
    END P_DAABCustomerName;
    
    
    -- Procedure  P_DAABMultipleCursor
    PROCEDURE P_DAABMultipleCursor (
                                    I_Reference IN VARCHAR2, 
                                    O_PostcodePresent       OUT     CURSOR_RESULTS,
                                    O_PostcodeNotPresent    OUT     CURSOR_RESULTS                                                                                
                                    )  IS
    BEGIN

        OPEN O_PostcodePresent 
                FOR      
                Select      DAABCUSTOMERID CustomerID, 
                            DAABCONTACTNAME Name, 
                            DAABADDRESS Address, 
                            DAABCITY City, 
                            DAABCOUNTRY Country, 
                            DAABPOSTCODE PostalCode 
                From        DAABCUSTOMER
                WHERE 
                            DAABCUSTOMER.DAABPOSTCODE = I_Reference;
                            
         OPEN O_PostcodeNotPresent 
                FOR      
                Select      DAABCUSTOMERID CustomerID, 
                            DAABCONTACTNAME Name, 
                            DAABADDRESS Address, 
                            DAABCITY City, 
                            DAABCOUNTRY Country, 
                            DAABPOSTCODE PostalCode 
                From        DAABCUSTOMER
                WHERE 
                            DAABCUSTOMER.DAABPOSTCODE != I_Reference;
      
        EXCEPTION
         WHEN OTHERS THEN
          raise_application_error(-20000,  'Unexpected error:' || sqlerrm);
      
    END P_DAABMultipleCursor;
                                    
                                    

END PKG_DAABCUSTOMER;
/