1 Reply Latest reply on Oct 13, 2014 4:10 PM by val.2046

    Unit Tests and Ref Cursors

    Subboss
    Subboss Oct 9, 2014 2:50 PM

      I have encountered an issue with Oracle SQL Developer (4.0.2.15) that I need help with writing unit tests for my stored procedures. An example of the procedure is as follows:

       

      PROCEDURE p_example (i_someinput  NUMBER, o_cursor REF CURSOR)
      AS
      BEGIN

      OPEN o_cursor FOR
      SELECT 1 seqno, t type , example description FROM example_table;
      END;

       

      In my unit test I set up the row in the database, I have my rollback and I use the dynamic query to define the input and what I expect to come out of the ref cursor.

       

      Example of Dynamic query:

      SELECT inputval i_someinput, CURSOR(SELECT 1 seqno, t type, example description  FROM dual) o_cursor$ FROM dual;

       

      My issue is that when running the test it fails and I then have to do comparisons between the expected result and the actual result.

      When comparing I realise I have to pad some of the columns so that they will line up correctly and once this is done, the test passes successfully.

      It would appear that the actual result and the expected result are being compared as a string, rather than comparing the contents of the cursors

      I was just wondering if anybody had a resolution for this or is it a known issue with SQL developer.

       

      Thanks

       

      Subboss

      • 641 Views
      • Tags:
        • 1. Re: Unit Tests and Ref Cursors
          val.2046
          val.2046 Oct 13, 2014 4:10 PM (in response to Subboss)

          Try to match the query of the Dynamic Value Query's cursor to the query of the cursor in the stored procedure... i.e. there is no "example" column in the "dual" table... why not to use "example_table where rownum=1" instead of "dual", for example?

           

          Hope this helps...

           

          BTW: Comparing the expected and received record sets as up to 32K strings works just fine, unless one of the expressions in the dynamic value query is larger than 30 characters, in which case the "Expected:[...]" header line would have the truncated 30 character expression header and the "Received:[...]" header line would have it at a full length... which would cause a false "failure" of the test... a workaround is to use a shorter than 30 character alias for the "offending" long expression...

           

          Personally I see it as a bug and a showstopper in SQL Developer, because if your stored procedure is already deployed in Production environment and it doesn't have a short alias in its query you cannot use this workaround... I will try to file a bug for this on Metalink after creating a test case for them using HR schema or something...

           

          Val