3 Replies Latest reply on Feb 15, 2013 1:39 PM by kendenny

    %ROWTYPE function

    G.Y
      Dear all
      Below is my Function code, It is compiled successfully.
      create or replace
      FUNCTION ITEM_DESC (ITM_CODE IMS.ITEMS.ITEM_CODE%TYPE) RETURN
      IMS.ITEMS%ROWTYPE IS
      REC ITEMS%ROWTYPE;
      BEGIN
      SELECT ITEM_DESC INTO REC
      FROM IMS.ITEMS
      WHERE ITEM_CODE = ITM_CODE;
      RETURN REC;
      EXCEPTION WHEN NO_DATA_FOUND THEN
      RETURN NULL;
      END;

      now I want to used it in SQL query, I want to select only one coloumsn (item_description)
      how it could be please guide.

      thanks
        • 1. Re: %ROWTYPE function
          Karthick2003
          Please post only formatted code and use {noformat}
          {noformat} tag to preserve your code format.
          
          Below is your code
          create or replace function item_desc
          (
          itm_code ims.items.item_code%type
          ) return ims.items%rowtype
          is
          rec items%rowtype;
          begin
          select item_desc into rec
          from ims.items
          where item_code = itm_code;

          return rec;
          exception when no_data_found then
          return null;
          end;
          You have defined rec as a record type of ITEMS and you are just selecting a single column ITEM_DESC of ITEMS into it, Why?
          
          Why do you need a record type then. Just define a variable of type ITEM_DESC and return it.
          
          Or better tell what your actual problem is and the objective behind this function.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
          • 2. Re: %ROWTYPE function
            stefan nebesnak
            Ghulam Yassen wrote:

            now I want to used it in SQL query, <font style="color: green; background-color: #ffff42">I want to select only one coloumsn (item_description)</font>
            how it could be please guide.
            Try this:
            (I assumed you mean collection of item_desc. ツ)
            create or replace type rec_id as table of varchar2(2000);
            --/
            
            create or replace function item_desc
            (
              itm_code ims.items.item_code%type;
            )
            return rec_id
            is
            
            l_rec_id rec_id;
            
            cursor c_id(in_itm_code in ims.items.item_code%type)
            is
               select item_desc --varchar2(2000)
                 from ims.items
                where item_code = in_itm_code;
            
            begin
            
            open c_id(itm_code);
            fetch c_id bulk collect into l_rec_id;
            close c_id;
            
            return l_rec_id;
            exception when no_data_found then
               return null;
            end;
            --/
            Output:
            SQL> select item_desc(123) from dual;
             
            ITEM_DESC(123)
            ---------------------
            <Object>
            -----
            Edited by: stefan nebesnak on Feb 15, 2013 5:38 AM

            Or you can use this:
            select t.column_value from table(item_desc(123)) t;
            .       COLUMN_VALUE
            1     ABC
            2     DEFG
            3     HI
            • 3. Re: %ROWTYPE function
              kendenny
              Ghulam Yassen wrote:
              Dear all
              Below is my Function code, It is compiled successfully.
              create or replace
              FUNCTION ITEM_DESC (ITM_CODE IMS.ITEMS.ITEM_CODE%TYPE) RETURN
              IMS.ITEMS%ROWTYPE IS
              REC ITEMS%ROWTYPE;
              BEGIN
              SELECT ITEM_DESC INTO REC
              FROM IMS.ITEMS
              WHERE ITEM_CODE = ITM_CODE;
              RETURN REC;
              EXCEPTION WHEN NO_DATA_FOUND THEN
              RETURN NULL;
              END;

              now I want to used it in SQL query, I want to select only one coloumsn (item_description)
              how it could be please guide.

              thanks
              If you're going to return items%rowtype then you need to select all the columns from items.
              If you're going to return only the item_desc then you need to return items.item_desc%type instead of items%rowtype
              So either
              FUNCTION ITEM_DESC (ITM_CODE IMS.ITEMS.ITEM_CODE%TYPE) RETURN
                IMS.ITEMS.ITEM_DESC%TYPE IS
                REC ITEMS.ITEM_DESC%TYPE;
              BEGIN
                SELECT ITEM_DESC INTO REC
                  FROM IMS.ITEMS
                 WHERE ITEM_CODE = ITM_CODE;
                RETURN REC;
              EXCEPTION 
                WHEN NO_DATA_FOUND THEN
                  RETURN NULL;
              END;
              Or
              FUNCTION ITEM_DESC (ITM_CODE IMS.ITEMS.ITEM_CODE%TYPE) RETURN
                IMS.ITEMS%ROWTYPE IS
                REC ITEMS%ROWTYPE;
              BEGIN
                SELECT * INTO REC
                  FROM IMS.ITEMS
                 WHERE ITEM_CODE = ITM_CODE;
                RETURN REC;
              EXCEPTION 
                WHEN NO_DATA_FOUND THEN
                  RETURN NULL;
              END;