9 Replies Latest reply: Jul 8, 2014 7:50 AM by kendenny RSS

    How can I return values in rows and columns from a ref cursor

    LuKKa

      Hi All

      I have a function in which I am using ref cursor and it is returning values in 1 row and 1 column .

      But in this I have a set of rows.

      means :- the value is coming like the below:-

      
      {<RNK=1,ITEM=40250247,TYPE=21963,DATE1=null,DATE1=30-JUN-14 12:52:50 PM,NAME1=HU
      AM>,<RNK=1,ITEM=40250247,TYPE=21963,DATE1=null,DATE2=30-JUN-14 12:52:50 PM,NAME1=NO
      AM>,<RNK=1,ITEM=40250247,TYPE=21963,DATE1=null,DATE2=30-JUN-14 12:52:50 PM,NAME1=LV
      AM>,<RNK=1,ITEM=40250247,TYPE=21963,DATE1=null,DATE2=30-JUN-14 12:52:50 PM,NAME1=DE>,}
      

       

       

      So how can I return this value in rows and columns ?

        • 1. Re: How can I return values in rows and columns from a ref cursor
          kendenny

          open mycursor for

          select rnk, item, type, date1, date2, name1

            from table_name

          where <conditions>;

          • 2. Re: How can I return values in rows and columns from a ref cursor
            LuKKa

            Hi,

            There is no table . The above value comes from a function .

            So what should I write FROM clause ?

            • 3. Re: How can I return values in rows and columns from a ref cursor
              odie_63

              Who's producing this content? Third party app?

               

              What's the datatype? VARCHAR2, CLOB, other?

               

              Anyway, you're probably going to have to write a custom parsing program (using string manipulation functions) to read the content into separate relational rows and columns.

              • 4. Re: How can I return values in rows and columns from a ref cursor
                kendenny

                Then the function needs to change. If you can't change the function then you need another function to reformat the output of the first function.

                • 5. Re: How can I return values in rows and columns from a ref cursor
                  LuKKa
                  Hi All,
                   Below is my Function . Here it is rerurning the above value .
                  
                   
                   
                   
                   
                   CREATE OR REPLACE FUNCTION get_val(i_item varchar2(30),
                                                               i_item_code varchar2(20))
                  RETURN SYS_REFCURSOR
                  IS
                  REF_data   SYS_REFCURSOR;
                  BEGIN
                      FOR I in 1..10 LOOP
                        OPEN REF_HIST FOR
                          SELECT /*+ PARALLEL */ * 
                          FROM (
                             
                  SELECT ROW_NUMBER()
                              over ( partition by a.item, a.item_code, a.date1, a.date2, a.desc1, a.date3 order by a.ITEM, a.item_code) RNK,
                              a.item, a.item_code, a.date1, a.date2, a.desc1, a.date3 FROM tab1 a, tab2 b 
                              where a.ITEM = B.ITEM 
                              AND   a.item_code    = i_item_code
                              ) WHERE rnk <= l_rnk;
                    END LOOP;
                     END IF;
                    RETURN REF_data;
                     
                  EXCEPTION
                    WHEN OTHERS THEN
                     RETURN NULL;
                  END;
                  
                  • 6. Re: How can I return values in rows and columns from a ref cursor
                    odie_63

                    means :- the value is coming like the below:-

                    {<RNK=1,ITEM=40250247,TYPE=21963,DATE1=null,DATE1=30-JUN-14 12:52:50 PM,NAME1=HU
                    AM>,<RNK=1,ITEM=40250247,TYPE=21963,DATE1=null,DATE2=30-JUN-14 12:52:50 PM,NAME1=NO
                    AM>,<RNK=1,ITEM=40250247,TYPE=21963,DATE1=null,DATE2=30-JUN-14 12:52:50 PM,NAME1=LV
                    AM>,<RNK=1,ITEM=40250247,TYPE=21963,DATE1=null,DATE2=30-JUN-14 12:52:50 PM,NAME1=DE>,}

                    Right, I thought the column returned by the ref cursor contained the above string-like content, but after seeing the function I was mistaken.

                     

                    It's just the tool you are using to "view" the output that processes the ref cursor for you and format the resultset like that. It's very misleading.

                     

                    So how can I return this value in rows and columns ?

                    It already does return a ref cursor, so it's up to you or another consumer to process as you like and close it afterwards.

                     

                    PL/SQL 101 : Understanding Ref Cursors

                    • 7. Re: How can I return values in rows and columns from a ref cursor
                      LuKKa

                      Now my requirement is I am creating a view and in that view I have 2 select statements with  UNION .

                      I want to use this function in another UNION , so can you please tell me how can I mention the column name s and all ?

                       

                      Example :-

                       

                      Select col1,col2,col3,col4,col5 from Table1;

                      UNION

                      Select col1,col2,col3,col4,col5 from Table2;

                      UNION

                      Here I want to use that function ,Please tell me how ?

                      • 8. Re: How can I return values in rows and columns from a ref cursor
                        odie_63

                        LuKKa wrote:

                         

                        Now my requirement is I am creating a view and in that view I have 2 select statements with  UNION .

                        I want to use this function in another UNION , so can you please tell me how can I mention the column name s and all ?

                         

                        Example :-

                         

                        Select col1,col2,col3,col4,col5 from Table1;

                        UNION

                        Select col1,col2,col3,col4,col5 from Table2;

                        UNION

                        Here I want to use that function ,Please tell me how ?

                        You cannot, at least not directly.

                        Read the link I posted above.

                         

                        What you can do is process the ref cursor in a pipelined function that'll output relational rows readily available to the SQL engine.

                        But it would be pointless, why not just make the first function pipelined then and remove the ref cursor stage?

                         

                        If your goal is to have a reusable module then the best way would be to wrap the query in a view and use it wherever needed.

                         

                        CREATE OR REPLACE VIEW my_view AS

                        SELECT row_number() over (...) as rnk

                             , ...

                        FROM tab1, ...

                        ;

                        • 9. Re: How can I return values in rows and columns from a ref cursor
                          kendenny

                          Wow that's a seriously messed up function. Who wrote that? Why the i in 1..10 loop? It does the same thing 10 times just to be sure? Also why do you pass it i_item as that parameter is never used. Also the ref cursor returned by this function does not return a single column as claimed, it returns multiple columns: rnk., item, item_code, date1, date2, desc1, and date3. It most likely returns multiple rows as well. But there's no way the ref cursor from this function returns the data all in one row and column.