8 Replies Latest reply: Jan 7, 2014 6:32 AM by Billy~Verreynne RSS

    Join of cursor with table

    Subhash Pophale


      Hi experts,

       

      Oracle Version: 11GR2

       

      I get dataset in the ref cursor as an out parameter from a procedure. Now, I have to derive column "NAME" from table "TEST" using ID as a join key between cursor and a table.

      How can we do this in oracle?

       

       

      Thanks

        • 1. Re: Join of cursor with table
          Karthick_Arp


          You cant join a cursor to a table. cursor is a SQL work area where instruction to execute a SQL is processed.

           

          I could think of three approaches

           

          1. The best way is to include the table "TEST" in the original SQL that builds the cursor.

           

          2. If that is not possible then you need to fetch them (may be) into a SQL collection type and then join the table "TEST" with the collection type.

           

          3. Or you may have to loop thorough the cursor and then for each row fetched you would have to join with the table "TEST"

           

          Lets say this is your procedure

           

          create or replace procedure getdept

          (

            p_out_rc out sys_refcursor

          )

          as

          begin

            open p_out_rc for

            select deptno

              from dept;

          end;

          /

           

          Example for 2nd approach

           

          Create a SQL Nested Table.

           

          create or replace type deptno_tbl as table of number(10)

          /

           

          And you write a PL/SQL code like this. Code in red is the one to consider.

           

          SQL> declare

            2    l_deptno deptno_tbl;

            3    l_rc     sys_refcursor;

            4  begin

            5    getdept(l_rc);

            6    fetch l_rc bulk collect into l_deptno;

            7

            8    for i in (

            9               select ename

          10                 from emp e

          11                 join table(l_deptno) t

          12                   on e.deptno = t.column_value

          13             )

          14    loop

          15      dbms_output.put_line(i.ename);

          16    end loop;

          17  end;

          18  /

          KING

          CLARK

          ADAMS

          SCOTT

          JONES

          SMITH

          TURNER

          BLAKE

          MARTIN

          WARD

          ALLEN

           

          Example for 3rd approach.

           

          SQL> declare
            2    l_rc     sys_refcursor;
            3    l_deptno number(10);
            4  begin
            5    getdept(l_rc);
            6    loop
            7      fetch l_rc into l_deptno;
            8      exit when l_rc%notfound;
            9      for i in (
          10                 select ename
          11                   from emp e
          12                  where e.deptno = l_deptno
          13               )
          14      loop
          15        dbms_output.put_line(i.ename);
          16      end loop;
          17    end loop;
          18  end;
          19  /
          CLARK
          KING
          SMITH
          JONES
          SCOTT
          ADAMS
          ALLEN
          WARD
          MARTIN
          BLAKE
          TURNER

           

          Finally I would again like to highlight that first approach is by far the best approach. The second and third approach has there own performance downside.

          • 2. Re: Join of cursor with table
            Girish Sharma

            Below thread may be of your interest too :

            https://community.oracle.com/message/9602289

             

            Regards

            Girish Sharma

            • 3. Re: Join of cursor with table
              user13179060

              DECLARE
                 n          NUMBER;

                 TYPE deptno_type IS TABLE OF NUMBER (10);

                 v_deptno   deptno_type;
                 v_out      sys_refcursor;
                 n_name     VARCHAR2 (25);
              BEGIN
                 getdept (v_out);

                 FETCH v_out
                 BULK COLLECT INTO v_deptno;

                 FOR i IN v_deptno.FIRST .. v_deptno.LAST
                 LOOP
                    SELECT dname
                      INTO n_name
                      FROM dept
                     WHERE deptno = v_deptno (i);

                    DBMS_OUTPUT.put_line (n_name);
                 END LOOP;
              END;

              • 4. Re: Join of cursor with table
                BluShadow

                Subhash Pophale wrote:

                 


                Hi experts,

                 

                Oracle Version: 11GR2

                 

                I get dataset in the ref cursor as an out parameter from a procedure. Now, I have to derive column "NAME" from table "TEST" using ID as a join key between cursor and a table.

                How can we do this in oracle?

                 

                 

                Thanks

                 

                I suggest you read:  PL/SQL 101 : Understanding Ref Cursors

                • 5. Re: Join of cursor with table
                  Billy~Verreynne

                  Subhash Pophale wrote:

                   

                  I get dataset in the ref cursor as an out parameter from a procedure. Now, I have to derive column "NAME" from table "TEST" using ID as a join key between cursor and a table.

                  How can we do this in oracle?

                  A cursor is not a dataset.

                  A cursor is not a dataset.

                  A cursor is not a dataset.

                   

                  A cursor is a set of instructions (aka a program) that outputs data. The very basic concept one learns the 1st day in computer science. A program receives input. Processes. Produces output. And that is what a cursor does.

                   

                  Input is received by the cursor program via bind variable values. Processing is done when a fetch call is made. Output is returned. Processing and output continues until the program no longer finds data to output (e.g. NO_DATA_FOUND is raised).

                   

                  Now you are asking how to use SQL to join  a program (outputting data), with a database table? That is the equivalent of asking how one uses SQL to join Microsoft Excel (containing worksheet data), with a database table?

                   

                  Conceptually this does not make sense - wanting to join code (a program) with data via SQL.

                  • 6. Re: Join of cursor with table
                    Billy~Verreynne

                    Karthick_Arp wrote:

                     

                    Finally I would again like to highlight that first approach is by far the best approach. The second and third approach has there own performance downside.

                     

                    Only when the data is smallish inside - as very expensive server memory is needed for caching the cursor's output, with option 1.

                     

                    A "better" solution would be to change the cursor's output back to a SQL data stream via a pipeline table function - as it can deal with the cursor's output with less strain on server memory (PGA).

                     

                    I would however argue that this entire concept of wanting to join cursor output with SQL to other data, is in itself totally flawed. That is not how one designs robust, performant, and scalable solutions.

                    • 7. Re: Join of cursor with table
                      BluShadow

                      BillyVerreynne wrote:

                       

                      Now you are asking how to use SQL to join  a program (outputting data), with a database table? That is the equivalent of asking how one uses SQL to join Microsoft Excel (containing worksheet data), with a database table?

                       

                       

                       

                       

                      Using hetorogeneous services (or whatever Oracle calls them now... Gateway services is it?) to treat the Excel worksheet as a table.

                      Okay, perhaps I shouldn't have mentioned it. 

                      • 8. Re: Join of cursor with table
                        Billy~Verreynne

                        But then you are not joining to the excel.exe program - but changing the program's data into a "SQL stream". Which is the point I tried (poorly) to convey. Joining with a program (e.g. cursor) is nonsensical.

                         

                        To slap such a SQL stream onto a cursor only to join it, is what makes no sense to me. Why not "push" that join SQL into the SQL program code of that original cursor instead?