5 Replies Latest reply: Mar 15, 2013 3:55 PM by Gary Graham-Oracle RSS

    PLSQL Unit Testing using Oracle SQL Developer with REF CURSOR issue

    994735
      Here is a very simple procedure that i am using for testing Ref cursor with Oracle SQL Developer
      CREATE OR REPLACE PROCEDURE UNIT_TEST_REPOS.get_emp_rs (p_employee_id    IN  Employees.Employee_Id%type ,
                            p_recordset OUT SYS_REFCURSOR) AS 
      BEGIN 
        Open P_Recordset For
      Select name  From Employees  
        Where Employee_Id > p_employee_id; 
      END get_emp_rs;
      /
      I could not past images so i am putting exaclty what I see on screen.
      Test Implementaion 1
      h1. Parameter                        Datatype                  IN/OUT                    INPUT                                  Result                                      Test Result
      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      h2. P_employee_Id                NUMBER                    IN                           P_employee_id(500)                  blank here                                uncheck
      h2. P_RECORDSET                 REF CURSOR               OUT                      (blank here)                             P_RECORDSET$                           check
      ------
      under dynamic query i have put following
      
        select cursor(Select 'Andy'  name from dual  ) as P_RECORDSET       
           from dual
      --------
      I created a implementation and when try to execute I got following on debug mode. I am still not sure why it says P_RECORDSET : Expected: [null] while i have mentioned the value should be compared with Dynamic Value Query. 
      
      The following procedure was run.
      
      Execution Call
      BEGIN
       "UNIT_TEST_REPOS"."GET_EMP_RS"(P_EMPLOYEE_ID=>:1,
        P_RECORDSET=>:2);
      END;
      
      Bind variables used
      :1              NUMBER          IN     500                                     
      :2              REF CURSOR      OUT    (null)                                  
      
      Execution Results
      ERROR
      P_RECORDSET : Expected: [null], Received: [NAME                                                                                                                                                                                                     
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
      Andy                                                                                                                                                                                                   
      ]
      Can any one point what is wrong with OUT refcursor ? I also need helpon IN/OUT ref cursor?

      Thank you so much,
      Rajan

      Edited by: user7286810 on Mar 8, 2013 1:06 PM

      Edited by: user7286810 on Mar 8, 2013 1:07 PM

      Edited by: user7286810 on Mar 13, 2013 1:41 PM

      Edited by: user7286810 on Mar 13, 2013 1:50 PM