TYPE product_type IS RECORD (      
product_id  NUMBER,      
Product_name      VARCHAR2(50),      
product_stat      VARCHAR2(50),      
product_type      VARCHAR2(50),      
product_uom       NUMBER);

FUNCTION product_info_typ(p_att1 IN VARCHAR2,p_att2 IN VARCHAR2)
 RETURN product_type IS

  CURSOR c_prod IS
    SELECT product_id,
      Product_name,
           product_stat,
           product_type,
           product_uom 
    FROM prod_table
   WHERE attribute1       = p_att1 
AND  attribute2       = p_att2 ;

  l_prod_typ     product_type;
 BEGIN
   l_prod_typ := NULL;
   OPEN c_prod ;
   FETCH c_prod INTO     l_prod_typ.product_id,
                         l_prod_typ.Product_name,
                         l_prod_typ.product_stat,
                         l_prod_typ.product_type,
                         l_prod_typ.product_uom ;
   CLOSE c_prod ;
  RETURN l_prod_typ;
   END product_info_typ;

SET SERVEROUTPUT ON;

ALTER SESSION SET CURRENT_SCHEMA = fixxxer;

DECLARE
  p_att1    VARCHAR2(200);
  p_att2    VARCHAR2(200);
  v_return  TEST_TYPE_PKG.PRODUCT_TYPE;
BEGIN
  p_att1 := 'fixxxer';
  p_att2 := 'fixxxer';

  v_return := TEST_TYPE_PKG.PRODUCT_INFO_TYP(p_att1 => p_att1,
                                             p_att2 => p_att2);
  
  DBMS_OUTPUT.PUT_LINE(v_return.product_name);
END;

CREATE OR REPLCAE
TYPE product_type IS OBJECT (      
product_id  NUMBER,      
Product_name      VARCHAR2(50),      
product_stat      VARCHAR2(50),      
product_type      VARCHAR2(50),      
product_uom       NUMBER);