This discussion is archived
5 Replies Latest reply: Mar 15, 2013 1:55 PM by Gary Graham RSS

PLSQL Unit Testing using Oracle SQL Developer with REF CURSOR issue

994735 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points