3 Replies Latest reply: Feb 10, 2013 11:08 PM by jeneesh RSS

    Data comparision: Cursor to table

    990273
      I am developing a test harness in pl/sql. we have a PL/SQL function which is used in a multi-layered application. We need to verify that the data being returned by the pl/sql function is matching to an expected table. the pl/sql function is returning a ref cursor.

      essentially what i need a function which can compare data of a cursor with a table.
        • 1. Re: Data comparision: Cursor to table
          sb92075
          http://www.oracle.com/pls/db112/search?remark=quick_search&word=dbms_comparison
          • 2. Re: Data comparision: Cursor to table
            rp0428
            Welcome to the forum!

            Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).
            >
            I am developing a test harness in pl/sql. we have a PL/SQL function which is used in a multi-layered application. We need to verify that the data being returned by the pl/sql function is matching to an expected table. the pl/sql function is returning a ref cursor.

            essentially what i need a function which can compare data of a cursor with a table.
            >
            A cursor doesn't have data.

            You need to FETCH the data using a cursor.

            Comparison methods depend on what you are comparing. Every column in the table including things like MODIFIED_DATE, MODIFIED_BY?

            You can create a PIPELINED function that iterates the refcursor and returns a result set. Then you can query the pipelined function as if it were a table and do DIFF queries against your other table
            SELECT * FROM myPipelinedFunction
            MINUS
            SELECT * FROM myOtherTable
            If everything matches you will have an empty result set.

            In short you need to refine your criteria for how to do the match and also specify how you want to capture the differences so you can do something about them.