12 Replies Latest reply: Sep 30, 2009 6:01 AM by Boneist RSS

    Cursor as output parameter

    716323
      HI

      I have a prcedure like this

      create or replace PROCEDURE SP_EMPLOYEE_LIST AS

      CURSOR C1 IS SELECT PK_EMP_ID FROM EMPLOYEE_TABLE;

      CURSOR C2(emp_id NUMBER) IS SELECT EMP_SAL,EMP_GRADE FROM EMPLOYEE_SALARY
      WHERE FK_EMP_ID =emp_id;

      CURSOR C3(emp_grade varchar2) IS SELECT EMP_INCREMENT FROM EMP_UPGRADE
      WHERE FK_EMP_GRADE = emp_grade;
      BEGIN
      FOR c1_rec in C1
      LOOP
      FOR c2_rec in C2(c1_rec.emp_id)
      LOOP
      FOR c3_rec in C3(c2_rec.emp_grade)
      DBMS_OUTPUT.PUT_LINE('Emplyoee salary is :'||c2_rec.EMP_SAL||'Increment is:'||c3_rec.EMP_INCREMENT);
      END LOOP;
      END LOOP;
      END LOOP;
      END;


      which means I need to return values from second cursor and third cursor.
      my output should be a cursor which will have all the data from cursor 2 and cursor 3
      how should I write procedure to give output in a cursor.
      NOTE: The output cursor is going to refered in java to write a csv file

      please help,

      Regards
      Yazhini
        • 1. Re: Cursor as output parameter
          Marwim
          Hello Yazhini,

          1. You can call a function returning one ref_cursor from JAVA.
          2. Why do you nest 3 cursors when you can do it in one select, or is there some nontrivial data manipulation inbetween?
          3. Wouldn't it be easier to write the csv from PL/SQL

          Regards
          Marcus
          • 2. Re: Cursor as output parameter
            716323
            Hi marcus,

            We are not given previlage to write files in any directory , this is from customers ponit of view.
            customer could not get previlages for using UTIL_FILE so to write csv file i could not rely on it.

            I need to give all the output as a cursor and java is going to write the file.

            for every employee i need to pick the salary and grade , and depending on the grade i need to pick the upgrade details.

            the actual requirement is even more comlpax I cannot make it as a single cursor, I need to be in a loop.

            plz help.
            • 3. Re: Cursor as output parameter
              Marwim
              Hello Yazhini,

              if you need to return complex calculated values look at pipelined functions. At AskTom you can find a good example. Your JAVA application can then make a simple SELECT like
              select empno, ename
              from TABLE(emp_etl(cursor( select * from external_table ) ) )
              where ename like '%a%';
              (Example from the linked page)

              Regards
              Marcus
              • 4. Re: Cursor as output parameter
                716323
                Marcus,

                Java will be taken care later, as of now i need to return a cursor which will have values from various other cursors.

                i need it as a procedure, so cursor need to be OUT parameter
                and this cursor will be called in java.

                Regards
                Yazhini.T
                • 5. Re: Cursor as output parameter
                  Boneist
                  user8707806 wrote:
                  the actual requirement is even more comlpax I cannot make it as a single cursor, I need to be in a loop.
                  Hmm.... if all you're doing is 3 selects, nothing else, then there should be a way of combining that into one sql statement.

                  That way, your code becomes easy to write as all you'd need is "open ref_cursor for <one_big_sql_statement>" and then you could pass the ref_cursor back to the front end. Easy to follow, easy to maintain and easy to debug.

                  Can you provide more details on what you're trying to do? We can try and help combine things into one sql statement....
                  • 6. Re: Cursor as output parameter
                    716323
                    HI Boneist

                    1) There can be more than 1 country code so fetchin country code is my first cursor
                    2) for each country code I can have different languages my seocnd cursor will do this giving counrt code as in parameter
                    3) For every langugae there can be number of people this is my third cursor and in paramater will be language

                    in this case I believe 1 query cannot help, we would get to see " many rows returned" sql error.

                    For every country code different file should be written , so obviously i need to keep it as different cursor.

                    my return cursor should have all the values .

                    How should I go about it?? plz assist

                    Regards
                    Yazhini
                    • 7. Re: Cursor as output parameter
                      Marwim
                      Hello Yazhini,
                      1) There can be more than 1 country code so fetchin country code is my first cursor
                      It can be the first part of your SELECT
                      2) for each country code I can have different languages my seocnd cursor will do this giving counrt code as in parameter
                      so you join it with the first select
                      3) For every langugae there can be number of people this is my third cursor and in paramater will be language
                      and this part you can join with the second part
                      in this case I believe 1 query cannot help, we would get to see " many rows returned" sql error.
                      You get no TOO_MANY_ROWS because you have a cursor
                      For every country code different file should be written , so obviously i need to keep it as different cursor.
                      Or you order the result by country code and while writing the file you check if it changes. If yes, then close the file and open a new one.

                      Your Select may look similar to
                      SELECT country_code
                            ,language
                            ,people_attribute
                      FROM countries
                      JOIN languages on countries.id = languages.country_id
                      JOIN peoples on languages.id = people.languages_id
                      ORDER BY country_code
                      Regards
                      Marcus
                      • 8. Re: Cursor as output parameter
                        Boneist
                        user8707806 wrote:
                        HI Boneist

                        1) There can be more than 1 country code so fetchin country code is my first cursor
                        2) for each country code I can have different languages my seocnd cursor will do this giving counrt code as in parameter
                        3) For every langugae there can be number of people this is my third cursor and in paramater will be language

                        in this case I believe 1 query cannot help, we would get to see " many rows returned" sql error.
                        No, you wouldn't because you're not going to be processing the statement in your procedure, you're just wanting to create and open it, before passing it on to the front end, which will then loop through the cursor to extract the results and close it.
                        For every country code different file should be written , so obviously i need to keep it as different cursor.

                        my return cursor should have all the values .

                        How should I go about it?? plz assist
                        Something like:
                        CREATE OR REPLACE PROCEDURE SP_EMPLOYEE_LIST (p_ref_cur OUT SYS_REFCURSOR)
                        AS
                        BEGIN
                          OPEN p_ref_cur FOR
                            SELECT emp_sal.emp_sal, emp_sal.emp_increment
                            FROM   employee_table emp,
                                   employee_salary emp_sal,
                                   emp_upgrade emp_ug
                            WHERE  emp.emp_id = emp_sal.emp_id
                            AND    emp_sal.emp_grade = emp_grade;
                        END;
                        /
                        Difficult to get an "exact" representation of the combined SQL statement, as the columns referenced in your cursor declarations don't match what you're referencing in the body of the text (eg. "c1_rec.emp_id" when c1 is defined as "SELECT pk_emp_id FROM ...").

                        With your loops, you're trying to manually replicate joins. Why reinvent the wheel, when SQL already handles that for you!

                        As you can see, the procedure I've provided you is a) simpler, b) easier to understand, c) going to be quicker to run (less context switching for a start!) and d) easier to debug - you can now just run the SQL statement yourself to see what values are returned, rather than having to loop through your code and work it out like that....

                        If you find yourself looping through several cursors like you were doing, stop and think: "Am I reinventing joins here?!".

                        ETA: You might find this page of use for how to deal with the java side: http://www.oradev.com/ref_cursor.jsp

                        Edited by: Boneist on 30-Sep-2009 11:11
                        • 9. Re: Cursor as output parameter
                          716323
                          Thanks marcus and boneist,
                          so is there no way to bring out a cursor with outputs of multiple cursor?

                          Marcus,
                          i can order my records, but how do i give it out for java ?
                          if i have 100 records how will I know my first 50 is one counrty code and next 50 is another country code?

                          before writing a file , we would have to work on it and split the records right?

                          Regards
                          Yazhini
                          • 10. Re: Cursor as output parameter
                            Boneist
                            user8707806 wrote:
                            so is there no way to bring out a cursor with outputs of multiple cursor?
                            More to the point, why would you want to?
                            i can order my records, but how do i give it out for java ?
                            if i have 100 records how will I know my first 50 is one counrty code and next 50 is another country code?
                            In your nice new shiny combined SQL statement, add an order by clause with the relevant columns. That will then order the results, so the Java front end just needs to check for when the id changes to do whatever processing it needs to.
                            • 11. Re: Cursor as output parameter
                              716323
                              now i have compiled my procedure
                              i need to execute it with a anonymous block.
                              can you help me with it??
                              • 12. Re: Cursor as output parameter
                                Boneist
                                The link I posted before (http://www.oradev.com/ref_cursor.jsp) has an example procedure for running in PL/SQL.

                                You'll have to amend it for your purposes, obviously, but it should give you an idea how to test your procedure in pl/sql